Scripts:从AWR中找到SQL执行相关的东西(弄懂脚本再用哦)find_sql_awr.sql
来源:互联网 发布:南京mac专柜价格 编辑:程序博客网 时间:2024/05/29 05:01
set long 32000
set lines 155
col sql_text format a40
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio,
sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.snap_id between nvl('&starting_snap_id',0) and nvl('&ending_snap_id',999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
and sql_text like nvl('&sql_text',sql_text)
and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/
set lines 155
col sql_text format a40
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio,
sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.snap_id between nvl('&starting_snap_id',0) and nvl('&ending_snap_id',999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
and sql_text like nvl('&sql_text',sql_text)
and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/
0 0
- Scripts:从AWR中找到SQL执行相关的东西(弄懂脚本再用哦)find_sql_awr.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- Scripts:重建索引的脚本(看懂脚本再用)rebuild_indx.sql
- Scripts:根据sql_id从AWR中查询执行计划(个人比较喜欢使用这个)display_awr_plan.sql
- Scripts:根据sql_id,child_no从AWR中查找执行计划dplan.sql
- Scripts:打印执行计划中AWR的执行计划信息awr_plan_stats.sql
- 利用从awr中查找好的执行计划来优化SQL
- Scripts:查看AWR中热点物理读awrtoprdrpt.sql
- Scrips:打印AWR中执行计划的改变awr_plan_change.sql
- Scripts:显示数据库中DML锁的脚本 locks_dml_lock_time.sql
- Scripts:根据sql_id和hash_value查询AWR中的执行计划dplan_awr.sql
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:查看回滚的SQL的脚本 huigun.sql
- Scripts:从shared_pool中创建并删除profile的脚本(注意,此脚本危险比较高,生产环境不要使用)flush_sql10p.sql
- Scripts:生成随机数的SQL脚本dba_random_number.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:给出系统pid找出sql的脚本pid4sql.sql
- .net中sql脚本的执行
- js判断 手机端还是pc端
- Maven help 插件
- 微信公众号开发者申请,Token验证(asp.net)
- Hibernate原理
- HDU 1180 诡异的楼梯(BFS:时间动态图)
- Scripts:从AWR中找到SQL执行相关的东西(弄懂脚本再用哦)find_sql_awr.sql
- week 14 lianxi
- OpenCV中关于cvGetCaptureProperty函数
- UDP端口绑定
- Maven 核心概念——插件和目标
- Linux下编译mongodb以及C++客户端驱动
- Ajax XMLHttpRequest对象 取得返回值
- c2java 回溯,下一个排列和子集和
- Maven 核心概念——生命周期