Oracle-監控緩衝區的命中率

Oracle-監控緩衝區的命中率

---

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1  oracle的記憶體結構
記憶體結構(SGA system global area)是oracle為一個例項分配的一組共享記憶體緩衝區, 它包含該例項的資料和控制資訊.
SGA在例項啟動時被自動分配, 當例項關閉時被收回. 資料庫的所有資料操作都透過SGA來進行.


資料庫緩衝區(Buffer Cache): 存放資料庫中資料庫塊的copy. 它由一組緩衝塊組成,這些緩衝塊為所有與該例項相連的使用者程式共享.
緩衝塊的數目由初始化引數DB_Block_buffers確定, 緩衝塊的大小由初始化引數DB_BLOCK_SIZE確定. 大的資料塊可提高查詢速度.

日誌緩衝區(Redo Log Buffer): 存放資料操作的更改資訊. 它們以日誌項(redo entry)的形式存放在日誌緩衝區中. 當需要進行資料庫恢復時,
日誌項用於重構或回滾對資料庫所做的變更. 日誌緩衝區的大小由初始化引數LOG_BUFFER確定. 大的日誌緩衝區可減少日誌檔案IO的次數.
後臺程式LGWR將日誌緩衝區中的資訊寫入磁碟的日誌檔案中, 可啟動ARCH後臺程式進行日誌資訊歸檔.

共享池(shared pool): 包含用來處理的sql語句資訊. 它包含共享sql區和資料字典儲存區. 共享sql區包含執行特定的sql語句所用的資訊.
資料字典區包含用於存放資料字典, 它為所有使用者程式共享.

Cursors: 一些記憶體指標, 執行待處理的sql語句.

其他資訊區: 除了上述幾個資訊區外, 還包括一些程式之間的通訊資訊(如封鎖資訊); 在多執行緒伺服器配置下, 還有一些程式全域性區的資訊, 請求佇列和響應佇列等.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 oracle資料庫緩衝區的內部機制
Oracle用這一記憶體來防止不必要的資料塊從磁碟重讀. 理解oracle資料緩衝區如何操作, 是成功地運用它們調整資料庫效能的關鍵.
在oracle8i之前的版本中, 當資料塊被從磁碟送進資料緩衝區的時候, 資料塊被自動地放置到最近使用過的資料列表的前部.

但是, 這種行為從oracle8i開始就變了: 新資料緩衝區被放置在緩衝區鏈的中部. 在調節資料庫的時候, 你的目標就是為資料緩衝區分配儘量多的記憶體,
而不會導致資料庫伺服器在記憶體裡分頁. 資料緩衝區每小時的命中率一旦低於90%, 你就應該為資料塊緩衝區增加緩衝區.

2.1 資料塊的存活時間
在調入資料塊之後, oracle會不停地跟蹤資料塊的使用計數(touch count, 也就是說, 這個資料塊被使用者執行緒所訪問的次數). 如果一個資料塊被多次使用,
它就被移動到最近使用過的資料列表的最前面, 這樣就確保它會在記憶體裡儲存一段較長的時間. 這種新的中點插入技術會確保最常使用的資料塊被保留在最
近使用過的資料列表的最前面, 因為新的資料塊只有在它們被重複使用的時候才會被移動到緩衝區鏈的最前面.

總而言之, oracle8i透過將新的資料塊插入緩衝區的中部, 並根據訪問活動調整緩衝區鏈, 每個資料緩衝區就被分割成兩個部分: 熱區(hot section),
代表資料緩衝區的最近使用的一半; 冷區(cold section), 代表資料緩衝區的最早使用的一半. 只有那些被反覆請求的資料塊才會被移近每個緩衝區的熱區,
這就讓每個資料緩衝區在緩衝常用資料塊的時候效率更高.

熱區的大小要用下面的隱藏引數來配置:
_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle
只有懂得內部機制和希望調節其資料緩衝區行為的有經驗人員才應該使用這些引數.

2.2 找到熱資料塊
Oracle8i保留了一個X$BH內部檢視錶, 用來顯示資料緩衝池的相對效能. X$BH檢視錶有下列資料列:
Tim: 兩次使用之間的時間差, 和_db_aging_touch_time引數相關;
Tch: 使用計數, 它和被使用過_db_aging_hot_criteria次之後從冷區移入熱區直接相關.

由於tch資料列用來追蹤特定資料塊的使用次數, 所以我們就可以編寫sql查詢來顯示緩衝區裡的熱資料塊—使用計數大於10的資料塊.
SELECT obj object, dbarfil file#, dbablk block#, tch touches
  FROM x$bh
 WHERE tch > 200  ORDER BY tch desc;
這項高階查詢技術在用於追蹤DEFAULT緩衝池裡的物件時尤其有用. 一旦定位了熱資料塊, 你就能夠把它從default緩衝池移動到keep緩衝池.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3 資料庫緩衝區命中率

select name,value
   from v$sysstat
  where name in ('physical reads', 'physical reads direct',
         'physical reads direct (lob)', 'session logical reads');


select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
  from v$sysstat physical,
       v$sysstat direct,
       v$sysstat lobs,
       v$sysstat logical
 where physical.name = 'physical reads'
   and direct.name = 'physical reads direct'
   and lobs.name = 'physical reads direct (lob)'
   and logical.name = 'session logical reads';



+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 改進資料庫緩衝區的效能

4.1 修改data buffer cache的大小
調整database buffer cache的效能最簡單的辦法就是修改它的大小. 佔SGA的45%最合適.
1, DB_BLOCK_SIZE主資料庫塊大小預設是8K, 在建立完資料庫之後就不能改變.
2, DB_CACHE_SIZE(預設緩衝區池)預設48M, 我們主要修改這個引數以提高效能.
Alter system set db_cache_advice = on;  
用oracle推薦的統計顧問, 在正常操作半個小時之後可以執行如下查詢得到推薦的大小.
select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from v$db_cache_advice  where block_size='8192' and advice_status='ON';

另外, 我們可以透過下述語句查詢哪些物件正快取在buffer cache中, 正使用多少個buffer cache緩衝區.


select do.owner,do.object_name,do.object_type,count(distinct a.BLOCK#) "NUM of buffers"
from v$bh a, dba_objects do
where a.OBJD = do.object_id
  and do.owner = 'CTL'
group by do.owner,do.object_name,do.object_type;



3, 使用多個緩衝池

Alter system set db_cache_size = 300M;
Alter system set db_keep_cache_size = 150M;
Alter system set db_recycle_cache_size = 30M;


將表分配給保持區.

Alter table col_cust storage(buffer_pool keep);


使用下面語句可以檢視分配情況.

select owner,segment_type,segment_name,buffer_pool
from dba_segments
where buffer_pool != 'DEFAULT';


使用下面語句可以檢視每個buffer pool的大小以及buffer cache的命中率.

select name "Buffer Pool",
       1 - (physical_reads / (db_block_gets + consistent_gets)) "Buffer Pool Hit Ratio"
  from v$buffer_pool_statistics
 order by name;

4, 在記憶體中快取表
雖然表可以放在保持區, 但是由於keep pool也是由LRU控制的, 所以當FTS(full table scan)時, 該表還是被放在LRU的最近最少使用那端. 這樣就有可能被移出.
所以我們可以將表變成一個快取記憶體區表.

Alter table col_cust cache;
Select owner, table_name from dba_tables where ltrim(cache)='Y';