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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25533574/viewspace-693626/