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:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
Hi Brijesh,
Thank you for your great article! Unfortunately i don’t have access to Oracle Support Site, could you please send the latest version of sqlhc on email?
BR
thanks Brijesh for so helpful article.
please post more article !!!
THANKS!!!
[…] 2020 年 10 月 07 日 ~ ivannexus Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) […]
A very informative article. Thanks a lot
Thank you very much , great article