转载

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生成日志

正文到此结束