oracle health check,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生成日志