概述
昨天分享了日常巡检的第一部分内容,偷个懒就续一下,哈哈,这里主要介绍第二部分内容:检查oracle数据库性能。
检查oracle数据库性能
这里包含:检查数据库的等待事件,检查死锁及处理,检查cpu、I/O、内存性能,查看是否有僵死进程,检查行链接/迁移,定期做统计分析,检查缓冲区命中率,检查共享池命中率,检查排序区,检查日志缓冲区,总共十个部分。
1、检查数据库的等待事件
set pages 100set lines 250col event for a80select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
如果数据库长时间持续出现大量像latch free,enqueue,buffer busy waits,db file sequential read,db file scattered read等等待事件时,需要对其进行分析,可能存在问题的语句。
![54243327b047289dcdf1ed6067295fe9.png](https://i-blog.csdnimg.cn/blog_migrate/4e65d71e4cc9f7f8adc2ff9e134fbb84.jpeg)
2、Disk Read最高的SQL语句的获取
col SQL_TEXT for a120;SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM <= 5;
![1abc6057ba12a4e1406454ef0f49ce78.png](https://i-blog.csdnimg.cn/blog_migrate/49f432c9f64464c67882855bf607d732.jpeg)
3、查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS, SORTS, COMMAND_TYPE, DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM < 10;
![5b79f50d89283572745d8285efcb88b4.png](https://i-blog.csdnimg.cn/blog_migrate/99432797f99f51d038ee826dd7de3e6c.jpeg)
4、等待时间最多的5个系统等待事件的获取
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM <= 5;
![290dcb956acd417f7e5a193cbf1a63e9.png](https://i-blog.csdnimg.cn/blog_migrate/fbdb9485330973de25f28e82f1976f92.jpeg)
5、检查运行很久的SQL
COLUMN USERNAME FORMAT A12COLUMN OPNAME FORMAT A16COLUMN PROGRESS FORMAT A8SELECT USERNAME,SID,OPNAME,ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS, V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS = ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
![4b39fe33b5ee54d6da77dfa4cdeae744.png](https://i-blog.csdnimg.cn/blog_migrate/c721556aeb6c3a02acd1ad103cb333f4.jpeg)
因为是测试环境,所以这里并没有运行很久的sql。
6、检查消耗CPU最高的进程
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQL FROM V$PROCESS P,V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%';
7、检查碎片程度高的表
SELECT segment_name table_name, COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
![ad3608d6225c4959c47c80fe5471c485.png](https://i-blog.csdnimg.cn/blog_migrate/269c3299868a26863439b0c2290b76e3.jpeg)
8、检查表空间的I/O比例
SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE