了解一下sqlhc

原创

wx5b8b656889613 2023-06-16 01:18:51 ©著作权

文章标签 数据库 mysql oracle sql python 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者wx5b8b656889613的原创作品，请联系作者获取转载授权，否则将追究法律责任

看到很多大神，尤其是老虎刘老师在做SQL调优的时候，都提到了sqlhc，找了一些资料，借此机会，学习一下。

了解一下sqlhc_oracle

sqlhc是SQL Tuning Health-Check Script的缩写，他是一个Oracle内部团队开发的工具，用来采集SQL语句执行的环境、SQL相关表和索引的数据、CBO统计信息、优化器参数、对象元数据、配置参数，SQL执行情况、等待事件、以及其他可能影响SQL性能的因素，帮助检查SQL存在的问题并优化SQL，

What is the SQL Tuning Health-Check Script (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.

sqlhc其实是一套SQL脚本，可以从这篇MOS下载，SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)，sqlhc.zip解压缩可以看到包含了这些脚本，其中要用的，就是sqlhc.sql，

了解一下sqlhc_sql_02

从注释可以看到，作者是Oracle的Mauro Pagano，该脚本无需在数据库中创建任何对象，仅需要对PLAN_TABLE执行DML，并且会回滚所有的临时性插入操作，同时这个脚本能用在DG或者任何只读的数据库。该脚本的执行需要使用SYS或者授予DBA角色的用户或者能访问数据字典视图的普通用户。他的输入参数，一个是Oracle Pack license，取值T|D|N，分别表示Tuning or Diagnostics or None，一般选择T，另一个就是sql_id，

REM AUTHOR
REM   carlos.sierra@oracle.com
REM   Mauro Pagano
REM   Stelios.charalambides@oracle.com
REM
REM SCRIPT
REM   sqlhc.sql SQL Health-Check (extract mode)
REM
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.
REM
REM PRE-REQUISITES
REM   1. Execute as SYS or user with DBA role or user with access
REM      to data dictionary views.
REM   2. The SQL for which this script is executed must be
REM      memory-resident.
REM
REM PARAMETERS
REM   1. Oracle Pack license (Tuning or Diagnostics or None) T|D|N
REM   2. SQL_ID of interest.
REM
REM EXECUTION
REM   1. Start SQL*Plus connecting as SYS or user with DBA role or
REM      user with access to data dictionary views.
REM   2. Execute script sqlhc.sql passing values for parameters.
REM
REM EXAMPLE
REM   # sqlplus / as sysdba
REM   SQL> START [path]sqlhc.sql [T|D|N] [SQL_ID]
REM   SQL> START sqlhc.sql T 51x6yr9ym5hdc
REM
REM NOTES
REM   1. For possible errors see sqlhc.log.
REM   2. If site has both Tuning and Diagnostics licenses then
REM      specified T (Oracle Tuning pack includes Oracle Diagnostics)
REM   3. On a read-only instance, the "Observations" p with the
REM      results of the health-checks will be missing.
REM

sqlhc无需license，唯一要注意的，就是他需要AWR数据，因此确认你的数据库取得了AWR的使用授权

Licensing (SQLHC requires no license and is FREE)
As in the title of this p, 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.

sqlhc会给出这些和CBO相关的数据，

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

这个PPT，对sqlhc做了更加详细的介绍，

https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=1366133.1%3ASQLHC_PPT&docType=SCRIPT&action=download

sqlhc输出包括了以下内容，

了解一下sqlhc_数据库_03

例如我们执行如下的测试SQL，SQL很简单，t表的object_id存在索引，但是用了to_char函数，根据to_char(object_id)条件得到object_name，

SQL> select object_name from t where to_char(object_id)='1000';


OBJECT_NAME
----------------------------------------------------------------
APPLY$_READER_STATS_I

执行如下指令，其中32675ztaq0296是该SQL的sql_id，

SQL> @sqlhc T 32675ztaq0296
...

我们就会在当前路径下，得到一个压缩包，格式形如"sqlhc_年月日_时分_sqlid.zip"，例如，sqlhc_20201226_2223_32675ztaq0296.zip，

了解一下sqlhc_sql_04

其中，

(1) 10053 trace文件的生成需要11.2版本以上，sql_id仍在library cache内的情况下。

(2) 如果SQL执行时间超过了5s，或者是并行的SQL，而且收集sqlhc时仍保留在sql monitor的内存，*sql_monitor.zip就会包含在sqlhc压缩包内。

从这些文件中，我们能知道SQL当前的执行计划，

了解一下sqlhc_python_05

可视化的SQL执行的数据，

了解一下sqlhc_python_06

同时生成了STA(SQL Tuning Advisor)，针对这条SQL，给出了两条建议，第一条是建议创建复合索引(to_char(object_id), object_name)，让SQL用上索引，同时避免回表，

1- Index Finding (see explain plans p below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.


  Recommendation (estimated benefit: 99.24%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index BISAL.IDX$$_01010001 on BISAL.T(TO_CHAR("OBJECT_ID"),"OBJECT_N
    AME");


  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

第二条是重构SQL，指出object_id存在索引，但是因为使用了表达式，无法使用索引，建议修改谓词条件以便用上索引，或者创建一个基于表达式的函数索引，

2- Restructure SQL finding (see plan 1 in explain plans p)
----------------------------------------------------------------
  The predicate TO_CHAR("T"."OBJECT_ID")='1000' used at line ID 1 of the
  execution plan contains an expression on indexed column "OBJECT_ID". This
  expression prevents the optimizer from selecting indices on table
  "BISAL"."T".


  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

并且给出了原始的执行计划，以及采用第一种建议，创建复合索引的执行计划，

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original
-----------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    40 |   395   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    40 |   395   (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("OBJECT_ID")='1000')


2- Using New Indices
--------------------
Plan hash value: 2175788560
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_01010001 |     1 |    40 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."SYS_QSMMIX_VCOL_5001"='1000')
-------------------------------------------------------------------------------

sqlhc就像个万花筒，帮助我们采集了定位SQL性能问题相关的各种数据，HTML中的数据还是值得我们学习了解的，而且如果有自研的数据库平台，应该是可以和他进行对接，