Oracle数据库调优利器SQLHC详细介绍(附带下载地址)

本文链接：https://blog.csdn.net/chenoracle/article/details/148025966

在这里插入图片描述

图片.png

SQLHC是什么?

什么是SQL调优健康检查脚本(SQLHC)(SQL Tuning Health-Check)？
SQL调优健康检查脚本是由Oracle Server技术专家中心开发的一种工具。
该工具也称为SQLHC，用于检查单个SQL语句运行的环境，检查基于成本的优化器（CBO）统计数据、模式对象元数据、配置参数和其他可能影响所分析SQL性能的元素。

SQLHC是否需要授权?

SQLHC不需要许可证，是免费的，由于如果安装了诊断(Diagnostic)或调整包(Tuning pack)，SQLHC可以使用AWR报告中的信息，因此它会询问这些包是否在您的站点获得许可。

SQLHC工具的目的:

SQLHC的目的是通过确保单个SQL运行的环境良好，使用户避免SQL性能受到可避免问题的影响。它做到了这一点，同时“没有数据库足迹”(no database footprint)，确保它可以在所有系统上运行。当对一个SQL_ID执行时，此脚本会生成一个HTML报告，其中包含围绕所提供的一个SQL语句进行的一组健康检查的结果。
您可以从AWR或ASH报告中找到语句的SQL_ID，也可以使用V$SQL视图从数据库中选择它。

Health-checks在以下部位进行：

(1)被分析的一个SQL语句访问的模式对象的CBO统计信息；
(2)CBO参数;
(3)CBO系统统计;
(4)CBO数据字典统计;
(5)CBO固定对象统计;

最佳实践:

主动问题避免和诊断收集
尽管有些问题可能是不可预见的，但在许多情况下，如果及早发现迹象，问题是可以避免的。
此外，如果确实发生了问题，那么在事件发生后收集有关该问题的信息是没有用的。
SQLHC是支持收集此类诊断的推荐工具之一。

必要条件:

以SYS、DBA或有权访问数据字典视图的用户身份从SQL*Plus连接执行此脚本。
注意：该脚本不会向数据库添加任何对象。它只是对现有对象进行报告和建议。

介绍:

1.登录数据库服务器并设置数据库实例使用的环境;
2.从本文档顶部下载“sqlhc.zip”文件，并将内容解压缩到合适的目录/文件夹中;
3.以SYS、DBA帐户或有权访问数据字典视图的用户身份连接到SQL*Plus，然后简单地执行“sqlhc.SQL”脚本。
它将请求输入两个参数：
(1)Oracle包许可证(Tuning, Diagnostics或None)T|D|N;
如果站点同时具有Tuning和Diagnostics许可证，请指定T(Oracle Tuning pack包括Oracle Diagnostics);
(2)要分析的SQL的有效SQL_ID。

例如:

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

限制:

请注意,SQL_ID不能是PL/SQL包的SQL_ID。这不会产生任何有用的结果。
此routine一次只对一个SQL_ID运行。您不能将一系列SQL_ID语句用作参数列表。
您无法对schema运行的所有SQL语句运行此routine。

示例图片:

图片.png
附件:

测试如下:

下载 sqlhc.zip ，并上传到服务器。

[oracle@cjc-db-02 tools]$ ls -lrth
total 6.4M
-rw-r--r-- 1 root root 223K May 17 08:40 sqlhc.zip
-rw-r--r-- 1 root root  50K May 17 08:40 hangfg.tar
-rw-r--r-- 1 root root 5.1M May 17 08:40 oswbb840.tar
-rw-r--r-- 1 root root 905K May 17 08:40 sqlt.zip
-rw-r--r-- 1 root root 171K May 17 08:40 sqlhc11g.zip

解压

[oracle@cjc-db-02 tools]$ unzip sqlhc.zip
[oracle@cjc-db-02 tools]$ ls -lrth sqlhc
total 880K
-rw-r--r-- 1 oracle oinstall  545 Oct  9  2024 sqlhc_pxhcdr_null.sql
-rw-r--r-- 1 oracle oinstall 2.3K Oct  9  2024 sqlhc_tcb.sql
-rw-r--r-- 1 oracle oinstall  48K Oct 10  2024 sqldx.sql
-rw-r--r-- 1 oracle oinstall  510 Oct 10  2024 sqlhc_db_null.sql
-rw-r--r-- 1 oracle oinstall 328K Oct 10  2024 sqlhc_db.sql
-rw-r--r-- 1 oracle oinstall  491 Oct 10  2024 sqlhc_exit.sql
-rw-r--r-- 1 oracle oinstall  75K Oct 10  2024 sqlhc_pxhcdr.sql
-rw-r--r-- 1 oracle oinstall 4.4K Oct 10  2024 sqlhc_sta.sql
-rw-r--r-- 1 oracle oinstall  60K Oct 10  2024 util_planx.sql
dr-xr-xr-x 4 oracle oinstall 4.0K Feb 23 17:00 utl
-rw-r--r-- 1 oracle oinstall 337K Feb 23 17:03 sqlhc.sql

测试数据库版本:19.22

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

新增测试数据

