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