oracle效能調憂工具AWR,ASH,ADDM

一.ASH(Active Session History)活動會話歷史記錄

      ASH以V$SESSION為基礎，每秒取樣一次記錄活動會話等待的事件，不活動的會話不會取樣，取樣工作由新引入的後臺程式MMNL來完成。ASH buffers 的最小值為1MB,最大值不超過30MBm，記憶體中記錄資料，期望值是記錄一小時的內容。

[oracle@ogg2 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:17:03 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ ashrpt.sql       --使用sql生成報告

End of Report

Report written to ashrpt_1_0411_1517.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$ vim ashrpt_1_0411_1517.txt      --檢視內容

二.ADDM(Automatic Database Diagnostic Monitor)自動資料庫診斷監視器

     ADDM是Oracle內部的一個顧問系統，能夠自動的完成最資料庫的一些最佳化的建議，給出SQL的最佳化，索引的建立，統計量的收集等建議。

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:23:12 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ addmrpt.sql

End of Report

Report written to addmrpt_1_10660_10723.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$ vim addmrpt_1_10660_10723.txt 

三.AWR(Automatic Workload Repository)自動工作負載資訊庫

     AWR報告生成時間是Oracle啟動到關閉中間，資料庫重啟後會影響AWR報告連續性。Oracle 11.2.0.3有個bug是AWR報告不能以html格式輸出，可以輸出text格式。

[oracle@ogg2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 15:23:12 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select snap_interval, retention from dba_hist_wr_control;      --查詢awr設定

SNAP_INTERVAL          RETENTION

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

+00000 01:00:00.0     +00008 00:00:00.0

SQL>  @ awrrpt.sql

End of Report

Report written to awrrpt_1_10692_10723.txt

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ogg2 admin]$ 

四.找出資料庫正在執行的慢SQL

    找出正在執行時的慢SQL語句可以透過v$session+v$sqlarea檢視。如果在一個小時以內發生的我們可以透過生成ASH報告來找出SQL。如果是1小時以上或幾天我們可以透過AWR報告來找出最影響系統的SQL語句。ADDM報告基於AWR庫，預設可以儲存30天的ADDM報告。

SQL> select sql_id,SQL_FULLTEXT,disk_reads from v$sqlarea order by disk_reads;      --查出最耗磁碟IO的sql語句

575n0h472axp5 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :myda 26071305

8rkkvsws8j7mc SELECT DISTINCT MAX("A1"."END_DATE"),"A1"."DATA_SOUR" FROM "UPINFO"."HK_SZ_SAMP_ 32066026

c5qwm85rqx158 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :myda 33885567

b5cbrvz8a2uhb SELECT LINE_ID, PUBLISH_DATE, TITLE, URL, AFFIX_NAME, AFFIX_PATH, ORIG_CLASS, UP 38591289

d2xrh5ata01ah SELECT URL, LINE_ID, PUBLISH_DATE, TITLE, AFFIX_PATH, AFFIX_NAME, UPDATETIME FRO 43511414

0hzc203fng3t4 begin upmain.PR_STK_SEC_TRADE_INFO(:a,:b); end; 45662039

5nrvy4cqq3aq8 SELECT "ISVALID","TRADE_DATE","MKT_CLS_PAR","SEC_UNI_CODE","SELL_BALANCE_VOL" FR130555461

74ju8jvk9k70t DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :myda253372505

56293grsrhapw begin upmain.PR_PUB_ORG_INFO(:a,:b); end; 270812668

8mp3aausbm77c MERGE INTO UPMAIN.PUB_ORG_INFO T USING( SELECT DISTINCT ORG_UNI_CODE ,GEN_MAN FR 1062109712

5842 rows selected.

SQL> select distinct USER_ID from dba_hist_active_sess_history where sql_id='8mp3aausbm77c';   --根據sql_id查sql

USER_ID

----------

85

SQL> select user_id,username from dba_users where user_id=85;    --找使用者

USER_ID USERNAME

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

85 UPMAIN

SQL> 

相關檢視：

v$sqlarea                                  --sql語句

v$session                                  --當前正在發生的會話
v$session_wait                          --當前正在發生的等待會話
v$session_wait_history             --會話最近的10次等待事件
v$active_session_history          --記憶體中的ASH採集資訊，理論為1小時
wrh$_active_session_history    --寫入AWR庫中的ASH資訊，理論為1小時以上
dba_hist_active_sess_history    --根據wrh$_active_session_history生成的檢視