Frequently Asked Questions

Everything from installation to advanced use.

Quick video tutorials

Here are some helpful short video tutorials:

Renewing Your StoriFree License

Renewing Your StoriFree License

You can get as many StoriFree licenses as you wish. StoriFree licenses last for around three days.

To get a new StoriFree license, do the following:

  • Reorder the StoriFree License here
  • Go to your existing Stori software distribution directory
  • Remove the storiLicense.key file (you can actually remove the two *.key files)
  • Restart Stori
  • Stori will automatically prompt you for the licence details
If you have questions, contact us.

Of course the three day limitation does not occur with a full Stori license.

System requirements

Stori has a few basic requirements:

  • Oracle Database Standard Edition 10.2 or higher
  • Oracle Database Enterprise Edition 10.2 or higher, limited analysis capabilities
  • Single node systems (Non-RAC) and no parallel query support
  • Client/Desktop: OEL Linux operating system for Stori client executable. The Linux gd package must be installed.
  • Database Server: Any operating system for the Stori PLSQL package
  • SSH access to the client machine
  • Initial access to a DBA account for installation (example: system)
  • Authority to have a new Oracle Database user created (example: stori)
  • Connection and read access to the StatsPack Oracle Database user (example: perfstat)
  • StatsPack data being collected at least once each hour and at level five or above
There are no other OS or Oracle accounts involved.

Installing Stori

It's really easy to get Stori talking!

If you have any configuation or installation questions, please contact OraPub Support.

You can watch the full installation process here.

This is important: The standard Stori installation expects everything to reside on the Linux database server. Everything means the Stori software, the production Oracle Database and the Statspack data. If you are installing in a differnt configuration, contact OraPub Support and we'll try and help you.

If you are testing Stori, you will want Stori to analyze a real production workload. Experienced DBAs know not to install a new product into production. But think of it like this: Would you hire a consultant to analyze a "zero load" environment? If you want to Stori really shine, give her some real data to analyze.

You will need 4 key things:

  • Easy Connect String (EZCONNECT) – All Stori database connections use the same EZCONNECT. You will be prompted for this during the initial installation. The simplest syntax we have found combines the database host name and service name. The service name is usally the database SID and in a non-RAC enviornment is usually the database name.

    The ORAFAQ website has an excellent FAQ about EZCONNECT.

    Here are some examples along with the actual connect string.

    Easy Connect (EZCONNECT) Examples for Stori
    Host Name Oracle Listener Port Database Name EZCONNECT String
    host.acme.com default: 5121 prod25 //host.acme.com/prod25
    192.168.1.25 1533 QA65 //192.168.1.25:1533/QA65
    192.168.1.25 default: 5121 QA65 //192.168.1.25/QA65
    192.168.1.25 default: 5121 QA65 //192.168.1.25:1521/QA65
    vbox01 default: 5121 superfun //vbox01/superfun
    vbox01 5122 superfun //vbox01:1522/superfun
    vbox01 default: 5121 prod50 //vbox01/prod50
  • Oracle DBA Account – Only during the initial installation is a DBA account needed. This is to allow the creation of the Stori Oracle user and a few grants. Details can be found near the top of the StoriCU.sql script. After the product is first installed, DBA access is not needed.

  • Oracle StatsPack Account (typically perfstat) – The StatsPack user must grant select access on some of its tables to Stori. This only occurs once during "installation" and details can be found in the StoriCU.sql script.

  • Oracle Stori Account – You can use whatever name you wish, but the documentation uses the name Stori. The Stori Oracle user is created near the top of the StoriCU.sql script. When the product is used it will connect to Oracle using the Stori username.

Download the software

Download the Stori software for your environment here.

Where to place the software

Place the Stori software distribution file on your production database server which, in a standard installation, will also contain your StatsPack data Stori will reference. An example location is, $ORACLE_BASE/local.

Unarchive the software

Type "tar xvzf stori2g.tgz". This will create subdirectories and files like this:

$ tar xvzf stori2k.tgz
storiDist/2k/
...
storiDist/2k/instantclient_12_1/sqlplus
...
storiDist/2k/Aller_Lt.ttf
storiDist/2k/arial.ttf
storiDist/2k/results/
...
storiDist/2k/storiLin64
storiDist/2k/storiParam0.sql
...
storiDist/2k/work/
$

Setup your environment

  • Linux Graphics Package Installed – Ninety percent of install problems are because the Linux graphics page is not installed. If it is not installed and you are not sure what to do, contact your Linux Administrator to have it installed.

    Here is example of checking if the gd graphics package is installed, which it is.

  • $ rpm -q gd
    gd-2.0.35-11.el6.x86_64
  • Environment Variables – Before Stori is run, the PATH and LD_LIBRARY_PATH environment variables must be properly set. Add the below lines to your profile (probably the .bash_profile file). Notice this example is for Stori release 2k. Make sure you adjust the version as appropriate.

  • # For OraPub Stori Product
    export STORI_REL=2k
    export STORI_HOME=$HOME/storiDist/$STORI_REL
    export PATH=./:$STORI_HOME:$STORI_HOME/instantclient_12_1:$PATH
    export LD_LIBRARY_PATH=./:$STORI_HOME/instantclient_12_1:$LD_LIBRARY_PATH
    

    Now log out of the operating system account and log back in. If your environment is properly set the command which sqlplus will display the full path to SQL*Plus. Do the same for the Stori executable, storiLin64. Here is an example:

    $ exit
    logout
    Connection to 192.168.1.150 closed.
    $ ssh stori@192.168.1.150
    stori@192.168.1.150's password: 
    Last login: Thu Jan 15 08:00:56 2015 from 10.0.1.41
    $ which sqlplus
    ~/storiDist/2k1/instantclient_12_1/sqlplus
    $ which storiLin64
    ~/storiDist/2k1/storiLin64
    $

Connect To The Database Using SQL*Plus

Use SQL*Plus to ensure you can connect to the database containing the Statspack data Stori will reference.

A good QA check is to ensure you can execute SQL*Plus connecting to the database as the Oracle Statspack user using your Easy Connect String. For example, if your EZCONNECT string is //main.acme.com/superfun and the Statspack username and password are perfstat then type:

$ sqlplus perfstat/perfstat@//main.acme.com/superfun

Here is a complete example using the same accounts.

$ exit
logout
Connection to 192.168.1.150 closed.

$ ssh stori@192.168.1.150
stori@192.168.1.150's password: 
Last login: Thu Jan 15 08:00:56 2015 from 10.0.1.41
$ sqlplus perfstat/perfstat@//main.acme.com/superfun

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 29 08:46:29 2013
...
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
...
$ 

First Execution

  1. Login into your Linux account, change your directory to the Stori software distribution directory and run Stori. Stori always looks for the license key file its software distribution directory. So be sure to always change your directory to the Stori software distribution directory before you start Stori. If you do not do this, you will unexpectedly prompted for the license details.

    Here is an example first session:

    $ ssh stori@192.168.1.150
    stori@192.168.1.150's password: 
    Last login: Tue Oct 29 08:46:12 2013 from 192.168.1.3
    $ cd storiDist/2k
    $ storiLin64
    
    Welcome to StoriFree - Automated. Interactive. Oracle performance analysis.
    
    I need to ask you for your StoriFree license details.
    ...
  2. Upon execution, the Stori program will look for and check the license file (StoriLicense.key) and the Oracle connection detail file (StoriConnect.key). If neither file exists, you will be prompted for the information.

    If you need a license key, click Stori license or click StoriFree license.

    You can delete the key files at any time because Stori will simply prompt you for the missing details. If you need to start over, simply drop the Oracle Stori user along removing the key files then restart Stori.

  3. When Stori asks for, TNS alias to connect to the Statspack repository enter your EZCONNECT string or an actual TNS alias (which is not discussed in this FAQ). Here is an example:

    Please enter the following:
    
      TNS alias to connect to the Statspack repository on this database server [orcl] : //main.acme.com/superfun
    
      An existing Oracle DBA account username [system]	 : 
    ...
     

