Skip to content

Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)

This post will cover the SQL Tuning Health Check script basics and how you can use it to collect the key information regarding a poorly performing SQL. SQLHC helps you on focusing on specific SQL and checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance.

Unlike SQLT, this tool SQLHC do not require any code to be installed in the database to provide recommendation. SQLHC uses the SQL_ID of a statement that has already been executed and is in memory to generate the report so it do not actually execute the SQL all lover again. SQLHC works 10g and above. SQLHC is also RAC aware

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.

Where to download this script:

You can download the code from the below Oracle MOS Note ID:

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

 

How to run this Health Check Script:

After downloading the scrip code from the MOS and  uploading to your server, next thing to do will be to find the SQL_ID for which you want to do performance evaluation. 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. 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.

Go to the location where you have uploaded the SQLHC script code. Execute this script from SQL*Plus connecting as SYS, DBA or a user with access to Data Dictionary views.

Parameters Required

It requires two parameters:

a) Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
Note: If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)

b) A valid SQL_ID for the SQL to be analyzed.

Example:

$ unzip sqlhc.zip
Archive: sqlhc.zip
 creating: sqlhc/
 inflating: sqlhc/sqlhc.sql
 inflating: sqlhc/sqldx.sql
 inflating: sqlhc/sqlhcxec.sql
$ cd sqlhc
$ ls -tlr
total 2
-rw-r--r-- 1 mfggprd dba 48747 Nov 11 2013 sqldx.sql
-rw-r--r-- 1 mfggprd dba 288298 Apr 16 2014 sqlhc.sql
-rw-r--r-- 1 mfggprd dba 292838 Apr 16 2014 sqlhcxec.sql

$ sqlplus / as sysdba
SQL> @sqlhc.sql T 1w6s3ayu0kw8m

Since SQLHC can use information from AWR reports if the Diagnostic or Tuning pack are installed, it asks if these packs are licensed at your site.

If they are licensed, then answer “Yes” to this question to perform additional checks.
If they are not licensed then Answer “No”.

In our case since we have all the Tuning/Diagnostic packs so we just mentioned “T” directly while execution.

It will generate a ZIP file in the same directory where you executed. You get that file copied out to your local machine for analysis.

 

Sample Output

 

 

 

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

[Post Views: 17]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *

error: Alert: Content is protected !!