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

最新推荐文章于 2024-09-30 17:19:22 发布

cuccntzd50572

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量208

点赞数

文章标签：数据库

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

To Bottom

Modified:12-Dec-2013

Type:SCRIPT

Language:

English日本語

In this Document

Purpose

What is the SQL Tuning Health-Check Script?

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Ask Questions, Get Help, And Share Your Experiences With This Article

Script

APPLIES TO:

Oracle Database - Personal Edition - Version 10.2.0.1 and later
Oracle Database - Standard Edition - Version 10.2.0.1 and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Products > Oracle Database > Oracle Database
Information in this document applies to any platform.

PURPOSE

Download the SQLHC Script Here.

What is the SQL Tuning Health-Check Script?

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking checks Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

An overview video about SQLHC is available here :

Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video

The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.

It does this while leaving "no database footprint" ensuring it can be run on all systems.

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. Health-checks are performed over:

CBO Statistics for schema objects accessed by the one SQL statement being analyzed
CBO Parameters
CBO System Statistics
CBO Data Dictionary Statistics
CBO Fixed-objects Statistics

NOTE: A webcast has been recorded entitled: "How to Improve SQL Performance with the New Health Check Tool?".
This can be found, along with many other recorded webcasts, here:

Document 740964.1 Advisor Webcast Archived Recordings

A FAQ for the SQL Healthcheck script (SQLHC) can be found here:

Document 1417774.1 FAQ: SQLHC HealthCheck Frequently Asked Questions

Additionally, 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 for any desired additions.

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. SQLHC is one of the tools that support recommend for collecting such diagnostics. For information on suggested uses, other proactive preparations and diagnostics, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

REQUIREMENTS

Execute this script from SQL*Plus connecting as SYS, DBA or a user with access to Data Dictionary views.

NOTE: The script ADDS NO OBJECTS TO THE DATABASE. It simply reports and advises on existing objects

CONFIGURING

There is no configuration required.

INSTRUCTIONS

Login to the database server and set the environment used by the Database Instance
Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute this script. It will request to enter two parameters:
1. Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
  If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
2. A valid SQL_ID for the SQL to be analyzed.

For example:

# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h

CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

SCRIPT

Download the SQLHC Script Here.

SAMPLE OUTPUT

downloadattachmentprocessor?attachid=1366133.1:SQLHC_Example1&clickstream=no

Discuss SQLHC!

The window below is a live discussion of this article (not a screenshot). We encourage you to join the discussion by clicking the "Reply" link below for the entry you would like to provide feedback on. If you have questions or implementation issues with the information in the article above, please share that below.

REFERENCES

NOTE:1455583.1 - SQL Tuning Health-Check Script (SQLHC) Video
NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
NOTE:215187.1 - SQLT Diagnostic Tool
NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data
NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues
NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces
NOTE:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports
NOTE:1417774.1 - FAQ: SQL Health Check (SQLHC) Frequently Asked Questions