监控数据库性能的sql
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;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN (’redo allocation’, ’redo copy’);
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN (’sorts (memory)’, ’sorts (disk)’);
12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14. 找ORACLE字符集
select * from sys.props$ where name=’NLS_CHARACTERSET’;
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type=’dispatcher’;
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,’free space’ segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN (’SYS’, ’SYSTEM’) GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name=’&tablespace_name’ and segment_type=’TABLE’ group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type=’INDEX’ and owner=’&owner’
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, ’None’,
1, ’Null’,
2, ’Row Share’,
3, ’Row Excl.’,
4, ’Share’,
5, ’S/Row Excl.’,
6, ’Exclusive’,
lmode, ltrim(to_char(lmode,’990’))) lmode,
DECODE(m.request,0, ’None’,
1, ’Null’,
2, ’Row Share’,
3, ’Row Excl.’,
4, ’Share’,
5, ’S/Row Excl.’,
6, ’Exclusive’,
request, ltrim(to_char(m.request,
’990’))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,’RW’,’RW - Row Wait Enqueue’,
’TM’,’TM - DML Enqueue’,
’TX’,’TX - Trans Enqueue’,
’UL’,’UL - User’,l.type||’System’) res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,’No Lock’,
2,’Row Share’,
3,’Row Exclusive’,
4,’Share’,
5,’Shr Row Excl’,
6,’Exclusive’,null) lmode,
decode(l.request,1,’No Lock’,
2,’Row Share’,
3,’Row Excl’,
4,’Share’,
5,’Shr Row Excl’,
6,’Exclusive’,null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid
and s.type != ’BACKGROUND’
and t.obj# = l.id1
and u.user# = t.owner#
显示表空间的使用情况：
col tsname format a16 justify c heading ’Tablespace’
col nfrags format 999,990 justify c heading ’Free|Frags’
col mxfrag format 999,999,990 justify c heading ’Largest|Frag (KB)’
col totsiz format 999,999,990 justify c heading ’Total|(KB)’
col avasiz format 999,999,990 justify c heading ’Available|(KB)’
col pctusd format 990 justify c heading ’Pct|Used’
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from
dba_data_files total,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes;
SELECT * FROM V$RESERVED_WORDS 数据库的保留字
SELECT * FROM V$INSTANCE 实例建立信息
SELECT * FROM V$LICENSE 连接许可
SELECT * FROM V$RESOURCE_LIMIT 资源使用限制
SELECT * FROM V$DBLINK 数据库连接
SELECT * FROM V$PWFILE_USERS 在密码文件中的用户
SELECT * FROM V$OPTION 数据库的应用选项
SELECT * FROM V$COMPATIBILITY 数据库的兼容性
SELECT * FROM V$FIXED_TABLE ORACLE数据字典
SELECT * FROM V$FIXED_VIEW_DEFINITION ORACLE视图及定义
SELECT * FROM V$INDEXED_FIXED_COLUMN ORACLE索引的列
SELECT * FROM SYS.PROPS$ 数据库NLS格式
SELECT * FROM V$TABLESPACE 表空间名字及数量
SELECT * FROM V$CONTROLFILE 控制文件名称及位置
SELECT * FROM V$LOGFILE 日志文件名称及位置
SELECT * FROM V$DATAFILE 数据文件位置及当前状态
SELECT * FROM V$BACKUP 数据文件备份状态
SELECT * FROM V$FILESTAT 数据文件读、写状态
SELECT * FROM V$ARCHIVED_LOG 数据库当前所有归档日志
SELECT * FROM V$ARCHIVE_DEST 归档日志路径及状态
SELECT * FROM V$ARCHIVE_PROCESSES 日志写进程状态
SELECT * FROM V$ROLLNAME 回滚段名
SELECT * FROM V$POLLSTAT 回滚段状态
SELECT * FROM V$SGASTAT SGA分配状态
SELECT * FROM V$SYSSTAT 系统资源使用状态
SELCT * FROM V$LIBRARYCACHE 对象使用命中率
SELECT * FROM V$RESERVED_WORDS 数据库的保留字
SELECT * FROM V$INSTANCE 实例建立信息
SELECT * FROM V$LICENSE 连接许可
SELECT * FROM V$RESOURCE_LIMIT 资源使用限制
SELECT * FROM V$DBLINK 数据库连接
SELECT * FROM V$PWFILE_USERS 在密码文件中的用户
SELECT * FROM V$OPTION 数据库的应用选项
SELECT * FROM V$COMPATIBILITY 数据库的兼容性
SELECT * FROM V$FIXED_TABLE ORACLE数据字典
SELECT * FROM V$FIXED_VIEW_DEFINITION ORACLE视图及定义
SELECT * FROM V$INDEXED_FIXED_COLUMN ORACLE索引的列
SELECT * FROM SYS.PROPS$ 数据库NLS格式
SELECT * FROM V$TABLESPACE 表空间名字及数量
SELECT * FROM V$CONTROLFILE 控制文件名称及位置
SELECT * FROM V$LOGFILE 日志文件名称及位置
SELECT * FROM V$DATAFILE 数据文件位置及当前状态
SELECT * FROM V$BACKUP 数据文件备份状态
SELECT * FROM V$FILESTAT 数据文件读、写状态
SELECT * FROM V$ARCHIVED_LOG 数据库当前所有归档日志
SELECT * FROM V$ARCHIVE_DEST 归档日志路径及状态
SELECT * FROM V$ARCHIVE_PROCESSES 日志写进程状态
SELECT * FROM V$ROLLNAME 回滚段名
SELECT * FROM V$POLLSTAT 回滚段状态
SELECT * FROM V$SGASTAT SGA分配状态
SELECT * FROM V$SYSSTAT 系统资源使用状态
SELCT * FROM V$LIBRARYCACHE 对象使用命中率
SELECT * FROM V$THREAD 数据库进程状态
SELECT SUM(sharable_mem) FROM V$DB_OBJECT_CACHE 对象占用数据库缓存数
SELECT SUM(locks) FROM V$DB_OBJECT_CACHE WHERE owner <> ‘SYS’ 缓存当前对象使用数
SELECT COUNT(*) FROM V$LOCKED_OBJECT 被锁住的对象数
SELECT COUNT(*) FROM V$SESSION WHERE lock_view IS NOT NULL 被阻塞的会话数
SELECT COUNT(*) FROM V$LOCKWHERE ADDR NOT IN(SELECT ADDR FROM V$BGPROCESS) ; 当前锁总数（除后台进程）
SELECT COUNT(decode(status,’free’,block#,NULL)) FREE , COUNT(decode(status,’cr’,block#,NULL)) 一致的读, COUNT(decode(status,’read’,block#,NULL)) Disk_Read, COUNT(decode(status,’scur’,block#,NULL)) Share_Mode, COUNT(decode(status,’xcur’,block#,NULL)) Exclu_Mode, COUNT(decode(dirty,’Y’,block#,NULL)) Dirty_BlockFROM V$BH 数据库缓存使用状态（按快数统计）
SELECT sum(decode(n.statistic#, 15, s.value,0)) UGA, Trunc(sum(decode(n.statistic#, 20, s.value,0))/1024)||’K’ PGA,sum(decode(n.statistic#, 180, s.value,0)) Sore_In_Member,sum(decode(n.statistic#, 181, s.value,0)) Sore_In_Disk,sum(decode(n.statistic#, 182, s.value,0)) Sore_Of_Row,sum(decode(n.statistic#, 6, s.value,0)) User_Call,sum(decode(n.statistic#, 5, s.value,0)) User_Rollback,sum(decode(n.statistic#, 4, s.value,0)) User_Commit,sum(decode(n.statistic#, 3, s.value,0)) Current_Opened_Cursor,sum(decode(n.statistic#, 1, s.value,0)) Current_Logon,sum(decode(n.statistic#, 9, s.value,0)) Session_Logical_Read ,sum(decode(n.statistic#, 150, s.value,0)) Short_Table_Scans ,sum(decode(n.statistic#, 151, s.value,0)) Long_Table_Scans FROM V$SESSTAT s,V$STATNAME nWHERE s.statistic# = n.statistic# 数据库当前使用的UGA、SGA大小，打开的游标数，当前登入数、在内存和磁盘中的排序数等。
SELECT owner,name,type,referenced_name, referenced_name,referenced_typeFROM DBA_DEPENDENCIES WHERE owner = ’wner’ AND name = ’:name’; 一个对象所依赖的对象
SELECT ss.sid, SUM(decode(sn.statistic#, 15, ss.value,0)) UGA, TRUNC(SUM(decode(sn.statistic#, 20, ss.value,0))/1024)||’K’ PGA, SUM(decode(sn.statistic#, 180, ss.value,0)) Sore_In_Member, SUM(decode(sn.statistic#, 181, ss.value,0)) Sore_In_Disk, SUM(decode(sn.statistic#, 182, ss.value,0)) Sore_Of_Row, SUM(decode(sn.statistic#, 6, ss.value,0)) User_Call, SUM(decode(sn.statistic#, 5, ss.value,0)) User_Rollback, SUM(decode(sn.statistic#, 4, ss.value,0)) User_Commit, SUM(decode(sn.statistic#, 3, ss.value,0)) Current_Opened_Cursor, SUM(decode(sn.statistic#, 9, ss.value,0)) Session_Logical_Read , SUM(decode(sn.statistic#, 150, ss.value,0)) Short_Table_Scans , SUM(decode(sn.statistic#, 151, ss.value,0)) Long_Table_Scans FROM v$sesstat ss,v$statname sn WHERE ss.sid in( SELECT sid FROM v$session WHERE terminal = ’Machine_Name’ AND USERNAME IS NOT NULL) AND ss.statistic# = sn.statistic#GROUP BY ss.sid 得到一台计算机当前使用的UGA、SGA大小，打开的游标数，在内存和磁盘中的排序数等。
SELECT a.sid,s.terminal,s.program,count(a.sid) FROM V$ACCESS a,V$SESSION sWHERE a.owner <> ’SYS’AND s.sid = a.sid GROUP BY a.sid,s.terminal,s.programORDER BY count(a.sid); 当前每个会话使用的对象数
SELECT DECODE(dbo.object_type,’PACKAGE BODY’, ’ALTER PACKAGE ’||dbo.owner||’.’||dbo.object_name||’ COMPILE BODY’, ’ALTER ’||dbo.object_type||’ ’||dbo.owner||’.’||dbo.object_name||’ COMPILE’)ReComplie_Invalid_Object FROM DBA_OBJECTS dbo,SYS.ORDER_OBJECT_BY_DEPENDENCY oodWHERE dbo.object_id = ood.object_id(+) AND dbo.status = ’INVALID’ ANDdbo.object_type in(’PACKAGE BODY’,’PACKAGE’,’FUNCTION’,’PROCEDURE’,’TRIGGER’,’VIEW’)ORDER BY ood.dlevel, dbo.object_type, dbo.object_name 得到重新编译无效的数据库对象的SQL语句
SELECT BG.NAME, SW.SID, SW.EVENT,SW.P1TEXT, SW.P1, SW.WAIT_TIME, SW.SECONDS_IN_WAIT, SW.STATE FROM V$SESSION_WAIT SW, V$SESSION SS, V$BGPROCESS BGWHERE SW.SID = SS.SIDAND BG.PADDR = SS.PADDR; 后台进程会话等状态
SELECT SID,SQL_TEXTFROM V$SESSION_LONGOPS SL,V$SQLTEXT STWHERE SL.SQL_ADDRESS = ST.ADDRESSAND SL.SQL_HASH_value = ST.HASH_valueAND SID IN(SELECT SID FROM V$ACCESS)ORDER BY SID,PIECE; 当前会话长时间执行的SQL语句
SELECT DECODE(default_tablespace,’SYSTEM’,’S.’||username,’USER_DATA’,’U.’||username,NULL) DEFAULT_TABLESPACE,DECODE(temporary_tablespace,’SYSTEM’,’S.’||username,’U.’||’USER_DATA’,username,NULL) DEFA_TEMP_TABLESPACE FROM DBA_USERS WHERE ((default_tablespace IN(’SYSTEM’,’USER_DATA’) OR temporary_tablespace IN(’SYSTEM’,’USER_DATA’))AND username IN(SELECT owner FROM DBA_TABLES GROUP BY owner))UNIONSELECT DECODE(default_tablespace,’SYSTEM’,’S.’||username,’USER_DATA’,’U.’||username,NULL) DEFAULT_TABLESPACE,DECODE(temporary_tablespace,’SYSTEM’,’S.’||username,’U.’||’USER_DATA’,username,NULL) DEFA_TEMP_TABLESPACE FROM DBA_USERS WHERE (default_tablespace IN(’SYSTEM’,’USER_DATA’) OR temporary_tablespace IN(’SYSTEM’,’USER_DATA’))AND username IN(SELECT owner FROM DBA_VIEWS GROUP BY owner); 默认表空间在SYSTME、USER_DATA的模式
SELECT owner,table_name FROM DBA_TABLESWHERE tablespace_name in(’SYSTEM’,’USER_DATA’) AND owner NOT IN(’SYSTEM’,’SYS’,’OUTLN’, ’ORDSYS’,’MDSYS’,’SCOTT’, ’HOSTEAC’); 非系统用户建在SYSTEM表空间中的表
SELECT owner,name,line,position,textFROM DBA_ERRORSORDER BY name,sequence; 对象编译错误信息
SELECT name,text FROM DBA_SOURCEWHERE name IN(SELECT object_name FROM DBA_OBJECTS WHERE status = ’INVALID’ AND object_type <> ’JAVA CLASS’ AND owner NOT IN(’ORDSYS’,’SKYWALKER’)) ORDER BY name,line;; 错误对象脚本