SQL> conn cjc/******

创建两张测试表
创建订单表（主表）

CREATE TABLE orders (
    order_id      NUMBER PRIMARY KEY,
    customer_id   NUMBER,
    order_date    DATE,
    total_amount  NUMBER(10,2)
);

创建订单详情表（子表）

CREATE TABLE order_details (
    detail_id    NUMBER PRIMARY KEY,
    order_id     NUMBER,
    product_id   NUMBER,
    quantity     NUMBER,
    price        NUMBER(10,2),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

生成模拟测试数据
向orders表插入10万条数据（耗时操作）

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
SELECT 
    LEVEL AS order_id,
    MOD(LEVEL, 5000) + 1 AS customer_id,  -- 模拟5000个客户
    TO_DATE('2020-01-01', 'YYYY-MM-DD') + MOD(LEVEL, 1461) AS order_date,  -- 日期范围4年
    ROUND(DBMS_RANDOM.VALUE(100, 5000), 2) AS total_amount
FROM DUAL 
CONNECT BY LEVEL <= 100000;

向order_details表插入200万条数据（每个订单20条详情）

INSERT INTO order_details (detail_id, order_id, product_id, quantity, price)
SELECT 
    (LEVEL * 20) AS detail_id,
    CEIL(LEVEL / 20) AS order_id,  -- 每20条对应一个order_id
    MOD(LEVEL, 1000) + 1 AS product_id,  -- 模拟1000种商品
    MOD(LEVEL, 10) + 1 AS quantity,
    ROUND(DBMS_RANDOM.VALUE(10, 500), 2)
FROM DUAL 
CONNECT BY LEVEL <= 2000000;

COMMIT;

收集统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CJC',tabname => 'ORDERS',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CJC',tabname => 'ORDER_DETAILS',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);

手动生成AWR快照

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

查询数据

场景：查询订单详情（假设没有索引）

SELECT /* CJC_TEST_SQLHC_20250517_CJC */
    o.order_id,
    o.order_date,
    d.product_id,
    d.quantity,
    d.price
FROM 
    orders o
    JOIN order_details d ON o.order_id = d.order_id
WHERE 
    o.order_date BETWEEN TO_DATE('2023-12-30', 'YYYY-MM-DD') 
                     AND TO_DATE('2023-12-31', 'YYYY-MM-DD');
/
/
/

执行多次

手动生成AWR快照

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

定位SQL_ID:

SET PAGESIZE 100
COL SQL_TEXT FOR A50
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* CJC_TEST_SQLHC_20250517_CJC */%';

SQL_ID	      PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------
9dmfm1manhtdp	   2245375256 SELECT /* CJC_TEST_SQLHC_20250517_CJC */

如果SQL在v$SQL中不再可用，您可以在AWR历史视图中找到它:DBA_HIST_SQLTEXT和DBA_HIST_SQLSTAT：

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE '%CJC_TEST_SQLHC_20250517_CJC%'
ORDER BY
s.sql_id;

SQL_ID	      PLAN_HASH_VALUE SQL_TEXT						    SNAP_ID
------------- --------------- -------------------------------------------------- ----------
9dmfm1manhtdp	   2245375256 SELECT /* CJC_TEST_SQLHC_20250517_CJC */			 10
				  o.order_id,
				  o.order_date,
          			  D

生成SQLHC日志

SQL> conn / as sysdba
SQL> START /home/oracle/tools/sqlhc/sqlhc.sql "T" 9dmfm1manhtdp

......
Archive:  sqlhc_20250517_0954_9dmfm1manhtdp.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
   159469  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_1_main.html
     5117  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_3_execution_plans.html
     5472  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_4_sql_detail.html
    26456  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_9_log.zip
     5387  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_10_sql_tuning_advisor.out
       25  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_6_10053_trace_from_cursor.trc
      477  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_11_tcb.zip
   444080  05-17-2025 09:58   sqlhc_20250517_0954_9dmfm1manhtdp_12_awr.zip
     3201  05-17-2025 09:58   sqlhc_20250517_0954_9dmfm1manhtdp_7_driver.zip
       42  05-17-2025 09:56   sqlhc_20250517_0954_9dmfm1manhtdp_2_startup_history.txt
    97970  05-17-2025 09:59   sqlhc_20250517_0954_9dmfm1manhtdp_8_sqldx.zip
---------                     -------
   747696                     11 files

查看文件

[oracle@cjc-db-02 tools]$ ls -lrth sqlhc_20250517_0954_9dmfm1manhtdp.zip 
-rw-r--r-- 1 oracle oinstall 592K May 17 09:59 sqlhc_20250517_0954_9dmfm1manhtdp.zip

图片.png

查看报告

sqlhc_20250517_0954_9dmfm1manhtdp_1_main.html

图片.png

SQL Monitor Reports(s)

SQL Tuning Advisor Output

常见问题:

已经对表收集统计信息了，报告里还是没有相关数据，需要在执行SQL前后，自动或手动执行AWR快照。
图片.png

下载:

Download the SQL Tuning Health-Script for 11g and 12c
Download the SQL Tuning Health-Check Script (19.0 and above)

图片.png

关注《IT小Chen》公众号，私信关键字“sqlhc”，可获取下载链接！
图片.png

参考:

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
SQL Tuning Health-Check Script (SQLHC) Video (Doc ID 1455583.1)
FAQ: SQL Health Check (SQLHC) Frequently Asked Questions (Doc ID 1417774.1)

###chenjuchao 20250517###
欢迎关注我的公众号《IT小Chen》