Oracle的大表,小表與全表掃描

通常對於小表，Oracle建議通過全表掃描進行數據訪問，對於大表則應該通過索引以加快數據查詢，當然如果查詢要求返回表中大部分或者全部數據，那麼全表掃描可能仍然是最好的選擇。

　　從V$SYSSTAT視圖中，我們可以查詢得到關於全表掃描的系統統計信息： 

SQL> col name for a30 
SQL> select name,value from v$sysstat
  2  where name in ('table scans (short tables)','table scans (long tables)');

NAME                                  VALUE
------------------------------     ----------
table scans (short tables)            828
table scans (long tables)             101

　　其中table scans (short tables)指對於小表的全表掃描的此時；table scans (long tables)指對於大表的全表掃描的次數。

　　從Statspack的報告中，我們也可以找到這部分信息：

Instance Activity Stats for DB: CELLSTAR  Instance: ora8i  Snaps:      20 -  
                                                                             
Statistic                                    Total   per Second    per Trans 
--------------------------------- ---------------- ------------ ------------ 
。。。。。。
table scan blocks gotten                38,228,349         37.0         26.9 
table scan rows gotten                 546,452,583        528.9        383.8 
table scans (direct read)                    5,784          0.0          0.0 
table scans (long tables)                    5,990          0.0          0.0 
table scans (rowid ranges)                   5,850          0.0          0.0 
table scans (short tables)               1,185,275          1.2           0.8 

　　通常，如果一個數據庫的table scans (long tables)過多，那麼db file scattered read等待事件可能同樣非常顯著，和以上數據來自同一個report的Top5等待事件就是如此：

Top 5 Wait Events                                                            
'''''''''''''''''                                    Wait     % Total
Event                                         Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file parallel write                         1,436,993    1,102,188   10.80
log buffer space                                   16,698      873,203    8.56
log file sync                                   1,413,374      654,587    6.42
control file parallel write                       329,777      510,078    5.00
db file scattered read                            425,578      132,537    1.30

　　數據庫內部，很多信息和現象都是緊密相關的，只要我們加深對於數據庫的了解，在優化和診斷數據庫問題時就能夠得心應手。

　　Oracle通過一個內部參數_small_table_threshold來定義大表和小表的界限。缺省的該參數等於2%的Buffer數量，如果表的大小小於該參數定義，Oracle認為該表為小表，否則Oracle認為該表為大表。

　　我們看一下Oracle9iR2中的情況：

SQL> @@GetParDescrb.sql
Enter value for par: small
old   6:    AND x.ksppinm LIKE '%&par%'
new   6:    AND x.ksppinm LIKE '%small%'

NAME                    VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_small_table_threshold         200                  threshold level of table size for direct reads

　　以上數據庫中，200正好約為Buffer數量的2%:

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 83886080
SQL> select (83886080/8192)*2/100 from dual;

(83886080/8192)*2/100
---------------------
                204.8

　　所以要區分大小表（Long/Short）是因為全表掃描可能引起Buffer Cache的抖動，缺省的大表的全表掃描會被置於LRU的末端，以期盡快老化，減少Buffer的占用。從Oracle8i開始，Oracle的多緩沖池管理技術（Default/Keep/Recycle池）給了我們另外一個選擇，對於不同大小、不同使用頻率的數據表，從建表之初就可以指定其存儲Buffer，以使得內存使用更加有效。