SQL Tuning Health-Check Script (SQLHC)

SQL Tuning Health-Check Script (SQLHC) is used to check the environment in which a single SQL statement is running, Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters, and other elements that can affect the performance of a SQL . You can find more information about this script on MOS document (Doc ID 1366133.1) .

SQLHC does not require any license and is free. You can download the script from below links :

Download the SQL Tuning Health-Check Script (19.0 and above)

Download the SQL Tuning Health-Script for 11g and 12c

The health check with the script is carried out for the following elements.

• CBO Statistics for schema objects accessed by an analyzed SQL statement
• CBO Parameters
• CBO system statistics
• CBO Data Dictionary statistics
• CBO Fixed-objects statistics

With this script, you can analyze a query in detail. You can see system-wide recommended actions for better query operation.

Instructions

1. Login to the database server and set the environment used by the Database Instance
2. Download the “sqlhc.zip” archive file from the top of this document 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:
   1. 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)
   2. A valid SQL_ID for the SQL to be analyzed.

For Example:

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

RESTRICTIONS

1. Please note the SQL_ID cannot be a SQL_ID of a PL/SQL package. This will produce no useful results.
2. This routine will run against ONE SQL_ID at a time, only. You cannot use a list of SQL_ID statements as a parameter list.
3. You cannot run this routine against ALL SQL statements run by a schema.

You can also examine below MOS documents :

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

SQL Tuning Health-Check Script (SQLHC) Video (Doc ID 1455583.1)