ORISNOOP / Oriole Corporation

Download ORISNOOP_*.tar.Z

Why ORISNOOP?

The empirical 80/20 rule is well known in IT : quite often, 20% of processes account for 80% of activity. With Oracle, you quite often encounter worse cases, for instance 2% of statements accounting for 95% of costs or executions.

The smaller the number of statements involved, the higher the potential gain if you succeed in improving them, even only a little. ORISNOOP can help.

Some statements can put a heavy load on the system because they need to access a huge number of blocks to run, or because they are executed very often, or both. If you succeed in tuning the statements which put the heaviest load on the system, you are sure to improve the overall throughput. However, sometimes some relatively costly but very often executed statements 'hide', as it were, statements which are executed less often but which nonetheless require some tuning. ORISNOOP can help you to identify all of these situations

ORISNOOP

Oracle and SQL*Net are registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

Purpose

ORISNOOP is a utility which identifies the most resource consuming statements running on an Oracle database. It has no pretense to being an 'expert system': it doesn't make any attempt at rewriting statements or suchlike. What it does, is to provide all the information available to pinpoint the, most often surprisingly few, statements which bog down your system, including execution plan, statistics, indexes available and indications about their relevance, hidden costs (such as triggers...) - everything which is required by a reasonably competent SQL programmer to improve performance, often dramatically.

Installing ORISNOOP on your machine

What you download from the Oriole Corporation web-site is a compressed tar file, named ORISNOOP_for_<operating system>.tar.Z

You first have to uncompress it on your machine :

uncompress ORISNOOP_for_<operating system>.tar.Z

then extract the files from the archive (it will create an ORISNOOP directory under the current directory) :

tar xvf ORISNOOP_for_<operating system>.tar

You must then change directory to ORISNOOP where you will find 6 files :

READ.ME
orisnoop.o
orisnoopid.o
liborisnoop.a
mkorisnoop
orisnoop.mk

The free trial version

To use the time limited free trial version you will need a software key which will be issued on request. Please see below under the section 'Buying the license for ORISNOOP' for the information you will need to supply to us.

ORISNOOP operating mode

ORISNOOP has three modes of operation :

it takes a snapshot of all the valid statements currently stored in the SGA, irrespective of how long they have been there. As most statements, even when their execution was very short, stay in the SGA for a long time, this usually gives a fair idea of what happened on the database,

it collects statements for a given period (defined as a parameter), in which case only the statements executed during this period are taken into account for finer tuning,

(Oracle 7.3 and above) it takes a snapshot of all the statements currently executing or most recently executed by the current sessions.

The overhead induced by orisnoop when collecting the statements is fairly negligible, however, the analysis which follows is a relatively heavy process (chiefly because all indexes are validated). Both phases can be run separately and on separate databases if one is a copy of the other; note that the data collected is stored in a (local) file which is removed after the analysis.

Running ORISNOOP

You can control how ORISNOOP runs by entering the orisnoop command followed by various arguments; the program has been designed to give it an Oracle utility look-and-feel, which should give it a flavor familiar to any Oracle DBA.

To specify parameters, you use keywords:

Format: orisnoop KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example:

orisnoop SYSTEM/MANAGER FEEDBACK=Y INTERVAL=AUTO

Keyword	Description (Default)	Keyword	Description (Default)
USERID	username/password	CURRENT	current sessions only (N)
PARFILE	parameter filename	FILE	data file (orisnoop.dat)
HELP	display this screen (N)	COLLECT	collect the data (Y)
TIMESPAN	duration in mn	ANALYZE	analyze the data (Y)
INTERVAL	mn between probes (AUTO)	EXPLAIN	pct of costs analyzed (80)
SORT	LOAD, COST or EXEC (LOAD)	TOP	max statements analyzed (25)
FEEDBACK	indicate activity (N)	KEEPFILE	keep data after analysis (N)

ORISNOOP parameters

ANALYZE

set it to 'N' if you are collecting information on a heavily loaded production database. Data collection does not carry much of an overhead; however, data analysis does. This is why the analysis process can be totaly separated from the data collection, so that you can either schedule it at a later time on the production database, or execute it alone (see COLLECT) on a copy of the production database.

COLLECT

set it to 'N' when you want to analyze, on a copy of the production database, information previously collected on the production database (see ANALYZE).

