监控Oracle数据库性能的脚本段整理

监控Oracle数据库性能的脚本段整理[@more@]1. 监控事例的等待

　　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;; 错误对象脚本

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1058313/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1058313/