DBA常用脚本性能监控.docx

1、DBA常用脚本性能监控DBA常用脚本(二) 性能监控Tags: oracle 二、性能监控 1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。 这个语句可以获得整体的数据缓冲命中率，越高越好 Code: Copy to clipboard 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$syss

2、tat 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语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用 以下语句查询了Sql语句的重载率，越低越好 Code: Copy to clipboard SELECT SUM(pins) total_pins,SUM(reloads) total_reloads, SUM(reloads)/SU

3、M(pins)*100 libcache_reload_ratio FROM v$librarycache 3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 可以通过alter system kill session sid,serial#来杀掉会话 Code: Copy to clipboard SELECT /*+ rule */ s.username, decode(l.type,TM,TABLE

4、 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、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 以下的语句可以查询到谁锁了表，而谁在

5、等待。 Code: Copy to clipboard 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 以上查询结果

6、是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN 5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。 Code: Copy to clipboard SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL UBA filenum, t.UBABLK UBA Block number,t.USED_UBLK Number os undo Block

7、s 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 7、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。 Code: Copy to clipboard SELECT p1.value

8、|p2.value|_ora_|p.spid filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = user_dump_dest AND p2.name = db_name AND p.addr = s.paddr AND s.audsid = USERENV (SESSIONID); 8、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。 以下就是开始索引监控与停止索引监控的脚本 Code: Copy to clipb

9、oard 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 pag

10、es 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中查询感谢fenng，他提供了一个更新版的show_space脚本 Code: Copy to clipboard CR

11、EATE OR REPLACE PROCEDURE show_space ( p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT TABLE, p_partition IN VARCHAR2 DEFAULT NULL ) - This procedure uses AUTHID CURRENT USER so it can query DBA_* - views using privileges from a ROLE and so it can be installed - o

12、nce per database, instead of once per user who wanted to use it. AUTHID CURRENT_USER as l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; l_segment_space_m

13、gmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; - Inline procedur

14、e to print out numbers nicely formatted - with a simple label. PROCEDURE p( p_label in varchar2, p_num in number ) IS BEGIN dbms_output.put_line( rpad(p_label,40,.) | to_char(p_num,999,999,999,999) ); END; BEGIN - This query is executed dynamically in order to allow this procedure - to be created by

15、 a user who has access to DBA_SEGMENTS/TABLESPACES - via a role as is customary. - NOTE: at runtime, the invoker MUST have access to these two - views! - This query determines if the object is an ASSM object or not. BEGIN EXECUTE IMMEDIATE select ts.segment_space_management FROM dba_segments seg, db

16、a_tablespaces ts WHERE seg.segment_name = :p_segname AND (:p_partition is null or seg.partition_name = :p_partition) AND seg.owner = :p_owner AND seg.tablespace_name = ts.tablespace_name INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner; EXCEPTION WHEN too_many_rows THEN d

17、bms_output.put_line ( This must be a partitioned table, use p_partition = ); RETURN; END; - If the object is in an ASSM tablespace, we must use this API - call to get space information; else we use the FREE_BLOCKS - API for the user managed segments. IF l_segment_space_mgmt = AUTO THEN dbms_space.sp

18、ace_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p( Unformatted Blocks , l_unformatted_blocks ); p( FS1 Blocks (0-2

19、5) , l_fs1_blocks ); p( FS2 Blocks (25-50) , l_fs2_blocks ); p( FS3 Blocks (50-75) , l_fs3_blocks ); p( FS4 Blocks (75-100), l_fs4_blocks ); p( Full Blocks , l_full_blocks ); ELSE dbms_space.free_blocks( segment_owner = p_owner, segment_name = p_segname, segment_type = p_type, freelist_group_id = 0,

20、 free_blks = l_free_blks); p( Free Blocks, l_free_blks ); END IF; - And then the unused space API call to get the rest of the - information. dbms_space.unused_space ( segment_owner = p_owner, segment_name = p_segname, segment_type = p_type, partition_name = p_partition, total_blocks = l_total_blocks

21、, total_bytes = l_total_bytes, unused_blocks = l_unused_blocks, unused_bytes = l_unused_bytes, LAST_USED_EXTENT_FILE_ID = l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID = l_LastUsedExtBlockId, LAST_USED_BLOCK = l_LAST_USED_BLOCK ); p( Total Blocks, l_total_blocks ); p( Total Bytes, l_total_bytes );

22、p( Total MBytes, trunc(l_total_bytes/1024/1024) ); p( Unused Blocks, l_unused_blocks ); p( Unused Bytes, l_unused_bytes ); p( Last Used Ext FileId, l_LastUsedExtFileId ); p( Last Used Ext BlockId, l_LastUsedExtBlockId ); p( Last Used Block, l_LAST_USED_BLOCK ); END; 隐含参数： select a.ksppinm parameter

23、, a.ksppdesc descriptoin from x$ksppi a,x$ksppcv b,x$ksppsv c where a.indx=b.indx and a.indx=c.indx and a.ksppinm like /_% escape /;回复： DBA常用SQL语句系列，欢迎补充，在不断更新中Check OS process id from Oracle sid Code: Copy to clipboard select spid from v$process where addr in ( select paddr from v$session where sid

24、=$sid) Check Oracle sid from OS process id Code: Copy to clipboard select sid from v$session where paddr in ( select addr from v$process where spid=$pid) Check current SQL in a session Code: Copy to clipboard select SQL_TEXT from V$SQLTEXT where HASH_VALUE = ( select SQL_HASH_VALUE from v$session wh

25、ere sid = &sid) order by PIECE Checking v$session_wait Code: Copy to clipboard select * from v$session_wait where event not like rdbms% and event not like SQL*N% and event not like %timer; Dictionary Cache Hits Code: Copy to clipboard SELECT sum(getmisses)/sum(gets) FROM v$rowcache; /*It should be &

26、lt; 15%, otherwise Add share_pool_size*/ Check DB object name from file id and block# Code: Copy to clipboard select owner,segment_name,segment_type from dba_extents where file_id = $fno and &dno between block_id and block_id + blocks 1 回复： DBA常用SQL语句系列，欢迎补充，在不断更新中#寻找hot block select /*+ ordered */

27、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

28、e.block_id and e.block_id + e.blocks - 1; #找出每个文件上的等待事件 select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#; #找出引起等待事件的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 #监控共享池中哪个对象引起了大的内存

29、分配 SELECT * FROM X$KSMLRU WHERE ksmlrsiz 0; 判断你是从pfile启动还是spfile启动的简单方法！判断你是从pfile启动还是spfile启动的简单方法！ select decode(count(*), 1, spfile, pfile ) from v$spparameter where rownum=1 and isspecified=TRUE / DECODE - spfile ORACLE常用技巧和脚本ORACLE常用技巧和脚本1.如何查看ORACLE的隐含参数？ ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用show parameter *，可以显示。但ORACLE还有一些参数是以“_”，开头的。如我们非常熟悉的“_offline_rollback_segments”等。这些参数可在sys.x$ksppi表中查出。语句：“select ksppinm from x$ksppi where substr(ksppinm,1,1)=_; ”2.如何查看安装了哪些ORACLE组件？ 进入$ORACLE_HOME/orainst/，运行./inspdver，显示安装组件和版本号。3.如何查看ORACLE所占用共享内存的大小？ 可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。