SQL Tuning Health-Check Script

 

SQL Tuning Health-Check Scripts

Overview of the SQL Tuning Health-Check Script

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.

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.

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:

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

Link - 

https://punitoracledba.blogspot.com/2021/08/how-to-determine-sqlid-for-sql-statement.html

INSTRUCTIONS

• Login to the database server and set the environment used by the Database Instance

• Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder

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

# sqlplus / as sysdba

SQL> START sqlhc.sql "T" djkbyr8vkc64h

Doc id - 1366133.1