這是itpub,conug的Oracle版主piner兄整理的，其中的show_space腳本來自
聞名的asktom網，是一個研究表空間存儲，擴展，碎片等問題的非常好的腳本！
一、數據庫構架體系
1、表空間的監控是一個重要的任務，我們必須時刻關心表空間的設置，是否滿足現在應用的需求，以下的語句可以查詢到表空間的具體信息
SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS, CONTENTS,LOGGING, EXTENT_MANAGEMENT, -- Columns not available in v8.0.x ALLOCATION_TYPE, -- Remove these columns if running PLUGGED_IN, -- against a v8.0.x database SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME;
2、對于某些數據文件沒有設置為自動擴展的表空間來說，假如表空間滿了，就將意味著數據庫可能會因為沒有空間而停止下來。監控表空間，最主要的就是監控剩余空間的大小或者是使用率。以下是監控表空間使用率與剩余空間大小的語句
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfile SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)", NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
3、除了監控表空間的剩余空間，有時候我們也有必要了解一下該表空間是否具有自動擴展空間的能力，雖然我們建議在生產系統中預先分配空間。以下語句將完成這一功能
SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T, DBA_DATA_FILES D WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME
4、我相信使用字典治理的表空間的也不少吧，因為字典治理的表空間中，每個表的下一個區間的大小是不可以預料的，所以我們必須監控那些表在字典治理的表空間中的下一個區間的分配將會引起性能問題或由于是非擴展的表空間而導致系統停止。以下語句檢查那些表的擴展將引起表空間的擴展。
SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME FROM ALL_TABLES A, (SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK
5、段的占用空間與區間數也是很需要注重的一個問題，假如一個段的占用空間太大，或者跨越太多的區間（在字典治理的表空間中，將有嚴重的性能影響），假如段沒有可以再分配的區間，將導致數據庫錯誤。所以，段的大小與區間監控也是一個很重要的工作
SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME, ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)", EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS, S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)" FROM DBA_SEGMENTS S WHERE S.OWNER NOT IN ('SYS','SYSTEM') ORDER BY Used_Extents DESC
6、對象的空間分配與空間利用情況，除了從各個方面的分析，如分析表，查詢rowid等方法外，其實oracle提供了一個查詢空間的包dbms_space，假如我們稍封裝一下，將是非常好用的一個東西。
CREATE OR REPLACE PROCEDURE show_space (p_segname in varchar2, p_type in varchar2 default 'TABLE' , p_owner in varchar2 default user) AS v_segname varchar2(100); v_type varchar2(10); 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; PROCEDURE p( p_label in varchar2, p_num in number ) IS BEGIN dbms_output.put_line( rpad(p_label,40,'.') p_num ); END; BEGIN v_segname := upper(p_segname); v_type := p_type; if (p_type = 'i' or p_type = 'I') then v_type := 'INDEX'; end if; if (p_type = 't' or p_type = 'T') then v_type := 'TABLE'; end if; if (p_type = 'c' or p_type = 'C') then v_type := 'CLUSTER'; end if; --以下部分不能用于ASSM dbms_space.free_blocks ( segment_owner => p_owner, segment_name => v_segname, segment_type => v_type, freelist_group_id => 0, free_blks => l_free_blks ); --以上部分不能用于ASSM dbms_space.unused_space ( segment_owner => p_owner, segment_name => v_segname, segment_type => v_type, 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( 'Free Blocks', l_free_blks ); p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', 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;
執行結果將如下所示
SQL> set serveroutput on; SQL> exec show_space('test'); Free Blocks.............................1 Total Blocks............................8 Total Bytes.............................65536 Unused Blocks...........................6 Unused Bytes............................49152 Last Used Ext FileId....................1 Last Used Ext BlockId...................48521 Last Used Block.........................2 PL/SQL procedure sUCcessfully completed
7、數據庫的常規參數我就不說了，除了V$parameter中的常規參數外，ORACLE還有大量的隱含參數，下面的語句就可以查詢到數據庫的所有隱含參數以及其值與參數的描述。
SELECT NAME ,VALUE ,decode(isdefault, 'TRUE','Y','N') as "Default" ,decode(ISEM,'TRUE','Y','N') as SesMod ,decode(ISYM,'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod ,decode(IMOD,'MODIFIED','U', 'SYS_MODIFIED','S','N') as Modified ,decode(IADJ,'TRUE','Y','N') as Adjusted ,description FROM ( --GV$SYSTEM_PARAMETER SELECT x.inst_id as instance ,x.indx+1 ,ksppinm as NAME ,ksppity ,ksppstvl as VALUE ,ksppstdf as isdefault ,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM ,decode(bitand(ksppiflg/65536,3), 1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM ,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD ,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ ,ksppdesc as DESCRIPTION FROM x$ksppi x ,x$ksppsv y WHERE x.indx = y.indx AND substr(ksppinm,1,1) = '_' AND x.inst_id = USERENV('Instance') ) ORDER BY NAME
8、數據庫的索引假如有比較頻繁的Delete操作，將可能導致索引產生很多碎片，所以，在有的時候，需要對所有的索引重新REBUILD，以便合并索引塊，減少碎片，提高查詢速度。
SQL> set heading off SQL> set feedback off SQL> spool d:/index.sql SQL> SELECT 'alter index ' index_name ' rebuild ' 'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);' FROM all_indexes WHERE ( tablespace_name != 'INDEXES' OR next_extent != ( 256 * 1024 ) ) AND owner = USER SQL>spool off
這個時候，我們打開spool出來的文件，就可以直接運行了。
9、表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規范的，所以我們需要監控表是否有主鍵
SELECT table_name FROM all_tables WHERE owner = USER MINUS SELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = 'P'
二、性能監控
1、數據緩沖區的命中率已經不是性能調整中的主要問題了，但是，過低的命中率肯定是不可以的，在任何情況下，我們必須保證有一個大的data buffer和一個高的命中率。
這個語句可以獲得整體的數據緩沖命中率，越高越好
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$sysstat 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語句的重載率，越低越好
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads, SUM(reloads)/SUM(pins)*100 libcache_reload_ratio FROM v$librarycache
3、用戶鎖，數據庫的鎖有的時候是比較耗費資源的，非凡是發生鎖等待的時候，我們必須找到發生等待的鎖，有可能的話，殺掉該進程。
這個語句將查找到數據庫中所有的DML語句產生的鎖，還可以發現，任何DML語句其實產生了兩個鎖，一個是表鎖，一個是行鎖。
可以通過alter system kill session ‘sid,serial#’來殺掉會話
SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE 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、鎖與等待，假如發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表，而誰在等待。
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
以上查詢結果是一個樹狀結構，假如有子節點，則表示有等待發生。假如想知道鎖用了哪個回滾段，還可以關聯到V$rollname，其中xidusn就是回滾段的USN
5、假如發生了事務或鎖，想知道哪些回滾段正在被使用嗎？其實通過事務表，我們可以具體的查詢到事務與回滾段之間的關系。同時，假如關聯會話表，我們則可以知道是哪個會話發動了這個事務。
SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum", t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks 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
6、想知道現在哪個用戶正在利用臨時段嗎？這個語句將告訴你哪個用戶正在利用臨時段。
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status,c.sql_text FROM v$session a,v$sort_usage b, v$sql c WHERE a.saddr = b.session_addr AND a.sql_address = c.address(+) ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
7、假如利用會話跟蹤或者是想查看某個會話的跟蹤文件，那么查詢到OS上的進程或線程號是非常重要的，因為文件的令名中，就包含這個信息，以下的語句可以查詢到進程或線程號，由此就可以找到對應的文件。
SELECT p1.value'/'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操作時的操作。
以下就是開始索引監控與停止索引監控的腳本
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 pages 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
