数据库监控脚本（一）

 

一、数据库构架体系
1、表空间的监控
2、监控表空间使用率与剩余空间大小的语句
3、表空间是否具有自动扩展空间的能力
4、使用字典管理的表空间哪些表的扩展将引起表空间的扩展
5、段的占用空间与区间数
6、重建索引
7、监控表是否有主键
二、性能监控
1、数据缓冲区的命中率
2、库缓冲说明了SQL语句的重载率，越低越好
3、用户锁
4、锁与等待，查询谁锁了表，而谁在等待
5、发生了事务或锁，查找使用的回滚段
6、哪个用户正在利用临时段吗？
7、在ORACLE 9i中，可以监控索引的使用，开始索引监控与停止索引监控的脚本
8、通过sid找到os进程号（Check OS process id from Oracle sid ）
9、通过os进程找sid（Check Oracle sid from OS process id ）
10、通过sid找sql语句（Check current SQL in a session ）
11、找等待事件Checking v$session_wait
12、数据缓冲区GETMISS相对gets的比例Dictionary Cache Hits MISS RATIO
13、通过文件号及块号找对应数据库对象Check DB object name from file id and block#
14、寻找hot block
15、找出每个文件上的等待事件
16、找出引起等待事件的SQL语句.
17、监控共享池中哪个对象引起了大的内存分配

1、表空间的监控
SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT, -- Columns not available in v8.0.x
ALLOCATION_TYPE, -- Remove these columns if running
PLUGGED_IN, -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

2、监控表空间使用率与剩余空间大小的语句
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

3、表空间是否具有自动扩展空间的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

4、使用字典管理的表空间哪些表的扩展将引起表空间的扩展。
SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
FROM ALL_TABLES A,
(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK;

5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），
如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作
SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
EXTENTS USED_EXTENTS,S.MAX_EXTENTS,
S.BLOCKS ALLOCATED_BLOCKS,
S.BLOCKS USED_BOLCKS,
S.PCT_INCREASE,
S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"
FROM DBA_SEGMENTS S
WHERE S.OWNER NOT IN ('SYS','SYSTEM')
ORDER BY Used_Extents DESC;

6、重建索引
数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。
SQL> set heading off
SQL> set feedback off
SQL> spool d:index.sql
SQL>
SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES' OR next_extent != ( 256 * 1024 ) )
AND owner = USER
SQL>spool off

这个时候，我们打开spool出来的文件，就可以直接运行了。

7、监控表是否有主键
SELECT table_name
FROM all_tables
WHERE owner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = USER
AND constraint_type = 'P';

二、性能监控

1、数据缓冲区的命中率
SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads' ;

2、库缓冲说明了SQL语句的重载率，越低越好
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,
SUM(reloads)/SUM(pins)*100 libcache_reload_ratio
FROM v$librarycache;

3、用户锁
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',
NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL

4、锁与等待，查询谁锁了表，而谁在等待
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN

5、发生了事务或锁，查找使用的回滚段
其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。
同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。
SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",
t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName
FROM v$session s,v$transaction t,v$rollname r
WHERE s.SADDR=t.SES_ADDR
AND t.XIDUSN=r.usn;

6、哪个用户正在利用临时段吗？
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status,c.sql_text
FROM v$session a,v$sort_usage b, v$sql c
WHERE a.saddr = b.session_addr
AND a.sql_address = c.address(+)
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

7、在ORACLE 9i中，可以监控索引的使用，开始索引监控与停止索引监控的脚本
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on ------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on

如果需要监控更多的用户，可以将owner=User改写成别的
监控结果在视图v$object_usage中查询

8、Check OS process id from Oracle sid
select spid from v$process
where addr in
( select paddr from v$session where sid=&sid) ;

9、Check Oracle sid from OS process id
select sid from v$session
where paddr in ( select addr from v$process where spid=&pid) ;

10、Check current SQL in a session
select SQL_TEXT
from V$SQLTEXT
where HASH_VALUE
= ( select SQL_HASH_VALUE from v$session
where sid = &sid)
order by PIECE;

11、Checking v$session_wait
select * from v$session_wait
where event not like 'rdbms%'
and event not like 'SQL*N%'
and event not like '%timer';

12、Dictionary Cache Hits
SELECT sum(getmisses)/sum(gets) FROM v$rowcache; /*It should be < 15%, otherwise Add share_pool_size*/

13、Check DB object name from file id and block#
select owner,segment_name,segment_type
from dba_extents
where file_id = &fno
and &dno between block_id
and block_id + blocks – 1 ;

14、寻找hot block
select /*+ ordered */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e
where l.name = 'cache buffers chains'
and l.sleeps > &sleep_count
and x.hladdr = l.addr
and e.file_id = x.file#
and x.dbablk between e.block_id
and e.block_id + e.blocks - 1;

15、找出每个文件上的等待事件
select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#;

16、找出引起等待事件的SQL语句.
select sql_text from v$sqlarea a,v$session b,v$session_wait c where a.address=b.sql_address and b.sid=c.sid and c.event=&ll;

17、监控共享池中哪个对象引起了大的内存分配
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;