Category Archives: Diagnostic and Tuning

This category contains scripts meant to help diagnose or troubleshoot performance problems related to the Oracle Database.

SQL Tuning Health Check (SQLHC)

Diagnostic and Tuning, Scripts, Troubleshooting

What is SQL Tuning Health Check?

Sql Tuning Health Check
The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs.
It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed.

The script generates an HTML report with information on data collected by the script. The script has no footprint and can be run on any system.

You must connect as SYS or with a user with the DBA role.
The script takes 2 parameters, the first one is about your licensing (Tuning or Diagnostics or None T|D|N), the second is the SQL_ID that needs to be analyzed. Not that if you want to use both the Diagnostic and Tuninbg pack you have to use T as the first parameter.
Continue reading SQL Tuning Health Check (SQLHC) →

SQL Activity for the last hour

Oracle Active Session History (ASH)

This script can be used to show the top 10 SQL activity for the last hour.
It uses the v$active_session_history view to search top SQL by resource consumption.

Top 10 SQL Activity

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1))   desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id 
order by trunc(sample_time,'MI') desc

Here is the result you can obtain:

SQL activity

and the active sessions history graph for the same period:

instance activity last hour