oracle优化------缓存对象与数据

jinghuainfo

浏览: 1525113 次
性别:
来自: 北京

最近访客更多访客>>

devcang

u012363178

hslb1985

chienchia

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1993)

社区版块

存档分类

2012-02 ( 11)
2012-01 ( 15)
2011-12 ( 16)
更多存档...

author：skate 
time：2010/03/16
<hr>
当系统出现性能瓶颈时，尽量较少不必要的资源消耗，最后就是平衡cpu，内存，io，network等资源，使数据库 
可以稳定的运行。
oracle数据库优化的根本是
 
1.尽量减少资源消耗，例如优化sql，减少sql本身的资源消耗 
2.如果无法进一步减少资源的消耗，那就让数据尽量靠近cpu，也就是把数据从硬盘转移到内存（内存的读写速度快） 
或者换更快的磁盘
本文就简单总结下如何缓存数据和数据库对象（也就是把数据移向内存，提高内存的命中率，以提高整体io速度）
1.缓存数据 
2.缓存数据对象的定义，例如package，procedure，pl/sql和sql（也就是cursor）等


上面说的这两种数据就存在oracle最重要的两个部件中share pool和buffer pool中，提高这两个pool的命中率也提高了 
io速度，而io又是当今技术发展最慢，系统的最大的瓶颈。
1. 缓存数据




 
这里说的oracle数据是占大量存储空间的，不是存在数据库字典里的数据；oracle的数据的类型一般为：
 
SQL> select se.segment_type from dba_segments se group by se.segment_type;
SEGMENT_TYPE 
------------------ 
LOBINDEX 
INDEX PARTITION 
TABLE PARTITION 
NESTED TABLE 
ROLLBACK 
LOB PARTITION 
LOBSEGMENT 
INDEX 
TABLE 
CLUSTER 
TYPE2 UNDO
11 rows selected
SQL> 
在大部分时候，把这些数据放到内存里，会很大的提升系统的性能
buffer pool分为三个子pool，这三个pool都主要使用LRU算法管理的
default buffer pool：默认所有的数据块都存在这，并遵循本pool的LRU算法
 
keep buffer pool：如果指定数据块缓存到keep区的，数据块就不太可能因为执行其他一些操作被其他数据块交换出，即使较长时间没使用了，只遵循本pool的LRU
 
recycle buffer pool：设置recycle是因为有时会有一些大的又教少使用的表的操作，如果不设置单独的缓存区，那么缺省的缓存区中的数据块就被这些偶尔使用的数据换出，它的空间比较小，所以说一般使用完就释放掉了，它也只遵守本pool的LRU算法
 以table为例：


 
修改table的缓存空间
alter table a_user storage(buffer_pool keep) cache/nocache;
---把表a_user缓存到keep buffer pool中最热端/把表a_user从keep buffer pool立刻释放出去
alter table a_user cache/nocache
---把表a_user缓存到default buffer pool中最热端/把表a_user从default buffer pool立刻释放出去
eg：

 1)

SQL> alter table a_user storage(buffer_pool keep) cache;
Table altered
2)
SQL> select t.table_name,t.cache,t.buffer_pool from user_tables t where t.table_name=upper('a_user');
TABLE_NAME CACHE BUFFER_POOL 
------------------------------ -------------------- ----------- 
a_user Y KEEP
说明：
 
