Oracle SQL Tuning Health-Check(SQLHC)

Oracle SQL Tuning Health-Check(SQLHC)

一條sql語句的效能主要依賴於好的物理結構,準確的系統統計資料,準確的對象統計資料,合理的查詢最佳化工具參數,合理的系統參數.這些資料也就是CBO計算的基本參數,SQLHC(文檔 ID 1366133.1)正是為使用者提供sql的這些資料,來輔助sql調優.特別適合無法在客戶環境中調優的情況,SQLHC雖然沒有SQLT(文檔 ID 215187.1)更強大,但不需要在採集的庫上建使用者,會被更多的客戶所接受.

以下示範:
os:CentOS 6.6
db:Oracle 11.2.0.4
#sqlhc.zip在mos文檔 ID 1366133.1下載

#解壓sqlhc.zip
[oracle@ct6604 ~]$ unzip sqlhc.zip
Archive:  sqlhc.zip
  creating: sqlhc/
  inflating: sqlhc/sqlhc.sql
  inflating: sqlhc/sqldx.sql
  inflating: sqlhc/sqlhcxec.sql
[oracle@ct6604 ~]$ cd sqlhc
[oracle@ct6604 sqlhc]$ ls
sqldx.sql  sqlhc.sql  sqlhcxec.sql

#運行測試sql
[oracle@ct6604 sqlhc]$ ORACLE_SID=ctdb
[oracle@ct6604 sqlhc]$ sqlplus / as sysdba
#sql中的gather_plan_statistics是為了在產生的報告中顯示a-row.
#sql中的monitor是為了產生sql_monitor報告.
SQL> select /*+gather_plan_statistics monitor */a.* from scott.emp a,scott.dept b where a.deptno=b.deptno and b.dname='SALES';
/*
...
*/

SQL> select sql_text,sql_id from v$sql where sql_text like 'select /*+gather_plan_statistics monitor */a.* from scott.emp a%';
/*
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
select /*+gather_plan_statistics monitor */a.* from scott.emp a,scott.dept b whe
re a.deptno=b.deptno and b.dname='SALES'
9pq9f4vkb9fvb
*/

#產生SQLHC報告
SQL> start sqlhc.sql T 9pq9f4vkb9fvb

/*
...
Archive:  sqlhc_20160516_145204_9pq9f4vkb9fvb.zip
  Length      Date    Time    Name
---------  ---------- -----  ----
    7756  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_1_health_check.html
  119553  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_2_diagnostics.html
    7938  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_3_execution_plans.html
    50903  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_4_sql_detail.html
  449937  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_9_log.zip
    6018  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_5_sql_monitor.zip
  147123  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_6_10053_trace_from_cursor.trc
    56808  05-16-2016 14:52  sqlhc_20160516_145204_9pq9f4vkb9fvb_8_sqldx.zip
---------                    -------
  846036                    8 files
*/

#SQLHC報告說明
1_health_check.html
    Observations:顯示health-checks輸出的可能存在問題的項目,確認是否需要更改.例如:對象統計資訊過舊
    SQL Text:要檢查的sql文本
    Tables Summary:顯示相關表的統計資訊大概
    Indexes Summary:顯示相關索引的統計資訊大概
2_diagnostics.html
    SQL Text:要檢查的sql文本
    SQL Plan Baselines (DBA_SQL_PLAN_BASELINES):DBA_SQL_PLAN_BASELINES中此sql的plan history
    SQL Profiles (DBA_SQL_PROFILES):DBA_SQL_PROFILES中此sql的profiles
    SQL Patches (DBA_SQL_PATCHES):DBA_SQL_PATCHES中此sql的相關patches
    Cursor Sharing and Reason:GV$SQL_SHARED_CURSOR是此sql的cursor sharing
    Cursor Sharing List:GV$SQL_SHARED_CURSOR中此sql的cursor sharing
    Current Plans Summary (GV$SQL):GV$SQL中此sql的平均消耗
    Current SQL Statistics (GV$SQL):GV$SQL中此sql的消耗
    Historical Plans Summary (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的曆史平均消耗
    Historical SQL Statistics - Delta (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的曆史消耗
    Historical SQL Statistics - Total (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的曆史消耗
    Active Session History by Plan (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的session state統計
    Active Session History by Plan Line (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的plan line統計
    AWR Active Session History by Plan (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的session state統計
    AWR Active Session History by Plan Line (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的plan line統計
    DBMS_STATS System Preferences:DBMS_STATS系統參數
    Tables:表相關的統計資訊
    DBMS_STATS Table Preferences:DBMS_STATS表參數
    Table Columns:列相關的統計資訊
    Table Partitions:表分區相關的統計資訊
    Table Constraints:表上的約束資訊
    Tables Statistics Versions:表相關的統計資訊
    Indexes:索引相關的統計資訊
    Index Columns:索引列相關的統計資訊
    Index Partitions:索引分割區相關的統計資訊
    Indexes Statistics Versions:索引相關的統計資訊
    System Parameters with Non-Default or Modified Values:GV$SYSTEM_PARAMETER2中isdefault = 'FALSE' OR ismodified != 'FALSE'的參數
    Instance Parameters:V$SYSTEM_PARAMETER2中的系統參數
    Metadata:表和索引的建立語句
3_execution_plans.html
    SQL Text:要檢查的sql文本
    Current Execution Plans (last execution):按child cursor顯示此sql的記憶體中的執行計畫
    Current Execution Plans (all executions):按child cursor顯示此sql的記憶體中的執行計畫
    Historical Execution Plans:按child cursor顯示此sql的awr的執行計畫
4_sql_detail.html
   圖形化顯示sql的運行統計
5_sql_monitor.zip
   圖形化顯示sql的監控資訊
6_10053_trace_from_cursor.trc
   顯示sql的10053追蹤檔案
8_sqldx.zip
   sql health check資料來源
9_log.zip
   sql health check組建記錄檔