这里先将针对于全库的 SQL ordered by Elapsed Time 脚本的用法,做一记录。 1.变量取得部分 这一部分意思是,取得结果部分需要的变量。取得的值,包括dbid,db_name,instance_number,inst_name,begin_snap_id,end_snap_id,elapsed,db_time,transaction_count。 但是,在实际使用时,总是提示 ORA-00933:SQL command ...
c.SAMPLE_TIME 执行时间, c.INSTANCE_NUMBER 实例数, u.username 用户名, a.sql_id SQL编号 from dba_hist_sqltext a, (select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime from dba_hist_sqlstat where ELAPSED_TIME_DELTA / 1000000 >= 1) b, dba_hist_active_sess_history c, dba_users u ...
sum(elapsed_time_delta) els, sum(elapsed_time_delta)/greatest(sum(executions_delta),1) els_per_exec, sum(buffer_gets_delta) gets, sum(buffer_gets_delta)/greatest(sum(executions_delta),1) get_per_exec, sum(executions_delta) execs, sum(rows_processed_delta) rowcnt, sum(elapsed_time_delta...
ELAPSED_TIME_DELTA/1000000ASetimeFROMdba_hist_sqlstatWHEREELAPSED_TIME_DELTA/1000000>=1) b, dba_hist_active_sess_history c, dba_users uWHEREa.sql_id=b.sql_idANDu.username='SYNC_PLUS_1_20190109'ANDc.user_id=u.user_idANDb.sql_id=c.sql_id--and a.sql_text like '%insert into GK_Z...
select to_char(sysdate-1/24,'yyyy-mm-dd hh24') as snapshot_time,--快照时间 v_1.sql_id, v_1.elapsed_time,--一小时内累计耗时 v_1.cpu_time,--一小时内累计CPU时间 v_1.iowait_time,--一小时内累计io等待时间 v_1.gets,--一小时内累逻辑读 ...
(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime from dba_hist_sqlstat where ELAPSED_TIME_DELTA / 1000000 >= 1) b, dba_hist_active_sess_history c, dba_users u where a.sql_id = b.sql_id and u.username = 'MYDB' and c.user_id = u.user_id ...
cpu_time_delta/1000000 cpu_time_delta_sec, elapsed_time_delta/1000000 elapsed_time_delta_sec, iowait_delta/1000000 iowait_time_delta_sec, ccwait_delta/1000000 concurrent_wait_delta_sec, clwait_delta/1000000 cluster_wait_delta_sec from dba_hist_snapshot st, dba_hist_sqlstat sql_st ...
,round(sum(hsql.ELAPSED_TIME_DELTA)/sum(EXECUTIONS_DELTA)/1000,3) "每次平均时间(毫秒)" from dba_hist_sqlstat hsql, dba_hist_snapshot hsnp where hsql.snap_id = hsnp.snap_id and hsql.instance_number=hsnp.instance_number and hsql.dbid=hsnp.dbid ...
/rdbms/admin/awrrpt 脚本中指定这2个快照的话,那么其elapsed = (6-4)=2 个小时),一个AWR性能报告 至少需要2个AWR snapshot性能快照才能生成 ( 注意这2个快照时间 实例不能重启过,否则指定这2个快照生成AWR性能报告 会报错),AWR性能报告中的 指标往往是 后一个快照和前一个快照的 指标的delta,这是因为 ...
SELECT sql_id, elapsed_time_delta / 1000000 AS total_elapsed_seconds, executions_delta, elapsed_time_delta / decode(executions_delta, 0, 1, executions_delta) / 1000000 AS avg_elapsed_per_exec FROM dba_hist_sqlstat WHERE begin_interval_time >= SYSDATE - 1 -- 过去一天内的数据 ORDER ...