Use of SQLHC SQL Health Check Script in Oracle

Steps to use the SQLHC Script in Oracle

1. Download the SQLHC script from Oracle Support site with Document id: SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

2. Extract the SQLHC.zip folder to a suitable location.

3. Go to the location of the folder with the command prompt/terminal.
In Windows, I unzip in D drive: D:\SQLHC. In the CMD window go to cd D:\SQLHC.

4. Start the SQLPLUS command from the location D:\SQLHC where you extracted the SQLHC for running the SQLHC scripts.

5. Run the script SQLHC.SQL from the SQLPLUS with the SQLID you want to check.

Find the SQLID from the following query or AWR reports:

Select SQL_ID, SQL_TEXT from v$sqlarea where sql_text like '%select * from emp%';

Run the SQLHC report for SQLID and Oracle pack info: T | D | N

T: (Oracle Tuning pack includes Oracle Diagnostics)
D: Diagnostics pack
N: None

cd d:\sqlhc> sqlplus / as sysdba
SQL> START sqlhc.sql "N" chkgk3zxf3jx2

Note: It will take around 3 to 20 minutes to complete and generate a file name like sqlhc_20211220_1657_chkgk3zxf3jx2.zip