oracle最佳化——緩衝對象與資料

author：skate
當系統出現效能瓶頸時，盡量較少不必要的資源消耗，最後就是平衡cpu，記憶體，io，network等資源，使資料庫
可以穩定的運行。

oracle資料庫最佳化的根本是

1.盡量減少資源消耗，例如最佳化sql，減少sql本身的資源消耗
2.如果無法進一步減少資源的消耗，那就讓資料盡量靠近cpu，也就是把資料從硬碟轉移到記憶體（記憶體的讀寫速度快）
  或者換更快的磁碟

本文就簡單總結下如何快取資料和資料庫物件（也就是把資料移向記憶體，提高記憶體的命中率，以提高整體io速度）

1.快取資料
2.快取資料對象的定義，例如package，procedure，pl/sql和sql（也就是cursor）等

上面說的這兩種資料就存在oracle最重要的兩個組件中share pool和buffer pool中，提高這兩個pool的命中率也提高了
io速度，而io又是當今技術發展最慢，系統的最大的瓶頸。

1. 快取資料

這裡說的oracle資料是佔大量儲存空間的，不是存在資料庫字典裡的資料；oracle的資料的類型一般為：

SQL> select se.segment_type from dba_segments se group by se.segment_type;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO

11 rows selected

SQL>

在大部分時候，把這些資料放到記憶體裡，會很大的提升系統的效能

buffer pool分為三個子pool，這三個pool都主要使用LRU演算法管理的

default buffer pool：預設所有的資料區塊都存在這，並遵循本pool的LRU演算法

keep buffer pool：如果指定資料區塊緩衝到keep區的，資料區塊就不太可能因為執行其他一些操作被其他資料區塊交換出，即使較長時間沒使用了，只遵循本pool的LRU

recycle buffer pool：設定recycle是因為有時會有一些大的又教少使用的表的操作，如果不設定單獨的緩衝區，那麼預設的緩衝區中的資料區塊就被這些偶爾使用的資料換出，它的空間比較小，所以說一般使用完就釋放掉了，它也只遵守本pool的LRU演算法

以table為例：

修改table的緩衝空間

alter table a_user storage(buffer_pool keep) cache/nocache;  ---把表a_user緩衝到keep buffer pool中最熱端/把表a_user從keep buffer pool立刻釋放出去

alter table a_user cache/nocache  ---把表a_user緩衝到default buffer pool中最熱端/把表a_user從default buffer pool立刻釋放出去

eg：

1)

SQL> alter table a_user storage(buffer_pool keep) cache;

Table altered

2)

SQL> select t.table_name,t.cache,t.buffer_pool from user_tables t where t.table_name=upper('a_user');

TABLE_NAME                     CACHE                BUFFER_POOL
------------------------------ -------------------- -----------
a_user                          Y                KEEP

說明：

user_tables.cache:這個表一旦被讀入buffer cache，就會放在鏈表的熱端~ 盡量不被擠出buffer cache
user_tables.buffer_pool:把這個表放入特殊的buffer cache中，這些特殊的buffer cache是獨立的

而視圖v$db_object_cache.kept:告知是否對象常駐shared pool(yes/no)，有賴於這個對象是否已經利用PL/SQL 過程

DBMS_SHARED_POOL.KEEP“保持”（永久固定在記憶體中）

eg:

SQL> Select oc.NAME,oc.TYPE,oc.KEPT from v$db_object_cache oc where oc.TYPE='TABLE' AND OC.OWNER='HPO';

NAME                                                                             TYPE                         KEPT
-------------------------------------------------------------------------------- ---------------------------- ----
A_USER                                                                            TABLE                       YES

這個v$db_object_cache視圖提供對象在library cache(shared pool)中對象統計，提供比v$librarycache更多的細節，並且常用於找出shared pool中的使用中的物件。

所以你沒有使用過對象時，是不存在這個視圖裡的，使用時用了，才會在這個視圖裡出現

可以參看三思的動態效能檢視介紹：http://space.itpub.net/7607759/viewspace-22241

例如修改索引的buffer pool

alter index IDX_ORG_TYPE storage(buffer_pool keep) cache;

分區表和分區索引好像不能把每個分區放在不同的buffer pool中，反正我測試通過

2.快取資料對象的定義，例如package，procedure，pl/sql和sql（也就是cursor）等