user_tables.cache:这个表一旦被读入buffer cache，就会放在链表的热端~ 尽量不被挤出buffer cache 
user_tables.buffer_pool:把这个表放入特殊的buffer cache中，这些特殊的buffer cache是独立的
而视图v$db_object_cache.kept:告知是否对象常驻shared pool(yes/no)，有赖于这个对象是否已经利用PL/SQL 过程
DBMS_SHARED_POOL.KEEP“保持”（永久固定在内存中）
eg:


 
SQL> Select oc.NAME,oc.TYPE,oc.KEPT from v$db_object_cache oc where oc.TYPE='TABLE' AND OC.OWNER='HPO';
NAME TYPE KEPT 
-------------------------------------------------------------------------------- ---------------------------- ---- 
A_USER TABLE YES
这个v$db_object_cache视图提供对象在library cache(shared pool)中对象统计，提供比v$librarycache更多的细节，并且常用于找出shared pool中的活动对象。

 
所以你没有使用过对象时，是不存在这个视图里的，使用时用了，才会在这个视图里出现
可以参看三思的动态性能视图介绍：
<a href="http://space.itpub.net/7607759/viewspace-22241">http://space.itpub.net/7607759/viewspace-22241
</a>

 
例如修改索引的buffer pool
 
alter index IDX_ORG_TYPE storage(buffer_pool keep) cache;
分区表和分区索引好像不能把每个分区放在不同的buffer pool中，反正我测试通过

2.缓存数据对象的定义，例如package，procedure，pl/sql和sql（也就是cursor）等


上面介绍了把数据尽量缓存在buffer pool中，提高数据在内存的命中率，避免从磁盘读写数据，间接提高系统io能力； 
buffer pool缓存的数据是用户最终的目标数据，而把这些用户最终目标数据要传达给用户，就需要oracle用另外一些 
动作来完成，而这些动作主要是在share pool中完成的，大概功能有：缓存语句文本，分析代码，执行计划，数据字典 
中的表和列的权限定义等；share pool主要也用LRU算法，所以怎样尽量缓存这些数据就是下面要说的
oracle分为sql引擎和pl/sql引擎，分别完成sql和pl/sql的解析等工作，而这里解析又是很耗资源的，所以就要想办法 
尽量少解析，使代码重用以提高效率
A。代码的重用

确定是否需要对语句进行（硬）解析时，是先比较语句的哈希值，下面的两种方法有助于获得相同的哈希值，从而可以实现重用代码，提高命中率： 
1）开发组的所有成员都使用相同的编码规范（包括大小写，空格，换行等）；
2）使用绑定变量（提高命中率的同时可能会产生不够好的执行计划，因为优化器不知道变量的确定值，在有栏位的柱状图统计数据时也不能够利用）。
调整相关初始化参数：

OPEN_CURSOR

 
这个参数指定每个用户会话能打开的游标最大数量；增大这个值可以减少重新解析会话曾打开的语句的机会，提高命中率，但需要更大的共享池空间。要确保该值足够，增加该值不会对内存造成太大的影响
 cursor_space_for_time

 
缺省是FALSE，如果设置为TRUE，那么SHARED SQL
AREA当CURSOR打开的时候，是PIN在共享池里的，不允许被换出（AGEOUT），这样提高了SQL的执行效率，另外PGA中的CURSOR的私
有内存部分，执行完SQL后也不关闭，下次执行的时候可以直接使用，节省了内存分配和释放的时间。对于同一个SQL反复被执行的情况，这种设置有助于提高
SQL执行的效率。但是这个参数设置会增加共享池的使用。如果共享池出现不足，或者碎片很严重的情况，使用这个参数会加剧问题，所以，一般在共享池足够
大的情况下才能考虑设为true，设为true时可以减少重解析，提高命中率，加快游标的执行（空间换时间）。 
这个参数一般情况不需要打开，一般情况下打开对于系统性能的提升不会很大，对于parse很频繁，而且SQL执行很频繁，共享池碎片较为严重的情况，建议不要使用。当然特殊情况有特殊的用途，否则这个参数也没必要存在了。
 session_cached_cursors

 
