性能分析时,使用以下语句做出总体判断,再结合使用Toad查看相关信息,非常方便!!! Toad-->Database-->Monitor-->Session Browser(会话浏览器)
+【紧紧抓住等待会话和阻塞会话!!!!!】+
常用视图:
v$system_event v$session_event v$session_wait v$event_name
v$session、v$transaction、v$sql、v$sqltext、dba_objects、v$locked_object、v$lock、dba_2pc_pending、v$process
V$SESSTAT V$SYSSTAT V$STATNAME
持续读测试语句:
select * from dba_objects where object_name in (select object_name from dba_objects);
等待事件
会话
查看进程、会话的历史最大值和最大限定值
select resource_name, max_utilization, limit_value from v$resource_limit where resource_name in ('processes','sessions');
当前总会话及活动会话数量
查询非后台会话
select t.INST_ID, count(*) count_all, sum(case when t.STATUS = 'ACTIVE' then 1 else 0 end) count_active from gv$session t where type <> 'BACKGROUND' and t.sid <>(select distinct sid from v$mystat) group by t.INST_ID order by t.inst_id;
等待会话
所有的等待会话
查询两类会话:
⑴ wait_class<>'Idle'的活动查询或活动事务或非活动事务
⑵ wait_class='Idle'的非活动事务
set linesize 160 col sid for 999999 col machine for a10 col program for a10 col event for a15 col blk_ses for 99999 col wait_class for a15 col SQL for a20 col PRE_SQL for a20 select * from (select * from ( (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class from v$session s where s.wait_class<>'Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat)) union all (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class from v$session s, v$transaction t where s.wait_class='Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat) and t.addr=s.taddr and t.ses_addr=s.saddr) ) order by wait_mins desc) where rownum <=10;
跟踪某个会话的当前等待事件
set linesize 150 col event for a55 col wait_class for a25 select sid, serial#, event, wait_class, round(seconds_in_wait/60) wait_mins from v$session where sid=&sid;
等待会话涉及的对象
涉及行等待对象的等待会话
set linesize 180 col sid for 999999 col machine for a10 col program for a10 col event for a15 col status for a10 col wait_class for a15 col row_wait_obj for a15 select * from (select sid, serial#, machine, program, event, round(seconds_in_wait/60) wait_mins, blocking_session, s.status, wait_class, o.owner||'.'||o.object_name||'('||o.object_type||')' row_wait_obj from v$session s, dba_objects o where wait_class<>'Idle' and type<>'BACKGROUND' and sid <>(select distinct sid from v$mystat) and o.data_object_id=row_wait_obj# order by wait_mins desc) where rownum <=10;
某种等待事件涉及的对象
需输入事件名,按事件名查询
set linesize 180 col sid for 999999 col machine for a10 col program for a10 col event for a10 col wait_class for a15 col row_wait_obj for a25 select * from (select sid, serial#, machine, program, event, round(seconds_in_wait/60) wait_mins, blocking_session, s.status, wait_class, o.owner||'.'||o.object_name||'('||o.object_type||')' row_wait_obj from v$session s, dba_objects o where wait_class<>'Idle' and type<>'BACKGROUND' and sid <>(select distinct sid from v$mystat) and o.data_object_id=row_wait_obj# and event='&event' order by wait_mins desc) where rownum <=10;
会话锁等待及会话阻塞
(v$locked_object,dba_objects, v$lock)(未提交事务会持有锁不释放!!!未提交事务的会话将阻塞其他需获得同一行锁的会话!!!)
引起会话争用的各种数据库锁资源: 对象锁:lock 内存锁:latch、mutex
会话锁:block(阻塞)
等待事件与阻塞及锁的关系:(都是在获取资源引起的,可能是系统资源,如内存、磁盘、网络等,也可能是数据库锁资源等) 等待事件:因某种原因(事件)而引起会话等待,这种原因称为一种事件。(等待事件是单个会话经历的等待) 阻塞:会话间因争用某种资源而引起的,一个会话阻塞了需要获取相同资源的其他会话。(阻塞是会话间因阻塞而经历的等待)
处于锁等待状态(请求锁)的会话
set linesize 160 col sid for 9999 col username for a15 col blk_ses for 99999 col lockwait for a10 col machine for a15 col program for a15 col SQL for a25 col PRE_SQL for a25 select inst_id, sid, serial#, username, machine, program, s.blocking_session blk_ses, s.status, (select type from v$lock where kaddr=LOCKWAIT) lockwait, (select sql_text from v$sql where sql_id=s.sql_id) SQL, (select sql_text from v$sql where sql_id=s.prev_sql_id) PRE_SQL from gv$session s where lockwait is not null;
阻塞会话
set linesize 160 col WAIT_EVENT_TEXT for a40 select * from V$SESSION_BLOCKERS;
被锁住会话
(只能看到因对象的锁引起的阻塞)
set linesize 160 col lock_type for a10 col locked_object for a45 select l1.sid, ' is blocking ', l2.sid, l1.type lock_type, a.owner||'.'||a.object_name||'('||a.object_type||')' "LOCKED_OBJECT", (select sql_text from v$sql where sql_id=s.sql_id) SQL, (select sql_text from v$sql where sql_id=s.prev_sql_id) PRE_SQL from v$locked_object lo, dba_objects a, v$session s, v$lock l1, v$lock l2 where lo.session_id=l1.sid and lo.object_id=a.object_id and l1.block > 0 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 and s.sid=l1.sid;
被锁定或持有锁的会话
set linesize 160 col process for a10 col row_wait_obj for a15 col is_locker for a25 select s.sid, s.serial#, l.process, o.owner||'.'||o.object_name||'('||o.object_type||')' row_wait_obj, decode(l.xidusn,0,'be locked','locker') "IS_LOCKER" from v$locked_object l, dba_objects o, v$session s where l.object_id=o.object_id and l.session_id=s.sid order by 4,5;
杀掉阻塞会话
set linesize 160 set head off select 'kill -9 '||p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid in (select l1.sid from v$locked_object lo, dba_objects a, v$lock l1, v$lock l2 where lo.session_id=l1.sid and lo.object_id=a.object_id and l1.block > 0 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2); set head on
事务信息
是否有活动事务(未提交事务)
set linesize 160 col sid for 9999 select sid, serial#, xidusn, xidslot, xidsqn, used_ublk, used_urec, to_char(start_date,'yyyymmdd hh24:mi:ss') start_time, to_char(sysdate,'yyyymmdd hh24:mi:ss') curr_time, start_scn, xid from v$transaction t, gv$session s where t.addr=s.taddr and t.ses_addr=s.saddr;
未提交的事务在做什么
(v$sql,v$transaction)(可能查询到未提交事务的第一条执行的DML语句,也可能查询不到,因为v$sql缓存内容可能被刷新了)
set linesize 160 col sid for 9999 col username for a8 col program for a15 col machine for a10 col sql_text for a50 SELECT s.SID, s.SERIAL#, s.USERNAME, s.PROGRAM, s.machine, TO_CHAR(s.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss') AS LOGON_TIME, TO_CHAR(t.START_DATE, 'yyyy-mm-dd hh24:mi:ss') AS START_DATE, s.STATUS, (SELECT q.SQL_TEXT FROM v$sql q WHERE q.LAST_ACTIVE_TIME = t.START_DATE AND rownum <= 1) AS SQL_TEXT FROM v$session s, v$transaction t WHERE s.SADDR = t.SES_ADDR;
二阶段未决事务
set linesize 160 col host for a20 select LOCAL_TRAN_ID, STATE, FAIL_TIME, HOST, OS_TERMINAL, DB_USER from dba_2pc_pending;
超长等待会话
等待时间超过10分钟的等待会话
set linesize 160 col sid for 9999 col machine for a10 col program for a10 col event for a35 col blk_ses for 99999 col wait_class for a15 col SQL for a20 col PRE_SQL for a20 select * from (select * from ( (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class, (select sql_text from v$sql q where q.sql_id=s.sql_id) SQL, (select sql_text from v$sql q where q.sql_id=s.prev_sql_id) PRE_SQL from v$session s where s.wait_class<>'Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat)) union all (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class, (select sql_text from v$sql q where q.sql_id=s.sql_id) SQL, (select sql_text from v$sql q where q.sql_id=s.prev_sql_id) PRE_SQL from v$session s, v$transaction t where s.wait_class='Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat) and t.addr=s.taddr and t.ses_addr=s.saddr) ) order by wait_mins desc) where wait_mins>10 and rownum <=10;
等待时间超过10分钟的等待会话,杀进程
set linesize 160 col sid for 9999 col machine for a10 col program for a10 col event for a35 col blk_ses for 99999 col wait_class for a15 col SQL for a20 col PRE_SQL for a20 set head off select 'kill -9 '||p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid in ( select sid from (select * from (select * from ( (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class, (select sql_text from v$sql q where q.sql_id=s.sql_id) SQL, (select sql_text from v$sql q where q.sql_id=s.prev_sql_id) PRE_SQL from v$session s where s.wait_class<>'Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat)) union all (select s.sid, s.serial#, s.machine, s.program, s.event, round(s.seconds_in_wait/60) wait_mins, s.blocking_session blk_ses, s.status, s.wait_class, (select sql_text from v$sql q where q.sql_id=s.sql_id) SQL, (select sql_text from v$sql q where q.sql_id=s.prev_sql_id) PRE_SQL from v$session s, v$transaction t where s.wait_class='Idle' and s.type<>'BACKGROUND' and s.sid <>(select distinct sid from v$mystat) and t.addr=s.taddr and t.ses_addr=s.saddr) ) order by wait_mins desc) ) where wait_mins>10 ); set head on
SQL语句文本
当前正在执行的SQL(活动会话)
set linesize 150 col sid for 9999 col username for a15 col sql_text for a35 select sid, serial#, USERNAME, a.sql_id, (SELECT command_name FROM v$sqlcommand WHERE command_type = a.command) command, sql_text from v$session a,v$sql b where type != 'BACKGROUND' and username is not null and a.sql_id=b.sql_id and sid <>(select distinct sid from v$mystat) order by sid,serial#;
已执行过的SQL(非活动会话)
#按v$session中的prev_sql_id查询v$sql
set linesize 150 col sid for 9999 col username for a15 col sql_text for a75 select sid, serial#, USERNAME, a.sql_id, (SELECT command_name FROM v$sqlcommand WHERE command_type = a.command) command, sql_text from v$session a,v$sql b where type != 'BACKGROUND' and username is not null and a.prev_sql_id=b.sql_id and sid <>(select distinct sid from v$mystat) order by sid,serial#;
根据sql_id查询sql_text
select sql_text from v$sql where sql_id=&sql_id;
sid及pid
查看当前session的sid
select distinct sid from v$mystat;
查询某个sid的操作系统pid
单个sid
select p.spid ospid from v$process p, v$session s where p.addr=s.paddr and s.sid=&sid;
多个sid
select p.spid ospid from v$process p, v$session s where p.addr=s.paddr and s.sid in (4330,6586,3013);
查看某个pid对应的sid
select s.sid sid from v$process p, v$session s where p.addr=s.paddr and p.spid=&spid;