If you have a SQL performance issue, Oracle Support may request you to run the SQL Health Check script. This is a pretty cool process, so I thought I would document it.
Download the SQLHC.zip archive from the Oracle support web site. The document id at the time of writing this blog post was Doc ID 1366133.1.
Extract the zip archive. I did this on my laptop. There are three files sqldx.sql, sqlhc.sql, and sqlhcxec.sql.
Place these three files in a directory on your server.
Find the SQL id of the problem SQL. This can be accomplished via AWR, ADDM, etc. In my case, the SQL was running for greater than 5 minutes so I was able to get the SQL id quite easily.
Run the SQLHC tool as follows:
Sign on as sysdba
Issue the command as follows:
START sqlhc.sql T 3t3ypamk8wu40
the first parameter “T” indicates that my site has the SQL tunning pack.
(Valid values are Tuning(T), Diagnostics (D) or None (N)). If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
the second parameter is the sql_id of the problem SQL statement
The process will display a lot of information on the terminal and write the output to a file named sqlhc_date_time_sqlid.zip. For example:
sqlhc_20180212_180710_3t3ypamk8wu40.zip
Attach this file to your open SR with Oracle.
