oracle效能優化-共享池調整

foxmile發表於2008-06-06
Oracle優化
    一.簡介:資料庫調整中最為重要的一部分是重寫執行效率差的SQL程式碼,重寫之後的SQL程式碼在執行效率方面可能會與之前的產生極大的差別！
       但是當遇到重寫之後效能還是未能突破瓶頸或者你是使用者(無法訪問SQL程式碼)的時候,可以通過調整ORACLE的共享記憶體結構SGA(System Global Area),最大限度地提高效能！
       SGA中比較重要的元件就是Shared Pool(共享池),它的作用就是快取記憶體SQL語句!共享池由一個最近最少使用(LRU,Least Recently Used)演算法來管理!
       共享池的好處:
1.select cust_id,cust_name from col_cust
2.上列語句的演算法被轉換成ASCII碼,然後通過一個雜湊演算法產生一個單獨的雜湊值~~接著Process會檢視該雜湊值在Shared Pool中是否存在,
如果存在,就執行快取記憶體中語句
3.如果不存在,就必須對該語句進行語法分析,這些分析步驟會產生額外的系統開銷,該操作是高代價的!
4.查詢到匹配的SQL叫做一次高速緩衝區命中(Cache Hit)
5.反之叫做高速緩衝區脫靶(Cache Miss)
6.注意是區分大小寫的
  SELECT CUST_ID,CUST_NAME FROM COL_CUST 
 
      二.共享池有三個元件組成,Library Cache(庫快取記憶體區),Data Dictionary Cache(資料目錄快取記憶體區),User Global Area(使用者全域性區)
      1.Library Cache(庫快取記憶體區)是用來快取SQL語句的場所.可以通過下面這句話對動態檢視進行查詢,檢查Library Cache的內容
 select p.username,l.sql_text,
 lpad(' ' ,4*(LEVEL-2)) || operation || ' ' || options || ' ' || object_name as "Execution Plan"
 from (
 select s.username,p.address,p.hash_value,p.operation,p.options,p.object_name,p.id,p.parent_id
 from v$sql_plan p,v$session s
 where (p.address=s.sql_address and p.hash_value=s.sql_hash_value) and s.username='citictest'
 ) p,v$sql l
 where(l.address=p.address and l.hash_value=p.hash_value)
 start with id=0
 connect by prior id = parent_id
      2.Data Dictionary Cache(資料目錄快取記憶體區):資料目錄是用來檢查SQL語句所引用的那些表是否已經存在,列名和資料型別是否也正確! Library Cache和Data Dictionary Cache使用互相獨立的LRU機制,好處是後續使用者釋出的語句與先前使用者所釋出的語句類似但不一致,雖然在Library Cache中無法找到匹配的,但是在資料目錄中會存在,也會有效能上的提高。

      三.(1)測量Library Cache(庫快取記憶體區)的效能
       select namespace,gethitratio,pinhitratio,reloads,invalidations
       from v$librarycache
       where namespace in ('SQL_AREA','TABLE/PROCEDURE','BODY','TRIGGER');
主要看gethitratio,pinhitratio>90%說明調整充分
       select sum(reloads)/sum(pins) "RELOAD　RATIO"
       from v$librarycache
當<1%意味著不是經常重新語法分析以前被裝載到Library Cache的語句
 
       (2)測量Data Dictionary Cache(資料目錄快取記憶體區)的效能
       select 1 - (sum(getmisses)/sum(gets)) "DATA DICTIONARY HIT RATIO"
       from v$rowcache;
當>85%時說明調整充分
    四.通過改進Library Cache和Data Dictionary Cache來提高shared pool效能
(1).得到當前shared pool的大小
  select pool,sum(bytes) "SIZE" from v$sgastat where pool='shared pool' group by pool;
(2).得到推薦的shared pool大小
  set echo off
  set feedback off
  set serveroutput on
  declare
  v_total_plsql_mem number := 0;
  v_total_sql_mem   number := 0;
  v_total_sharable_mem number := 0;
  begin
       select sum(sharable_mem) into v_total_plsql_mem from v$db_object_cache;
      
       select sum(sharable_mem) into v_total_sql_mem from v$sqlarea where executions > 10;
      
       v_total_sharable_mem := v_total_plsql_mem + v_total_sql_mem;
      
       Dbms_Output.put_line('Estimated required shared pool size is:' || to_char(v_total_sharable_mem,'fm9,999,999,999,999') || ' bytes');
  end;
  /
(3).動態加大shared pool大小
alert system set shared_pool_size = 200M;
*大小不能超過SGA_MAX_SIZE的值
(4).初始SGA大小的計算
(TSGA)SGA總的大小=伺服器實體記憶體*0.55   (1G以上實體記憶體的話可以相應60%-75%)
(TSGAI)每個例項的總SGA大小=TSGA/oracle上例項的個數
shared pool的總記憶體=TSGAI*0.45
(5).上述四點是其實完成的是同一個做法，就是使Shared Pool更大
(6).可以將PL/SQL程式包裝入Shared Pool Reserved Area(共享池保留區)
Shared_Pool_Reserved_Size用來設定這一區域的大小，預設是5%，這是不夠的
select owner,name,sharable_mem from v$db_object_cache
where type in('PACKAGE','PACKAGE BODY') order by sharable_mem desc;
上述這句語句可以檢視當前快取區中的PL/SQL程式包的名稱和大小，當發現大小 > Shared_Pool_Reserved_Size時說明保留區的大小不夠，你需要增加Shared_Pool_Reserved_Size的值
(7).把重要的PL/SQL程式碼保持在記憶體中
你可以把常用的PROCEDURE銷定(Pinning)在Shared_Pool_Reserved_Size中
做法如下:
          a)sys使用者登入
          b)執行@%ORACLE_HOME%/rdbms\admin\dbmspool.sql
          c)SQL>execute DBMS_SHARED_POOL.KEEP('PROCEDURENAME') (銷定,必須用sys完成)
          d)找到銷定的物件select owner,name,type from v$db_object_cache where kept='YES';
          e)銷定對單獨的SQL語句無法操作,儘可能的把大語句做成PROCEDURE，可以用一下語句尋找出比較大的語句
select substr(sql_text,1,45),length(sql_text) "STMT_SIZE" from v$sqlarea where command_type=47 order by length(sql_text) desc;
          f)可以編寫一個指令碼，當例項啟動之後執行，把所有需要銷定的語句執行一下
          h)只有使用UNKEEP或者例項關閉時才會取消銷定
(8).其他的一些調整Library Cache引數
a)open_cursors :預設50
b)cursor_space_for_time 預設false
c)session_cached_cursors 預設0(無遊標快取記憶體)
d)cursor_sharing 預設EXACT --2條SQL語句必須完全匹配才能共享shared pool中所快取的已分析程式碼.
                 SIMILAR --允許2條僅在字面上不同的SQL語句共享shared pool中所快取的已分析程式碼.
例如:select cust_id from col_cust where cust_name = 'wang'
     select cust_id from col_cust where cust_name = 'huang'
上述兩句在SIMILAR模式中是相等，可以使用快取的已分析程式碼.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/79499/viewspace-341794/，如需轉載，請註明出處，否則將追究法律責任。

相關文章