SQL Health Check (SQLHC)
Description
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.
SQL Health Check (SQLHC)
Overview: SQLHC checks elements that may affect the performance of the SQL being analyzed, such as Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters. SQLHC is a subset of SQLT, which we will discuss next. This script is a quick way to diagnose problems without having SQLT installed.
Documentation and Install:SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
Proactive: Use this script to examine SQL that is not running up to standards while testing an upgrade.
Reactive: Use this script to get recommendations on problem SQL after an upgrade.
Caveats: SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems.
Parameters Required:
- Login to the database server and set the environment used by the Database Instance.
- Download the “sqlhc.zip” archive file and extract the contents to a suitable directory/folder.
- Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the “sqlhc.sql” script. It will request to enter two parameters:
- Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics) - A valid SQL_ID for the SQL to be analyzed.
- Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
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
[oracle@ram oracle]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 24 22:06:19 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- TEST READ WRITE SQL> create user d1 identified by d1 default tablespace tbsnew; User created. SQL> grant dba to d1; Grant succeeded. SQL> conn d1/d1 Connected. SQL> create table t1 (id number,name varchar2(100),salary number); Table created. SQL> begin for i in 1..100000 loop insert into t1 values(i,'testing',20000); commit; end loop; end; / PL/SQL procedure successfully completed. SQL> create table t2 (id number,name varchar2(100),salary number); Table created. SQL> begin for i in 1..1000 loop insert into emp values(i,'testing',2000); commit; end loop; end; / SQL> begin for i in 1..1000 loop insert into t2 values(i,'testing',2000); commit; end loop; end; / PL/SQL procedure successfully completed. SQL>
I am doing Few updations in t1 table
SQL>update t1 set name='vicky' where id between 1 and 1000; 2000 rows updated. SQL>update t1 set name='venkat' where id between 1001 and 2000; 2000 rows updated. SQL>update t1 set name='gayathri' where id between 2001 and 3000; 2000 rows updated. SQL>update t1 set name='haja' where id between 3001 and 4000; 2000 rows updated. SQL>update t1 set name='srinath' where id between 4001 and 5000; 2000 rows updated. SQL>update t1 set name='senthil' where id between 5001 and 6000; 2000 rows updated. SQL>update t1 set name='ram' where id between 6001 and 7000; 2000 rows updated. SQL>update t1 set name='karthi' where id between 7001 and 8000; 2000 rows updated. SQL>update t1 set name='venkat' where id between 10001 and 20000; 20000 rows updated. SQL>update t1 set name='gayathri' where id between 20001 and 30000; 20000 rows updated. SQL>update t1 set name='haja' where id between 30001 and 40000; 19999 rows updated. SQL>update t1 set name='srinath' where id between 40001 and 50000; 10000 rows updated. SQL>update t1 set name='senthil' where id between 50001 and 60000; 10000 rows updated. SQL>update t1 set name='ram' where id between 60001 and 70000; 10000 rows updated. SQL>update t1 set name='karthi' where id between 70001 and 80000; 10000 rows updated. SQL>delete from t1 where id between 1 and 1000; 2000 rows deleted. SQL>delete from t1 where id between 1001 and 2000; 2000 rows deleted. SQL>delete from t1 where id between 2001 and 3000; 2000 rows deleted. SQL>delete from t1 where id between 3001 and 4000; 2000 rows deleted. SQL>delete from t1 where id between 4001 and 5000; 2000 rows deleted. SQL>delete from t1 where id between 5001 and 6000; 2000 rows deleted. SQL>delete from t1 where id between 6001 and 7000; 2000 rows deleted. SQL>delete from t1 where id between 7001 and 8000; 2000 rows deleted. SQL>delete from t1 where id between 10001 and 20000; 20000 rows deleted. SQL>delete from t1 where id between 40001 and 50000; 10000 rows deleted.
For particular SQL Id : b62q7nc33gzwx we need going to check health
I attached the output files here.
SQL HEALTH CHECK:
Full output screen you can see here
SQL DIAGNOSTICS:
Full output screen you can see here
SQL EXECUTION PLAN:
Full output screen you can see here
SQL DETAIL:
sqlhc_20181024_232227_b62q7nc33gzwx_4_sql_detail
Log Files:
sqlhc_20181024_232227_b62q7nc33gzwx_8_sqldx sqlhc_20181024_232227_b62q7nc33gzwx_9_log
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
