溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--Oracle ADDM
應用環境：
操作系統： RedHat EL55
Oracle： Oracle 10gR2
一、ADDM簡介
在Oracle9i及之前，DBA們已經擁有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。這些工具能夠幫助DBA很快的定位性能問題。但這些工具都只給出一些統計數據，然后再由DBA們根據自己的經驗進行優化。
那能不能由機器自動在統計數據的基礎上給出優化建議呢？Oracle10g中就推出了新的優化診斷工具：數據庫自動診斷監視工具（Automatic Database Diagnostic Monitor ADDM）和SQL優化建議工具（SQL Tuning Advisor STA）。這兩個工具的結合使用，能使DBA節省大量優化時間，也大大減少了系統宕機的危險。簡單點說，ADDM就是收集相關的統計數據到自動工作量知識庫(Automatic Workload Repository AWR)中，而STA則根據這些數據，給出優化建議。例如，一個系統資源緊張，出現了明顯的性能問題，由以往的辦法，做個一個statspack快照，等30分鐘，再做一次。查看報告，發現’ db file scattered read’事件在top 5 events里面。根據經驗，這個事件一般可能是因為缺少索引、統計分析信息不夠新、熱表都放在一個數據文件上導致IO爭用等原因引起的。根據這些經驗，我們需要逐個來定位排除，比如查看語句的查詢計劃、查看user_tables的last_analysed子段，檢查熱塊等等步驟來最后定位出原因，并給出優化建議。但是，有了STA以后，它就可以根據ADDM采集到的數據直接給出優化建議，甚至給出優化后的語句（搶了DBA的飯碗嘍）。
ADDM能發現定位的問題包括：
操作系統內存頁入頁出問題 由于Oracle負載和非Oracle負載導致的CPU瓶頸問題 導致不同資源負載的Top SQL語句和對象——CPU消耗、IO帶寬占用、潛在IO問題、RAC內部通訊繁忙 按照PLSQL和JAVA執行時間排的Top SQL語句. 過多地連接 (login/logoff). 過多硬解析問題——由于shared pool過小、書寫問題、綁定大小不適應、解析失敗原因引起的。 過多軟解析問題 索引查詢過多導致資源爭用. 由于用戶鎖導致的過多的等待時間 (通過包dbms_lock加的鎖) 由于DML鎖導致的過多等待時間(例如鎖住表了) 由于管道輸出導致的過多等待時間(如通過包dbms_pipe.put進行管道輸出) 由于并發更新同一個記錄導致的過多等待時間(行級鎖等待) 由于ITL不夠導致的過多等待時間（大量的事務操作同一個數據塊） 系統中過多的commit和rollback(logfile sync事件). 由于磁盤帶寬太小和其他潛在問題（如由于logfile太小導致過多的checkpoint，MTTR設置問題，過多的undo操作等等）導致的IO性能問題I 對于DBWR進程寫數據塊，磁盤IO吞吐量不足 由于歸檔進程無法跟上redo日至產生的速度，導致系統變慢 redo數據文件太小導致的問題 由于擴展磁盤分配導致的爭用 由于移動一個對象的高水位導致的爭用問題 內存太小問題——SGA Target, PGA, Buffer Cache, Shared Pool 在一個實例或者一個機群環境中存在頻繁讀寫爭用的熱塊 在一個實例或者一個機群環境中存在頻繁讀寫爭用的熱對象 RAC環境中內部通訊問題 LMS進程無法跟上導致鎖請求阻塞 在RAC環境中由于阻塞和爭用導致的實例傾斜 RMAN導致的IO和CPU問題 Streams和AQ問題 資源管理等待事件
有一點要記住：AWR收集的數據時放到內存中（share pool），通過一個新的后臺進程MMON定期寫到磁盤中。所以10g的share pool要求比以前版本更大，一般推薦比以前大15-20%。另外，還要求系統參數STATISTICS_LEVEL設置為TYPICAL（推薦）或ALL;
ALTER SESSION SET STATISTICS_LEVEL= TYPICAL;
二、案例：
1、采集AWR Snapshot
SQL> begin
2 dbms_workload_repository.create_snapshot('TYPICAL');
3 end;
4 /2、運行事務
scott用戶執行一個資源消耗高的事務：
15:14:47 SCOTT@ prod>begin
15:34:41 2 for i in 1..1000000 loop
15:34:41 3 execute immediate 'insert into scott.t1 values ('||i||')';
15:34:41 4 end loop;
15:34:41 5 end;
15:34:41 6 /tom用戶同時執行一個資源消耗高的事務：
15:34:45 TOM@ prod>begin
15:34:49 2 for i in 1..1000000 loop
15:34:49 3 execute immediate 'insert into scott.t1 values ('||i||')';
15:34:49 4 end loop;
15:34:49 5 end;
15:34:49 6 /3、再次采集AWR Snapshot
SQL> begin
2 dbms_workload_repository.create_snapshot('TYPICAL');
3 end;
4 /4、查詢生成的快照
15:37:57 SYS@ prod> select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc 190 15-AUG-14 03.18.34.663 PM 15-AUG-14 03.36.36.686 PM 191 15-AUG-14 03.36.36.686 PM 15-AUG-14 03.37.18.352 PM 192 15-AUG-14 03.37.18.352 PM 15-AUG-14 03.40.17.649 PM 193 15-AUG-14 03.40.17.649 PM 15-AUG-14 03.42.38.632 PM 12 rows selected.
5、創建優化任務并執行
15:51:01 SYS@ prod>DECLARE
15:51:57 2 task_name VARCHAR2(30) := 'DEMO_ADDM03';
15:51:57 3 task_desc VARCHAR2(30) := 'ADDM Feature Test';
15:51:57 4 task_id NUMBER;
15:51:57 5 BEGIN
15:51:57 6 dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
15:51:57 7 dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 192);
15:51:57 8 dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 193);
15:51:58 9 dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
15:51:58 10 dbms_advisor.set_task_parameter(task_name, 'DB_ID', 199802235);
15:51:58 11 dbms_advisor.execute_task(task_name);
15:51:58 12 END;
15:51:59 13 /
PL/SQL procedure successfully completed. 其中，set_task_parameter是用來設置任務參數的。START_SNAPSHOT是起始快照ID，END_SNAPSHOT是結束快照ID，INSTANCE是實例號，對于單實例，一般是1，在RAC環境下，可以通過查詢視圖v$instance得到，DB_ID是數據庫的唯一識別號，可以通過查詢v$database查到。
6、查看優化建議結果
15:53:18 SYS@ prod>SELECT dbms_advisor.get_task_report('DEMO_ADDM03','TEXT', 'ALL') FROM DUAL;
DETAILED ADDM REPORT FOR TASK 'DEMO_ADDM03' WITH ID 1012
--------------------------------------------------------
Analysis Period: 15-AUG-2014 from 15:40:18 to 15:42:39
Database ID/Instance: 199802235/1
Database/Instance Names: PROD/prod
Host Name: rh65
Database Version: 10.2.0.1.0
Snapshot Range: from 192 to 193
Database Time: 305 seconds
Average Database Load: 2.2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (305 seconds)
------------------------------------
Host CPU was a bottleneck and the instance was consuming 88% of the host CPU.
All wait times will be inflated by wait for CPU.
RECOMMENDATION 1: Host Configuration, 100% benefit (305 seconds)
ACTION: Consider adding more CPUs to the host or adding instances
serving the database on other hosts.
ACTION: Also consider using Oracle Database Resource Manager to
prioritize the workload from various consumer groups.
RECOMMENDATION 2: Application Analysis, 33% benefit (101 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please
refer to other findings in this task about parsing for further
details.
ADDITIONAL INFORMATION:
Host CPU consumption was 100%. CPU runqueue statistics are not
available from the host's OS. This disables ADDM's ability to estimate
the impact of this finding. The instance spent significant time on CPU.
However, there were no predominant SQL statements responsible for the
CPU load.
FINDING 2: 96% impact (294 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 92% benefit (280 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
RECOMMENDATION 2: SQL Tuning, 2.5% benefit (8 seconds)
ACTION: Investigate the SQL statement with SQL_ID "7ng34ruy5awxq" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7ng34ruy5awxq and
PLAN_HASH 3992920156
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prop
erty,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.
lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataob
j#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i
.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0)
,nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres
$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cacheh
it,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled,
min(cols) unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
RATIONALE: SQL statement with SQL_ID "7ng34ruy5awxq" was executed 596
times and had an average elapsed time of 0.012 seconds.
RECOMMENDATION 3: SQL Tuning, 2.1% benefit (6 seconds)
ACTION: Investigate the SQL statement with SQL_ID "0k8522rmdzg4k" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 0k8522rmdzg4k and
PLAN_HASH 2057665657
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and
privilege#>0
RECOMMENDATION 4: SQL Tuning, 2% benefit (6 seconds)
ACTION: Use bigger fetch arrays while fetching results from the SELECT
statement with SQL_ID "7ng34ruy5awxq".
RELEVANT OBJECT: SQL statement with SQL_ID 7ng34ruy5awxq and
PLAN_HASH 3992920156
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prop
erty,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.
lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataob
j#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i
.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0)
,nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres
$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cacheh
it,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled,
min(cols) unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
FINDING 3: 49% impact (149 seconds)
-----------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 49% benefit (149 seconds)
ACTION: Investigate application logic to keep open the frequently used
cursors. Note that cursors are closed by both cursor close calls and
session disconnects.
RECOMMENDATION 2: DB Configuration, 49% benefit (149 seconds)
ACTION: Consider increasing the maximum number of open cursors a session
can have by increasing the value of parameter "open_cursors".
ACTION: Consider increasing the session cursor cache size by increasing
the value of parameter "session_cached_cursors".
RATIONALE: The value of parameter "open_cursors" was "300" during the
analysis period.
RATIONALE: The value of parameter "session_cached_cursors" was "20"
during the analysis period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
FINDING 4: 32% impact (97 seconds)
----------------------------------
Hard parsing of SQL statements was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Hard parses due to cursor environment mismatch were not consuming
significant database time.
Hard parsing SQL statements that encountered parse errors was not
consuming significant database time.
Hard parses due to literal usage and cursor invalidation were not
consuming significant database time.
The SGA was adequately sized.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
FINDING 5: 2.6% impact (8 seconds)
----------------------------------
Session connect and disconnect calls were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 2.6% benefit (8 seconds)
ACTION: Investigate application logic for possible reduction of connect
and disconnect calls. For example, you might use a connection pool
scheme in the middle tier.
FINDING 6: 2.2% impact (7 seconds)
----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 2.2% benefit (7 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
FINDING 7: 1.8% impact (5 seconds)
----------------------------------
Buffer cache writes due to small log files were consuming significant database
time.
RECOMMENDATION 1: DB Configuration, 1.8% benefit (5 seconds)
ACTION: Increase the size of the log files to 188 M to hold at least 20
minutes of redo information.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
FINDING 8: 1.2% impact (4 seconds)
----------------------------------
Undo I/O was a significant portion (59%) of the total database I/O.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
FINDING 9: 1% impact (3 seconds)
--------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 1% benefit (3 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 18 K per second for reads and 74 K per second for
writes. The average response time for single block reads was 19
milliseconds.
RECOMMENDATION 2: Host Configuration, 1% benefit (3 seconds)
ACTION: The performance of file
/u01/app/oracle/oradata/prod/system01.dbf was significantly worse
than other files. If striping all files using the SAME methodology is
not possible, consider striping this file over multiple disks.
RELEVANT OBJECT: database file
"/u01/app/oracle/oradata/prod/system01.dbf"
RATIONALE: The average response time for single block reads for this
file was 20 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(2.1% impact [6 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TERMINOLOGY
-----------
DATABASE TIME: This is the ADDM's measurement of throughput. From the user's
point of view: this is the total amount of time spent by users waiting for
a response from the database after issuing a call (not including
networking). From the database instance point of view: this is the total
time spent by forground processes waiting for a database resource (e.g.,
read I/O), running on the CPU and waiting for a free CPU (run-queue). The
target of ADDM analysis is to reduce this metric as much as possible,
thereby reducing the instance's response time.
AVERAGE DATABASE LOAD: At any given time we can count how many users (also
called 'Active Sessions') are waiting for an answer from the instance. This
is the ADDM's measurement for instance load. The 'Average Database Load' is
the average of the the load measurement taken over the entire analysis
period. We get this number by dividing the 'Database Time' by the analysis
period. For example, if the analysis period is 30 minutes and the 'Database
Time' is 90 minutes, we have an average of 3 users waiting for a response.
IMPACT: Each finding has an 'Impact' associated with it. The impact is the
portion of the 'Database Time' the finding deals with. If we assume that
the problem described by the finding is completely solved, then the
'Database Time' will be reduced by the amount of the 'Impact'.
BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM
analysis estimates that the 'Database Time' can be reduced by the 'benefit'
amount if all the actions of the recommendation are performed.
Elapsed: 00:00:00.517、診斷分析結果
我們從上面的建議結果看到了，ADDM Report的結果與Statspack Report的結果大不相同。Statspack Report的結果給出的都是統計數據、各種事件，然后由DBA根據這些數據給出優化建議，而ADDM Report的結果包含就已經是給出的優化建議了
第一部分：
Analysis Period: 15-AUG-2014 from 15:40:18 to 15:42:39 Database ID/Instance: 199802235/1 Database/Instance Names: PROD/prod Host Name: rh65 Database Version: 10.2.0.1.0 Snapshot Range: from 192 to 193 Database Time: 305 seconds Average Database Load: 2.2 active sessions
這一部分包括一些基礎信息，分析時間段、DB和instance ID&名字、主機名字、Oracle版本、快照范圍、數據庫消耗時間、多少個活動會話。
第二部分：
下面就是ADDM發現的問題，并給出的相應建議。在我們這個例子中總共發現9個問題，下面一一解釋一下。
第一個問題：
FINDING 1: 100% impact (305 seconds)
------------------------------------
Host CPU was a bottleneck and the instance was consuming 88% of the host CPU.
All wait times will be inflated by wait for CPU.
RECOMMENDATION 1: Host Configuration, 100% benefit (305 seconds)
ACTION: Consider adding more CPUs to the host or adding instances
serving the database on other hosts.
ACTION: Also consider using Oracle Database Resource Manager to
prioritize the workload from various consumer groups.
RECOMMENDATION 2: Application Analysis, 33% benefit (101 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please
refer to other findings in this task about parsing for further
details.
ADDITIONAL INFORMATION:
Host CPU consumption was 100%. CPU runqueue statistics are not
available from the host's OS. This disables ADDM's ability to estimate
the impact of this finding. The instance spent significant time on CPU.
However, there were no predominant SQL statements responsible for the
CPU load.
這個問題的描述是，實例消耗的CPU事件占據了大量的數據庫運行時間。ADDM給了兩個建議
建議一：由于占用了大量的CPU資源，Oracle建議添加更多的CPU，或者使用Oracle Resource Manager進行資源的優化
建議二：Parse占用了大量的CPU時間，建議對sql語句進行優化，減少parse占用的cpu時間
第二個問題：
FINDING 2: 96% impact (294 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 92% benefit (280 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
Oracle建議對“ execute immediate 'insert into scott.t1 values ('||i||')'; ”語句進行優化，可以將性能提高92%，這個sql也是影響我們性能的最主要的語句。
第三個問題：
FINDING 3: 49% impact (149 seconds)
-----------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 49% benefit (149 seconds)
ACTION: Investigate application logic to keep open the frequently used
cursors. Note that cursors are closed by both cursor close calls and
session disconnects.
RECOMMENDATION 2: DB Configuration, 49% benefit (149 seconds)
ACTION: Consider increasing the maximum number of open cursors a session
can have by increasing the value of parameter "open_cursors".
ACTION: Consider increasing the session cursor cache size by increasing
the value of parameter "session_cached_cursors".
RATIONALE: The value of parameter "open_cursors" was "300" during the
analysis period.
RATIONALE: The value of parameter "session_cached_cursors" was "20"
during the analysis period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
問題三，Oracle建議減少軟解析的次數，對經常調用的cursor進行cache；可以調整“ session_cached_cursors"， "open_cursors"等參數。
第四個問題：
FINDING 4: 32% impact (97 seconds)
----------------------------------
Hard parsing of SQL statements was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Hard parses due to cursor environment mismatch were not consuming
significant database time.
Hard parsing SQL statements that encountered parse errors was not
consuming significant database time.
Hard parses due to literal usage and cursor invalidation were not
consuming significant database time.
The SGA was adequately sized.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
減少硬解析次數，使用綁定變量或者調整library cache的size
第五個問題：
FINDING 5: 2.6% impact (8 seconds)
----------------------------------
Session connect and disconnect calls were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 2.6% benefit (8 seconds)
ACTION: Investigate application logic for possible reduction of connect
and disconnect calls. For example, you might use a connection pool
scheme in the middle tier.
Oracle建議通過中間件建立connection pool減少會話連接的資源消耗
第六個問題：
FINDING 6: 2.2% impact (7 seconds)
----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 2.2% benefit (7 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
Oracle建議對“ execute immediate 'insert into scott.t1 values ('||i||')'; ”語句進行優化
第七個問題：
FINDING 7: 1.8% impact (5 seconds)
----------------------------------
Buffer cache writes due to small log files were consuming significant database
time.
RECOMMENDATION 1: DB Configuration, 1.8% benefit (5 seconds)
ACTION: Increase the size of the log files to 188 M to hold at least 20
minutes of redo information.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
由于日志切換比較頻繁，Oracle建議調整redo size；建議調整為：Increase the size of the log files to 188 M to hold at least 20 minutes of redo information.
第八個問題：
FINDING 8: 1.2% impact (4 seconds)
----------------------------------
Undo I/O was a significant portion (59%) of the total database I/O.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
Oracle建議調整undo datafile的I/O
第九個問題：
FINDING 9: 1% impact (3 seconds)
--------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 1% benefit (3 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 18 K per second for reads and 74 K per second for
writes. The average response time for single block reads was 19
milliseconds.
RECOMMENDATION 2: Host Configuration, 1% benefit (3 seconds)
ACTION: The performance of file
/u01/app/oracle/oradata/prod/system01.dbf was significantly worse
than other files. If striping all files using the SAME methodology is
not possible, consider striping this file over multiple disks.
RELEVANT OBJECT: database file
"/u01/app/oracle/oradata/prod/system01.dbf"
RATIONALE: The average response time for single block reads for this
file was 20 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(2.1% impact [6 seconds])調整磁盤的I/O,可以通過strip，來對磁盤I/O進行優化；建議對system表空間進行I/O優化，可以通過條帶化將數據存儲到多個磁盤上。
8、使用STA來優化語句
ADDM得出了診斷結果，并給出了優化建議。通常90%的性能問題都是由于應用引起的，而應用問題肯定離不開問題語句。那么如何優化這些語句呢，以前靠的是DBA的經驗，現在就可以使用STA了。
1）創建優化任務并執行
14:43:29 SYS@ prod> DECLARE
my_task_name VARCHAR2(30);
14:44:01 2 14:44:01 3 my_sqltext CLOB;
14:44:01 4 i number(20):=10 ;
14:44:01 5 BEGIN
14:44:01 6 my_sqltext := 'insert into t1 values ('||i||')';
14:44:01 7 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
14:44:01 8 sql_text => my_sqltext,
14:44:01 9 user_name => 'SCOTT',
14:44:01 10 scope => 'COMPREHENSIVE',
14:44:01 11 time_limit => 60,
14:44:01 12 task_name => 'TEST1_sql_tuning_task',
14:44:01 13 description => 'Task to tune a query on a specified PRODUCT');
14:44:01 14
14:44:01 15 dbms_sqltune.Execute_tuning_task (task_name => 'TEST1_sql_tuning_task');
14:44:01 16 END;
14:44:01 17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.38 DBMS_SQLTUNE.CREATE_TUNING_TASK就是用來創建優化任務的函數。其中，sql_text是需要優化的語句，user_name是該語句通過哪個用戶執行，scope是優化范圍（limited或comprehensive），time_limit優化過程的時間限制，task_name優化任務名稱，description優化任務描述。
dbms_sqltune.Execute_tuning_task是執行優化的函數。
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
2）查看優化建議結果
14:44:17 SYS@ prod>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_sql_tuning_task') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TEST1_sql_tuning_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 08/18/2014 14:44:02
Completed at : 08/18/2014 14:44:03
Number of Statistic Findings : 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3w3k77yf4pd8f
SQL Text : insert into t1 values (10)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_SQL_TUNING_TASK')
-----------------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T1" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_SQL_TUNING_TASK')
-----------------------------------------------------------------------------------------
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
--------------------------------------------
| Id | Operation | Name | Time |
--------------------------------------------
| 0 | INSERT STATEMENT | | 00:00:10 |
--------------------------------------------
-------------------------------------------------------------------------------
Elapsed: 00:00:00.73
14:44:45 SYS@ prod>分析優化結果：
第一部分：優化的基礎信息
---------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TEST1_sql_tuning_task Tuning Task Owner : SYS Scope : COMPREHENSIVE Time Limit(seconds) : 60 Completion Status : COMPLETED Started at : 08/18/2014 14:44:02 Completed at : 08/18/2014 14:44:03 Number of Statistic Findings : 1 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 3w3k77yf4pd8f SQL Text : insert into t1 values (10) ------------------------------------------------------------------------------- 這部分信息包括：任務名稱、任務所有者、任務范圍、任務執行時間限制（前面幾個信息實際上就是我們創建任務時指定的參數）、任務狀態、任務開始時間、完成時間、發現的需要重新構造的問題語句數量。接下來就是schema名稱、SQL ID和SQL內容。
第二部分：優化器發現需要優化的語句
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3w3k77yf4pd8f
SQL Text : insert into t1 values (10)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_SQL_TUNING_TASK')
-----------------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T1" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order toOracle建議，對訪問的對象“T1”做統計分析！（由于語句和執行時的環境不同，Oracle給出的建議值可以作為參考）
第三部分：Sql的執行計劃
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST1_SQL_TUNING_TASK')
-----------------------------------------------------------------------------------------
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
--------------------------------------------
| Id | Operation | Name | Time |
--------------------------------------------
| 0 | INSERT STATEMENT | | 00:00:10 |
--------------------------------------------
-------------------------------------------------------------------------------
Elapsed: 00:00:00.73通過OEM查看的診斷結果：
1、啟動OEM服務
[oracle@rh65 ~]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-AUG-2014 11:59:19 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh65)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 15-AUG-2014 11:56:16 Uptime 0 days 0 hr. 3 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh65)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod_XPT" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rh65 ~]$ [oracle@rh65 ~]$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost ::1 localhost6.localdomain6 localhost6 192.168.8.239 rh65 192.168.8.12 rac1-vip 192.168.8.13 rac2-vip [oracle@rh65 ~]$ emctl start dbconsole TZ set to PRC Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved. http://rh65:1158/em/console/aboutApplication Starting Oracle Enterprise Manager 10g Database Control ................. started. ------------------------------------------------------------------ Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/rh65_prod/sysman/log [oracle@rh65 ~]$ netstat -an |grep :1158 tcp 0 0 0.0.0.0:1158 0.0.0.0:* LISTEN tcp 0 0 192.168.8.239:25766 192.168.8.239:1158 TIME_WAIT
2、連接OEM
Advisor Cenetral
進入ADDM
運行ADDM，生成報告
查看報告
ADDM的Report和RECOMMENDATION
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
