Tuning a Simple SQL Statement; What to Consider

[vaillan]

I recently reviewed a gather info report from a site which has a around 1000 registered users and a peak concurrency of around 100 users a day.  The SQL statement below stuck out like a sore thumb in the report as needing to be tuned; it was the last statement in both the "Report End: Worst Total SQL by CPU Time" & "Report End: Worst SQL By Factor" sections of the the report.txt file.  The statement had been executed more than 1500 times, with an average elapsed time of 11 seconds for each execution.  The statement had a cumulative elapsed time of 17,535 seconds which was almost five hours users of the system spent waiting for this one statement to complete.  But the statement only had a CPU time of 1,118 seconds suggesting there was a significant delay which on analysis was determined to be 40 million disk I/O reads.  Any statement that performs excessive disk I/O reads inhibits the overall system scalability in two ways:

• First by using a disproportionate amount finite disk I/O resources available, other operations may need to wait or are delayed because of over use of resources by one statement. 
• The second way is by is by forcing out cached table index blocks from memory forcing them to be re-read (incurring additional disk I/O) delaying the next SQL statements which will need to wait for the I/O to happen rather than getting a more immediate response if the blocks had already been cached in memory.

When looking to improve the performance of a SQL statement several things to initially consider are:

• How many rows & blocs are in the table.  Does it make sense based on this information to look evaluate adding an index?
  • In this case the table had almost 500k rows, and 26k table blocks with an 8k block size.  It was reasonable to assume that a table of this size could cause performance problems and has enough rows that adding an index could be justified.  In some reports high disk I/O tables have very a modest number of rows, maybe 2K rows or fewer.  In other cases there is sometimes an extreme shortage of SGA available (see this link on how to do a quick SGA check) or if there is a large amount of empty space in the table which is caused by row deletion.  To tell if the table is made up of a lot more blocks than are necessary check and a table should be rebuilt, use a this formula like the following (the numbers required for this equation are in the gather info report output):
    • The number of table rows (NUM_ROWS)* average row length (AVG_ROW_LEN)
      • should be about equal (or within 10-15%)
    • of the number of table BLOCKS * db_block_size

• Are all of the necessary indexes present to ensure optimal execution of the statement?
  • There are several different reasons to make an index:
    1. The most common reason reason is to reduce I/O by allowing Oracle to more quickly identify the table rows which need to be returned to the user making the request.
    2. The next most common reason is to keep Oracle from needing to access the table when the statement is executing by having all of the columns in the SQL statement contained within the index.  This type of index can work very well, but can have a serious downside if not done correctly which I'll discuss in a future blog post
    3. The index is created to give Oracle better information about the columns in the where clause.  This is the least common type of index to create, and requires an in depth understanding of the understanding of the current execution plan and why it's incorrect.

In this case I suspected since there was only one table in the FROM clause the index would likely fall into the first category of reducing disk I/O, if there been only a few columns in the 'select' part of the statement adding the second type of index might be worth considering.  If the right index had existed for this statement (which it didn't) and was then not being used, it's possible the third type of index would have been considered.

In this case as in most cases when I tuning a SQL initial statement the steps I followed for a statement are:

1. Bring the SQL statement into a new file, reformat it, include the existing table index definitions along with the table's statistics.  The index definitions and table statistics are in the gather info report output.  An example of this out below (reformatted and abbreviated to fit)
2. Determine if the 'right' index or indexes exist for the statement.  Start with the input conditions to the WHERE clause, which in this case are the idA3A2A5 & markForDeleteA2 columns.
3. Based on the existing indexes for the table, it was clear neither of these columns was part of any index.  The question is then which if either column should be indexed and if it's both columns should be in the same index which order should the columns be included in the index definition?
4. Using the table column statistics, I could see:
   • There was one distinct value for the markForDeleteA2 column, an index on this column would not decrease the number of table blocks Oracle needed to access and therefore this column for a type #1 index should not be included in an index
   • There are 13770 distinct values for the idA3A2A5 column, compared against the distinct values for the ida2a2 column (which is the Primary Key for all Windchill tables, the number of distinct values in the ida2a2 should/will be the same as the number of table rows) indicates that for one input value, there will be 392344/13770 ~= 29 rows returned on average.  Oracle should prefer to access the index and then at most 29 table blocks (compared to the 26k total number of table blocks the table is made up from) instead of performing a full tablescan which is what should be happening now.
     • Note: wether Oracle uses an index or not depends on large number of things and there is no guarantee any index will be used
5. Creating an index on idA3A2A5 is a reasonable next step based on the analysis.
   • create index EPMDocConfigSpec$idA3A2A5 on EPMDocConfigSpec(idA3A2A5);
     
     

SQL Statement to tune:

Select 'wt.epm.EPMDocConfigSpec',A0.administrativeLockIsNull,A0.typeadministrativeLock,A0.asStoredActive,A0.asStoredConfig,

A0.baselineIsNull,A0.classnamekeyA2A5,A0.idA3A2A5,A0.baselineActive,A0.blob$asStoredConfig,A0.blob$sandboxConfig,A0.classnamekeydomainRef,

A0.idA3domainRef,A0.effectivityIsNull,A0.effectiveConfigItemRefIsNull,A0.classnamekeyA2D5,A0.idA3A2D5,TO_CHAR(A0.effectiveDateD5,'dd mm yyyy hh24:mi:ss'),

A0.effectiveUnitD5,A0.effectivityActive,A0.eventSet,A0.folderedIsNull,A0.classnamekeyA2C5,A0.idA3A2C5,A0.folderedActive,A0.inheritedDomain,A0.lifeCycleIsNull,

A0.lifeCycleStateB5,A0.lifeCycleActive,A0.classnamekeyA2ownership,A0.idA3A2ownership,A0.promotionNoticeIsNull,A0.baselineConfigSpecIsNullE5,A0.classnamekeyA2A2E5,

A0.idA3A2A2E5,A0.classnamekeyB2E5,A0.idA3B2E5,A0.promotionNoticeActive,A0.sandboxActive,A0.sandboxConfig,A0.securityLabels,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),

