Frequently Asked Questions
What does the product do?
Here's the situation: You're not in a performance firefighting situation and you want to keep it that way. As you know, there are literally hundreds if not thousands of statistics you could monitor. What you need to know is what in your particular Oracle system is stressing your CPU, IO, and network subsystem. Even better would be able to know this for specific periods of time, like during Sunday evening batch processing or weekday afternoons. The Stress Identifier will tell you exactly how Oracle is stressing your database server's CPU, IO, and network subsystem for a given window of time. With this information, you know what's important and what to monitor, so when an anomaly is detected (an unexpected jump or trend breach) you can take specific action...before the problems gets out of hand, users call you, and the wait interface shows the problem is severe. So the Stress Identifier allows you to be proactive and efficient and prevent unexpected performance problems. There are other FAQs that delve more into the details.
Can I use the product for free?
Yes you can! Simply
download the latest version of the product,
install it, and you're ready to roll. However, without a full operational license, the product will operate in
try-and-buy mode.
What can I use the results for?
While the analysis results tell us what specific Oracle internal activity places a stress on a particular operating system subsystem, to plainly state, what can this information be used for?
The analysis results can be used in a number of different ways. Most DBAs use the results to identify exactly which Oracle internal statistics should be monitored. For advanced Oracle performance analysis the Stress Identifier can be used to discover the best workload metric, that is, the unit of work that highly relates to how Oracle is stressing the database server.
Experienced DBAs know there are litterly hundreds of statistics to monitor. While Oracle's wait interface and even Oracle response time analysis provide a fantastic diagnostic foundation to fight existing performance fires, determining which specific Oracle internal activity that is likely to be at the root of a future performance firefight is not so easy. Most DBAs can only monitor a handful of statistics before they and their performance management systems become overwhelmed with data. The Stress Identifier cuts through the noise and numerically quantifies which specific statistics are worth the effort to monitor. That is very useful indeed!
For advanced Oracle performance analysis, it is very important to identify a unit of work that highly relates with how the system is being stressed and limiting performance. For example, if performance is being limited by a lack of CPU resources a typical unit of work is logical IO (buffer gets). With lots of experience this can usually be inferred, but if you really want to know and you don't have that experience base yet, the Stress Identifier will identify the absolute best unit of work(s). Now instead of inferring the best workload metric, you will scientifically and quantifiable know. That is very useful indeed!
These are just the two of the more obvious areas of practically using the Stress Identifier in your daily work. The bottom line is the Stress Identifier allows you to focus on what counts preventing performance fires so operations become smoother!
What type of stresses are analyzed?
In summary, the stress Oracle internal activity places on the CPU, IO, and network subsystems is analyzed. But it goes much deeper than this. Within the IO subsystem the product determines what Oracle internal activity is causing the Oracle server process read activity which stresses the IO subsystem, and what Oracle internal activity is causing the DBWR and the LGWR to write to the IO subsystem. For network activity stresses, the product determines what Oracle internal activity stresses the network subsystem due to non-distributed (client) and distributed (dblink) SQL*Net activity. Each of these stress types is a parameter (IN_STRESS_TYPE) when running an analysis. And don't forget, all this occurs within a potentially very granular historical time segment. Here is the currently supported stresser list:
IN_STRESS_TYPE - The stress type:
| cpu | What Oracle internal activity is stressing the CPU subsystem. |
| ior | What Oracle internal activity is stressing the IO subsytem's read capabilities. |
| iow-dbwr | What Oracle internal activity is causing the database writer to stress the IO subsystem's write capabilities. |
| iow-lgwr | What Oracle internal activity is causing the log writer to stress the IO subsystem's write capabilities. |
| network-client | What Oracle internal activity is stressing the network subsystem based only on network activity between Oracle server and client processes. |
| network-dblink | What Oracle internal activity is stressing the network subsystem based only on network activity between Oracle server and client processes via dblinks. |
| memory | What Oracle internal activity is causing Oracle server processes to consume database server memory. This option is in beta testing, so please let us know about your results! |
How do I actually run the product?
There is a short video about basic product usage available on
YouTube.
The Stress Identifier allows you to zero in on a particular block of time. First decide what type of stress you are interested in analyzing (e.g., CPU). Then determine the date range (e.g., 2008-Jan-01 to 2008-Mar-31), the day range (e.g., Monday through Friday), and finally the time of day range (e.g., 0600 to 1759). To help identify your analysis results, determine a short unique identifier, such as wkday. You will also need the Statspack database ID and instance number related to your actual Statspack data (the opsi_helper.sql script can be helpful). This is the same database ID and instance number used to run a Statspack report.
For example, let's say you wanted to understand what is stressing your CPU subsystem during normal day-time weekday operations during the first quarter of 2008. Here is what the inputs may look like:
| run id | wkday | Make up some analysis identifier.
|
| stress type | cpu |
|
| database id | 1055146963 |
|
| instance number | 1 |
|
| start date | 2008-Jan-01 |
|
| end date | 2008-Mar-31 |
|
| day start | 2 | Monday=2, Tuesday=3, etc. |
| day end | 6 | Friday=6 |
| start time | 0600 | The Statspack sample start time must be greater than or equal to this time.
|
| end time | 1759 | The Statspack sample start time must be less than or equal to this time.
|
Here is the actual procedure call using the above data.
SET TIMING ON
SET SERVEROUTPUT ON
exec opsi.id_stress('wkday','cpu',1055146963,1,'2008-Jan-01','2008-Mar-31','2','6','0600','1759');
When the analysis has completed, the analysis output is inserted into the opsi_result table. You can select directly from the table and also use the sample_report[1,2,3].sql as examples.
When the time of interest crosses midnight, the analysis parameters can get tricky;
more...
Once you get used to this, you will quickly understand that you can run a similar analysis, perhaps to better understand the CPU stresser during the week day evenings or during weekend batch jobs. The week day nights procedure call would then look like this:
SET SERVEROUTPUT ON
exec opsi.id_stress('wknite','cpu',1055146963,1,'2008-Jan-01','2008-Mar-31','2','6','1700','2359');
By running a variety of analyzes, you will become very familiar with specifically how the Oracle system is stressing your database server based upon your specific organization's work schedule.
A few reports have been included with the product;
sample_repor[1,2,3].sql. Run the first to get an idea of the analysis results. The sample reports can be modified and enhanced to create your own customized analysis reports.
If you want a good idea of how long the analysis will take you can run the
time estimate procedure. This is a good idea if you have months and months of data to analyze.
How do I select the data range to be analyzed?
The general rule is keep the data analysis range wide open and unconstrained unless you have a good reason to constrain it. The data to be analyzed must pass through
all the date, day, and time filters. Wide open constraints are; for the date that would be 1900-Jan-01 to 2050-Jan-01, for the day that would be Sunday (1) through Saturday (7), and for the time that would be 0000 to 2359. It is very rare to
not constrain the date range. While this is very straightforward, when your time of interest crosses midnight, two analysis runs must be submitted (more below).
Here is simple example:
SET SERVEROUTPUT ON
exec opsi.id_stress('wkaftr','cpu',1055146963,1,'2009-Jan-01','2009-Mar-31','2','6','1200','1700');
where the objective is to know what Oracle internal activity is stressing the CPU subsystem on weekday afternoons during the first quarter of 2009. The resulting parameters area; a run identifier of wkaftr (weekday afternoons), the CPU subsystem stresser is being analyzed (cpu), the Statspack database ID is 1055146963, the Statspack instance number is 1, the days of interest are Monday (2) through Friday (6), and the time of interest is between 1200 to 1700.
Here is a more complex example. Suppose you wanted to know the IO read stresser for Friday night batch processing over the past year. This will require two separate analysis calls because the analysis crosses midnight.
SET SERVEROUTPUT ON
exec opsi.id_stress('frinite1','ior',1055146963,1,'2007-Feb-01','2009-Feb-01','5','5','1800','2359');
exec opsi.id_stress('frinite2','ior',1055146963,1,'2007-Feb-01','2009-Feb-01','6','6','0000','0559');
Keep in mind that unless there are 30 or more samples analyzed, the analysis value is
diminished. One of the values of the Stress Identifier is it can
quickly analyze a tremendous amount of data within a highly defined
time constraint.
The analysis output is inserted into the opsi_result table. You can select
directly from the table and also use the
sample_report[1,2,3].sql as
examples.
How do I view the analysis results?
The product comes with three sample reports, which can serve as a template for you to create your own customized reports.
All analysis results are stored in the opsi_result table. The table contents can be manipulated as you wish. That is, you can delete rows, truncate, or update. Just do not change the table structure!
If the sample report require an input parameter(s), you will be prompted.
Here is a quick comment on each of the sample reports:
sample_report1.sql - This report shows the key analysis output for all analysis data. This is the report to get you started, but once you begin running lots of analysis, you will find the volume of output overwhelming and will want to try the 2nd or 3rd sample report.
sample_report2.sql - This report shows the key analysis output for the prompted database ID and instance number. If you performing analysis on multiple instances, this report can help organize your analysis output.
sample_report3.sql - This report shows the key analysis output for the prompted run identifier. Each analysis requires a run identifier. This is typically the best way to easily identify a single or group of analysis runs. For example, support you want to analyze the stressors for weekday nights and as a result you consistently used a run identifier of wknite. This report will allow you to show only these specific analysis results.
It's a great way to zero in on just the results you are interested in.
Once you get going analyzing the results, you will undoubtedly want to create your own specialized reports. We would like to see what you come up with! So please feel free to email us with your ideas!
How does the product work?
The Stress Identifer makes a very complex analysis process seem sometimes very simple. The first phase is selecting the data you want to analyzed. For example, perhaps you want to only analyze data for 2008 Q3, during the weekends and between the hours of 9am and 3pm. Based on this time segment criteria and also the stress area of interest (for example, IO reads), the analysis engine automatically pulls all the required data from your Statspack repository and then analyzes all the different types Oracle internal activity related to the selected stress (for example, IO reads). The analysis process is looking for Oracle internal activity that highly correlates with the selected stress and then saves the best in an Oracle table (opsi_result) so you can easily report on the results. The entire analysis process has been highly optimized and typically occurs within one to fifteen minutes.
How long does the analysis take?
As you can image, there is a tremendous variation in how long the analysis takes. Depending on your Statspack repository size, repository server speed, the repository Oracle configuration, and the amount of data to be analyzed, it can take between 1 to 20 minutes to for the analysis to complete. We agree, that is quite a range! So to help set your expectations and give you a very good idea of just how long the analysis will take, we created an estimation function called, OPSI.EST_RUN_TIME_S which tells you just how many seconds the analysis will take. (To determine the analysis minutes, just divide by sixty.) The input parameters are exactly the same as the analysis procedure so you can be sure you entered the parameters correctly! Here is an example:
SQL> SELECT OPSI.EST_RUN_TIME_S('ABCD','cpu',1055146963,1,'2008-Jan-01','2008-Mar-31','0','7','0000','2359') FROM DUAL;
The estimation function usually runs for about 10 seconds.
What is try-and-buy mode?
If you simply download the product and install it, or if there is a licensing problem, the product will run in what we call, try-and-buy mode.
Try-and-buy mode allows the product to operate without a license, the product will technically work and function properly, but the amount of data analyzed is so drastically limited the results are not meaningful. However, the product can be operationally tested, you will gain familiarity with the product, and you can perform integration tests with any larger performance management system.
To operate in full operational license mode, you will need to
license the product.
How do I install the product?
The installation process is extremely simple. Follow these few quick steps. You can also watch a quick video demonstration on
YouTube.
STEP 1. DOWNLOAD PRODUCT DISTRIBUTION FILE
Go to:
http://filebank.orapub.com/SI_DIST
and download the most recent product version for your platform (Windows or U/Linux).
STEP 2. UN-ARCHIVE THE PRODUCT DISTRIBUTION FILE
Place the downloaded product distribution file in a directory on either a client machine or a database server. The key is to be able to access the Statspack repository of interest directly or via SQL*Net.
Next, un-archive the product distribution file. On U/Linux do the standard "tar xvf" and on Windows simply unzip the file. An opsi directory will be created and all the product distribution files will be placed within the opsi directory.
STEP 3. CREATE OBJECTS
Navigate into the product directory, opsi. Run SQL*Plus and connect to an Oracle user that has access to the Statspack data of interest and can create objects (table, indexes, truncate tables, select and delete rows, execute procedures and functions). Note: The simplest way to do this is to connect as the Statspack data owner, which is typically PERFSTAT.
- Connect to the Statspack data of interest owner.
- Run the SELECT below statement to ensure there is Statspack data available.
- One at a time, run the two SQL scripts below to create the product objects. Do NOT simply copy and paste or the second script may not be run. So please, make sure to run both scripts! If the product is being installed for the first time, you can expect the typical "object does not exist" errors to occur.
CONNECT PERFSTAT/PERFSTAT
SELECT COUNT(*) FROM STATS$DATABASE_INSTANCE;
START opsi_050.sql
START opsi_100.plb
STEP 4. TEST
This test will quickly check the key objects exist. Simply copy and
past the following SQL into SQL*Plus. There should be no errors.
SET ECHO ON
SET FEEDBACK ON
DESC OPSI_INTERIM
DESC OPSI_RESULT
DESC OPSI_PARAMS
DESC OPSI
If OPSI does not exist (the final test), the
opsi_100.plb script was probably not run. So run it and then redo the test steps.
If no errors result, then the product is properly installed and will work properly in
try-and-buy mode. For full analysis power the product will need to be licensed. For instructions, click
here.
How do I license the product?
You can watch a quick video demonstration of licensing the product on
YouTube.
The product will operate without an license. However, until the product is fully licensed it operates in
try-and-buy mode. In fact, if the product is not properly licensed, it automatically defaults to try-and-buy mode.
For RAC systems: Since the product is licensed per Oracle instance, for example, if you want to license the product for both nodes in a two node RAC system, two licenses will need to be purchased and the standard licensing process followed. The product can not be installed on node 1, uninstalled on node 1, and then installed on node 2. Each license is associated with a specific database and instance combination.
Upon product license purchase completion you will receive an email from orapub@comcast.net which directs you through the simple licensing process resulting in a standard SQL INSERT statement. To complete the license process, complete these steps:
1. As the Statspack Oracle user, run license INSERT statement.
2. Now
check the license status.
That's all there is to licensing the product.
Where is my license key email?
Upon purchasing a license (by adding this product to your shopping cart and going through the standard checkout process) you will near-immediately receive an email containing the licensing details from orapub.com. You must ensure you can receive emails from orapub.com. That is, your email filtering system is not blocking emails from orapub.com. It's also a good idea to check your junk email folder...and then enable all email from orapub.com.
How do I check my license status?
If at any time you want to check if the product is properly licensed, run this SQL statement:
SET SERVEROUTPUT ON
EXEC OPSI.QUICK_LICENSE_CHECK;
This procedure will check each license entry. If
the product is not fully licensed it will run in
try-and-buy mode. If you ran the license
insert SQL statement and the license check result is not what you expect, then please write down the complete result,
run the included opsi/opsi_helper.sql script, and contact OraPub support.
Can AWR data be used?
No, it is not necessary. The Stress Identifier pulls all analysis data directly from the Statspack repository. Be aware that just because your organization has a license to use AWR data, this does not mean Statspack data is not being collected. You may be surprised to find the data is automatically being collected. The default Statspack Oracle user is PERFSTAT. Check if the PERFSTAT user exists and if there is any data in its tables. If so, you're all set!
You may be wondering why we don't pull from AWR data. It is because AWR reporting requires an additional Oracle license (just ask your Oracle salesperson) and all the required analysis data is available in the Statspack data.
How much space does the product use?
Not much. Only the analysis results are stored in the opsi_result table, which consumes kilo-bytes of data, not even a few mega-bytes. Remember, the Stress Identifier pulls data directly from the Statspack repository, analyzes the data, and stores only the analysis results.
What do the analysis results mean?
The key to interpreting the analysis results is a statistic's stresser strength. The stresser strength has a range from 0 to 99 and numerically represents how closely related the Oracle internal activity statistic is related to the stresser (e.g., CPU subsystem). A high value means the the statistic is important to monitor because as it increases so will the operating system subsystem activity. And just as important, the core Oracle internal activity which highly stresses the subsystem has been identified. Now any change (SQL tuning, instance tuning, OS changes, application workload changes, etc.) that affect the statistic will be under increased scrutiny.
A stresser strength above 90 is amazing, 80 to 89 is wonderful, 70 to 79 is OK, but anything below 70 is not that useful. Usually there is at least one Oracle internal statistic that is in the 90s.
What if I discover multiple good stressers?
It is very common for the top one to five statistics results to be above 90. You now have luxury to pick the one or two that most naturally relates to your application workload or any planning changes to your operating system, Oracle release, application change, etc. For example, one of the statistics may be particularly helpful in identifying and monitoring SQL that is causing or will likely cause problems. Or perhaps the statistic relates to an Oracle instance parameter that may need to be adjusted now or perhaps in the future. But the key is, you now have useful information that you can act upon without be distracted with all the noise!
How much Statspack data is needed?
For any meaningful analysis to occur at least 30 Statspack snapshots are needed. If Statspack is normally collecting data (usually once each hour), then unless your analysis was tightly constrained, you will have plenty of data. Most DBAs are performing the Stress Identifier analysis using literally hundreds or thousands of samples. The number of samples that underwent the analysis is also stored in the analysis results table, opsi_result and shown on the sample_report1.sql report.
Does the product access any non-Statspack data?
No it does not. While the product itself creates, owns, and uses a couple of small tables, it never ever touches production (non-Statspack) data AND never queries information from your production systems. Specifically, your production system's data dictionary or business objects are not accessed.
Must the product be installed on production?
Absolutely not. The key is the Stress Identifier Oracle user account must be able to read the Statspack data of interest and be able to create some small interim processing objects and analysis result storage. (This is why it is easiest to install the product as the Statpack Oracle user, typically PERFSTAT.) For example, the software could be stored on your desktop and you can access the Statspack repository via SQL*Net. In fact, the Statspack repository may be a copy from your production system residing on non-production Oracle database.
Can a text file containing Statspack data be used?
Not unless you feel very creative and have some extra time on your hands. The product pulls all data exclusively from a Statspack repository stored in an Oracle database. While the Oracle database does not need to be a production system, it does need to contain the Statspack data you are interested in analyzing.
The other reason text files are not supported is typically thousands and thousands of rows are analyzed from a variety of Statspack tables. The ETL-like process would become a project in itself. Look at it like this: If you can perform an ETL-like process on your Statspack data, there is a very good chance you can simply export the Statspack user's data and import it into another non-production Oracle database.
Can the product use a copy of the Statspack data?
Absolutely! While you may not be able to run a Statspack report using this strategy, it works wonderfully for the Stress Identifier. Just export the production Statspack user's data (typically PERFSTAT) and import the data in another non-production system Just remember when you install the Stress Identifier to point to the non-production Statspack data!
Can analysis results be deleted from the opsi_result table?
Yes. When a stress analysis is completed rows in the opsi_result table of the same run_id and stress type are deleted and then the new analysis result rows (always 10 rows) are inserted. The opsi_result data is never referenced during the actual analysis, so you may remove or even update the rows as you wish.
What Oracle versions are supported?
Oracle version 10g and above are supported. Oracle versions 6, 7, 8, and 9 are not supported.