When the installation has completed, Stori will prompt you with,
"How can I help you?"

Well done! Stori has been installed and is awaiting your command!

Does Stori run on Oracle Enterprise Edition?

Stori is designed specifically for Oracle Standard Edition. And Stori is also awesome on Oracle Enterprise Edition!

Yes, Stori does run on Oracle Enterprise Edition but with some capability limitation. All but the analysis commands work as expected.

Stori references Statspack data, not AWR data. For more detail about Statspack, see $ORACLE_HOME/rdbms/admin/spdoc.txt.

Because there is no capability difference between Stori and StoriFree, to see if Stori for EE integrates well into your environment, simply get a StoriFree license and give Stori a try!

If you have any questions, please contact us.

Restarting the installation

Not a big deal...

If the installation is failing for any reason, you can always start over from the beginning.

You can watch how to restart a botched installation process here.

If the installation failed and you have not been returned to the OS prompt, enter "exit" and press return. You may need to do this a few times. You may not see the letters you type. (Strange, but possible!)

For a QUICK reset:

  • Remove the "key" files: StoriConnect.key and StoriLicense.key. These files are located in the Stori distribution directory.
  • Run Stori.

For a FULL reset:

  • Remove the "key" files: StoriConnect.key and StoriLicense.key. These files are located in the Stori distribution directory.
  • Drop the Oracle user Stori. As an Oracle DBA user type, "drop user stori cascade;". This will NOT remove any StatsPack data.
  • Run Stori.

What Oracle permissions are needed?

Stori permissions are minimal. You can view the actual Stori user creation script storiCU.sql. It resides in the software distribution directory.

During installation:

  • Output is recorded in the log files, which have an extension of "log".
  • When connected as an Oracle DBA account, the DBA Oracle user creates the Stori Oracle user and makes some grants.
  • When connected as the Oracle StatsPack user, the StatsPack Oracle user grants Stori only SELECT access to the StatsPack tables and execution on the Stackpack package. Stori will never ever delete or update any Statspack data.
As stated above, you can always examine the short and simple Stori user creation script, storiCU.sql.

Can I run Stori with multiple databases on the same server?

Yes! If you want to analyze multiple databases with their StatsPack data residing on a single database server, install the Stori software in another/different directory and run it from the other/different directory. A Stori license is required for the StatsPack's data database (DBID) being analyzed, not the database used to access the StatsPack data.

Feel free to contact OraPub Support about various configuration options and ideas.

Your first session with Stori

Welcome to Stori! This FAQ was created for you to get a taste of what Stori can do, either by reading or typing the commands on your system. So let's get started! First, make sure Stori has been started and is asking you, How can I help you?

We interact with Stori by asking her to do something for us. Stori references a specific set of StatsPack data. When you first installed Stori, you were asked for a beginning and ending snap id. This snapshot range represents the scope of data Stori will reference until you change the scope. At any time, you can change the scope by asking Stori, to "set scope".

Once you are satisfied with the analysis scope, we can begin solving Oracle Database performance problems by interacting with Stori. Let's start by asking Stori if there is a performance problem. Ask Stori, "Is there a problem?" Here is an example:

How can I help you? Is there a problem?
It is a very serious CPU resource shortage.

Let's ask Stori to give a short and to-the-point performance summary. Ask Stori, "analyze summary". Stori will perform the analysis summary, display a text version of the results, but also create a beautiful web browser version as well. Here is an example:

How can I help you? analyze summary
...
Your analysis html formatted report has been created and placed here:
results/1375286945/analyze_results.html

Below is the result. Click on the image to get a larger view.



 
Since CPU consumption appears to be the issue, let's ask Stori for a more detailed CPU-centric analysis. Here is an example:

How can I help you? analyze os cpu
...
Your analysis html formatted report has been created and placed here:
results/1375291569/analyze_results.html

Below is the CPU-centric analysis result. Click on the image to get a larger view.



 
The operating system is just one aspect of a holistic performance analysis. We also need to look at Oracle and the application as well. As you might have expected, you can ask Stori, "analyze oracle" and "analyze application". Go ahead and try these on your system.

Let's get real specific and ask Stori for just the top five wait events. To see our command options for low level Oracle specific data ask Stori, "help get oracle" and you will see this:

How can I help you? help get oracle

  Here are the 'get oracle' commands I understand.
            
  oracle cpu consumed
         cpu util
         wait (time, count) by class CLASS [top n, rank n]
                            by event [class CLASS] [top n, rank n] *1
                            by event name EVENT  *2
			
    CLASS: nonidle, nonio, io, ior, iow, concur, network, serial, other
    EVENT: the actual event and wildcards (%) are OK
    *1   : events from the class on down are considered
    *2   : name, total wait time (sec), average wait time (ms)

Based on the help get oracle results, I'll enter this:

How can I help you? get oracle wait time by event top 5

db file scattered read,5812.650059
db file sequential read,4103.03788
db file parallel read,507.839431
direct path read,375.10297
read by other session,42.170686

First you see the wait event name and then you see the total time (in seconds) that all Oracle processes waited on this event over the snapshot interval.

Here's a neat little trick. Suppose you commonly want to know the top five wait events. Wouldn't it be nice to simply ask Stori, "top5waits"? Using Stori's alias capability we can do this. Here is an example:

How can I help you? set alias top5waits "get oracle wait time by event top 5"

Alias "top5waits" has been set to, "get oracle wait time by event top 5".

How can I help you? top5waits

db file scattered read,5812.650059
db file sequential read,4103.03788
db file parallel read,507.839431
direct path read,375.10297
read by other session,42.170686

That's pretty cool. Stori has a number of features like this. For example, there is variable substitution, Playback Facility, and quick scope set. But let's get back to this analysis.

It looks like Oracle processes are waiting on IO read related waits. But CPU consumption is just as important. Instead of asking Stori for the raw number, let's have her create a chart for us! Ask Stori, "get chart pie oracle" like this:

How can I help you? get chart pie oracle

work/1375294612_oracle_x_pie.png

Below is the result.



We can see that CPU consumption actually plays a larger role in a user's experience than Oracle wait time! This means any Oracle focused solution set will need to include ways to reduce both Oracle CPU consumption and Oracle wait time. If you want to explore Stori's charting capabilities, ask "help get chart". Let's continue!

In addition to the operating system and Oracle, we need to analyze the application as well. Asking Stori to "analyze appl" typically results in a very detailed report. Stori considers the OS and Oracle situation when determining what aspects of the application need attention. To best communicate the situation, Stori uses text, charts, and matrixes. While I will highlight a small portion of the analysis below, you can view the entire Application Analysis here.

The SQL Opportunity Focus pie chart is one of my favorites because Stori uses all available data to determine which SQL statements deserve our attention. Why? Because they present the greatest opportunity for improving performance! While this chart is shown in the full Application Analysis, you can create it yourself by asking Stori, "get chart pie appl opr". (The opr is short for opportunity.) Here is an example:

