原文:http://space.itpub.net/79499/viewspace-341794
一.簡介:數(shù)據(jù)庫調(diào)整中最為重要的一部分是重寫運(yùn)行效率差的SQL代碼,重寫之后的SQL代碼在運(yùn)行效率方面可能會(huì)與之前的產(chǎn)生極大的差別！
但是當(dāng)遇到重寫之后性能還是未能突破瓶頸或者你是用戶(無法訪問SQL代碼)的時(shí)候,可以通過調(diào)整ORACLE的共享內(nèi)存結(jié)構(gòu)SGA(System Global Area),最大限度地提高性能！
SGA中比較重要的組件就是Shared Pool(共享池),它的作用就是高速緩存SQL語句!共享池由一個(gè)最近最少使用(LRU,Least Recently Used)算法來管理!
共享池的好處:
1.select cust_id,cust_name from col_cust
2.上列語句的算法被轉(zhuǎn)換成ASCII碼,然后通過一個(gè)散列算法產(chǎn)生一個(gè)單獨(dú)的散列值~~接著Process會(huì)查看該散列值在Shared Pool中是否存在,
如果存在,就執(zhí)行高速緩存中語句
3.如果不存在,就必須對(duì)該語句進(jìn)行語法分析,這些分析步驟會(huì)產(chǎn)生額外的系統(tǒng)開銷,該操作是高代價(jià)的!
4.查找到匹配的SQL叫做一次高速緩沖區(qū)命中(Cache Hit)
5.反之叫做高速緩沖區(qū)脫靶(Cache Miss)
6.注意是區(qū)分大小寫的
SELECT CUST_ID,CUST_NAME FROM COL_CUST
二.共享池有三個(gè)組件組成,Library Cache(庫高速緩存區(qū)),Data Dictionary Cache(數(shù)據(jù)目錄高速緩存區(qū)),User Global Area(用戶全局區(qū))
1.Library Cache(庫高速緩存區(qū))是用來緩存SQL語句的場(chǎng)所.可以通過下面這句話對(duì)動(dòng)態(tài)視圖進(jìn)行查詢,檢查Library Cache的內(nèi)容
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(數(shù)據(jù)目錄高速緩存區(qū)):數(shù)據(jù)目錄是用來檢查SQL語句所引用的那些表是否已經(jīng)存在,列名和數(shù)據(jù)類型是否也正確! Library Cache和Data Dictionary Cache使用互相獨(dú)立的LRU機(jī)制,好處是后續(xù)用戶發(fā)布的語句與先前用戶所發(fā)布的語句類似但不一致,雖然在Library Cache中無法找到匹配的,但是在數(shù)據(jù)目錄中會(huì)存在,也會(huì)有性能上的提高。
三.(1)測(cè)量Library Cache(庫高速緩存區(qū))的性能
select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$librarycache
where namespace in ('SQL_AREA','TABLE/PROCEDURE','BODY','TRIGGER');
主要看gethitratio,pinhitratio>90%說明調(diào)整充分
select sum(reloads)/sum(pins) "RELOAD RATIO"
from v$librarycache
當(dāng)<1%意味著不是經(jīng)常重新語法分析以前被裝載到Library Cache的語句
(2)測(cè)量Data Dictionary Cache(數(shù)據(jù)目錄高速緩存區(qū))的性能
select 1 - (sum(getmisses)/sum(gets)) "DATA DICTIONARY HIT RATIO"
from v$rowcache;
當(dāng)>85%時(shí)說明調(diào)整充分
四.通過改進(jìn)Library Cache和Data Dictionary Cache來提高shared pool性能
(1).得到當(dāng)前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).動(dòng)態(tài)加大shared pool大小
alert system set shared_pool_size = 200M;
*大小不能超過SGA_MAX_SIZE的值
(4).初始SGA大小的計(jì)算
(TSGA)SGA總的大小=服務(wù)器物理內(nèi)存*0.55 (1G以上物理內(nèi)存的話可以相應(yīng)60%-75%)
(TSGAI)每個(gè)實(shí)例的總SGA大小=TSGA/oracle上實(shí)例的個(gè)數(shù)
shared pool的總內(nèi)存=TSGAI*0.45
(5).上述四點(diǎn)是其實(shí)完成的是同一個(gè)做法，就是使Shared Pool更大
(6).可以將PL/SQL程序包裝入Shared Pool Reserved Area(共享池保留區(qū))
Shared_Pool_Reserved_Size用來設(shè)置這一區(qū)域的大小，默認(rèn)是5%，這是不夠的
select owner,name,sharable_mem from v$db_object_cache
where type in('PACKAGE','PACKAGE BODY') order by sharable_mem desc;
上述這句語句可以查看當(dāng)前緩存區(qū)中的PL/SQL程序包的名稱和大小，當(dāng)發(fā)現(xiàn)大小> Shared_Pool_Reserved_Size時(shí)說明保留區(qū)的大小不夠，你需要增加Shared_Pool_Reserved_Size的值
(7).把重要的PL/SQL代碼保持在內(nèi)存中
你可以把常用的PROCEDURE銷定(Pinning)在Shared_Pool_Reserved_Size中
做法如下:
a)sys用戶登錄
b)運(yùn)行@%ORACLE_HOME%/rdbms\admin\dbmspool.sql
c)SQL>execute DBMS_SHARED_POOL.KEEP('PROCEDURENAME') (銷定,必須用sys完成)
d)找到銷定的對(duì)象select owner,name,type from v$db_object_cache where kept='YES';
e)銷定對(duì)單獨(dú)的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)可以編寫一個(gè)腳本，當(dāng)實(shí)例啟動(dòng)之后運(yùn)行，把所有需要銷定的語句執(zhí)行一下
h)只有使用UNKEEP或者實(shí)例關(guān)閉時(shí)才會(huì)取消銷定
(8).其他的一些調(diào)整Library Cache參數(shù)
a)open_cursors :默認(rèn)50
b)cursor_space_for_time默認(rèn)false
c)session_cached_cursors默認(rèn)0(無游標(biāo)高速緩存)
d)cursor_sharing默認(rèn)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模式中是相等，可以使用緩存的已分析代碼.