Sunday 1 December 2013

SQL Behaviour with the SQL_ID


You can get the behavior of SQL query with SQL ID


set linesize 200
set pagesize 200

col BEGIN_INTERVAL_TIME format A28
col END_INTERVAL_TIME format A28
col SNAP_ID format 999999 heading "Snap"
col PLAN_HASH_VALUE format 99999999999 heading "Plan|Hash"
col TOTAL_CNT noprint
col EXECUTIONS_DELTA format 999,999,999 heading "Executions"
col ELAPSED_TIME_DELTA format 999,999 heading "Total|Elapsed|(secs)"
col BUFFER_GETS_DELTA format 999,999,999 heading "Buffer Gets"
col DISK_READS_DELTA format 999,999,999 heading "Disk|Reads"
col CPU_TIME_DELTA format 999,999 heading "Total CPU|(secs)"
col IOWAIT_DELTA format 999,999,999,999 heading "IO|Delta"
col ELAPSED_PER_EXECUCTION format 9,999.9 heading "Elapsed|/Exec|(secs)"
col GETS_PER_EXECUCTION format 999,999,999 heading "Buffer|Gets|/Exec"
col CPU_PER_EXECUCTION format 999.99 heading "CPU Time|/Exec|(secs)"
col DISKREADS_PER_EXECUCTION format 999,999,999 heading "Disk Reads|/Exec"
col ROWS_PROCESSED_DELTA format 999,999,999 heading "Rows|Processed"
col ROWS_PROCESSED_PER_EXECUTION format 999,999 heading "Rows|/Exec"
col IO_PER_EXECUCTION format 999,999,999 heading "IO|/Exec"
col REPORT_TIME format A15
col SNAP_ID_FOUND format A12 heading "First/Last|snap|found in"
col SPACER format A5 heading ' '
col INSTANCE_NUMBER format 9 heading "I" print
set linesize 200
SELECT B.INSTANCE_NUMBER, B.BEGIN_INTERVAL_TIME, B.END_INTERVAL_TIME, PLAN_HASH_VALUE, A.EXECUTIONS_DELTA, A.DISK_READS_DELTA, A.BUFFER_GETS_DELTA,
round(A.ROWS_PROCESSED_DELTA / DECODE(A.EXECUTIONS_DELTA,0,1, A.EXECUTIONS_DELTA)) ROWS_PROCESSED_PER_EXECUTION,
round(A.DISK_READS_DELTA / DECODE(A.EXECUTIONS_DELTA,0,1, A.EXECUTIONS_DELTA)) diskreads_per_execuction,
round(A.BUFFER_GETS_DELTA / DECODE(A.EXECUTIONS_DELTA,0,1, A.EXECUTIONS_DELTA)) gets_per_execuction,
round(( A.ELAPSED_TIME_DELTA / 1000 / 1000) / DECODE(A.EXECUTIONS_DELTA,0,1, A.EXECUTIONS_DELTA),1) elapsed_per_execuction
from DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT B
where A.SNAP_ID = B.SNAP_ID
and A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
and A.SQL_ID = '&SQL_ID'
and B.BEGIN_INTERVAL_TIME >= sysdate - &GO_BACK
order by B.BEGIN_INTERVAL_TIME, B.INSTANCE_NUMBER;


Please pass the SQL_ID and GO_BACK (for how many days you want to check the SQL behaviour)

Here is an example to pass the values.

Enter value for sql_id: 1fkh93md0802n
Enter value for go_back: 3




No comments: