sql调优之 寻找我们最感兴趣的语句

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:
■ Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)
■ Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)
■ 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 percentag