SQL Tuning Health-Check Script (SQLHC) (文档 ID 1366133.1)

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


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.


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


There is no configuration required.

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


    SQL Text:要检查的sql文本
    Tables Summary:显示相关表的统计信息大概
    Indexes Summary:显示相关索引的统计信息大概
    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系统参数
    DBMS_STATS Table Preferences:DBMS_STATS表参数
    Table Columns:列相关的统计信息
    Table Partitions:表分区相关的统计信息
    Table Constraints:表上的约束信息
    Tables Statistics Versions:表相关的统计信息
    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中的系统参数
    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的执行计划
    sql health check数据来源
    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)统计信息。



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


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

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


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)


电子邮件地址不会被公开。 必填项已用*标注