My Oracle Support Banner

FAQ: SQL Health Check (SQLHC) Frequently Asked Questions (Doc ID 1417774.1)

Last updated on SEPTEMBER 04, 2020

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


Purpose



This document answers some of the frequently asked questions about the SQLHC tool.

NOTE: We welcome any additional health-checks that you may suggest. If any specific health-checks are needed and not covered by this script, then as long as the health-check can be produced with a SQL Command (leaving no database footprint ) then these can be implemented in future versions.

Please add comments to this Document or to:

<Document 1366133.1> SQL Tuning Health-Check Script (SQLHC)

for any desired additions.

 

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Questions and Answers
 Background and Structure
 What is the SQL Tuning Health-Check Script?
 What is the origin of SQLHC?
 Is SQLHC an 'advanced' version of SQLTXPLAIN?
 Are the underlying selects used by SQLHC available?
 Setup and Usage
 Where can SQLHC be downloaded from?
 How can I identify the SQL I want to Healthcheck?
 Does SQLHC have to execute the SQL to generate a report?
 What if the SQL_ID in question has been aged out from memory?
 Can SQLHC retrieve an explain plan from memory or the AWR?
 Is SQLHC safe to use in a production environment?
 Is SQLHC  practical to use for complex sql statements?
 Part of the sqlhc.sql script that inserts rows into the plan_table, but the documentation states that SQLHC Leaves "no database footprint". How can this be the case?
 The SQLHC Documentation states that there is no configuration required, but it does not state how to create the 'plan_table'. How can I create the plan_table?
 Compatible Versions and licensing
 Is the SQLHC script the same for all versions?
 Can SQLHC be used on Oracle 9i or below?
 What is the licensing requirement for SQLHC?
 What is missing from the report if the Tuning Pack is not installed?
 When using SQLHC, is it more beneficial to have AWR than not having AWR?
 Are the global health checks specific to any version of Oracle?
 Software Compatibility
 Does SQLHC work with --Insert application here--
 Are there any limitations on using this script in a Database running EBusiness Suite?
 Is it practical to use this tool for SAP?
 Does this SQL Health Check tool take into consideration that the query is executed on an Exadata system/database ?
 Are there any Exadata specific settings?
 Can this tool be used with Data Guard?
 How we can integrate this script in to GridControl?
 Are there specific checks related to Peoplesoft?
 Are there specific checks related to Siebel?
 Compatibility with Specific Database Features
 Can SQLHC be used on a remote database?
 Can SQLHC be used on SQL that accesses a remote database?
 Does SQLHC work with XML Type and XML indexes?
 Does it work with encrypted columns?
 Does SQLHC work with LOB columns?
 Does SQLHC advise for extended statistic or index reordering?
 Is SQLHC RAC aware?
 Can I use SQLHC to analyze a call to a pl/sql procedure?
 Is there a way to run without having to execute the SQL (like EXPLAIN PLAN?)
 Does SQLHC show multiple plans from the SQL Tuning Advisor?
 What is the difference between SQLHC and --Insert application here--
 How does SQLHC differ from SQLT?
 What is the difference between SQLHC and AWR and TKProf?
 Checks related to Statistics
 SQLHC mentions some issues with system statistics. Are system statistics supposed to be gathered?
 Are statistics on dictionary objects supposed to be gathered?
 Are statistics required on temporary tables?
 Can the tool determine whether statistics were gathered using ANALYZE instead of DBMS_STATS
 Since 11g Oracle automates the statistics collection, why is the script checking for statistics health?
 Does SQLHC use just the most recent statistics for health check?
 If the statistics have been locked, then will SQLHC give the desired results?
 Questions regarding specific checks performed
 SQLHC says that Value A is greater than Value B. This is impossible. How can this have occurred?
 If a collective parameter such as "OPTIMIZER_FEATURES_ENABLE" has been set, does SQLHC just report on that parameter change or on all the affected underlying parameters?
 Miscellaneous Questions
 Does SQLHC show all the checks done or just the results that are non-compliant?
 Can SQLHC be executed in a multitentant environment?
 Is the "Tables" and "Indexes Summary" for all tables or only for the tables affected by a particular SQL?
 Does SQLHC check the dictionary statistics against the actual values in the objects?
 Does the health check account for bind variables in any way?
 Will SQLHC tell you that the same SQL is used with different plans ?
 Can the health check script help me identify changes to the explain plan over a long period of time?
 Does SQLHC provide any advise to improve the performance or is it just a health check?
 Does SQLT provide SQL recommendations or does it only Provide the Execution plan?
 Is SQLHC able to identify disk I/O performance issues?
 How is it best to use the information provided by SQLHC?
 Does SQLHC take into account session parameter changes as well as global ones?
 Could SQLHC not be integrated with advisor tools rather than having multiple tools?
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.