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 percentage calculations manually
After you have identified the candidate SQL statements, the next stage is to gather
information that is necessary to examine the statements and tune them.

調試一個應用/減少負載
如果整個應用表現的是最佳化的狀態，或者如果你在嘗試減少資料庫伺服器的CPU或者IO負載，那麼你可以用如下的方式來識別
消耗資源的sql語句：
1、確定在一天的哪個時間段來抽樣資料，一般選擇在應用最繁忙的時間段。
2、在那天的初始和結尾抓取作業系統的、資料庫的資訊。我們至少要包含這些資訊，
系統的IO資訊（v$filestat)，系統資訊（v$sysstat)，sql語句的資訊（v$sqlarea,v$sql或者v$sqlstats,v$sqltext,v$sqlstat,
v$sql_plan以及v$sql_plan_statistics)
3、使用第二部收集到的資訊，定位最耗資源的sql語句。一個比較好的方法來識別待甄選的SQL語句的方法是查詢
v$sqlstats. v$sqlstats 包含了在共用池中所有sql語句的資源使用資訊。在V$SQLSTATS中的資訊應該根據資源的使用
來進行排序，而最常見的資源套件括：
Buffer gets(V$SQLSTATS.BUFFER_GETS,針對高CPU利用率的語句)
Disk reads(V$SQLSTATS.DISK_READS,針對高I/O的語句）
Sorts（V$SQLSTATS.SORTS,針對sort 操作多的語句）
識別產生最高負載的sql語句的方法是，讓某個sql語句消耗的資源和所有的資源進行比較。
針對buffer_gets，讓每個sql語句消耗的buffer_gets除以這段時間消耗的buffer_gets的總量。

系統中所有的buffer gets資訊存在v$sysstat這個表中，針對的是靜態邏輯讀。
相似的，我們可以用某個語句導致的disk讀（v$sql_stats.disk_reads)除以這段時間的系統disk讀(v$sysstat)。
AWR 報告的 SQL部分將包含這些資訊，所以你不需要手工來計算。
當你獲得了待選語句之後，你可以進一步抓取資訊並調試這些語句。

Gathering Data on the SQL Identified
If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL
statement that performed the most DISK_READS.
Information to Gather During Tuning
The tuning process begins by determining the structure of the underlying tables and
indexes. The information gathered includes the following:
1. Complete SQL text from V$SQLTEXT
2. Structure of the tables referenced in the SQL statement, usually by describing the
table in SQL*Plus
3. Definitions of any indexes (columns, column orderings), and whether the indexes
are unique or non-unique
4. Optimizer statistics for the segments (including the number of rows each table,
selectivity of the index columns), including the date when the segments were last
analyzed
5. Definitions of any views referred to in the SQL statement
6. Repeat steps two, three, and four for any tables referenced in the view definitions
found in step five
7. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN,
or the TKPROF output)
8. Any previous optimizer plans for that SQL statement

進一步抓取資訊
如果你最關心的是CPU，那麼檢查這段時間裡最消耗BUFFER_GETS的語句。
或者，選擇導致最多磁碟讀操作的語句。