一、資料庫構架體系
1、表空間的監控
2、監控表空間使用率與剩餘空間大小的語句
3、表空間是否具有自動擴充套件空間的能力
4、使用字典管理的表空間哪些表的擴充套件將引起表空間的擴充套件
5、段的佔用空間與區間數
6、重建索引
7、監控表是否有主鍵
二、效能監控
1、資料緩衝區的命中率
2、庫緩衝說明了SQL語句的過載率，越低越好
3、使用者鎖
4、鎖與等待，查詢誰鎖了表，而誰在等待
5、發生了事務或鎖，查詢使用的回滾段
6、哪個使用者正在利用臨時段嗎？
7、在ORACLE 9i中，可以監控索引的使用，開始索引監控與停止索引監控的指令碼
8、通過sid找到os程式號（Check OS process id from Oracle sid ）
9、通過os程式找sid（Check Oracle sid from OS process id ）
10、通過sid找sql語句（Check current SQL in a session ）
11、找等待事件Checking v$session_wait
12、資料緩衝區GETMISS相對gets的比例Dictionary Cache Hits MISS RATIO
13、通過檔案號及塊號找對應資料庫物件Check DB object name from file id and block#
14、尋找hot block
15、找出每個檔案上的等待事件
16、找出引起等待事件的SQL語句.
17、監控共享池中哪個物件引起了大的記憶體分配

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
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、重建索引
資料庫的索引如果有比較頻繁的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 wner = USER
SQL>spool off

這個時候，我們開啟spool出來的檔案，就可以直接執行了。

7、監控表是否有主鍵
SELECT table_name
FROM all_tables
WHERE wner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE wner = USER
AND constraint_type = 'P';

二、效能監控

1、資料緩衝區的命中率
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語句的過載率，越低越好
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,
SUM(reloads)/SUM(pins)*100 libcache_reload_ratio
FROM v$librarycache;

3、使用者鎖
任何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、在ORACLE 9i中，可以監控索引的使用，開始索引監控與停止索引監控的指令碼
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 wner = 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 wner = USER;
spool off
set heading on
set echo on
set feedback on

如果需要監控更多的使用者，可以將owner=User改寫成別的
監控結果在檢視v$object_usage中查詢

8、Check OS process id from Oracle sid
select spid from v$process
where addr in
( select paddr from v$session where sid=&sid) ;

9、Check Oracle sid from OS process id
select sid from v$session
where paddr in ( select addr from v$process where spid=&pid) ;

10、Check current SQL in a session
select SQL_TEXT
from V$SQLTEXT
where HASH_VALUE
= ( select SQL_HASH_VALUE from v$session
where sid = &sid)
order by PIECE;

11、Checking v$session_wait
select * from v$session_wait
where event not like 'rdbms%'
and event not like 'SQL*N%'
and event not like '%timer';

12、Dictionary Cache Hits
SELECT sum(getmisses)/sum(gets) FROM v$rowcache; /*It should be < 15%, otherwise Add share_pool_size*/

13、Check DB object name from file id and block#
select owner,segment_name,segment_type
from dba_extents
where file_id = &fno
and &dno between block_id
and block_id + blocks – 1 ;

14、尋找hot block
select /*+ ordered */
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 e.block_id
and e.block_id + e.blocks - 1;

15、找出每個檔案上的等待事件
select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#;

16、找出引起等待事件的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;

17、監控共享池中哪個物件引起了大的記憶體分配
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;