Applies to: 
Oracle Database – Personal Edition – Version 10.2.0.1 and later
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Oracle Database – Standard Edition – Version 10.2.0.1 and later
Oracle Database Products > Oracle Database Suite > Oracle Database
Information in this document applies to any platform.

Purpose 

Download the SQL Tuning Health-Check Script

SQL Health Check

Download the SQLHC Script Here (.zip archive).

What is the SQL Tuning Health-Check Script (SQLHC)?

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

Licensing (SQLHC requires no license and is FREE)

As in the title of this section, SQLHC requires no license and is FREE. 
Since SQLHC can use information from AWR reports if the Diagnostic or Tuning pack are installed, it asks if these packs are licensed at your site. For more details refer to the licensing section in the SQLHC FAQ:

Document 1454160.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions
 

Overview of the SQL Tuning Health-Check Script

An overview video about SQLHC is available here :

Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video

The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.

It does this while leaving "no database footprint" ensuring it can be run on all systems.

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view. See:

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement

Health-checks are performed over:
•CBO Statistics for schema objects accessed by the one SQL statement being analyzed
•CBO Parameters
•CBO System Statistics
•CBO Data Dictionary Statistics
•CBO Fixed-objects Statistics

NOTE: A webcast has been recorded entitled: "How to Improve SQL Performance with the New Health Check Tool?".

This can be found, along with many other recorded webcasts, here:

Document 740964.1 Advisor Webcast Archived Recordings

A FAQ for the SQL Healthcheck script (SQLHC) can be found here:

Document 1417774.1 FAQ: SQLHC HealthCheck Frequently Asked Questions

Additionally, we welcome any additional health-checks that you may suggest. If any specific health-checks are needed and not covered by this script, then as long as the health-check can be produced with a SQL Command (leaving no database footprint ) then these can be implemented in future versions.

Please add comments to this Document for any desired additions.
 

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. SQLHC is one of the tools that support recommend for collecting such diagnostics. For information on suggested uses, other proactive preparations and diagnostics, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Requirements

Execute this script from SQL*Plus connecting as SYS, DBA or a user with access to Data Dictionary views.

NOTE: The script ADDS NO OBJECTS TO THE DATABASE. It simply reports and advises on existing objects

Configuring

There is no configuration required.

Instructions
1.Login to the database server and set the environment used by the Database Instance
2.Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder
3.Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters:

i.Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)

ii.A valid SQL_ID for the SQL to be analyzed.  

For Example:

# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h

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

对于SQLHC（SQL Health Check，SQL性能健康检查脚本）工具，Mos文档1626277.1有非常详细的介绍。SQLHC是Oracle Server Technologies Center of Expertise开发的一个工具。SQLHC用于检查单条SQL语句运行的环境，包括基于成本的优化器（CBO）的统计数据，用户对象的元数据定义，配置参数和其它可能影响到目标SQL性能的因素。SQLHC和SQLT工具一样，本身都是免费的，不需要任何许可证（License）。当对某一个SQL_ID运行SQLHC后，该脚本会生成一系列针对该SQL语句健康检查的一份HTML报告。SQLHC会检查的内容包括：①　待分析的单条SQL涉及的用户对象的CBO统计信息；②　CBO参数；③　CBO系统统计信息；④　CBO数据字典统计信息；⑤　CBO固定对象（Fixed-Objects）统计信息。

SQLHC运行时不会在数据库中创建任何对象（“数据库中不留足迹”），它只是对已有的对象提供报告和建议，可以确保它在所有系统上运行。SQLHC的脚本需要以SYS、DBA或者能访问数据字典视图的用户通过SQL*Plus连接运行。SQLHC一共包含3个脚本，分别为sqlhc.sql、sqldx.sql和sqlhcxec.sql，其中sqlhc.sql里边会调用sqldx.sql脚本。sqlhcxec.sql是单独执行的，不过该脚本需要输入一个脚本文件作为入参，而且该脚本文件可以包含绑定变量。

本地测试：http://blog.itpub.net/28539951/viewspace-2100920/

SQL 性能健康检查脚本 (SQLHC) (文档 ID 1626277.1)

表收集信息（防止过旧信息）：

begin
  fnd_stats.GATHER_TABLE_STATS('SYS', 'TAB$');
end;

EBS系统数据库统计信息收集总结- gather_schema_stats [final]_ITPUB博客

分析一个SCEHMA (EBS系统) :
exec  fnd_stats.GATHER_TABLE_STATS('PA');

分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
　　ownname          => 'HRM',
　　options          => 'GATHER AUTO',
　　estimate_percent => dbms_stats.auto_sample_size,
　　method_opt       => 'for all columns size repeat', — 稳定后推荐repeat
      cascade          =>TRUE,
　　degree           => 12
    )

–一天内执行的sql

select a.*,b.*
  from v$sqltext a, v$session b
 where a.sql_id = b.sql_id
   and b.logon_time between sysdate – 1 and sysdate;

select * from dba_hist_active_sess_history t;

扩展：

All About the SQLT Diagnostic Tool (文档 ID 215187.1)