SQL History Plan change

COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT h.instance_number,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'YYYY-MM-DD HH24:MI') end_time,
       h.plan_hash_value,
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h,
       dba_hist_snapshot s
 WHERE h.sql_id = '1ptjkbswfrthv'
   AND h.executions_total > 0
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/

EL1PROD1










select sql_id, snap.dbid, snap.snap_id, begin_interval_time, end_interval_time, plan_hash_value, executions_delta,
trunc(decode(executions_delta, 0, elapsed_time_delta, elapsed_time_delta/executions_delta)*0.000001) as "ELAPSED_TIME_AVG IN SECONDS",
trunc(decode(executions_delta, 0, rows_processed_delta, rows_processed_delta/executions_delta)) rows_avg,
trunc(decode(executions_delta, 0, fetches_delta, fetches_delta/executions_delta)) fetches_avg,
trunc(decode(executions_delta, 0, disk_reads_delta, disk_reads_delta/executions_delta)) disk_reads_avg,
trunc(decode(executions_delta, 0, buffer_gets_delta, buffer_gets_delta/executions_delta)) buffer_gets_avg,
trunc(decode(executions_delta, 0, cpu_time_delta, cpu_time_delta/executions_delta)) cpu_time_avg,
trunc(decode(executions_delta, 0, iowait_delta, iowait_delta/executions_delta)) iowait_time_avg
from dba_hist_sqlstat stat, dba_hist_snapshot snap
where stat.snap_id = snap.snap_id
and stat.dbid = snap.dbid
and stat.instance_number = snap.instance_number
and sql_id ='f8hmtwdb4p3vy'
order by sql_id, snap.snap_id desc;








set serveroutput on
begin
 for c in (select address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='ffvgfm36z5dkc')
 loop
  dbms_output.put_line(c.users_executing||' users executing '||c.sql_text);
  sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'...');
  dbms_output.put_line('flushed.');
 end loop;
end;
/

2 comments:

  1. AdithyaWednesday, September 11, 2019

    select inst_id,sql_id,LAST_ACTIVE_TIME,PLAN_HASH_VALUE,ROWS_PROCESSED,EXECUTIONS,round(ELAPSED_TIME/1000000) as ELAPSED_TIME_SECONDS,
    BUFFER_GETS,CPU_TIME
    from gv$sqlstats where sql_id ='9dtxw5f7qdnvg'

    ReplyDelete
  2. AnonymousThursday, November 14, 2024

    select
    sql_id,
    (case command_type
    when 1 then 'CREATE TABLE'
    when 2 then 'INSERT'
    when 3 then 'SELECT'
    when 6 then 'UPDATE'
    when 7 then 'DELETE'
    when 9 then 'Create Index'
    when 11 then 'ALTER INDEX'
    when 25 then 'ALTER PROCEDURE'
    when 26 then 'LOCK Table'
    when 42 then 'ALTER_SESSION'
    when 44 then 'COMMIT'
    when 45 then 'Rollback'
    when 46 then 'Savepoint'
    when 47 then 'PL SQL BLOCK'
    when 48 then 'Set transaction'
    when 50 then 'Explain'
    when 62 then 'Analyze table'
    when 90 then 'Set Constraints'
    when 170 then 'Call'
    when 189 then 'Merge'
    end ) Command_Type,
    (select substr(sql_text,1,200) from dba_hist_sqltext m where m.sql_id=A.sql_id and DBID=(select dbid from v\$database)) sql_text,
    (select max(PARSING_SCHEMA_NAME) from dba_hist_sqlstat k where k.sql_id=a.sql_id ) USER_NAME,
    ELAP_SEC ,
    executions TOTAL_EXECUTIONS,
    pio_per_exec,
    lio_per_exec
    from (
    select
    q.sql_id,H.command_type
    , sum(q.EXECUTIONS_DELTA) executions
    , round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
    , round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
    , round(sum(ELAPSED_TIME_delta),1) ELAPSED_TIME_delta
    , round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),2) ELAP_SEC
    from dba_hist_sqlstat q, dba_hist_snapshot s, dba_hist_sqltext H
    where s.snap_id = q.snap_id
    and s.begin_interval_time>=sysdate-1
    --and q.Module='JDBC Thin Client'
    and q.parsing_schema_name not in ( 'SYS')
    and s.dbid = q.dbid
    and s.instance_number = q.instance_number
    and q.dbid=h.dbid
    and q.sql_id=H.sql_id
    group by q.sql_id,h.command_type
    ) A where ELAP_SEC >1 and command_type <>47 order by 5 desc);

    ReplyDelete

Subscribe to: Posts (Atom)