缺省是0，也就是不CACHE
CURSOR，如果设置了SESSION_CACHED_CURSORS,某个CURSOR被频繁调用，那么当第三次被调用的时候会被CACHE，一个被
CACHE的CURSOR下次再被调用的时候，可以省去PARSE的过程，提高SQL执行的效率，这些缓存也是用LRU算法来管理的。应该注意
SESSION_CACHED_CURSORS的值不能超过OPEN_CURSORS的值。在设置SESSION_CACHED_CURSORS参数之
前，首先要确定共享池的大小是否足以支持缓冲这些SQL。因为SESSION_CACHED_CURSORS是针对每个SESSION的，对于拥有几百，
甚至上千个SESSION的OLTP系统，设置SESSION_CACHED_CURSORS的时候要十分注意，设置大的
SESSION_CACHED_CURSORS参数，需要比较大的共享池来支持，如果调整了这个参数后出现共享池空间不足的情况，调整共享池的大小或者减
少SESSION_CACHED_CURSORS参数就是DBA应该进行的操作
cursor_sharing

 
定义CURSOR共享的模式，EXCAT(精
确），FORCE(强制），SIMILAR(类似），如果采用缺省的（精确），那么系统不自动合并和共享CURSOR，只有书写完全一致的CURSOR才
能共享。如果设置为SIMILAR，那么SQL
PARSE的时候会做PEEKING，如果觉得是可以共享的，那么就共享这个SQL，Oracle自动会将非绑定变量转换为绑定变量。要注意的是，如果某
个WHERE条件里的字段存在柱状图，那么PEEKING过程会认为这个SQL的共享是不安全的，那么将不共享这个SQL,此时这个CURSOR会产生一
个子CURSOR，形成一个新的版本。这种情况下，只有非绑定变量的值是相同的，PARSER才认为共享是安全的，不产生新的VERSION。如果设置为
FORCE，和SIMILAR类似，会将非绑定变量转为绑定变量，和SIMILAR不同的是，PARSER强行认为共享是安全的，因此不会理会柱状图的信
息，直接共享该CURSOR。
CURSOR_SHARING的设置，最佳建议是用精确，在开发过程中，该用绑定变量的地方用绑定变量，不该用的地方不用（什么时候不该用呢？），
实在不行，用SIMILAR，但是使用非缺省值的情况，需要查找是否存在BUG,尽早打补丁，另外要测试应用，某些SQL在某些版本使用绑定变量的情况下
会出错（不是BUG），FORCE的BUG比较多，更要做好测试。
 
SQL语句分析分为软分析和硬分析两种。减少软分析和硬分析，特别是减少硬分析，对于降低CPU的使用率有着十分关键的作用 
SQL执行的时候，如果某个语句已经被缓冲了，那么这个SQL就不需要进行分析，可以直接执行，因此保证SQL能够在缓冲区中长 
时间存在将可以减少SQL分析的发生。有2个参数可以控制SQL在SESSION缓冲池中的时间长短：OPEN_CURSORS和SESSION_CACHED_CURSORS。
 那怎样来调整这两个参数呢？检查目前SESSION_CACHED_CURSORS和OPEN_CURSORS的使用率情况

select 'session_cached_cursors' parameter, 
lpad(value, 5) value, 
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage 
from (select max(s.value) used 
from v$statname n, v$sesstat s 
where n.name = 'session cursor cache count' 
and s.statistic# = n.statistic#), 
(select value from v$parameter where name = 'session_cached_cursors') 
union all 
select 'open_cursors' parameter, 
lpad(value, 5) value, 
to_char(100 * used / value, '990') || '%' usage 
from (select max(sum(s.value)) used 
from v$statname n, v$sesstat s 
where n.name in 
('opened cursors current', 'session cursor cache count') 
and s.statistic# = n.statistic# 
group by s.sid), 
(select value from v$parameter where name = 'open_cursors');
查看系统级cursor的命中率，软分析和硬分析的比率

select 
to_char(100 * sess / calls, '9999990.00') || '%' cursor_cache_hits, 
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, 
to_char(100 * hard / calls, '999990.00') || '%' hard_parses 
from 
( select value calls from v$sysstat where name = 'parse count (total)' ), 
( select value hard from v$sysstat where name = 'parse count (hard)' ), 
( select value sess from v$sysstat where name = 'session cursor cache hits' );
如果返回SESSION_CACHED_CURSORS缓冲区的使用率是100％，那么说明SESSION_CACHED_CURSORS参数还不够大，如果共享池的大小足够，可以调整该参数，直到使用率低于100％为止。
对于没有使用绑定变量的系统，如果CURSOR_SHARING设置为EXACT的时候，如果设置SESSION_CACHED_CURSORS的
时候要十分注意，由于应用原因，CURSOR的重用率十分低，如果设置过高的SESSION_CACHED_CURSORS，会导致共享池空间被大量占
用，在系统负载较高的时候会出现共享池的性能问题。
B。保留大型对象


加载大型对象是造成共享池碎片的主要原因；由于大量的小型对象需要从共享池释放以腾出空间，会影响响应时间 
为了避免这样情况发生，我们就把大型的，经常使用的对象keep在共享池中，哪些对象需要keep呢？
 
1）经常用到的大型对象，如standard等程序包，使用共享内存超过阀值的对象 
2）经常在常用表中执行的触发器 
3）序列，因为当序列从共享池中释放时，序列号就丢失了
使用命令 alter system flush shared_pool命令刷新共享池，但不刷新保留对象
例如用下面的sql查出长度大于500个字符，共享内存大于10000个字节的对象

select * 
from v$db_object_cache oc 
where length(oc.NAME) > 500 
and oc.TYPE in ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY') 
and oc.KEPT='NO' 
and oc.SHARABLE_MEM>10000
 查看长度超过500字符,共享内存大于20000个字节的匿名pl/sql

select * 
from v$sqlarea sq 
where sq.COMMAND_TYPE = 47 
and length(sq.SQL_TEXT) > 500 
and sq.SHARABLE_MEM>20000
 
把这些对象要keep在共享池中要用dbms_shared_pool.keep,系统默认是没有安装这个包的，需要运行dbmspool.sql这个脚本
 <a href="mailto:SYS@skatedb%3E@/home/oracle/10.2.0/db_1/rdbms/admin/dbmspool.sql">SYS@skatedb>@/home/oracle/10.2.0/db_1/rdbms/admin/dbmspool.sql</a>

Package created.
 
Grant succeeded.
 
View created.
 
Package body created.
<a href="mailto:SYS@skatedb">SYS@skatedb</a>
>
 
SQL> desc dbms_shared_pool 
Element Type 
------------------------- --------- 
SIZES PROCEDURE 
KEEP PROCEDURE 
UNKEEP PROCEDURE 
ABORTED_REQUEST_THRESHOLD PROCEDURE 
SQL> desc dbms_shared_pool.keep 
Parameter Type Mode Default? 
--------- -------- ---- -------- 
NAME VARCHAR2 IN 
FLAG CHAR IN Y

SQL> desc dbms_shared_pool.unkeep 
Parameter Type Mode Default? 
--------- -------- ---- -------- 
NAME VARCHAR2 IN 
FLAG CHAR IN Y

ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER)：
这个方法可以设定一个界限，保证如果要进入SHARED POOL的对象太大，那么可以设置一个阀值，超过这个阀值的直接报错，而不是经过LRU查找和内存交换之后发现SHARED POOL不够了再报错，可以防 
止超大对象过度占用SHARED POOL空间。
UNKEEP
就是KEEP
的反操作
SIZES (minsize NUMBER)：
这个是列出SHARED POOL中所有大于minsize的对象，对于查找SHARED POOL中大对象并设置合理 
的ABORTED_REQUEST_THRESHOLD很有用。
说明 flag：

 
Value Kind of Object to keep 
-- ----- ---------------------- 
-- P package/procedure/function 
-- Q sequence 
-- R trigger 
-- T type 
-- JS java source 
-- JC java class 
-- JR java resource 
-- JD java shared data 
-- C cursor 
如果这个flag是空，那么他的默认值是'P'

保留package

 
sql> exec dbms_shared_pool.keep('package_name','P');
保留squence（避免sequence跳号）

 
sql> exec dbms_shared_pool.keep('sequence_name','Q');
保留匿名块

 
SQL> select address,hash_value 
2 from v$sqlarea sq 
3 where sq.COMMAND_TYPE = 47 
4 and length(sq.SQL_TEXT) > 500 
5 and sq.SHARABLE_MEM>20000 
6 ;
ADDRESS HASH_VALUE 
---------------- ---------- 
00000000A78655E8 1599878706
sql> exec dbms_shared_pool.keep('address','hash_value','C');
注意：查看47是什么命令 
SQL> select * from audit_actions where action=47;
 ACTION NAME 
---------- ---------------------------- 
47 PL/SQL EXECUTE
 eg：

查看需要keep的匿名块

SQL> select address,hash_value 
2 from v$sqlarea sq 
3 where sq.COMMAND_TYPE = 47 
4 and length(sq.SQL_TEXT) > 500 
5 and sq.SHARABLE_MEM>20000 
6 ;
ADDRESS HASH_VALUE 
---------------- ---------- 
000000008E8532A8 97348712
 
1 rows selected
确认当前匿名块是否被keep

SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE 
2 from v$db_object_cache oc, 
3 v$sqlarea sq 
4 where sq.SQL_TEXT=oc.NAME 
5 and sq.HASH_VALUE='97348712' 
6 ;
KEPT ADDRESS HASH_VALUE 
---- ---------------- ---------- 
NO 000000008E8532A8 97348712
SQL> 
 keep住匿名块

SQL> exec dbms_shared_pool.keep('000000008E8532A8,97348712');
begin dbms_shared_pool.keep('000000008E8532A8,97348712'); end;
ORA-01426: 数字溢出 
ORA-06512: 在 "SYS.DBMS_UTILITY", line 114 
ORA-06512: 在 "SYS.DBMS_SHARED_POOL", line 45 
ORA-06512: 在 "SYS.DBMS_SHARED_POOL", line 53 
ORA-06512: 在 line 1
SQL> exec dbms_shared_pool.keep('000000008E8532A8,97348712','C');
PL/SQL procedure successfully completed
SQL> 
检查是否被keep住

SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE 
2 from v$db_object_cache oc, 
3 v$sqlarea sq 
4 where sq.SQL_TEXT=oc.NAME 
5 and sq.HASH_VALUE='97348712' 
6 ;
KEPT ADDRESS HASH_VALUE 
---- ---------------- ---------- 
YES 000000008E8532A8 97348712
SQL> 
取消对象的keep

SQL> exec dbms_shared_pool.unkeep('000000008E8532A8,97348712','C');
PL/SQL procedure successfully completed
SQL> 
 确认当前匿名块是否被取消keep
 
SQL> select oc.KEPT,sq.ADDRESS,sq.HASH_VALUE 
2 from v$db_object_cache oc, 
3 v$sqlarea sq 
4 where sq.SQL_TEXT=oc.NAME 
5 and sq.HASH_VALUE='97348712' 
6 ;
KEPT ADDRESS HASH_VALUE 
---- ---------------- ---------- 
NO 000000008E8532A8 97348712
 
SQL>
 
剩下keep住package和sequnce就都类似了
共享池碎片问题 
绑定变量问题
-----end-----
cursor分为：1.缓存打开的游标--可以从v$open_cursor，2.查到缓存关闭的游标，3.未被缓存打开的游标
。session_CACHED_CURSORS正是缓存关闭的游标； OPEN_CURSORS
是包括缓存打开的游标和未被缓存打开的游标（可以从v$sesstat 查到）。
假如session_CACHED_CURSORS=200，缓存200个关闭游标，而open_cursor=100，允许最大打开100个游标，缓存
用的就不是很充分，而这些缓存资源又是非常宝贵的，所以如果缓存太多的游标占用的资源太多，反而起副作用。

分享到：