五個常用又重要的參數校調

DB_BLOCK_BUFFERS,

SHARED_POOL_SIZE,

LOG_CHECKPOINT_INTERVAL,

LOG_BUFFER,

SORT_AREA_SIZE

 


1.DB_BLOCK_BUFFERS
目標值> 維持 90%以上
1.1 統計方法
select round(((1-(sum(decode(name,
        'physical reads', value,0))/
        (sum(decode(name, 'db block gets', value,0))+
        (sum(decode(name, 'consistent gets', value, 0))))))*100),2)
        || '%' "Buffer Cache Hit Ratio"
        from v$sysstat;

1.2 實作
案例A
SQL> show sga

Total System Global Area  279433108 bytes
Fixed Size                           73620 bytes
Variable Size                156307456 bytes
Database Buffers          122880000 bytes
Redo Buffers                     172032 bytes
SQL>
SQL> select round(((1-(sum(decode(name,
  2          'physical reads', value,0))/
  3          (sum(decode(name, 'db block gets', value,0))+
  4          (sum(decode(name, 'consistent gets', value, 0))))))*100),2)
  5          || '%' "Buffer Cache Hit Ratio"
  6          from v$sysstat;

Buffer Cache Hit Ratio
-----------------------------------------
99.48%

案例B
SQL> show sga

Total System Global Area 1452044180 bytes
Fixed Size                           73620 bytes
Variable Size                714518528 bytes
Database Buffers          737280000 bytes
Redo Buffers                     172032 bytes

SQL> select round(((1-(sum(decode(name,
  2          'physical reads', value,0))/
  3          (sum(decode(name, 'db block gets', value,0))+
  4          (sum(decode(name, 'consistent gets', value, 0))))))*100),2)
  5          || '%' "Buffer Cache Hit Ratio"
  6          from v$sysstat;

Buffer Cache Hit Ratio
-----------------------------------------
67.51%

案例B的記憶體配置比案例A還大，卻發生hit-ration較小的怪現象



1.3修正方式
    For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
    For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.
針對案例B的修正方式如下
  1.3.1  在parameter 設 db_block_lru_statistics為 true
  1.3.2  重開instance 後會產生v$current_bucket
  1.3.3  執行以下指令查看 分佈狀況
           SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' ||
              1000 * (TRUNC (rownum / 1000) + 1) "Interval",
              SUM (count) "Buffer Cache Hits"
     FROM     v$current_bucket
     WHERE    rownum > 0
     GROUP BY TRUNC (rownum / 1000)

     Interval     Buffer Cache Hits
     ------------ -----------------
     1 to 1000                 668415   
     1001 to 2000            281760   
     2001 to 3000            166940   
     3001 to 4000              14770    
     4001 to 5000                7030     
     5001 to 6000                  959
     前3000筆 66.8%+28.1%+16.7% 有達98% 表示目前記憶體配置適中，如果只達50% 那表示記憶體
     設太大，宜縮小。
  1.3.4   調整完大小後，記得把 db_block_lru_statistics 改為 false 免得耗用資源。
  1.3.5   實務上不可能這樣調，資料庫哪有可能讓你隨易重開。只能微調parameter檔，然後期待下次db重開

2.SHARED_POOL_SIZE
2.1統計方法
  select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
        from v$rowcache;
 
2.2 實作
案例A
SQL> show sga

Total System Global Area  279433108 bytes
Fixed Size                           73620 bytes
Variable Size                156307456 bytes
Database Buffers          122880000 bytes
Redo Buffers                     172032 bytes
SQL>
SQL> select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
        from v$rowcache;  2

 Hit Ratio
----------
99.1405331


Shared Pool
    Dictionary Hit Ratio    : .991435472 (>0.95)
    Shared SQL Buffers (Library Cache)
        Cache Hit Ratio     : .999754059 (>0.95)

案例B
SQL> show sga

Total System Global Area 1452044180 bytes
Fixed Size                           73620 bytes
Variable Size                714518528 bytes
Database Buffers          737280000 bytes
Redo Buffers                     172032 bytes
SQL> select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
        from v$rowcache;  2

 Hit Ratio
----------
99.7383081


Shared Pool
    Dictionary Hit Ratio    : .997772111 (>0.95)
    Shared SQL Buffers (Library Cache)
        Cache Hit Ratio     : .999838379 (>0.95)


2.3 修改
 2.3 .1一般而言，當shared pool hit ratio低於 90%，則SHARED_POOL_SIZE 一次增加原有的20%
      直到滿足為止。
 2.3.2 注意bind value SQL之改善



