   一條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生成日誌

ORACLE SQL Tuning Health-Check(SQLHC)

相關文章