Oracle 共享池操作

(1) 大小由shared_pool_size引數定義

(2) 遊標是否共享：SQL> select l.NAMESPACE, l.GETHITRATIO from v$librarycache l where l.NAMESPACE = 'SQL AREA';

GETHITRATIO = GETHITS / GETS 這個比率要高於90%，否則，應用程式程式碼的效率可能還有提高的餘地

(3) 查明使用者正在執行那些語句：select s.SQL_TEXT, s.USERS_EXECUTING, s.EXECUTIONS, s.LOADS from v$sqlarea s;

select * from v$sqltext sq where sq.SQL_TEXT like 'select * from scott.emp where empno = %';

(4) 庫快取記憶體重新載入理想情況下為0，永不大於連線數的1%；

select sum(b.PINS) "Executions", sum(b.RELOADS) "Cache Misses", sum(b.RELOADS) / sum(b.PINS) from v$librarycache b;

原則：如果重新載入數與連線數的比率大於1%，請增大shared_pool_size引數

(5) 失效—》此列代表名稱空間的物件被標記為無效而導致重新載入的次數

SQL> select b.NAMESPACE, b.PINS, b.RELOADS, b.INVALIDATIONS from v$librarycache b ;

SQL> analyze table hr.departments compute statistics;

(6) 計算所用的共享記憶體空間

-儲存物件：SQL> select sum(d.SHARABLE_MEM) from v$db_object_cache d where d.TYPE = 'PACKAGE' or d.TYPE = 'PACKAGE BODY' or d.TYPE = 'FUNCTION' or d.TYPE = 'PROCEDURE';

-sql語句：SQL> select sum(s.SHARABLE_MEM) from v$sqlarea s where s.EXECUTIONS > 5;

-應該在共享池中為每位使用者的每個開啟的遊標留出250個位元組可用以下的查詢在高峰期時測試SQL> select sum(250 * s.USERS_OPENING) from v$sqlarea s;

-可以通過為測試使用者選擇開啟的遊標數來測定可共享的記憶體空間；在將所得的值乘上使用者總數：SQL> select 250 * 2 bytes_per_user from v$sesstat se, v$statname n where

se.STATISTIC# = n.STATISTIC# and n.NAME = 'opened cursors current' and se.SID = 137;

(7) 大型記憶體需求

-{滿足對大型連續記憶體的需求；在共享池中保留不會碎片化的記憶體}shared_pool_reserved_size：控制為大型分配而保留的shared_pool_size的大小(將它的初始值設定為shared_pool_size的10%)；如果shared_pool_reserved_size的值大於shared_pool_size值的一半，則Oracle伺服器就會報告一條錯誤的資訊；

-SQL> select * from v$shared_pool_reserved; --此檢視有助於優化保留池和共享池內的空間

(9) 保留大型物件

-查詢未保留在庫快取記憶體中的那些PL/SQL物件:

SQL> select * from v$db_object_cache ca where SHARABLE_MEM > 10000 and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION' or type = 'PROCEDURE') AND KEPT = 'NO'

-連線庫快取記憶體中的大型程式包

SQL> exec dbms_shared_pool.keep('package_name');

--下面命令重新整理共享池並不重新整理保留物件

SQL> alter system flush shared_pool;

-保留物件：dbms_shared_pool程式包和keep過程

可執行dbmspool.sql；prvtpool.plb指令碼將會在前一個指令碼執行結束時自動執行；

用unkeep過程將被固定的物件從共享池中刪除；

(10)        匿名PL/SQL塊

-查詢匿名PL/SQL塊，並將它們轉換為呼叫打包函式的小型匿名PL/SQL塊：SQL> select a.SQL_TEXT from v$sqlarea a where a.COMMAND_TYPE = 47 and length(a.SQL_TEXT) > 500;

-排除大型匿名PL/SQL塊的兩個解決辦法：

<1>轉換為小的匿名PL/SQL塊，這些小的塊可以呼叫打包函式

