监控和分析锁

select name from v$event_name where name like 'enq: TX%';

查看等待锁的时长
with system_event as
(select case when event like 'enq:%'
then event else wait_class
end wait_type,e.*
from v$system_event e)
select wait_type,sum(total_waits) total_waits,
round(SUM(time_waited_micro)/1000000,2)
time_waited_seconds,round(sum(time_waited_micro)*100/sum(sum(time_waited_micro)) over (),2) pct
from (select wait_type,event,total_waits,time_waited_micro
from system_event e
union
select 'CPU',stat_name,NULL,VALUE
from v$sys_time_model
where stat_name IN ('background cpu time','DB CPU')) l
where wait_type<> 'Idle'
group by wait_type
order by 4 desc
/



查询显示在活动会话历史表中贡献了所等待的SQL和对象
with ash_query as (
select substr(event,6,2) lock_type,program,
h.module,h.action,object_name,
SUM(time_waited)/1000 time_ms,count(*) waits,
username,sql_text,
rank() over (order by sum(time_waited) desc)
as time_rank,
round(SUM(time_waited)*100/sum(sum(time_waited))
over (),2) pct_of_time
from v$active_session_history h
join dba_users u using (user_id)
left outer join dba_objects o
ON (o.object_id=h.current_obj#)
left outer join v$sql s using (sql_id)
where event like 'enq:%'
group by substr(event,6,2),program,h.module,h.action,
object_name,sql_text,username )
select lock_type,module,username,object_name,time_ms,pct_of_time,sql_text
from ash_query
where time_rank <11
order by time_rank;


查询语句获得根据APPLICATION_WAIT_TIME列排序的前10条SQL
with sql_app_waits as
(select sql_id,substr(sql_text,1,80) sql_text,
application_wait_time/1000 app_time_ms,
elapsed_time,
round(application_wait_time*100/elapsed_time,2) app_time_pct,
round(application_wait_time*100/sum(application_wait_time) over (),2) pct_of_app_time,
RANK() over (ORDER by application_wait_time desc) ranking
from v$sql
where elapsed_time > 0 and application_wait_time > 0)
select sql_text,app_time_ms,app_time_pct,pct_of_app_time
from sql_app_waits
where ranking <= 10
order by ranking;


查询遭遇最多行级锁的数据库对象
select object_name,value row_lock_waits,
round(value*100/sum(value) over (),2) pct
from v$segment_statistics
where statistic_name='row lock waits' and value >0
order by value desc;



使用V$SESSION_EVENT和V$SESS_TIME_MODEL视图来汇总账号登陆的等待次数
with session_event as
( select case when event like 'enq:%'
then event else wait_class
end wait_type,e.*
from v$session_event e)
select wait_type,sum(total_waits) total_waits,
round(sum(time_waited_micro)/1000000,2) time_waited_seconds,
round( sum(time_waited_micro)*100/sum(sum(time_waited_micro)) over (),2) pct
from (select e.sid,wait_type,event,total_waits,time_waited_micro
from session_event e
union
select sid,'CPU',stat_name,NULL,VALUE
from v$sess_time_model
where stat_name IN ('bankground cpu time','DB CPU')) l
where wait_type<>'Idle'
and sid in (select sid from v$session where username='TEST1')
group by wait_type
order by 4 desc
/

ITPub博客

锁分析的常用脚本——20170104

最新文章