了解一下sqlhc

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.

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" section with the
REM      results of the health-checks will be missing.
REM

Licensing (SQLHC requires no license and is FREE)

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.

Health-checks are performed over:CBO Statistics for schema objects accessed by the one SQL statement being analyzedCBO ParametersCBO System StatisticsCBO Data Dictionary StatisticsCBO Fixed-objects Statistics

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

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

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

SQL> @sqlhc T 32675ztaq0296
...

1- Index Finding (see explain plans section 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.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  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')
-------------------------------------------------------------------------------