社区首页 >专栏 >SQL调优和诊断利器之SQLHC介绍

SQL调优和诊断利器之SQLHC介绍

SQLplusDB

发布于 2022-08-19 13:39:20

82700

代码可运行

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

运行总次数：0

代码可运行

概述

本文介绍在SQL问题诊断过程中利用的工具SQLHC。

SQLHC

SQL Tuning Health-Check Script (SQLHC)是SQLT的一个简化版本，同样用于诊断SQL问题，检查单条 SQL 语句运行的环境，包括基于成本的优化器（CBO）的统计数据，用户对象的元数据定义，配置参数和其他可能影响到待分析 SQL 性能的因素。

SQLHC安装

SQLHC安装也不需要任何配置，并且运行时不会在数据库中创建任何对象。

你只需要通过MOS 文档Doc ID 1366133.1下载后，解压即可运用。

SQLHC主要包含3个脚本：

sqlhc.sql：SQL Health-Check (extract mode)

REM DESCRIPTION
REM Produces an HTML report with a list of observations based on
REM health-checks performed in and around a SQL statement that
REM may be performing poorly.
REM
REM Inputs a memory-resident SQL_ID.
REM
REM In addition to the health_check report, it generates some
REM additional diagnostics files regarding SQL performance.
REM
REM This script does not install any objects in the database.
REM It does not perform any DDL commands.
REM It only performs DML commands against the PLAN_TABLE then it
REM rolls back those temporary inserts.
REM It can be used in Dataguard or any read-only database.

sqldx.sql：SQL Dynamic eXtract

REM DESCRIPTION
REM Produces a set of reports with information about one SQL
REM statement.
REM
REM This script does not install any objects in the database.
REM It does not perform any DDL commands.
REM It can be used in Dataguard or any read-only database.

sqlhcxec.sql：SQL Health-Check (execute mode)

REM DESCRIPTION
REM Produces an HTML report with a list of observations based on
REM health-checks performed in and around a SQL statement that
REM may be performing poorly.
REM
REM Inputs a script name which contains one SQL. This input script
REM could include binds. For an example of its format see
REM sqlt/input/sample/script1.sql.
REM
REM In addition to the health_check report, it generates some
REM additional diagnostics files regarding SQL performance.
REM
REM This script does not install any objects in the database.
REM It does not perform any DDL commands.
REM It only performs DML commands against the PLAN_TABLE then it
REM rolls back those temporary inserts.
REM It can be used in Dataguard or any read-only database.

SQLHC的使用

1.通过V$SQL或AWR(dba_hist_sqltext)找到要收集数据的SQL的SQL_ID 。

SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

2.执行sqlhc.sql脚本

SQL> START sqlhc.sql T <SQL_ID>
SQL> exit

3.执行例

--确认SQL_ID
SQL> conn teacherwhat/teacherwhat
Connected.
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE 'select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*)%'; 2 3


no rows selected


SQL> select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';


COUNT(*)
----------
1000


SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE 'select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*)%'; 2 3


SQL_ID HASH_VALUE
------------- ----------
TEXT
--------------------------------------------------------------------------------
acsdndc43qs74 138109156
select/*+ GATHER_PLAN_STATISTICS MONITOR

--执行脚本
SQL> START sqlhc.sql T acsdndc43qs74
...
SQLDX files have been added to sqlhc_20160828_090351_acsdndc43qs74.zip

Archive: sqlhc_20160828_090351_acsdndc43qs74.zip
Length Date Time Name
--------- ---------- ----- ----
7027 08-28-2016 09:04 sqlhc_20160828_090351_acsdndc43qs74_1_health_check.html
114794 08-28-2016 09:05 sqlhc_20160828_090351_acsdndc43qs74_2_diagnostics.html
5937 08-28-2016 09:05 sqlhc_20160828_090351_acsdndc43qs74_3_execution_plans.html
7066 08-28-2016 09:05 sqlhc_20160828_090351_acsdndc43qs74_4_sql_detail.html
23891 08-28-2016 09:05 sqlhc_20160828_090351_acsdndc43qs74_9_log.zip
2994 08-28-2016 09:05 sqlhc_20160828_090351_acsdndc43qs74_5_sql_monitor.zip
83540 08-28-2016 09:06 sqlhc_20160828_090351_acsdndc43qs74_8_sqldx.zip
--------- -------
245249 7 files