How can I help you? get chart pie appl sql

work/1375295882_appl_sql_opr_pie.png

Below is the result.



Immediately we know there is something special out this "chnzz" SQL statement. For visual clarity, only the last five characters of the SQL IDs are shown. To get the full SQL ID ask Stori, "get top sql". I will then typically ask for the full text and finally full statement details. By the way, Stori automatically does this when you ask, "analyze appl". Here is the dialog.

How can I help you? get top sql

7aw349n5chnzz

How can I help you? get sql text id [1]

Substituted 7aw349n5chnzz for [1]

SELECT "SUMATRON_ATT_ID","SUMATRON_ATT_EFF_DATE","LATEST_VERSION
_IND" FROM "CDR"."SUMATRON_ATTENDANCE" "SUMATRON_ATTENDANCE" WHE
RE "SUMATRON_ATT_EFF_DATE">=:1 AND "LATEST_VERSION_IND"='Y' AND 
LNNVL("SUMATRON_ATT_ID"<>:2)

How can I help you? is sql id 7aw349n5chnzz all  

It is as follows:

SQL ID                   :  7aw349n5chnzz

SQL type                 :          query

Module                   : oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3

Executions
.....Total completed     :           1835
.....Per second          :            .51
.....Percent of all SQL  :           .968

Elapsed Time
.....Total (sec)         :       22672.35
.....Average (sec/exe)   :         12.356
.....Percent of all SQL  :         75.605

CPU Consumed             : 
.....Total (sec)         :      18759.775
.....Average (sec/exe)   :         10.223
.....Percent for this SQL:         82.743
.....Percent of all SQL  :         62.558

Wait Time                : 
.....Total (sec)         :       3912.575
.....Average (sec/exe)   :          2.132
.....Percent for this SQL:         17.257
.....Percent of all SQL  :         13.047

