Oracle 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.

SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems.

What all information can SQLHC provide:

Some of the key information that this tool can provide you is below:

Explain plan of the SQL ID and any changes

Validity of various statistics and parameters with a brief explanation

Tables and index details

Objects Statistics Details

SQL_TEXT and SQL profile, baseline details

Historical Plan details

Many other information pieces from dynamic performance views.

Good thing is that it saves you from running multiple scripts and collecting all the above data. Running single SQLHC accumulates all the data and present it in HTML easy-to-read format.

Parameters 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:

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)

A valid SQL_ID for the SQL to be analyzed.

SQl> @sqlhc.sql T djkbyr8vkc64h

or 

SQL> START sqlhc.sql T djkbyr8vkc64h

IF in case sqlid is not present in database and we want to execute actual sql statement using sqlhcxec.sql 

REM EXAMPLE

REM # sqlplus / as sysdba

REM SQL> START [path]sqlhcxec.sql [T|D|N] [path]scriptname

REM SQL> START utl/sqlhcxec.sql T input/sample/script1.sql

Reference : 

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

FAQ: SQL Health Check (SQLHC) Frequently Asked Questions (Doc ID 1417774.1)

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

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues  

Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Primary Note: SQL Query Performance Overview (Doc ID 199083.1)