DBA常用脚本2-性能监控

数据缓沖区的命中率已经不是性能调整中的主要问题了但是过低的命中率肯定是不可以的在任何情况下我们必须保证有一个大的data buffer和一个高的命中率

这个语句可以获得整体的数据缓沖命中率越高越好

Code: [Copy to clipboard]

SELECT aVALUE + bVALUE logical_reads

cVALUE phys_reads

round(*(cvalue/(avalue+bvalue))) hit_ratio

FROM v$sysstat av$sysstat bv$sysstat c

WHERE aNAME=db block gets

AND bNAME=consistent gets

AND cNAME=physical reads

库缓沖说明了SQL语句的重载率当然一个SQL语句应当被执行的越多越好如果重载率比较高就考虑增加共享池大小或者是提高Bind变量的使用以下语句查询了Sql语句的重载率越低越好

Code: [Copy to clipboard]

SELECT SUM(pins) total_pinsSUM(reloads) total_reloads

SUM(reloads)/SUM(pins)* libcache_reload_ratio

FROMv$librarycache

用户锁数据库的锁有的时候是比较耗费资源的特别是发生锁等待的时候我们必须找到发生等待的锁有可能的话杀掉该进程

这个语句将查找到数据库中所有的DML语句产生的锁还可以发现任何DML语句其实产生了两个锁一个是表锁一个是行锁

可以通过alter system kill session sidserial#来杀掉会话

Code: [Copy to clipboard]

SELECT /*+ rule */ susername

decode(ltypeTMTABLE LOCK

TXROW LOCK

NULL) LOCK_LEVEL

oowneroobject_nameoobject_type

ssidsserial#sterminalsmachinesprogramsosuser

FROM v$session sv$lock ldba_objects o

WHERE lsid = ssid

AND lid = oobject_id(+)

AND susername is NOT NULL

锁与等待如果发生了锁等待我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以查询到谁锁了表而谁在等待

Code: [Copy to clipboard]

SELECT /*+ rule */ lpad( decode(lxidusn ))||loracle_username User_name

oowneroobject_nameoobject_typessidsserial#

FROM v$locked_object ldba_objects ov$session s

WHERE lobject_id=oobject_id

AND lsession_id=ssid

ORDER BY oobject_idxidusn DESC

以上查询结果是一个树状结构如果有子节点则表示有等待发生如果想知道锁用了哪个回滚段还可以关联到V$rollname其中xidusn就是回滚段的USN

如果发生了事务或锁想知道哪些回滚段正在被使用吗？其实通过事务表我们可以详细的查询到事务与回滚段之间的关系同时如果关联会话表我们则可以知道是哪个会话发动了这个事务

Code: [Copy to clipboard]

SELECT sUSERNAMEsSIDsSERIAL#tUBAFIL UBA filenum

tUBABLK UBA Block numbertUSED_UBLK Number os undo Blocks Used

tSTART_TIMEtSTATUStSTART_SCNBtXIDUSN RollIDrNAME RollName

FROM v$session sv$transaction tv$rollname r

WHERE sSADDR=tSES_ADDR

AND tXIDUSN=rusn

如果利用会话跟蹤或者是想查看某个会话的跟蹤文件那么查询到OS上的进程或线程号是非常重要的因为文件的令名中就包含这个信息以下的语句可以查询到进程或线程号由此就可以找到对应的文件

Code: [Copy to clipboard]

SELECT pvalue||\||pvalue||_ora_||pspid filename

FROM

v$process p

v$session s

v$parameter p

v$parameter p

WHERE pname = user_dump_dest

AND pname = db_name

AND paddr = spaddr

AND saudsid = USERENV (SESSIONID);

在ORACLE i中可以监控索引的使用如果没有使用到的索引完全可以删除掉减少DML操作时的操作

以下就是开始索引监控与停止索引监控的脚本

Code: [Copy to clipboard]

set heading off

set echo off

set feedback off

set pages

spool start_index_monitorsql

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

spool stop_index_monitorsql

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中查询

Code: [Copy to clipboard]

CREATE OR REPLACE PROCEDURE show_space

( p_segname IN VARCHAR

p_owner IN VARCHAR DEFAULT USER

p_typeIN VARCHAR DEFAULT TABLE

p_partition IN VARCHAR 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

once per database instead of once per user who wanted to use it

AUTHID CURRENT_USER

as

l_free_blks number;

l_total_blocksnumber;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytesnumber;

l_LastUsedExtFileId number;

l_LastUsedExtBlockIdnumber;

l_LAST_USED_BLOCK number;

l_segment_space_mgmtvarchar();

l_unformatted_blocks number;

l_unformatted_bytes number;

l_fs_blocks number; l_fs_bytes number;

l_fs_blocks number; l_fs_bytes number;

l_fs_blocks number; l_fs_bytes number;

l_fs_blocks number; l_fs_bytes number;

l_full_blocks number; l_full_bytes number;

Inline procedure to print out numbers nicely formatted

with a simple label

PROCEDURE p( p_label in varchar p_num in number )

IS

BEGIN

dbms_outputput_line( rpad(p_label) ||

to_char(p_num) );

END;

BEGIN

This query is executed dynamically in order to allow this procedure

to be created by 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 tssegment_space_management

FROM dba_segments seg dba_tablespaces ts

WHERE segsegment_name= :p_segname

AND (:p_partition is null or

segpartition_name = :p_partition)

AND segowner = :p_owner

AND segtablespace_name = tstablespace_name

INTO l_segment_space_mgmt

USING p_segname p_partition p_partition p_owner;

EXCEPTION

WHEN too_many_rows THEN

dbms_outputput_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_spacespace_usage

( p_owner p_segname p_type l_unformatted_blocks

l_unformatted_bytes l_fs_blocks l_fs_bytes

l_fs_blocks l_fs_bytes l_fs_blocks l_fs_bytes

l_fs_blocks l_fs_bytes l_full_blocks l_full_bytes p_partition);

p( Unformatted Blocks l_unformatted_blocks );

p( FS Blocks () l_fs_blocks );

p( FS Blocks () l_fs_blocks );

p( FS Blocks () l_fs_blocks );

p( FS Blocks () l_fs_blocks );

p( Full Blocks l_full_blocks );

ELSE

dbms_spacefree_blocks(

segment_owner => p_owner

segment_name=> p_segname

segment_type=> p_type

freelist_group_id =>

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_spaceunused_space

( segment_owner => p_owner

segment_name=> p_segname

segment_type=> p_type

partition_name=> p_partition

total_blocks=> l_total_blocks

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 );

p( Total MBytes trunc(l_total_bytes//) );

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;