3.log_checkpoint_interval =n (n is number of OS block)
3.1 統計方法
    一般是以  redo log的大小 / OS block size
    例如128m/4096　=32768 那就是當每滿一個日誌檔時就發生checkpoint 同步一下datafiles.
    如果OS 512K 設10000就是每5M寫一次，相對如果redo log是20M，那就要寫4次才會切換LOG。


3.2 何謂checkpoint ?
      指資料庫同步 記憶體中的block buffer和硬碟中的datafile block 使一致性和回復recover用途

3.3 觸發時機
     a.alter system checkpoint;
     b.alter system switch logfile;
     c.當 log_checkpoint_interval之大小 達到時
     d.當 log_checkpoint_timeout 達到時
      觸發後，DBWR會把記憶體中的dirty block寫入硬體裡，然後CKPT更新所有  datafile 檔頭
      對應至目前的SCN.

3.4 調整
      主要是和redo log size有關，如果redo log太小，那就切換頻繁 造成I/O瓶頸，太大的話到時
      異常recover就花很多時間。一般而言是盡量減少i/o為前提，除非系統有需要才改大量i/o
     133169664 Feb 27 08:13 S0000003267.ARC
     133169664 Feb 27 08:48 S0000003268.ARC
     133169664 Feb 27 09:07 S0000003269.ARC
     133169664 Feb 27 09:25 S0000003270.ARC
     133169664 Feb 27 09:41 S0000003271.ARC
     133169664 Feb 27 10:01 S0000003272.ARC
     133169664 Feb 27 10:13 S0000003273.ARC
     133169664 Feb 27 10:31 S0000003274.ARC
     133169664 Feb 27 10:47 S0000003275.ARC
     133169664 Feb 27 11:06 S0000003276.ARC
     133169152 Feb 27 11:30 S0000003277.ARC
     133169664 Feb 27 11:50 S0000003278.ARC
     133168640 Feb 27 12:04 S0000003279.ARC
     133169664 Feb 27 12:11 S0000003280.ARC
     133169664 Feb 27 12:18 S0000003281.ARC
    
以這為例 ，大概每20分鐘產生一次，故此值較無客觀標準，隨各家系統而定。
你可以調整每十分鐘 或每20分或每小時，以你能接受的db recovery time來設會比較適當。


      
4.LOG_BUFFER  (又稱Redo Log  Buffer 重置日誌緩衝區)
LGWR會進行寫日誌的操作：
當一個事務提交(commit)或回滾(rollback)的時候、
每3秒、
log buffer內容達到三分之一時都會寫入。

4.1 統計方法
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';
應該接近0 ，太大表示正在checkpoint or log buffer 太小。

在oracle 8i 最大是設512k，或128k*CPU數 或OS block的四倍，因為設太小 LGWR會對REDO log
寫入頻繁，產生多餘I/O
；到了Oracle 9i,10g 已經不重要了，通常會變很大


4.2實作
案例A和B的LOG_BUFFER都設為OS的四倍 即4096X4=16384 (BYTES)
很顯然的太小，導致 wait事件

SQL> SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';  2    3

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries                                         3896

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production


4.3 調整
在oracle 8i 最大也只能設512k，沒什麼好多說的。

5.
SORT_AREA_SIZE (bytes)
供每個session排序(group by/ order by)使用，記憶體中能先排就排，不行才去用到temporary tablespace
的硬碟。

5.1 統計方式
select current_utilization, max_utilization from v$resource_limit
where resource_name = 'sort_segment_locks';


5.2實作

案例A
SQL> show parameter sort

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer 19200
sort_area_size                       integer 192000
sort_multiblock_read_count           integer 2

計算一下：
CURRENT_UTILIZATION MAX_UTILIZATION
------------------- ---------------
                  5              13


案例B
SQL> show parameter sort

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer 0
sort_area_size                       integer 65536
sort_multiblock_read_count           integer 2

計算一下：
CURRENT_UTILIZATION MAX_UTILIZATION
------------------- ---------------
                  0               6


5.3調整
案例A的資料庫有設192000(BYTES)，因該資料庫語法 常出現排序，該資料庫平日約2000條session
大概會占用366m的記憶體(sort_area_size 不屬於SGA，是UGA)，而其統計結果現值6<最大值13
故此設定不必調整。

案例B根本沒設定，採預設參數 65536(64k)，但由於B資料庫幾乎都沒排序的語法，故其統計值現值為0
也不必調整。

arrow
arrow
    文章標籤
    DB_BLOCK_BUFFERS SHARED_POOL_SIZE LOG_CHECKPOINT_INTERVAL LOG_BUFFER SORT_AREA_SIZE ORACLE PARAMETER TUNING
    全站熱搜

    哈哈小熊 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