FAQ: SQL Health Check (SQLHC) Frequently Asked Questions
(Doc ID 1417774.1)
Last updated on JANUARY 26, 2023
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle 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:
for any desired additions.
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 |