CURRENT

if set to 'Y', it will collect statements executed by the currently active sessions only (requires Oracle 7.3 or above). This can be useful to try to identify why a program which accesses the database is, or seems to be, stuck.

EXPLAIN

ORISNOOP's purpose is not to provide detailed information about absolutely all the statements but to indicate clearly those which require tuning. Although all statements are collected, it's usually pointless to provide detailed analysis for all of them as, usually, very few statements account for a large share of the costs. ORISNOOP will analyze the smaller number of statements of the number specified by TOP (see explanations for this parameter) and the number of statements which explains at least EXPLAIN % of the total (what the total is depends on the SORT parameter - number of logical reads by default but can also be the number of executions).

FEEDBACK

when set to 'Y', ORISNOOP will display a message on the standard output every time it collects information in the SGA, when it doesn't take a single snapshot (see TIMESPAN) and then a crude progress meter during the analysis phase.

FILE

specifies the name of the file where ORISNOOP will store the information it gathers during its collection phase.

HELP

self-explanatory

INTERVAL

number of minutes between two successive queries of the SGA. As, unless the shared spool is excessively small, statements stay for hours, sometimes days, in the SGA before ageing out and being ousted by other statements, this doesn't need to be set at a very high frequency. The AUTO value is self-regulating : orisnoop raises the frequency whenever it detects a burst of activity and decreases it when all's quiet at the front

KEEPFILE

set this parameter to 'Y' if you want to retain the data file collected by ORISNOOP to execute a new analysis either on a different database (to check a new indexing scheme for instance) or with different parameters (eg for SORT). By default, the data file is deleted after the analysis.

PARFILE

same as with exp/imp

SORT

this will specify which kind of statement you want to see at the top of the list. LOAD, the default value, lists statements by decreasing total number of Oracle blocks accessed (logical reads) to process the query. Statements can put a heavy load on the system because they need to access a huge number of blocks to run, or because they are executed very often, or both. If you succeed in tuning the statements which put the heaviest load on the system, you are sure to improve the overal throughput. However, sometimes some relatively costly but very often executed statements 'hide', as it were, statements which are executed less often but which nonetheless require some tuning. In that case, you can specify SORT=COST to have statements listed by decreasing single-execution average number of blocks accessed. Finally, if you only want to see which statements, even though very efficient, are the most executed, you can specify SORT=EXEC.

TIMESPAN

specifies for how long (from now) you want orisnoop to collect statements in memory. Only statements which were executed in the time interval thus specified will be taken into account. This can be useful to trace a specific batch program.

TOP

specifies the maximum number of statements to analyze in detail (see EXPLAIN).

USERID

DBA username and password. (optionally followed by a connection string, if your license allows you to run across SQL*Net)

Interpreting the ORISNOOP output

ORISNOOP doesn't provide a magic wand which automatically improves everything but it does identify where performance problems lie - usually a complicated and tedious process in itself.

Summary tables

Statistics by type of statement - this shows the various weight of SELECT, INSERT, UPDATE and DELETE statements (plus PL/SQL) merely in terms of the number of different statements, cost (which is the number of Oracle blocks which had to be accessed to process the statements) and number of executions. This can help to make a decision about indexes : more indexes may be beneficial to SELECT statements but will add to the cost of other statements, so it really depends on the proportion. As a general guideline, however, don't add too many indexes and consider that 3 indexes on a single table are a reasonable number and 5 a maximum.

Distribution statistics - this shows what proportion of statements account for what proportion of the total (what this total is, depends on the SORT parameter). The empirical 80/20 rule is well known in IT : quite often, 20% of processes account for 80% of activity. With Oracle, you quite often encounter worse cases, for instance 2% of statements accounting for 95% of costs or executions. The smaller the number of statements involved, the higher the potential gain if you succeed in improving them, even only a little.

Top statements summary - this lists (with direct URLs to details) the top consuming statements.

We can have three cases for a high total cost query :

Elementary cost	Executions
High	High	Ideal case ! If you manage to improve the elementary cost, the result will be dramatic.
Very high	Low	Not very good. We could improve the query a lot and it still wouldn't have a major impact in terms of throughput. However, if it concerns a very important process, the improvement may be quite noticeable and, if nothing else, impress the end-user.
Low	Very high	If the elementary cost is very low (5 and under) there is unfortunately not much which can be done (reviewing the algorithms, perhaps). However, updates, inserts and deletes may be improved by eliminating useless indexes (see below). A low to medium cost can however still provide scope for spectacular improvement, as a gain of a few cost units can bring a huge overall boost of performance.