<2>不能轉換為程式包，可以在v$sqlarea檢視中識別；並被標記為KEPT；

SQL> declare x number;

begin x := 5;

end;

改變為

SQL> declare/*+ keep_me*/ x number;

begin x := 5;

end;

SQL> select s.ADDRESS, s.HASH_VALUE from v$sqlarea s where s.COMMAND_TYPE = 47 and s.SQL_TEXT like '%keep_me%'; --一定要和上面的大小寫一致

ADDRESS  HASH_VALUE

-------- ------------------------------------

2CB3ED04   3785812334

{keep過程在匿名PL/SQL塊中執行，這個塊根據從上一條語句中檢索到的地址和hash_value來識別}

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

SQL> exec dbms_shared_pool.keep('3703DFCC, 3561892341', 'w');

(12)        優化資料字典快取記憶體

SQL> select r.PARAMETER, r.GETS, r.GETMISSES from v$rowcache r;

SQL> select sum(gets) / sum(getmisses) from v$rowcache;

(13)        調整使用者全域性區的大小

<1>測試連線使用的uga空間：

SQL> select sum(m.VALUE) || ' bytes' "Total session memory" from v$mystat m, v$statname s where s.NAME = 'session uga memory' and m.STATISTIC# = s.STATISTIC#;

<2>所有MTS使用者使用的UGA空間

SQL> select sum(t.VALUE) || ' bytes' "Total session memory" from v$sesstat t, v$statname s where s.NAME = 'session uga memory' and t.STATISTIC# = s.STATISTIC#;

<3>所有MTS使用者使用的最大uga空間

SQL> select sum(t.VALUE) || ' bytes' "Total max memory" from v$sesstat t, v$statname s where s.NAME = 'session uga memory max' and t.STATISTIC# = s.STATISTIC#;

(14)        大共享池

SQL>select * from v$sgastat s where s.POOL = 'large pool';

(15)        命中率

--庫快取命中率 最好大於98%

SQL> select sum(b.PINS) / (sum(b.PINS) + sum(b.RELOADS)) * 100 "Hit Ratio" from v$librarycache b;

--資料字典快取命中率 最好大於98%

SQL> select (1 - (sum(r.GETMISSES)/sum(r.GETS))) * 100 from v$rowcache r;

-如果自由記憶體總是在共享池中的可用，然後增加池的大小提供很少或根本沒有好處。然而，僅僅因為共享池已滿並不一定意味著有問題。這可能是一個良好的配置系統的指標

SQL> SELECT * FROM V$SGASTAT WHERE NAME = 'free memory'  AND POOL = 'shared pool';

(17)        Dbms_shared_pool.keep例子

使用dbms_shared_pool包將物件pin到記憶體中

<1>執行dbmspool指令碼建立dbms_shared_pool包，預設不存在；

SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmspool.sql

SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtpool.plb

<2>執行許可權授予i2_db使用者

SQL> grant execute on dbms_shared_pool to i2_db;

SQL> conn i2_db/i2_db

<3>建立一個測試儲存過程

SQL>create or replace procedure p_test

as t date;

begin

select sysdate into t from dual;

dbms_output.put_line(t);

end p_test;

<4>將p_test儲存過程pin到記憶體中

SQL> exec sys.dbms_shared_pool.keep('p_test');

<5>檢視是否pin成功{dba}

SQL> select owner, name, type, kept from v$db_object_cache where name = 'P_TEST';

<6>檢視sys.dbms_shared_pool定義

SQL> desc sys.dbms_shared_pool

FLAG的值如下：

value      king of object to keep

----------------------------------

C            cursor

JC           java class

JD           java shared data

JR           java resource

JS           java source

P            procedure/package/function{預設}

Q            sequence

R            trigger

T            type

keep sql需要知道sql的addr和hash_value

SQL> select * from dual;

SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select * from dual';

exec dbms_shared_pool.keep('689667FC,942515969','W');

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