本篇内容主要讲解“Oracle常用性能分析语句有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle常用性能分析语句有哪些”吧!

1、查看数据库中过去15分钟内排在前5位的等待事件及其总等待时间

select*from(SELECTNVL(event,'ONCPU')event,COUNT(*)total_wait_tmFROMv$active_session_historyWHEREsample_time>SYSDATE-15/(24*60)GROUPBYeventORDERBY2desc)whererownum<=5;

2、查看过去15分钟内使用CPU及资源最多的前5位会话

columnusernameformata12columnmoduleformata30SELECT*FROM(SELECTs.username,s.module,s.sid,s.serial#,count(*)FROMv$active_session_historyh,v$sessionsWHEREh.session_id=s.sidANDh.session_serial#=s.serial#ANDsession_state='ONCPU'ANDsample_time>sysdate-interval'15'minuteGROUPBYs.username,s.module,s.sid,s.serial#ORDERBYcount(*)desc)whererownum<=5;

session_state列有两个有效的值：ON CPU和WAITING，分别表示会话是活动的还是在等待资源，同样将session_state指定为WAITING值，可以查看在等待资源的前5位会话信息。

3、查看那些业务用户占用最多的资源

SELECT*FROM(SELECTu.username,h.module,h.session_idsid,h.session_serial#serial#,count(*)FROMdba_hist_active_sess_historyh,dba_usersuWHEREh.user_id=u.user_idANDsession_state='ONCPU'AND(sample_timebetweento_date('2018-07-22:00:00:00','yyyy-mm-dd:hh34:mi:ss')ANDto_date('2018-07-22:23:59:59','yyyy-mm-dd:hh34:mi:ss'))ANDu.username!='SYS'GROUPBYu.username,h.module,h.session_id,h.session_serial#ORDERBYcount(*)desc)whererownum<=5;

4、定位造成大量资源消耗的对象信息

SELECT*FROM(select*from(SELECTo.object_name,o.object_type,s.event,s.time_waitedFROMdba_hist_active_sess_historys,dba_objectsoWHEREs.sample_timebetweento_date('2018-07-22:00:00:00','yyyy-mm-dd:hh34:mi:ss')ANDto_date('2018-07-22:23:59:59','yyyy-mm-dd:hh34:mi:ss')ANDs.current_obj#=o.object_idORDERBY4desc)WHERErownum<=5;

5、查看过去15分钟内造成最多数据库等待的sql语句

selectash.user_id,u.username,s.sql_text,sum(ash.wait_time+ash.time_waited)ttl_wait_timefromv$active_session_historyash,v$sqlareas,dba_usersuwhereash.sample_timebetweensysdate-15/(24*60)andsysdateandash.sql_id=s.sql_idandash.user_id=u.user_idgroupbyash.user_id,s.sql_text,u.usernameorderbyttl_wait_time

6、找出谁持有阻塞锁

selects1.username||'@'||s1.machine||'(SID='||s1.sid||')isblocking'||s2.username||'@'||s2.machine||'(SID='||s2.sid||')'ASblocking_statusfromv$lockl1,v$sessions1,v$lockl2,v$sessions2wheres1.sid=l1.sidands2.sid=l2.sidandl1.BLOCK=1andl2.request>0andl1.id1=l2.id1andl2.id2=l2.id2;

到此，相信大家对“Oracle常用性能分析语句有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！