首页 > 数据库 > Oracle > 正文
oracle优化------缓存对象与数据
2011-07-02       个评论      
收藏    我要投稿

系统出现性能瓶颈时,尽量较少不必要的资源消耗,最后就是平衡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中的活动对象。


所以你没有使用过对象时,是不存在这个视图里的,使用时用了,才会在这个视图里出现

可以参看三思的动态性能视图介绍: http://space.itpub.net/7607759/viewspace-22241


例如修改索引的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这个脚本


SYS@skatedb>@/home/oracle/10.2.0/db_1/rdbms/admin/dbmspool.sql

Package created.


Grant succeeded.


View created.


Package body created.

SYS@skatedb >


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个游标,缓存用的就不是很充分,而这些缓存资源又是非常宝贵的,所以如果缓存太多的游标占用的资源太多,反而起副作用。

点击复制链接 与好友分享!回本站首页
相关TAG标签 缓存 对象 数据
上一篇:Oracle数据导入MySQL的快捷工具:MySQL Migration Toolkit
下一篇:SQL Server 2000打不上sp4补丁的解决办法
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站