原文:http://space.itpub.net/79499/viewspace-341794
一.簡介:數據庫調整中最為重要的一部分是重寫運行效率差的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模式中是相等，可以使用緩存的已分析代碼.