oracle监控慢sql,Oracle种常用性能监控SQL语句

--Oracle常用性能监控SQL语句

--1

SELECT * FROM SYS.V_$SQLAREA WHERE DISK_READS > 100;

--2 监控事例的等待

SELECT EVENT,

SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev",

SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr",

COUNT(*) "Tot"

FROM V$SESSION_WAIT

GROUP BY EVENT

ORDER BY 4;

--3 回滚段的争用情况

SELECT NAME, WAITS, GETS, WAITS / GETS "Ratio"

FROM V$ROLLSTAT A, V$ROLLNAME B

WHERE A.USN = B.USN;

--4 查看前台正在发出的SQL语句

SELECT USER_NAME, SQL_TEXT

FROM V$OPEN_CURSOR

WHERE SID IN (SELECT SID

FROM (SELECT SID, SERIAL#

, USERNAME, PROGRAM

FROM V$SESSION

WHERE STATUS = 'ACTIVE'));

--5 数据表占用空间大小情况

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS

FROM USER_SEGMENTS

WHERE SEGMENT_TYPE = 'TABLE'

ORDER BY BYTES DESC, BLOCKS DESC;

--6 查看表空间碎片大小

SELECT TABLESPACE_NAME,

ROUND(SQRT(MAX(BLOCKS) / SUM(BLOCKS)) *

(100 / SQRT(SQRT(COUNT(BLOCKS)))),

2) FSFI

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME

ORDER BY 1;

--7 查看表空间占用磁盘情况

SELECT B.FILE_ID "文件ID号",