Identifying High-Load SQL

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i26072

High-load SQL statements can be identified by:

·         Automatic Database Diagnostic Monitor

·         Automatic Workload Repository

·         V$SQL view

·         Custom Workload

·         SQL Trace

If you are tuning a specific program , then identifying the SQL to examine is a simple matter of looking at the SQL executed within the program.
If it is not possible to identify the SQL (for example, the SQL is generated dynamically), then use SQL_TRACE to generate a trace file that contains the SQL executed, then use TKPROF to generate an output file.
The SQL statements in the TKPROF output file can be ordered by various parameters, such as the execution elapsed time (exeela), which usually assists in the identification by ordering the SQL statements by elapsed time (with highest elapsed time SQL statements at the top of the file). This makes the job of identifying the poorly performing SQL easier if there are many SQL statements in the file.

Tuning an Application / Reducing Load

If your whole application is performing suboptimally, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:

1.      Determine which period in the day you would like to examine; typically this is the application's peak processing time.

2.      Gather operating system and Oracle statistics at the beginning and end of that period. The minimum of Oracle statistics gathered should be file I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS).

3.      Using the data collected in step two, identify the SQL statements using the most resources. A good way to identify candidate SQL statements is to query V$SQLSTATS. V$SQLSTATS contains resource usage information for all SQL statements in the shared pool. The data in V$SQLSTATS should be ordered by resource usage. The most common resources are:

o        Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)

o        Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)

o        Sorts (V$SQLSTATS.SORTS, for many sorts)

One method to identify which SQL statements are creating the highest load is to compare the resources used by a SQL statement to the total amount of that resource used in the period. For BUFFER_GETS, divide each SQL statement's BUFFER_GETS by the total number of buffer gets during the period. The total number of buffer gets in the system is available in the V$SYSSTAT table, for the statistic session logical reads.

Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_STATS.DISK_READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.