If you’re an Oracle DBA or Developer, you may need to find SQL details from library cache to troubleshoot and optimize SQL query performance. In this article, we’ll show you how to retrieve vital execution statistics using a simple yet powerful SQL script.
Table of Contents
Find the query details from library cache:
set lines 200
col node for 99999
col begin_interval_time for a30
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col avg_cpu_time for 999,999.999
col avg_lio for 999,999,999.9
col avg_pio for 9,999,999.9
break on plan_hash_value on startup_time skip 1
select sql_id, plan_hash_value,
sum(execs) execs,
sum(etime) etime,
sum(etime)/sum(execs) avg_etime,
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio,
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','ambasa_sqlid')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5
/
Sample Output:
The output of the script will look something like this:
SQL_ID: unique identifier for the SQL statement.
CHILD: Differentiates between execution plans.
EXECS: total number of times the SQL statement has been executed.
EXECS_PER_SEC: The average number of times the SQL statement has been executed per second since it was first loaded into the library cache.
ETIME: This represents the total elapsed time in seconds that the SQL statement took to execute.
AVG_ETIME: The average elapsed time (in seconds) for each execution of the SQL statement. This is calculated by dividing ETIME by EXECS.
CPU: Total CPU time in seconds spent executing the SQL statement.
AVG_CPU: average CPU time (in seconds) per execution. It is calculated by dividing CPU by EXECS.
PIO: total number of physical I/O operations (disk reads) required to execute the SQL statement.
AVG_PIO: The average number of physical disk reads per execution. This is calculated by dividing PIO by EXECS.
LIO: The total number of logical I/O operations (buffer gets) performed by the SQL statement. Logical I/O refers to the number of blocks Oracle accesses in the buffer cache.
AVG_LIO: The average number of logical I/O operations per execution, calculated by dividing LIO by EXECS.
This output shows the SQL ID, child number, execution count, execution time, CPU time, disk reads, and buffer gets, giving you an in-depth view of the query performance.
By running this Find SQL Details From Library Cache script, you can quickly access key performance metrics for SQL queries in Oracle databases. This data will help you fine-tune your queries for better performance, leading to faster execution and more efficient resource usage.
For more information on Oracle SQL performance tuning, check out this Oracle SQL Tuning Guide
If you want to find Top SQL in the library cache, You can go through my other article, Find top SQL in Library Cache