上面介紹了把資料盡量緩衝在buffer pool中，提高資料在記憶體的命中率，避免從磁碟讀寫資料，間接提高系統io能力；
buffer pool緩衝的資料是使用者最終的目標資料，而把這些使用者最終目標資料要傳達給使用者，就需要oracle用另外一些
動作來完成，而這些動作主要是在share pool中完成的，大概功能有：緩衝語句文本，分析代碼，執行計畫，資料字典
中的表和列的許可權定義等；share pool主要也用LRU演算法，所以怎樣盡量緩衝這些資料就是下面要說的

oracle分為sql引擎和pl/sql引擎，分別完成sql和pl/sql的解析等工作，而這裡解析又是很耗資源的，所以就要想辦法
盡量少解析，使代碼重用以提高效率

A。代碼的重用

 確定是否需要對語句進行（硬）解析時，是先比較語句的雜湊值，下面的兩種方法有助於獲得相同的雜湊值，從而可以實現重用代碼，提高命中率：
 
 1）開發組的所有成員都使用相同的編碼規範（包括大小寫，空格，換行等）；

 2）使用綁定變數（提高命中率的同時可能會產生不夠好的執行計畫，因為最佳化器不知道變數的確定值，在有欄位的柱狀圖統計資料時也不能夠利用）。

調整相關初始化參數：

OPEN_CURSOR

 這個參數指定每個使用者會話能開啟的遊標最大數量；增大這個值可以減少重新解析會話曾開啟的語句的機會，提高命中率，但需要更大的共用池空間。要確保該值足夠，增加該值不會對記憶體造成太大的影響

cursor_space_for_time

 預設是FALSE，如果設定為TRUE，那麼SHARED SQL AREA當CURSOR開啟的時候，是PIN在共用池裡的，不允許被換出（AGEOUT），這樣提高了SQL的執行效率，另外PGA中的CURSOR的私人記憶體部分，執行完SQL後也不關閉，下次執行的時候可以直接使用，節省了記憶體配置和釋放的時間。對於同一個SQL反覆被執行的情況，這種設定有助於提高SQL執行的效率。但是這個參數設定會增加共用池的使用。如果共用池出現不足，或者片段很嚴重的情況，使用這個參數會加劇問題， 所以，一般在共用池足夠大的情況下才能考慮設為true，設為true時可以減少重解析，提高命中率，加快遊標的執行（空間換時間）。
 
 這個參數一般情況不需要開啟，一般情況下開啟對於系統效能的提升不會很大，對於parse很頻繁，而且SQL執行很頻繁，共用池片段較為嚴重的情況，建議不要使用。當然特殊情況有特殊的用途，否則這個參數也沒必要存在了。

session_cached_cursors

 預設是0，也就是不CACHE CURSOR，如果設定了SESSION_CACHED_CURSORS,某個CURSOR被頻繁調用，那麼當第三次被調用的時候會被CACHE，一個被CACHE的CURSOR下次再被調用的時候，可以省去PARSE的過程，提高SQL執行的效率，這些緩衝也是用LRU演算法來管理的。應該注意SESSION_CACHED_CURSORS的值不能超過OPEN_CURSORS的值。在設定SESSION_CACHED_CURSORS參數之前，首先要確定共用池的大小是否足以支援緩衝這些SQL。因為SESSION_CACHED_CURSORS是針對每個SESSION的，對於擁有幾百，甚至上千個SESSION的OLTP系統，設定SESSION_CACHED_CURSORS的時候要十分注意，設定大的SESSION_CACHED_CURSORS參數，需要比較大的共用池來支援，如果調整了這個參數後出現共用池空間不足的情況，調整共用池的大小或者減少SESSION_CACHED_CURSORS參數就是DBA應該進行的操作

cursor_sharing