A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss'),A0.workingIncluded

FROM EPMDocConfigSpec A0

WHERE ((A0.idA3A2A5 = :1 ))

   AND (A0.markForDeleteA2 =0)

Indexes Available:

TABLE_NAME                     INDEX_NAME                               COLUMN_NAME      COLUMN_POSITION           DISTINCT_KEYS

------------------------------           ------------------------------                          ------------                     ---------------                          -------------

EPMDOCCONFIGSPEC       PK_EPMDOCCONFIGSPEC            IDA2A2                           1                                      392344

Table Statistics:

TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	DENSITY	NUM_NULLS
------------------------------	--------------------------------	------------	----------	----------
EPMDOCCONFIGSPEC	ASSTOREDACTIVE	2	0.5	0
EPMDOCCONFIGSPEC	ASSTOREDCONFIG	146880	6.81E-06	93516
EPMDOCCONFIGSPEC	BASELINEACTIVE	2	0.5	0
EPMDOCCONFIGSPEC	BASELINEISNULL	1	1	360562
EPMDOCCONFIGSPEC	CLASSNAMEA2A2	1	1	0
EPMDOCCONFIGSPEC	CLASSNAMEKEYA2A5	2	0.5	360562
EPMDOCCONFIGSPEC	CLASSNAMEKEYA2C5	1	1	389118
EPMDOCCONFIGSPEC	CLASSNAMEKEYDOMAINREF	1	1	0
EPMDOCCONFIGSPEC	CREATESTAMPA2	358016	2.79E-06	0
EPMDOCCONFIGSPEC	EFFECTIVEDATED5	110	0.009090909	389118
EPMDOCCONFIGSPEC	EFFECTIVITYACTIVE	1	1	0
EPMDOCCONFIGSPEC	EFFECTIVITYISNULL	1	1	389118
EPMDOCCONFIGSPEC	FOLDEREDACTIVE	1	1	0
EPMDOCCONFIGSPEC	FOLDEREDISNULL	1	1	389118
EPMDOCCONFIGSPEC	IDA2A2	392344	2.55E-06	0
EPMDOCCONFIGSPEC	IDA3A2A5	13770	0.000072622	360562
EPMDOCCONFIGSPEC	IDA3A2C5	4	0.25	389118
EPMDOCCONFIGSPEC	IDA3A2OWNERSHIP	1	1	0
EPMDOCCONFIGSPEC	IDA3DOMAINREF	25	0.04	0
EPMDOCCONFIGSPEC	INHERITEDDOMAIN	1	1	0
EPMDOCCONFIGSPEC	LIFECYCLEACTIVE	2	0.5	0
EPMDOCCONFIGSPEC	LIFECYCLEISNULL	1	1	389114
EPMDOCCONFIGSPEC	LIFECYCLESTATEB5	2	0.5	389114
EPMDOCCONFIGSPEC	MARKFORDELETEA2	1	1.29E-06	0
EPMDOCCONFIGSPEC	MODIFYSTAMPA2	358080	2.79E-06	0
EPMDOCCONFIGSPEC	PROMOTIONNOTICEACTIVE	1	1	0
EPMDOCCONFIGSPEC	SANDBOXACTIVE	2	0.5	0
EPMDOCCONFIGSPEC	SANDBOXCONFIG	2	0.5	392342
EPMDOCCONFIGSPEC	UPDATECOUNTA2	6	1.29E-06	0
EPMDOCCONFIGSPEC	UPDATESTAMPA2	358080	2.79E-06	0
EPMDOCCONFIGSPEC	WORKINGINCLUDED	1	1	0