SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1) SQL调整运行状况检查脚本

最新推荐文章于 2024-03-14 15:50:20 发布

张玉龙

最新推荐文章于 2024-03-14 15:50:20 发布

阅读量793

点赞数

分类专栏： oracle优化学习

原文链接：https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=538873635314791&id=1366133.1&_afrWindowMode=0&_adf.ctrl-state=17s4adhw91_176

版权

oracle优化学习专栏收录该内容

62 篇文章 4 订阅

订阅专栏

PURPOSE 目的

Download the SQL Tuning Health-Check Script 下载脚本

Download the SQLHC Script Here (.zip archive).

或者

https://pan.baidu.com/s/1WWSjI2vCQfonX_kBhZ_Bfg

What is the SQL Tuning Health-Check Script (SQLHC)?

什么是SQL调整运行状况检查脚本（SQLHC）？

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 Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

SQL Tuning Health-Check脚本是由Oracle服务器技术专家中心开发的工具。该工具也称为SQLHC，用于检查运行单个SQL语句的环境，检查基于成本的优化器（CBO）统计信息，架构对象元数据，配置参数和其他可能影响一个SQL性能的元素并分析。

Licensing (SQLHC requires no license and is FREE)

许可（SQLHC不需要许可证，并且是免费的）

As in the title of this section, SQLHC requires no license and is FREE.
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. For more details refer to the licensing section in the SQLHC FAQ:

Document 1454160.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions

SQL运行状况检查（SQLHC）常见问题

Overview of the SQL Tuning Health-Check Script 脚本概述

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.
SQLHC的目的是通过确保运行单个SQL的环境良好，使用户避免SQL性能受到可避免的问题的影响。
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. 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. See:

当针对一个SQL_ID执行时，此脚本将生成一个HTML报告，其中包含围绕所提供的一条SQL语句的一组运行状况检查的结果。您可以从AWR或ASH报告中找到语句的SQL_ID，也可以使用V$SQL视图从数据库中选择它。
Document 1627387.1 How to Determine the SQL_ID for a SQL Statement 如何确定SQL语句的SQL_ID

Health-checks are performed over: 运行状况检查通过

CBO Statistics for schema objects accessed by the one SQL statement being analyzed 对象的CBO统计信息
CBO Parameters CBO参数
CBO System Statistics CBO系统统计信息
CBO Data Dictionary Statistics CBO数据字典统计
CBO Fixed-objects Statistics CBO固定对象统计

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:

尽管可能无法预料到某些问题，但在很多情况下，如果足够早地发现迹象，就可以避免问题。此外，如果确实发生问题，则在事件发生后收集有关该问题的信息是没有用的。SQLHC是支持建议收集此类诊断的工具之一。

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.

从SQL * Plus以SYS，DBA身份或有权访问数据字典视图的用户身份执行此脚本。

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
Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder
Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters:只需执行“ sqlhc.sql”脚本即可。它将要求输入两个参数：
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. 要分析的SQL的有效SQL_ID

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.

提供此示例代码仅出于教育目的，Oracle支持不支持此示例代码。它已经过内部测试，但是，我们不能保证它会为您服务。使用前，请确保在测试环境中运行它。

SCRIPT 脚本

Download the SQLHC Script Here (.zip archive).

SAMPLE OUTPUT 样本输出

REFERENCES 参考

NOTE:1455583.1 - SQL Tuning Health-Check Script (SQLHC) Video
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:1417774.1 - FAQ: SQL Health Check (SQLHC) Frequently Asked Questions
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:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports
NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql)
NOTE:1627387.1 - How to Determine the SQL_ID for a SQL Statement