What is SQL Tuning Health-Check Script?
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 checks Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.
The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.
It does this while leaving "no database footprint" ensuring it can be run on all systems.
What is the history of SQLHC?
SQLHC is a subset of the SQL used by the SQLTXPLAIN script:
but unlike that tool, has no database footprint.
It is a relatively short SQL script that generates a HTML output. It extracts database statistic information using selects or standard APIs (such as DBMS_XPLAN) from the database and combines that with Automatic Workload Repository (AWR) output if the correct licenses are available.
Download the Download the SQL Tuning Health-Check Script
https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1366133.1&attachid=1366133.1:SQLHC_LATEST_12108&clickstream=yes
SQLHC does not require installation and will not add/modify any objects in database.
2. 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.
Please Note: SQL Tuning Advisor can take up to 20 minutes to run. Please don't interrupt it with ctrl-C
For example:
sqlplus / as sysdba
SQL> START sqlhc.sql "T" (sql_id)
When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view.
How to identify the SQL_ID of a statement
The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view.
For Ex:
SELECT /* TARGET SQL */ * FROM dual;
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql
WHERE sql_text like 'SELECT /* TARGET SQL */%'
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrta4gkpjs 272002086 SELECT /* TARGET SQL */ * FROM dual
You can also find the SQL_ID in the V$SQL view using a substitution variable:
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';
If SQL is no longer available in v$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and 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 'SELECT /* TARGET SQL */%'
ORDER BY
s.sql_id
How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block
If you have a PL/SQL block such as:
declare v1 number;
begin
select /* SimpleTest */ sum(sal) into v1 from emp;
end;
/
Then if you try to find the SQL_ID from v$sql then you will see the SQL_ID of the PL/SQL block NOT the SQL itself:
SQL> select sql_id, sql_text from v$sql where sql_text like '%SimpleTest%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------
88hjjr9qgwtzm declare v1 number; begin select /* SimpleTest */ sum(sal) into v1 from emp; end;
The SQL statement within the PL/SQL block is actually stored separately, but you cannot see it
In other words,
select /* SimpleTest */ sum(sal) into v1 from emp
is stored as
SELECT SUM(SAL) FROM EMP
In order to find it's SQL_ID you would need to search on something similar to the following:
SQL> select sql_id, sql_text from v$sql where sql_text like '%SUM(SAL)%EMP%';
SQL_ID SQL_TEXT
------------- -------------------------------
4mqhh85sm178a SELECT SUM(SAL) FROM EMP
The SQL_ID can also be determined by using the hash_value from a SQL_TRACE. The hash value can be seen in the raw trace file identified by the "hv=" string.
------------------------------------------------------------------------
PARSING IN CURSOR #1 len=24 dep=1 uid=54 oct=3 lid=54 tim=1194298465705687 hv=2899044105 ad='997aa660'
SELECT SUM(SAL) FROM EMP
END OF STMT
-------------------------------------------------------------------
In this case the hash value is 2899044105. To find the SQL_ID using the hash value use the following select:
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE hash_value = &Hash_Value;
SQL_ID HASH_VALUE SQL_TEXT
------------- ---------- -------------------------------
4mqhh85sm278a 1899044106 SELECT SUM(SAL) FROM EMP
Enjoy the work and do like me if you like this article!!!
