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