Statement details

For each statement, the text is displayed - note that ORISNOOP automatically recognizes that statements in which only constant values are different are basically the same (although of course specific constant values can yield quite different response times), so the text is rather a sample or pattern -, the execution plan is given, hidden costs (indexes to update/triggers) are shown where appropriate and a bevy of details is given for all tables involved in the statement.

Tables :

Full scan cost : this is the number of blocks used to store the table, so it is, at most, the number of blocks we have to access when doing a full scan (at most because everything which is allocated may, and quite often does not, contain data).

Indexes :

Avg search cost : number of blocks to access when we select with a condition such as WHERE KEY=value, where KEY is the indexed column (one or several of course). Usually there are 2 or 3 blocks accessed to go down the index (a tree), and then there is a number of accesses to the data blocks in the table which contains values which match the key. If the index is very selective (a unique index is of course the most selective) very few lines in the table match the key and therefore you will have to access one, or perhaps two, data blocks to return all you need. On the other hand, if the index is not selective at all (such as an index on two values which both occur roughly equally) using the index will mean accessing all the blocks in the table (as a block usually contains several rows, you will find lines containing all possible values), usually several times each (there is no reason why lines in the same data block should have their addresses clustered together in the index), plus the additional (admittedly relatively minor) cost of accessing the index itself. Quite often, people who do tuning are quite obsessed by full scans and try to suppress all of them.

In fact, it is quite common that the full scan cost is less than the average index search cost (experiments show that the limit is around 7 different key values - under this, full scan always costs less). This is why, when an index is not unique, there is the 'one key value = n% of rows on average' to see whether the index is really useful (up to 10% it is OK, but the bigger the table, the lower the limit).

However, distribution may blur the picture, since if some often queried values match a very limited number of lines, although on the face of it and on average the index would seem to be totally uninteresting, it can be in practice very efficient because the values are unevenly distributed. Hence the distribution table, which gives you 'key%' and '% of rows matched'. If you have key values which match a very small percentage of the rows, the index may be useful - if most WHERE clauses actually refer to these key values.

General guidelines :

The idea is to give you a complete picture of what exists and to compare it with what is used. If indexes are inefficient and unused, drop them : it will speed up inserts, updates and deletes. If they are efficient and unused, try an alternative way to write the query and possibly try using 'Hints' (refer to Oracle specific tuning documentation)

It is quite difficult to say here what does and what doesn't work, it's mainly trial and error.
Things to watch for :

NOT IN (replace by NOT EXISTS, or INTERSECT or MINUS...)

DISTINCT and a join - usually indicates loose programming. Replace by EXISTS

EXISTS can sometimes be replaced by joins themselves.

The usual functions applied to indexed columns,

Concatenated indexes built the wrong way. If the information is available, ORISNOOP will display the number of distinct values for each column in a concatenated index. A quite common error is to put the less selective (i.e. columns with the fewer number of distinct values) at the beginning of the index, when they should come at the end.

etc.

Buying the license for ORISNOOP

To run the fully functional orisnoop, you need to buy a license from Oriole Corporation; orisnoop is protected by a software key (a string of digits and letters) which will be forwarded to you on receipt of your payment. As this key depends on the machine on which you will run orisnoop, you must provide Oriole Corporation with:

1. your identifier.
This identifier is a hexadecimal number returned by orisnoopid, which is built at the same time as orisnoop.
2. the license class you want for orisnoop.
Two license classes are available :

class 1 allows you to monitor as many databases as you want on the machine where orisnoop was installed,
class 2 allows you to monitor databases across SQL*Net as well.

Once you have received your software key, you just have to set the environment variable ORISNOOP_KEY to this value, by a command such as :

ORISNOOP_KEY=<software key>; export ORISNOOP_KEY
(Bourne shell)

setenv ORISNOOP_KEY <software key>
(C shell)

This is best done in a command file which is run when you log on, such as .profile, .login or .cshrc, depending on your environment. Once the environment variable is set, you can enjoy orisnoop in all its glory.

Download ORISNOOP_*.tar.Z