oracle脚本整理2-等待事件检测

转载请标明出处：

 

使用ORACLE的等待事件检测性能瓶颈

案例分析：以sys用户登录

1.首先从整个系统考虑，找到从系统启动以来最主要的等待事件是什么？
SQL>@syssum.sql

2.更为重要的是，在应用执行的时候，最主要的等待事件是什么？
SQL>@cr_base.sql
SQL>@in_base.sql
执行用户应用操作一段时间后

SQL>@re_base.sql

3. 查询产生等待事件最多的哪些会话？？（以等待事件log file parallel write为例）

SQL>@seswt.sql log

4.查找哪些语句引起的等待，按照最消耗资源的顺序排列显示
SQL>@sqls1.sql 1000 1000

--以最消耗资源会话13为例
SQL>@sqlst.sql 13
查找出相关sql语句的地址比如：2168CBB8
SQL>@sqls2.sql 2168CBB8

 

 

 

附录：脚本

--syssum.sql
set linesize 200
col "Wait Event" for a45
select EVENT "Wait Event",TIME_WAITED "Time Waited",
       TIME_WAITED/(SELECT SUM(TIME_WAITED) FROM v$system_event) "%Time waited",
       TOTAL_WAITS "Waits",
       TOTAL_WAITS/(SELECT SUM(TOTAL_WAITS) FROM V$system_event) "%Waited"
from v$system_event
order by 3 desc;

--cr_base.sql
create table sys_b(
event varchar2(64),
time_waited number,
total_waits number);

create table sys_e(
event varchar2(64),
time_waited  number,
total_waits  number);

--in_base.sql
insert into sys_b
select event,time_waited,total_waits
from v$system_event;

--re_base.sql
insert into sys_e
select event,time_waited,total_waits
from v$system_event;

create table sys_dif
as
select e.event ,e.time_waited-b.time_waited  TIME_WAITED,
                e.total_waits-b.total_waits  TOTAL_WAITS
from sys_b b,sys_e e
where b.event=e.event;

select event "Wait Event",time_waited "Time Waited",
       time_waited/(select sum(time_waited) from sys_dif) "%Time waited",
       total_waits "Waits",
       total_waits /(select sum(total_waits) from sys_dif) "%Waited"
from sys_dif
order by 3 desc;

drop table sys_dif;
drop table sys_b;
drop table sys_e;

 

等待事件的等待次数
--seswa.sql
select event "Wait Event",
       count(seconds_in_wait) "Waited So Far(sec)",
       count(sid) "Num Sess Waiting"
from v$session_wait
group by event;

--seswt.sql
select sid,event "Wait Event",state "Wait Stat",
       wait_time "W'd So Far(secs)", seconds_in_wait "Time W'd (secs)"
from v$session_wait
where event like '&a'
order by 5;

--sqls1.sql
select * from
        (select address "Stmt Addr",
                disk_reads "Disk RDS",
                buffer_gets "Buff Gets",
                sorts "Sorts",
                executions "Runs",
                loads "Body Loads"
          from v$sqlarea where disk_reads > &A
          order by disk_reads  )
where rownum < &B;

 

--sqlst.sql
select cpu.sid "SID",cpu.username "USER Name",cpu.value "CPU(sec)",
       reads.value "IO Read(k)",writes.value "IO Write(k)"
from
(select a.sid sid,a.username username,b.name,c.value value, a.serial# serial#
from v$session a,v$statname b,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='CPU used by this session') cpu,
(select a.sid,a.username,b.name,c.value value from v$session a,v$statname b ,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='physical reads') reads,
(select a.sid,a.username,b.name,c.value value from v$session a,v$statname b ,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='physical writes') writes
where cpu.sid=reads.sid and reads.sid=writes.sid and cpu.username is not null;

 

--sqls2.sql
select sql_text "SQL Statement Text"
from v$sqlarea
where ADDRESS='&a';