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
　　FROM　v$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_type　　IN 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_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_mgmt　　　　varchar();
　　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;
From:http://tw.wingwit.com/Article/program/Oracle/201311/18788.html