q_sql_avg_time

admin

1. --sql hist execs avg_time
   
2. set lines 200
   
3. col execs for 999,999,999
   
4. col avg_etime for 999,999,999
   
5. col avg_lio for 999,999,999
   
6. col begin_interval_time for a30
   
7. col node for 99999
   
8. break on plan_hash_value on startup_time skip 1
   
9. select ss.snap_id,ss.instance_number node,begin_interval_time,sql_id,plan_hash_value,
   
10. nvl(executions_delta,0) execs,
    
11. (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime
    
12. from dba_hist_sqlstat s,dba_hist_snapshot ss
    
13. where sql_id='&sql_id'
    
14. and ss.snap_id=s.snap_id
    
15. and ss.instance_number=s.instance_number
    
16. and executions_delta>0
    
17. order by 1,2,3
    
18. /

复制代码