oracle数据库语句分析,Oracle常用性能分析定位语句记录

在Oracle数据库日常管理中，性能问题永远是最热点的话题，本篇内容将部分常用的性能分析语句进行总结记录，以供参考。

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

select * from (

SELECT NVL(event, 'ON CPU') event, COUNT(*) total_wait_tm

FROM   v$active_session_history

WHERE  sample_time > SYSDATE - 15/(24*60)

GROUP BY event

ORDER BY 2 desc

)

where rownum <= 5;

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

column username format a12

column module format a30

SELECT * FROM

(

SELECT s.username, s.module, s.sid, s.serial#, count(*)

FROM v$active_session_history h, v$session s

WHERE h.session_id = s.sid

AND   h.session_serial# = s.serial#

AND   session_state= 'ON CPU' AND

sample_time > sysdate - interval '15' minute

GROUP BY s.username, s.module, s.sid, s.serial#

ORDER BY count(*) desc

)

where rownum <= 5;

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

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

SELECT * FROM

(

SELECT u.username, h.module, h.session_id sid,

h.session_serial# serial#, count(*)

FROM dba_hist_active_sess_history h, dba_users u

WHERE h.user_id = u.user_id

AND   session_state= 'ON CPU'

AND  (sample_time between to_date('2018-07-22:00:00:00','yyyy-mm-dd:hh24:mi:ss')

AND   to_date('2018-07-22:23:59:59','yyyy-mm-dd:hh24:mi:ss'))

AND u.username != 'SYS'

GROUP BY u.username, h.module, h.session_id, h.session_serial#

ORDER BY count(*) desc

)

where rownum <= 5;

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

SELECT * FROM

(

select * from (

SELECT o.object_name, o.object_type, s.event, s.time_waited

FROM dba_hist_active_sess_history s, dba_objects o

WHERE s.sample_time

between to_date('2018-07-22:00:00:00','yyyy-mm-dd:hh24:mi:ss')

AND   to_date('2018-07-22:23:59:59','yyyy-mm-dd:hh24:mi:ss')

AND s.current_obj# = o.object_id

ORDER BY 4 desc

)

WHERE rownum <= 5;

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

select ash.user_id,

u.username,

s.sql_text,

sum(ash.wait_time +

ash.time_waited) ttl_wait_time

from v$active_session_history ash,

v$sqlarea s,

dba_users u

where ash.sample_time between sysdate - 15/(24*60) and sysdate

and ash.sql_id = s.sql_id

and ash.user_id = u.user_id

group by ash.user_id,s.sql_text, u.username

order by ttl_wait_time

6、找出谁持有阻塞锁

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;