Logical IO Consumed      : 
.....Total (#)           :     1413190231
.....Average (#/exe)     :     770130.916
.....Percent of all SQL  :         91.054

Physical IO Blocks Read  : 
.....Total (#)           :     1407755356
.....Average (#/exe)     :     767169.131
.....Percent of all SQL  :         91.115

Parse Calls (soft+hard)  : 
.....Total (#)           :          29040
.....Average (#/exe)     :              1
.....Percent of all SQL  :         66.258

SQL hash value new       :      180900863
...............old       :     3264153475

SQL text (partial): SELECT "SUMATRON_ATT_ID","DONAT

There are two things I want to highlight. First, I snuck in a Stori feature called, Variable Substitution. Look closely above for the [1] and you see how it works. For details about Variable Substitution see the related FAQ. Second, shown above is the text output. The HTML formatted output is beautifully formatted. As mentioned above, you can view the full Application Analysis here.

Matrixes are a fantastic way to compare and contrast lots of data. Stori uses matrixes for the top SQL, top module, and top programs! Stori ranks based on a proprietary quantitative algorithm that results in what we call an opportunity ranking or OPR for short. It allows us to understand from a more holistic perspective how SQL statements (or modules) compare. You will see the OPR column in all matrixes.

Stori includes the relevant matrixes automatically in the Application Analysis. Of course, you can ask Stori to create them at any time! Here is how to ask for the top SQL matrix.

How can I help you? is top sql matrix

It is 

SQL ID             EL CPU LIO PIO Srt Exe Par DW OPR
------------------ -- --- --- --- --- --- --- -- ----
7aw349n5chnzz       1   1   1   1  18   5   1  2   1 
33z4yynbc063m       2   2   2   2  18  29      2  .5 
8b9hnf7af7z36       3   3   3   3  18          2 .33 
gm61prsakf2ud       4   4   5   4  15          1 .25 
b02ykty03gsxg       6   5   4  13  18  34  25  2  .2 
3k8yvvqc0zazk       7   6  10  15  18   8   3  2 .17 
db78fxqxwxt7r       5   7   6   6   1   1  18  2 .14 
3dwb0ndumvaa6      12   8  21  24  18  17   9  2 .13 
7ng34ruy5awxq       8   9   7   9   2   4   5  2 .11 
3v3jhaqxm6xn5       9  10  11   5  15          2  .1 

EL:  elapsed time, CPU: CPU time, LIO: logical IOs,
PIO: physical IO block reads, Srt: sorts to disk,
Exe: executions, Par: parses, DW: direct writes
OPR: opportunity rating

Below is the above matrix but taken from the HTML based Application Analysis report.



 
So, what do we do with these results? You guessed it! Ask Stori for advice. When we ask for a full analysis, Stori creates a summary, a list of just the top ranked recommendations, and an analysis of the operating system, Oracle, and the application. We ask Stori for a full analysis by asking, "analyze all". While you can see the entire analysis here, shown below is just the recommendation section.

Below is the result.



 
We hope you have received a taste of what Stori can do for you. Stori has an incredible and flexible command set, all stemming from four basic commands: get, is, analyze, and set. Most Stori users will frequently ask for specific area of help. Here are a few of the most common:

help get
help get oracle
help get sql
help get chart
help is

And of course, there's the classic, "help help" as well!

Interacting with Stori

Interacting with Stori is easy, enlightening and fun! To begin using Stori, you need to understand the analysis scope and the four basic Stori commands.

Analysis scope

Stori references StatsPack performance data within a defined scope or StatsPack snapshot range. When you first install Stori, you are prompted for a begin and end snapshot ID. This snapshot range becomes the initial scope. The scope remains the same and does not need to be reset, but you can change it at any time.

  1. IS SCOPE. When you start Stori, the current analysis scope is displayed. Stori essentially ran the is scope command. This is what it looks like:

    $ ./storiLin64
    
    Welcome to Stori - Automated. Interactive. Oracle performance Analysis.
    
    I am centering myself...done. Thanks for your patience.
    
    It is as follows:
    
    Date and time   : 04-Dec-2012 03:00:04 to 04-Dec-2012 04:00:03
    Snap duration   : 60 minutes
    Host            : rexsou20
    DB Name / ID    : ABC123 / 1523308526
    Instance number : 1
    Snap ID / level : 14515 to 14525 / 5
    
    How can I help you?

    Notice the similarity when you ask Stori, "is scope" or "What is the scope?":

    How can I help you? What is the scope?
    
    It is as follows:
    
    Date and time   : 04-Dec-2012 03:00:04 to 04-Dec-2012 04:00:03
    Snap duration   : 60 minutes
    Host            : rexsou20
    DB Name / ID    : ABC123 / 1523308526
    Instance number : 1
    Snap ID / level : 14515 to 14525 / 5
  2. SET SCOPE. You can also change the analysis scope at any time by issuing the set scope command. It is no different than setting a StatsPack report's snapshot range.

    But Stori gives you intelligent choices! For example you can simply ask, set scope to go through the default scope-setting dialogue. Or, you can ask Stori, set scope yesterday 0900 60 to set the scope's snapshot ID range to begin yesterday at 0900 with a duration of 60 minutes, therefore ending at 1000. You can see all the scope setting options by asking Stori, help scope.

    How can I help you? help scope
     Here are the 'scope' commands I understand.
    
      get scope [, dbid, instance_number, snap_id_(begin|end), snap_date_(begin|end),
                snap_time_(begin|end), snap_duration, snap_level]
    
      is  scope
      set scope [ START_DATE START_TIME DURATION ]
      set scope snap_ids X Y
      set scope latest
    
         START_DATE format options:
            DD-MON-YYYY
            today
            yesterday
     
         START_TIME in 24 hour format
    
         DURATION in minutes
    
         Examples:
           set scope
           set scope yesterday   1430 60
           set scope today       1430 60
           set scope 12-Jul-2012 1430 60
           set scope snap_ids 105 115
           set scope latest
           pause 30
    
         The scope will be set to the closest valid snapshots
         unless the snap IDs are specifically given.
    
    
    How can I help you?

    Here is a final example:

    How can I help you? set scope 04-Dec2012 0600 60
    OK... Resetting the scope with snap IDs 14545 to 14546.
    This can take a minute...
    
    Finished. The scope has been reset.
    
    How can I help you?

Four basic commands

While Stori offers a rich set of interactive commands, there are just four basic command types: set/show, get, is and analyze.

  1. ANALYZE. The analyze capability is Stori's soul; automatically perform a complete Oracle performance analysis that is technically robust, well organized, actionable and makes perfect sense. Click here for an example. Below is just the first parts of the analysis results.



    Besides asking for a full analysis, you can ask Stori to perform only one specific part.

    Here are some analyze command examples:

    • analyze summary
    • analyze oracle
    • analyze application
    • analyze os cpu
    • analyze all
    For on-line help, ask Stori, help analyze.

  2. SET/SHOW. Stori references many parameters. You can view and reset the parameters at any time. For help ask Stori, "help set".

    Here are some examples regarding parameter variables:

    • Set the number of help suggestions by telling Stori to set help_suggestions 2.
    • View any variable setting by telling Stori to, show X where X is the variable parameter.
    • View all parameters by telling Stori to, show all.
    • Set any parameter by telling Stori to, set X where X is the variable parameter.
    • View the current state of the context sensitive help facility by asking, show help_on_off.
    • Turn the context sensitive help facility OFF by asking, set help_on_off off
    Do not set parameters until you know what you are doing as Stori may operate incorrectly. To reset parameters to their default, simply exit and restart Stori. To keep parameter changes permanent, change the storiParam1.sql file.

  3. GET. At its core, Stori responds to detailed get commands. Stori will quickly respond in a very raw, direct and unformatted way. You will naturally begin to use the get commands once you get familiar with Stori and your performance analysis ability increases.

    Some example get commands are:

    • get cpu utilization
    • get top sql
    • get sql id by elapsed_time_s top 3
    • get oracle wait time by event top 3
    • get oracle wait time by event class io top 3
    • get stats systimemodel db%cpu
    • get chart pie appl sql
    • get sql text 5yv0nj5hwfz5d
    • get sql cpu_time_s id 5yv0nj5hwfz5d

    For on-line help, ask Stori, help get.

  4. IS. Issue an is command when you want Stori to respond with a nicer format and plain English. You can also ask Stori is commands in a more natural way though more typing is involved. For example, instead of simply asking, is io problem you can ask, Is there an IO problem?

    Here are some is command examples:

    • Is there a problem?
    • Is there a CPU problem?
    • is top sql 5
    • is top sql matrix
    • is top sql matrix module west%
    • is top module
    • is sql id 5yv0nj5hwfz5d basic
    • is sql id 5yv0nj5hwfz5d basic
    • is top module
    • is top module matrix 5
    The is command output is textual, but when Stori performs an analyze command, much of the output is placed into beautifully formatted tables. Here is an example of an html top SQL matrix:

     
    For on-line help, tell Stori to, "help is".

How do I find the top SQL?

There is a ton of SQL statement data Stori can provide. The trick is determining what top means and showing just what is important enough to warrant your attention. The true top SQL statement depends on your objectives, what's important to your users, and what is happening inside of Oracle and the operating system. This is why tagging the most important SQL statement is difficult.

Stori is smart! It considers all these factors when determining the most important SQL statements. OraPub has designed into Stori a proprietary method for quantifying the relative performance improvement opportunity between SQL statements (and also modules). The higher the Opportunity Rating (OPR) value and the larger the difference between statements directs us to focus on the higher value statement.

Here is how easy it is to get the top SQL statement followed by how to get the top three.

How can I help you? get top sql

7aw349n5chnzz

How can I help you? get top sql 3

7aw349n5chnzz 33z4yynbc063m 8b9hnf7af7z36

To see the statements opportunity ranking, just add the value option at the end of the command.

How can I help you? get top sql 3 value

7aw349n5chnzz (1)   33z4yynbc063m (.5)   8b9hnf7af7z36 (.333)

Stori can create either a pie or big bar chart to help is visually grasp the relative SQL statement importance as well as their ranking by key components such as CPU time, buffer gets and many others. Ask Stori, get chart pie appl sql to get a pie chart based on SQL statement opportunity ranking. Below is an example.



It is also important to see statements relation to each other from a variety of perspectives. But that's a lot of data, so Stori creates a matrix type display.

How can I help you? is top sql matrix top 5

It is 

SQL ID             EL CPU LIO PIO Srt Exe Par DW OPR
------------------ -- --- --- --- --- --- --- -- ----
7aw349n5chnzz       1   1   1   1  18   5   1  2   1 
33z4yynbc063m       2   2   2   2  18  29      2  .5 
8b9hnf7af7z36       3   3   3   3  18          2 .33 
gm61prsakf2ud       4   4   5   4  15          1 .25 
b02ykty03gsxg       6   5   4  13  18  34  25  2  .2 

EL:  elapsed time, CPU: CPU time, LIO: logical IOs,
PIO: physical IO block reads, Srt: sorts to disk,
Exe: executions, Par: parses, DW: direct writes
OPR: opportunity rating

The matrix makes it pretty obvious the statement with the SQL ID ending in chnzz is extremely important. The opportunity ranking is (1.0) is twice that of the second ranked statement! In this system is there is CPU bottleneck, so we would expect the top statement to be one of the top CPU consumers...and it is!

When the application is analyzed, for example asking Stori, analyze appl, the Top SQL Matrix is created in a nicely formatted table. Below is the above matrix but taken from an Stori analysis report.


 
Now that we know which SQL to focus on, we will want to get more details. To get raw data, use a get sql command and to get more information nicely formatted use a is sql command.

Let's look at the get sql command first. Here is the command line help. Notice there are a few examples near the end.

How can I help you? help get sql

  Here are the 'get sql' commands I understand.

  sql  id by STAT [top n, rank n] [module MODULE] [type TYPE] [value]
       (text, STAT) id SQLID
       (STAT, total STAT) [top n, rank n] [type TYPE] 

     STAT   : elapsed_time_s, cpu_time_s, buffer_gets,
              sorts, executions, parse_calls, disk_reads,
              direct_writes
     TYPE   : query, dml, other
     MODULE : Module name (% OK, replace space with '_')
     SQLID  : the actual SQL ID

     Examples: get sql id by elapsed_time_s top 3
               get sql id by cpu_time_s module ModuleABC
               get sql elapsed_time_s id 7aw349n5chnzz
               get sql total buffer_gets
    
  top (sql|sql_id, module, program|program_id, command|command_class) [n] [value]

Let's ask for the CPU consumption in seconds for the top statement during the snapshot interval.

How can I help you? get sql cpu_time_s id 7aw349n5chnzz

18759.775

Let's get the actual SQL text.

How can I help you? get sql text id 7aw349n5chnzz

SELECT "SUMATRON_ATT_ID","SUMATRON_ATT_EFF_DATE","LATEST_VERSION
_IND" FROM "CDR"."SUMATRON_ATTENDANCE" "SUMATRON_ATTENDANCE" WHE
RE "SUMATRON_ATT_EFF_DATE">=:1 AND "LATEST_VERSION_IND"='Y' AND 
LNNVL("SUMATRON_ATT_ID"<>:2)

As you can see from the help get sql results, we can get quite a bit of detail about the SQL statement. While raw data can be useful, adding a little analysis and comparing it other statements becomes even more useful. This is when the is sql commands are useful.

How can I help you? help is

  Here are the 'is' commands I understand.

  is top (sql,module,program,command) [n]
     top (sql,module,program,command) matrix [module MODULE] [top n]
     (problem, issue, bottleneck)
     cpu     (problem, issue, bottleneck)
     io      (problem, issue, bottleneck)
     network (problem, issue, bottleneck)
     memory  (problem, issue, bottleneck)
     other   (problem, issue, bottleneck)
     scope
     sql id SQL_ID [basic, all, type, execution|exec, elapsed,
                    cpu, wait, lio, pio, parse, hash, text, module]
                    
     MODULE : Module name (% OK)

Focus on the commands near the bottom of the above help text: the is sql id SQL_ID commands. Stori does not want to present to you all available information. That is, unless you ask for it. That is why there are various information groupings such as basic, execution, elapsed, cpu, and wait. When the application is analyzed the output is nicely formatted in HTML, but here are a few text based examples.

How can I help you? is sql id 7aw349n5chnzz basic

It is as follows:

SQL ID                   :  7aw349n5chnzz

SQL type                 :          query

Module                   : oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3

Elapsed Time
.....Total (sec)         :       22672.35
.....Average (sec/exe)   :         12.356
.....Percent of all SQL  :         75.605

Executions
.....Total completed     :           1835
.....Per second          :            .51
.....Percent of all SQL  :           .968

SQL hash value new       :      180900863
...............old       :     3264153475

SQL text (partial): SELECT "SUMATRON_ATT_ID","SUMAT

As you can see, Stori quickly and intelligently provides the true top statements and allows you continue your investigation by providing lots of raw data (if you want it) as well as summarized information.

How do I find the top module?

Developers have the ability to associate statements to a named module. Module analysis allows us to grasp how multiple good performing statements can together result in a poor user experience. When this activity grouping is assigned a module name, we can analyze their activity together. Then by focusing on the PL/SQL and SQL within the identified module, performance tuning efforts can directly impact the user experience.

How can I help you? get top module 3

oracle@ummdc142.arexs.rabcss.org.au_(TNS_V1-V3 undefined pmdtm@ummdc149_(TNS_V1-V3)

While the full module name is important to know, seeing the modules in relation to each other is just as important. Stori provides the ability to provides lots of information into wonderful format using a matrix output and also charts.

How can I help you? is top module matrix

It is 

Module Name        EL CPU LIO PIO Srt Exe Par DW OPR
------------------ -- --- --- --- --- --- --- -- ----
oracle@ummdc142.ar  1   1   1   1   4   2   1  2 1.83
pmdtm@uaxmc149_(TN  2   2   2   2   3   3   3  1 .88 
sqlplus@udaxmu20_(  4   4   4   4   4   4   4  2 .15 
TOAD_11.6.0.43      5   5   5   5   2   5   5  2 .12 

EL:  elapsed time, CPU: CPU time, LIO: logical IOs,
PIO: physical IO block reads, Srt: sorts to disk,
Exe: executions, Par: parses, DW: direct writes
OPR: opportunity rating

The matrix above is one way to view module activity. Another is using a pie or big bar chart. Here is how to create a big bar chart followed by the chart.

How can I help you? get chart bigbar appl module

work/1375482549_appl_module_x_BB.png

In this example the module names are relatively long. While the a pie or big bar chart can be used to visually show top module relative impact, we will need annotate the chart with actual module name.

There is a ton of module data Stori can provide. It's common for DBAs and Developers to focus on a specific module. But if not, it is not simple task to determine what top means and showing just what is important enough to warrant your attention. The true top module depends on your objectives, what's important to your users, and what is happening inside of Oracle and the operating system. This is why automated module tagging is difficult.

Stori is smart! It considers all these factors when determining the most important module. OraPub has designed into Stori a proprietary method for quantifying the relative performance improvement opportunity between SQL statements (and also modules). The higher the Opportunity Rating (OPR) value and the larger the difference between statements directs us to focus on the higher value statement.

Now that we know the top module, we probably want to know the top statements within that module! Stori can do this analysis for us!

How can I help you? is top sql matrix module oracle@ummdc142.arexs.rabcss.org.au_(TNS_V1-V3

It is 

SQL ID             EL CPU LIO PIO Srt Exe Par DW OPR
------------------ -- --- --- --- --- --- --- -- ----
7aw349n5chnzz       1   1   1   1   1   1   1  1   1 
33z4yynbc063m       2   2   2   2   1   2   2  1  .5 
8b9hnf7af7z36       3   3   3   3   1   3   2  1 .33 

EL:  elapsed time, CPU: CPU time, LIO: logical IOs,
PIO: physical IO block reads, Srt: sorts to disk,
Exe: executions, Par: parses, DW: direct writes
OPR: opportunity rating

Now that we know the top statements within the top module (or the module we care about), we can continue drilling down into the SQL that is truly important!

As you can see, Stori quickly and intelligently provides the module and allows you continue your investigation by providing lots of raw data (if you want it) as well as summarized information... even drilling down to the most important SQL within the most important module!

Time based analysis (TBA)

Modern Oracle performance analysis determines where time is being spent in the database and derives the best solutions to reduce and minimize that time. Stori embraces TBA and Unit of Work TBA (uniting TBA with Operations Research queuing theory). Understanding TBA is key to understanding why Stori makes specific analysis statements and recommendations.

First, Oracle TBA is about determining where time is spent in the database and then deriving solutions to minimize the time. Below is a big bar chart showing the CPU and Oracle wait time accumulated over the analysis scope duration. You can create this chart by asking Stori, get chart bigbar oracle or numerically, get oracle cpu consumed and get oracle wait time by class nonidle.

 
A central part of understanding the situation and effectively communicating the situation is to group the time by categories, for example, CPU time, IO time, or IO read time. Below is an example visually showing all Oracle wait time categorized as either IO time or not. You can create this chart by asking Stori, get chart bigbar oracle wait or numerically, get oracle wait time by class nonidle

Do you think there an IO related issue?!

Second, at the lowest Oracle time level are the Oracle wait events. Oracle wait events are the result of Oracle instrumenting its kernel code to determine how much non–CPU time something takes. For example, this "something" could be waiting for an IO to complete, waiting to acquire a lock, or waiting for data from a remote database. There are over a thousand wait events. This is why Oracle TBA is so precise.

Here is how to ask Stori for the top five wait events:

How can I help you? get oracle wait time by event top 5

db file scattered read,5812.650059
db file sequential read,4103.03788
db file parallel read,507.839431
direct path read,375.10297
read by other session,42.170686

Sometimes a pie chart or a big bar chart tells the story nicely. Here is how to ask Stori to create a pie chart showing the top wait events.

How can I help you? get chart pie oracle events

work/1375458708_oracle_event_x_pie.png
 
A Performance Analyst has to combine an Oracle TBA with an understanding of the application, user requirements and the operating system. This is where Stori comes in. Its automated analysis is amazing and precise. Stori is what every Oracle DBA needs!

To learn more about getting database related time information and how Stori uses time in its analysis, check out the FAQ Advanced Use tab.

How do I customize output?

Stori is highly parameter driven...and you have the ability to customize aspects of Stori.

Variable parameters can be easily shown and set.

  • Show. Parameters can be easily seen using the show command. For example, show text_wrap. You can show all the parameters by asking Stori, show all.
  • Set. Parameters can be temporarily reset using the set command. For example, set text_wrap 55. You can permanently set parameters by including them in the storiParam1.sql file. Use the existing settings as a format guide. Never change the settings in the storiParam0.sql unless directed by OraPub Support.

Here are a few of the more common settings.

  • text_wrap. The line size as an integer number, and how many characters until the text wraps. Setting the text_wrap less than 55 can make some tables virtually unreadable.
  • matrix_module_top_n. The default number of modules shown when asking Stori, is top module matrix type command.
  • matrix_sql_top_n. The default number of modules shown when asking Stori, is top sql matrix type command.
  • solutions_max. The number of solutions, as an integer number, presented per subsystem (that is, Oracle, OS, Application). Like most performance analysts Stori develops a number of performance solutions but only presents the best solutions. Presenting too many solutions can be overwhelming and potentially distracts from the best solutions.
  • rec_rank_cutoff_num. Used when telling Stori to analyze .... This parameter instructs Stori to only display the top "ec_rank_cutoff_num impactful solutions. Displaying all the solutions can be overwhelming and distracting.
  • rec_rank_cutoff_pct. Used when telling Stori to analyze .... This parameter instructs Stori to display solutions that fall within the top rec_rank_cutoff_pct percent of total solution impact. For example, suppose Stori technically creates fifteen recommendations, yet the top six recommendations account for 80% of all solutions potential impact. Setting this parameter to 0.80 will result in only six recommendations being displayed. Just showing the most impactful solutions minimizes distractions and keeps solution selection focused on what is important.
  • rec_rank_show. Normally, when telling Stori to analyze ... each recommendation has its relative impact value shown at the end of the recommendation in parenthesis. If this is distracting, set this parameter to FALSE instead of the default of TRUE. The parameter has no effect on how Stori creates, ranks or displays solutions.
  • chart_font_file_1. The charting font file name, relative to the Stori distribution directory. It can interesting and fun to experiment with different fonts.
  • var_sub_dialog_on_off. Determines if Stori will display the variable number and contents about to be substituted. Once you test out a command sequence that uses variable substitution you will likely want to reduce the feedback Stori provides.
How do I customize help?

Stori's context help facility was purposely designed to be helpful yet not obtrusive. The more you use Stori, the less you will rely on the context sensitive help system. Note: Stori's command line help, for example help get, is always available. By default the context sensitive help will display some sample commands, like this:

How can I help you? get cpu utilization

.875

Type your request or select a suggestion below.
  1. help get sql 
  2. help get 
  3. get cpu util 
  4. help get oracle 
  5. get io problem 

How can I help you? 

You can turn off context sensitive help by asking Stori, set help_on_off off. You can change the number of commands suggestions to three by asking Stori, set help_suggestions 3. Notice the help facility change in the dialog below.

How can I help you? Is there a problem?

It is a very serious CPU resource shortage.

Type your request or select a suggestion below.
  1. is top sql 3 
  2. help basic 
  3. help is 
  4. is cpu problem 
  5. is problem 
  6. is io problem 

How can I help you? set help_on_off off

The variable help_on_off has been set to off

How can I help you? Is there a problem?

It is a very serious CPU resource shortage.

How can I help you? set help_on_off on

The variable help_on_off has been set to on

Type your request or select a suggestion below.
  1. set scope 
  2. help basic 
  3. is scope 
  4. set trace_out off 
  5. show all 
  6. set help_on_off on 
  7. set help_suggestions 7 

How can I help you? set help_suggestions 3

The variable help_suggestions has been set to 3

Type your request or select a suggestion below.
  1. is scope 
  2. set scope 
  3. help basic 

How can I help you?

Remember, you can always ask Stori to display command line help, such as help get oracle.

Can I alias commands? (Command Alias)

Yes! Even with only four basic commands, Stori command combinations are limitless. When you have a complex command, yet don't want to type it every time, create an alias.

For command line options ask Stori, help alias. Here is an example of setting and using an alias.

How can I help you? set alias top5we "get oracle wait time by event top 5"

Alias "top5we" has been set to, "get oracle wait time by event top 5".

How can I help you? top5we

db file scattered read,5812.650059
db file sequential read,4103.03788
db file parallel read,507.839431
direct path read,375.10297
read by other session,42.170686

How can I help you? 

You can see all your available aliases by asking, "show alias all".

How can I help you? show alias all

Here are the aliases currently set:

top5we => get oracle wait time by event top 5
issue => is problem
util => get cpu util

How can I help you? 

You can see see a previously set alias by asking, "show alias top5we".

How can I help you? show alias top5we

get oracle wait time by event top 5

How can I help you? 
How do I save command aliases? (Command Alias)

Aliases are released when you exit Stori. If you want to keep a library of aliases, a good solution is to place them in a Playback file. More specifically, create an alias.pbf file containing your alias commands. Then when you want your aliases active, simply run the Playback file.

Here's a neat idea: Instead of manually running the Playback file, call it directly from the startup.pbf file.

See the Playback Facility for more details.

For alias command line help ask Stori, help alias. For Playback facility command line help ask Stori, help playback or help pbf.

Can I use output for future input? (Variable Substitution)

Stori saves the previous command output into variables. The variables can be used in the next command. Use wildcard % liberally. Here is an example:

How can I help you? get top module

oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3

How can I help you? is top sql matrix module [1]%

Substituted oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3 for [1]

It is 

SQL ID             EL CPU LIO PIO Srt Exe Par DW OPR
------------------ -- --- --- --- --- --- --- -- ----
7aw349n5chnzz       1   1   1   1   1   1   1  1   1 
33z4yynbc063m       2   2   2   2   1   2   2  1  .5 
8b9hnf7af7z36       3   3   3   3   1   3   2  1 .33 

EL:  elapsed time, CPU: CPU time, LIO: logical IOs,
PIO: physical IO block reads, Srt: sorts to disk,
Exe: executions, Par: parses, DW: direct writes
OPR: opportunity rating

Stori saves the first eight groups of characters delineated by either a space or a comma into the variables. As shown above, you access the variable contents in a Stori command by encapsulating the variable number in brackets. Stori does a direct character-by-character substitution and also shows you the substitution it is about to make.

Here is an nice drilldown example using Variable Substitution.

How can I help you? get oracle wait time by class nonidle

io,10892.737557
nonio,69.592922

How can I help you? get oracle wait time by class [1]

Substituted io for [1]

ior,10845.91405
iow,46.823507

How can I help you? get oracle wait time by class [1]

Substituted ior for [1]

ior,10845.91405

How can I help you? get oracle wait time by event class [1] top 3

Substituted ior for [1]

db file scattered read,5812.650059
db file sequential read,4103.03788
db file parallel read,507.839431

How can I help you? get oracle wait time by event name [1]%[2]%[3]%

Substituted db for [1]

Substituted file for [2]

Substituted scattered for [3]

db file scattered read,5812.650059,1.214941

Powerful analytical capabilities can be created when combining variable substitution with Playback files. Even better is when command aliases are involved.

The substitution dialog can be turned off by asking Stori, set var_sub_dialog_on_off off.

Can I script a sequence of commands? (Playback Facility)

Sit back and watch! Stori allows you to create script full of Stori commands. The script is called playback file, which can be run at any time within Stori or at the operating system command line!

If you look in the Stori software distribution directory you will see some files with a suffix of pbf. Those are Playback files. Go ahead and run the sample.pbf on the Stori command line like this:

How can I help you? start pbf sample.pbf
It's that simple!

A Playback file can contain displayed comments, any Stori command, any number of Stori commands, and even other Playback files!

Playback files are commonly used for a command sequence that is either technically or functionally specific. For example, each day you may want to provide:

  • yourself with a performance summary of the previous day's peak activity period
  • developers with details about top SQL statements or Module details that they are coded
  • your manager with a performance summary about the previous day's peak period
  • the OS Administrator with key Oracle and operating system metrics such as CPU utilization or Oracle IO read and write times
  • yourself with a standard report you run when phone rings and a user says performance was horrible this morning!

When Stori begins, it automatically runs the Playback file, startup.pbf.

The Playback Facility is simple to use, but contains powerful features. When used with variable substitution, command line scope setting, command aliasing, and various Playback facility timing controls you can create compelling demonstrations and create your own detailed analysis scripts. OraPub uses Playback extensively for Stori product development QA.

A Playback file can be run on the Stori command line using the start pbf command or when Stori is invoked from the operating system using the -pbf option. Here is how to run the sample.pbf Playback file from the operating system command line.

$ ./storiLin64 -pbf sample.pbf

The best to learn about the Playback Facility to start simple and then build up a set of playback scripts. To you help you, the Stori distribution contains a sample Playback file, sample.pbf. The Stori command help for the Playback Facility is quite extensive. Just ask Stori, help pbf or help playback.

What types of charts are available?

As of Stori version 2, there are three types of charts available (pie, big bar, and line). For full Stori command details ask Stori, help get chart. There is also an FAQ about creating charts.

Here is single example of each chart type.



How do I create charts?

Stori also allows you to independently create the same charts using a very simple commands. Stori uses text, matrixes, and charts to create and convey a complex performance story when responding to an analyze command. To see an example, click here.

When you see the word opportunity or opr the chart is based on Stori's proprietary ranking used to rank and compare various performance aspects identifying performance improving opportunities.

When Stori creates a chart it given a unique name and sequence number allowing you to identify the chart and places it into the Stori software distribution work subdirectory. You can remove all the files in the work directory without causing any problems. When a Stori analyze command is run, the chart files are copied into the appropriate results subdirectory. Stori also displays actual file name after the chart is created. For example:

How can I help you? get chart pie oracle

work/1375471263_oracle_x_pie.png

The chart command line help facility provides everything you need to know about Stori charting.

How can I help you? help get chart

  Here are the 'get chart' commands I understand.

  chart        line os cpu rt
               (pie, bigbar) (opr|opportunity)
                             oracle (, wait, io, nonio, events)
                             appl (sql, SQL, module)
                              
      SQL: sql id by STAT [module MODULE] [type TYPE]

      STAT   : elapsed_time_s, cpu_time_s, buffer_gets,
               sorts, executions, parse_calls, disk_reads,
               direct_writes
      TYPE   : query, dml, other
      MODULE : Module name (% OK)

Here are some sample charting commands. Type them out on your system!

  • get chart line os cpu_rt
  • get chart pie appl sql
  • get chart bigbar appl sql
  • get chart pie appl module
  • get chart bigbar appl module
  • get chart pie oracle events
  • get chart bigbar oracle events
  • get chart pie opr
  • get chart bigbar opr
  • get chart bigbar oracle
  • get chart bigbar oracle wait
  • get chart bigbar oracle io
  • get chart bigbar oracle nonio
  • get chart pie oracle
  • get chart pie oracle wait
  • get chart pie oracle io
  • get chart pie oracle nonio
  • get chart bigbar appl module
  • get chart pie appl sql id by elapsed_time_s
  • get chart pie appl sql id by buffer_gets
  • get chart pie appl sql id by executions
  • get chart pie appl sql id by parse_calls
  • get chart pie appl sql id by disk_reads
  • get chart bigbar appl sql id by direct_writes

Here is an example of asking Stori, get chart bigbar oracle events.

Here is an example of creatively use Stori's variable substitution facility to create a chart.

    How can I help you? get top module
    
    oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3
    
    How can I help you? get chart pie appl sql id by elapsed_time_s module [1]%
    
    Substituted oracle@uaadc142.acxbs.rexboxes.org.pe_(TNS_V1-V3 for [1]
    
    work/1375472105_appl_sql_elapsed_pie.png

    And here is the resulting chart showing only the top elapsed times SQL statements tagged with the specific module!

Can I quickly set the scope on the command line? (Quick Scope Reset)

Yes. There are a number of ways to quickly reset the scope by issuing a single command without requiring any additional keystrokes. This is called the, Quick Scope Reset facility.

Stori is smart! We rarely know the exact snapshot start or end time, so Stori finds the closest match and then sets the snapshot IDs accordingly! Stori also knows we would rather input today or yesterday instead of something like, 10-Feb-2013. This thinking led to design a very natural command line sequence with a basic format of start date, start time, and snapshot duration. For example, just ask Stori, set scope today 0900 60. Advanced Stori users, make of this facility extensively within Playback files!

Stori provides very nice command line help, including some examples. To see the command line help ask Stori, help set scope.

How can I help you? help set scope

   Here are the 'scope' commands I understand.

  get scope [, dbid, instance_number, snap_id_(begin|end), snap_date_(begin|end),
            snap_time_(begin|end), snap_duration, snap_level]

  is  scope
  set scope [ START_DATE START_TIME DURATION ]
  set scope snap_ids X Y
  set scope latest

     START_DATE format options:
        DD-MON-YYYY
        today
        yesterday
 
     START_TIME in 24 hour format

     DURATION in minutes

     Examples:
       set scope
       set scope yesterday   1430 60
       set scope today       1430 60
       set scope 12-Jul-2012 1430 60
       set scope snap_ids 105 115
       set scope latest
       pause 30

     The scope will be set to the closest valid snapshots
     unless the snap IDs are specifically given.

Here are some examples of how Oracle DBAs use the Quick Scope Reset facility.

Suppose when you arrive back in your office after lunch you want a quick performance summary for the morning's typical peak activity times. All you need to do this:

How can I help you? set scope today 0900 120
OK... Resetting the scope with snap IDs 14515 to 14525.
This can take a minute...

Finished. The scope has been reset.

How can I help you? analyze summary
...

Or how about each morning you want send your manager a report about the previous nights payroll run, which starts at 2200 and lasts around three hours. Just to be safe, the snapshot duration will be set for four hours. All you need to do this.

How can I help you? set scope yesterday 2200 240
OK... Resetting the scope with snap IDs 14215 to 14225.
This can take a minute...

Finished. The scope has been reset.

How can I help you? analyze summary
...

It's worth mentioning again, that the Quick Scope Reset Facility was designed with the Playback Facility in mind.

Time Classifications

Before delving into Time Classifications, it is important to understand the essence of Time Based Analysis (TBA). Check out the FAQ entitled, Time Based Analysis (TBA) in the Using Stori tab.

Modern Oracle performance analysis determines where time is being spent in the database and derives the best solutions to reduce and minimize that time. Stori embraces TBA and Unit of Work TBA (uniting TBA with Operations Research queuing theory). Understanding TBA is key to understanding why Stori makes specific analysis statements and recommendations.

An important aspect of TBA is grouping or classifying time. Classifying time:

  • Enhances Communication. Instead of saying, "Forty percent of the wait time is associated with db file sequential reads and 30% of the wait time is associated with db file scattered reads" we can simply say, "Seventy percent of the wait time is associated with IO." This approach is very effective when communicating with management.
  • Highlights Performance Improvement Opportunities. If seventy percent of the wait time is associated with IO, we have a massive chunk of time we can reduce! This is more desirable than focusing on 10% here and 15% there. Performance analysts focus on reducing the largest chunk of time because they get the most return for their investment.

Here is Stori's database time hierarchy with the associated time classification abbreviations in parenthesis:

  • CPU
  • Non-Idle Wait Time (nonidle)
    • IO time (io)
      • Read IO time (ior)
      • Write IO time (iow)
    • Non-IO time (nonio)
      • Memory serialization control time (serial)
      • Relational structure locking control time (concur)
      • Network transfer time (network)
      • All other non IO wait time (other)

You can ask Stori for time details at a variety of detail levels using the "get oracle" commands. For the command options ask Stori, help get oracle.

Here are some "get oracle" command examples:

  • get oracle cpu consumed
  • get oracle wait time by class nonidle
  • get oracle wait time by class io
  • get oracle wait time by class serial
  • get oracle wait time by event class serial top 4
  • get oracle wait time by event class nonidle top 5
  • get oracle wait time by event name db%file%scat%read%

Stori can also visually show the time using either a pie or big bar chart. For charting command options ask Stori, help get chart. Below is a short dialog drilling down from all Oracle time down to the individual wait events.

How can I help you? get chart bigbar oracle

work/1375485434_oracle_x_BB.png

How can I help you? get chart pie oracle wait

work/1375485454_oracle_wait_x_pie.png

How can I help you? get chart bigbar oracle io

work/1375485470_oracle_io_x_BB.png

How can I help you? get chart bigbar oracle events

work/1375485642_oracle_event_x_BB.png

Database Time

The term "database time" or "DB Time" (for short), is used to describe Oracle CPU consumption time and non idle wait event time for active (non idle) Oracle sessions. Oracle has a statistic named, "DB Time" containing this time. However, Oracle itself does not have enough data and insight to understand if some wait time should truly be considered non idle or idle wait time. This is a problem and why automatic analysis based on DB Time is inherently flawed.

Stori, however, understands this limitation and does not blindly use Oracle's DB Time statistic. Instead, Stori builds a true picture of database time by incorporating what is occurring in the application and the operating system. This is one reason why Stori's automatic analysis capability is so amazing and actually makes sense (not to mention it actually works!)

To get Oracle related database time, use Stori's "get oracle" commands. To get the statistic DB Time (in the default microseconds), use the Stori command, "get stats systimemodel db%time".

Installation Package Body Errors

Here is an example of this error during the initial Stori installation:

DBD::Oracle::db do failed: ORA-04063: package body "STORI.ORAPUB1" has errors
ORA-06508: PL/SQL: could not find program unit being called: "STORI.ORAPUB1"

Stori must be able to execute the DBMS_RANDOM package. Most systems have DBMS_RANDOM as publicly executable. However, there are rare situations where this is not the case.

To test the situation do this:

$ sqlplus oracle_stori_user
SQL> select dbms_random.value from dual;

     VALUE
----------
.608796152

SQL>

If the above statement returns with an error (which did not occur above) then do the following:

		
$ sqlplus sys as sysdba
SQL> grant execute on dbms_random to oracle_stori_user;

Now re-test the situation:

$ sqlplus oracle_stori_user
SQL> select dbms_random.value from dual;

     VALUE
----------
.608796152

SQL>

And the results should look like the above.

Important: Remove the two license files (*key) and restart Stori. Stori will prompt you for licensing and access details.

If you have any questions, contact OraPub Support.

DBD Errors

It is possible, though unlikely, to receive a DBD error if Stori does not recognize your command. The best course of action is to exit Stori and restart. If you do not exit, Stori could possibly return another error even though your command is correct.

Here is a DBD error example:

Use of uninitialized value $show_error in string eq at stori.pl line 226, line 231.
DBD::Oracle::st fetch failed: ERROR fetching field 2 of 2. LOB value truncated from 284 to 80. DBI attribute LongReadLen too small and/or LongTruncOk not set [for Statement " SELECT out_seq_no, out_content FROM op$septure order by out_seq_no "] at stori.pl line 192, line 231.
I do not understand what you're asking. Please try again.

If you get this type of error, exit Stori and restart.

Can't load '/...Oracle.so' cannot open shared object file...

It may look like this:

$ ./storiLin64 
Can't load '/tmp/p2xtmp-17816/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory ...
Here is what to do:

  • Ensure the environment variable, LD_LIBRARY_PATH is set to $ORACLE_HOME/lib. At the OS prompt, type:

         echo $LD_LIBRARY_PATH

    The output will look something like:

         /home/oracle/product/11.2.0/lib

  • If the environment variable is NOT set, set it and restart Stori. Set the environment variable like this:

         export LD_LIBRARY_PATH=$ORACLE_HOME/lib

  • If the environment variable is set, then contact OraPub Support.

Can't load '/...GD/GD.so' cannot open shared object file...

It may look like this:

$ ./storiLin64 
Can't load '/tmp/p2xtmp-16559/auto/GD/GD.so' for module GD: libgd.so.2: cannot open shared object file: No such file or directory ...
Here is what to do: Check out the Core Dump: GD Package FAQ.

Core Dump: GD Package

If Stori core dumps, it is likely the Linux GD package (gd = graphics library) was not installed on your system, where the Stori executable resides.

The Stori executable (the Oracle client process) expects the GD package libraries to exist. To be specific, the GD package must to be installed on the host where the Stori executable is installed.

Here is how to check if the GD package exists on the host where the Stori executable resides.

$ pwd  <== Go to the Stori software distribution directory.
/apps/bob/storiDist/2g

$ ls storiLin* <== Check that the Stori client process exists.
storiLin64 storiLin64exe

$ rpm -q gd <== Use the package manager to check if the GD packaged has been installed.
gd-2.0.35-11.el6.x86_64

In the example above, the package exists. If the GD package does not exist, contact your system administrator, get the package, and install it.

If there remains a problem, then contact OraPub Support.

Stori keeps prompting me for connection details

When Stori is started, it looks for the connection information files in your current directory. These files are called the key files and end with key. Here is a example of you can expect to see:

$ pwd
/home/oracle/storiDist/2k
$ ls *key
storiConnect.key  storiLicense.key
$	

Therefore, before you start Stori change your directory to the Stori software distribution directory. Here is an example:

$ ssh stori@192.168.1.150
stori@192.168.1.150's password: 
Last login: Tue Oct 29 08:46:12 2013 from 192.168.1.3
$ cd storiDist/2k
$ storiLin64

Welcome to StoriFree - Automated. Interactive. Oracle performance analysis.
...