定義CURSOR共用的模式，EXCAT(精確），FORCE(強制），SIMILAR(類似），如果採用預設的（精確），那麼系統不自動合并和共用CURSOR，只有書寫完全一致的CURSOR才能共用。如果設定為SIMILAR，那麼SQL PARSE的時候會做PEEKING，如果覺得是可以共用的，那麼就共用這個SQL，Oracle自動會將非綁定變數轉換為綁定變數。要注意的是，如果某個WHERE條件裡的欄位存在柱狀圖，那麼PEEKING過程會認為這個SQL的共用是不安全的，那麼將不共用這個SQL,此時這個CURSOR會產生一個子CURSOR，形成一個新的版本。這種情況下，只有非綁定變數的值是相同的，PARSER才認為共用是安全的，不產生新的VERSION。如果設定為FORCE，和SIMILAR類似，會將非綁定變數轉為綁定變數，和SIMILAR不同的是，PARSER強行認為共用是安全的，因此不會理會柱狀圖的資訊，直接共用該CURSOR。

CURSOR_SHARING的設定，最佳建議是用精確，在開發過程中，該用綁定變數的地方用綁定變數，不該用的地方不用（什麼時候不該用呢？），實在不行，用SIMILAR，但是使用非預設值的情況，需要尋找是否存在BUG,儘早打補丁，另外要測試應用，某些SQL在某些版本使用綁定變數的情況下會出錯（不是BUG），FORCE的BUG比較多，更要做好測試。

SQL語句分析分為軟分析和硬分析兩種。減少軟分析和硬分析，特別是減少硬分析，對於降低CPU的使用率有著十分關鍵的作用
SQL執行的時候，如果某個語句已經被緩衝了，那麼這個SQL就不需要進行分析，可以直接執行，因此保證SQL能夠在緩衝區中長
時間存在將可以減少SQL分析的發生。有2個參數可以控制SQL在SESSION緩衝池中的時間長短：OPEN_CURSORS和SESSION_CACHED_CURSORS。

那怎樣來調整這兩個參數呢？檢查目前SESSION_CACHED_CURSORS和OPEN_CURSORS的使用率情況

select 'session_cached_cursors' parameter,
       lpad(value, 5) value,
       decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%') usage
  from (select max(s.value) used
          from v$statname n, v$sesstat s
         where n.name = 'session cursor cache count'
           and s.statistic# = n.statistic#),
       (select value from v$parameter where name = 'session_cached_cursors')
union all
select 'open_cursors' parameter,
       lpad(value, 5) value,
       to_char(100 * used / value, '990') || '%' usage
  from (select max(sum(s.value)) used
          from v$statname n, v$sesstat s
         where n.name in
               ('opened cursors current', 'session cursor cache count')
           and s.statistic# = n.statistic#
         group by s.sid),
       (select value from v$parameter where name = 'open_cursors');

查看系統級cursor的命中率，軟分析和硬分析的比率

select
  to_char(100 * sess / calls, '9999990.00') || '%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
  to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
  ( select value calls from v$sysstat where name = 'parse count (total)' ),
  ( select value hard  from v$sysstat where name = 'parse count (hard)' ),
 ( select value sess  from v$sysstat where name = 'session cursor cache hits' );

如果返回SESSION_CACHED_CURSORS緩衝區的使用率是100％，那麼說明SESSION_CACHED_CURSORS參數還不夠大，如果共用池的大小足夠，可以調整該參數，直到使用率低於100％為止。

對於沒有使用綁定變數的系統，如果CURSOR_SHARING設定為EXACT的時候，如果設定SESSION_CACHED_CURSORS的時候要十分注意，由於應用原因，CURSOR的重用率十分低，如果設定過高的SESSION_CACHED_CURSORS，會導致共用池空間被大量佔用，在系統負載較高的時候會出現共用池的效能問題。

B。保留大型物件

載入大型物件是造成共用池片段的主要原因；由於大量的小型對象需要從共用池釋放以騰出空間，會影響回應時間
為了避免這樣情況發生，我們就把大型的，經常使用的對象keep在共用池中，哪些對象需要keep呢？

1）經常用到的大型物件，如standard等程式包，使用共用記憶體超過閥值的對象
2）經常在常用表中執行的觸發器
3）序列，因為當序列從共用池中釋放時，序號就丟失了

使用命令 alter system flush shared_pool命令重新整理共用池，但不重新整理保留對象

例如用下面的sql查出長度大於500個字元，共用記憶體大於10000個位元組的對象

select *
  from v$db_object_cache oc
 where length(oc.NAME) > 500
   and oc.TYPE in ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
   and oc.KEPT='NO'
   and oc.SHARABLE_MEM>10000

查看長度超過500字元,共用記憶體大於20000個位元組的匿名pl/sql

select *
  from v$sqlarea sq
 where sq.COMMAND_TYPE = 47
   and length(sq.SQL_TEXT) > 500
   and sq.SHARABLE_MEM>20000

把這些對象要keep在共用池中要用dbms_shared_pool.keep,系統預設是沒有安裝這個包的，需要運行dbmspool.sql這個指令碼

SYS@skatedb>@/home/oracle/10.2.0/db_1/rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

View created.

Package body created.

SYS@skatedb>

SQL> desc dbms_shared_pool
Element                   Type     
------------------------- ---------
SIZES                     PROCEDURE
KEEP                      PROCEDURE
UNKEEP                    PROCEDURE
ABORTED_REQUEST_THRESHOLD PROCEDURE

SQL> desc dbms_shared_pool.keep
Parameter Type     Mode Default?
--------- -------- ---- --------
NAME      VARCHAR2 IN           
FLAG      CHAR     IN   Y   

   

SQL> desc dbms_shared_pool.unkeep
Parameter Type     Mode Default?
--------- -------- ---- --------
NAME      VARCHAR2 IN           
FLAG      CHAR     IN   Y   

   

ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER)：這個方法可以設定一個界限，保證如果要進入SHARED POOL的對象太大，那麼可以設定一個閥值，超過這個閥值的直接報錯，而不是經過LRU尋找和記憶體交換之後發現SHARED POOL不夠了再報錯，可以防
止超大對象過度佔用SHARED POOL空間。

UNKEEP就是KEEP的反操作

SIZES (minsize NUMBER)：這個是列出SHARED POOL中所有大於minsize的對象，對於尋找SHARED POOL中大對象並設定合理
的ABORTED_REQUEST_THRESHOLD很有用。

說明 flag：

 Value        Kind of Object to keep
   --        -----        ----------------------
   --        P          package/procedure/function
   --        Q          sequence
   --        R          trigger
   --        T          type
   --        JS         java source
   --        JC         java class
   --        JR         java resource
   --        JD         java shared data
   --        C          cursor

如果這個flag是空，那麼他的預設值是'P'

保留package

sql> exec dbms_shared_pool.keep('package_name','P');

保留squence（避免sequence跳號）

sql> exec dbms_shared_pool.keep('sequence_name','Q');

保留匿名塊

SQL> select address,hash_value
  2    from v$sqlarea sq
  3   where sq.COMMAND_TYPE = 47
  4     and length(sq.SQL_TEXT) > 500
  5     and sq.SHARABLE_MEM>20000
  6  ;

ADDRESS          HASH_VALUE
---------------- ----------
00000000A78655E8 1599878706

sql> exec dbms_shared_pool.keep('address','hash_value','C');

注意：查看47是什麼命令
SQL> select * from audit_actions  where action=47;

    ACTION NAME
---------- ----------------------------
        47 PL/SQL EXECUTE

eg：

查看需要keep的匿名塊

SQL> select address,hash_value
  2    from v$sqlarea sq
  3   where sq.COMMAND_TYPE = 47
  4     and length(sq.SQL_TEXT) > 500
  5     and sq.SHARABLE_MEM>20000
  6  ;

ADDRESS          HASH_VALUE
---------------- ----------
000000008E8532A8   97348712

1 rows selected

確認當前匿名塊是否被keep

SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE
  2    from v$db_object_cache oc,
  3         v$sqlarea sq
  4   where sq.SQL_TEXT=oc.NAME
  5   and   sq.HASH_VALUE='97348712'
  6  ;

KEPT ADDRESS          HASH_VALUE
---- ---------------- ----------
NO  000000008E8532A8   97348712

SQL>

keep住匿名塊

SQL> exec dbms_shared_pool.keep('000000008E8532A8,97348712');

begin dbms_shared_pool.keep('000000008E8532A8,97348712'); end;

ORA-01426: 數字溢出
ORA-06512: 在 "SYS.DBMS_UTILITY", line 114
ORA-06512: 在 "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: 在 "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: 在 line 1

SQL> exec dbms_shared_pool.keep('000000008E8532A8,97348712','C');

PL/SQL procedure successfully completed

SQL>

檢查是否被keep住

SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE
  2    from v$db_object_cache oc,
  3         v$sqlarea sq
  4   where sq.SQL_TEXT=oc.NAME
  5   and   sq.HASH_VALUE='97348712'
  6  ;

KEPT ADDRESS          HASH_VALUE
---- ---------------- ----------
YES  000000008E8532A8   97348712

SQL>

取消對象的keep

SQL> exec dbms_shared_pool.unkeep('000000008E8532A8,97348712','C');

PL/SQL procedure successfully completed

SQL>

確認當前匿名塊是否被取消keep
SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE
  2    from v$db_object_cache oc,
  3         v$sqlarea sq
  4   where sq.SQL_TEXT=oc.NAME
  5   and   sq.HASH_VALUE='97348712'
  6  ;

KEPT ADDRESS          HASH_VALUE
---- ---------------- ----------
NO   000000008E8532A8   97348712

SQL>

剩下keep住package和sequnce就都類似了

共用池片段問題
綁定變數問題

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wyzxg/archive/2010/03/16/5384762.aspx