Oracle SQL Tuning Health-Check (SQLHC)

Oracle SQL Tuning Health-Check (SQLHC)

The performance of an SQL statement mainly depends on good physical structure, accurate system statistics, accurate object statistics, reasonable query optimizer parameters, and reasonable system parameters. this data is the basic parameter for CBO computing. SQLHC (Document ID 1366133.1) provides the SQL data to help you optimize SQL statements. it is especially suitable for situations where optimization cannot be performed in the customer's environment. Although SQLHC is not more powerful than SQLT (Document ID 215187.1), it does not need to be created on the collected database, will be accepted by more customers.

The following demo:
OS: CentOS 6.6
Db: Oracle 11.2.0.4
#Sqlhc.zip download in mos Document ID 1366133.1

Unzip decompress sqlhc.zip
[Oracle @ ct6604 ~] $ Unzip sqlhc.zip
Archive: sqlhc.zip
Creating: sqlhc/
Inflating: sqlhc/sqlhc. SQL
Inflating: sqlhc/sqldx. SQL
Inflating: sqlhc/sqlhcxec. SQL
[Oracle @ ct6604 ~] $ Cd sqlhc
[Oracle @ ct6604 sqlhc] $ ls
Sqldx. SQL sqlhc. SQL sqlhcxec. SQL

# Run the test SQL
[Oracle @ ct6604 sqlhc] $ ORACLE_SID = ctdb
[Oracle @ ct6604 sqlhc] $ sqlplus/as sysdba
# In SQL, gather_plan_statistics is used to display a-row in the generated report.
# The SQL monitor is used to generate the SQL _monitor report.
SQL> select/* + gather_plan_statistics monitor */a. * from scott. emp a, scott. dept B where a. deptno = B. deptno and B. dname = 'sales ';
/*
...
*/

SQL> select SQL _text, SQL _id from v $ SQL where SQL _text like 'select/* + gather_plan_statistics monitor */a. * from scott. emp a % ';
/*
SQL _TEXT
--------------------------------------------------------------------------------
SQL _ID
-------------
Select/* + gather_plan_statistics monitor */a. * from scott. emp a, scott. dept B whe
Re a. deptno = B. deptno and B. dname = 'sales'
9pq9f4vkb9fvb
*/

# Generate a SQLHC report
SQL> start sqlhc. SQL T 9pq9f4vkb9fvb

/*
...
Archive: sqlhc_20160516_145204_9pq9f4vkb9fvb.zip
Length Date Time Name
----------------------------
7756 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_1_health_check.html
119553 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_2_diagnostics.html
7938 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_3_execution_plans.html
50903 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_4_ SQL _detail.html
449937 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_9_log.zip
6018 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_5_ SQL _monitor.zip
147123 05-16-2016 1452 sqlhc_20160516_145204_9pq9f4vkb9fvb_6_10053_trace_from_cursor.trc
56808 05-16-2016 sqlhc_20160516_145204_9pq9f4vkb9fvb_8_sqldx.zip
----------------
846036 8 files
*/

# SQLHC report description
1_health_check.html
Observations: displays the items output by health-checks that may be faulty and determines whether to change the items. For example, the object statistics are too old.
SQL Text: the SQL Text to be checked
Tables Summary: displays the statistics of related Tables.
Indexes Summary: displays the statistics of related Indexes.
2_diagnostics.html
SQL Text: the SQL Text to be checked
SQL Plan Baselines (DBA_ SQL _PLAN_BASELINES): plan history of this SQL statement in DBA_ SQL _PLAN_BASELINES
SQL Profiles (DBA_ SQL _PROFILES): profiles of this SQL statement in DBA_ SQL _PROFILES
SQL Patches (DBA_ SQL _PATCHES): patches related to this SQL in DBA_ SQL _PATCHES
Cursor Sharing and Reason: GV $ SQL _SHARED_CURSOR is the cursor sharing of this SQL statement.
Cursor Sharing List: cursor sharing of this SQL statement in GV $ SQL _SHARED_CURSOR
Current Plans Summary (GV $ SQL): average consumption of this SQL statement in GV $ SQL
Current SQL Statistics (GV $ SQL): the consumption of this SQL statement in GV $ SQL
Historical Plans Summary (DBA_HIST_SQLSTAT): average Historical consumption of this SQL statement in DBA_HIST_SQLSTAT
Historical SQL Statistics-Delta (DBA_HIST_SQLSTAT): Historical consumption of this SQL statement in DBA_HIST_SQLSTAT
Historical SQL Statistics-Total (DBA_HIST_SQLSTAT): Historical consumption of this SQL statement in DBA_HIST_SQLSTAT
Active Session History by Plan (GV $ ACTIVE_SESSION_HISTORY): session state statistics for this SQL statement in GV $ ACTIVE_SESSION_HISTORY
Active Session History by Plan Line (GV $ ACTIVE_SESSION_HISTORY): plan line statistics of this SQL statement in GV $ ACTIVE_SESSION_HISTORY
AWR Active Session History by Plan (DBA_HIST_ACTIVE_SESS_HISTORY): session state statistics for this SQL statement in DBA_HIST_ACTIVE_SESS_HISTORY
AWR Active Session History by Plan Line (DBA_HIST_ACTIVE_SESS_HISTORY): plan line statistics of this SQL statement in DBA_HIST_ACTIVE_SESS_HISTORY
DBMS_STATS System Preferences: DBMS_STATS System Parameters
Tables: Table-related statistics
DBMS_STATS Table Preferences: DBMS_STATS Table parameters
Table Columns: column-related statistics
Table Partitions: Table partition-related statistics
Table Constraints: Constraints on a Table
Tables Statistics Versions: Table-related Statistics
Indexes: Index-related statistics
Index Columns: Index column-related statistics
Index Partitions: Index partition-related statistics
Indexes Statistics Versions: Index-related Statistics
System Parameters with Non-Default or Modified Values: isdefault = 'false' OR ismodified in GV $ SYSTEM_PARAMETER2! = 'False' Parameter
Instance Parameters: System Parameters in V $ SYSTEM_PARAMETER2
Metadata: Table and index creation statement
3_execution_plans.html
SQL Text: the SQL Text to be checked
Current Execution Plans (last execution): displays the Execution plan in memory of this SQL statement by child cursor
Current Execution Plans (all executions): displays the Execution plan in memory of this SQL statement by child cursor
Historical Execution Plans: displays the awr Execution plan of this SQL statement by child cursor
4_ SQL _detail.html
Graphical display of SQL running statistics
5_ SQL _monitor.zip
Graphical display of SQL monitoring information
6_10053_trace_from_cursor.trc
Display SQL 10053 trace files
8_sqldx.zip
SQL health check Data Source
9_log.zip
SQL health check logs generated