合 Oracle的内存结构以及ORA-04030和ORA-04031错误
Tags: Oracle体系结构Shared PoolBuffer Cache内存结构数据字典缓存(Data Dictionary Cache)大池(Large Pool)默认池(Default Pool)ORA-04031Redo Log BufferPGAJava PoolORA-04030Java池(Java Pool)Library CacheDatabase Buffer CacheLarge Pool保留池(Keep Pool)Redo日志缓冲区(Redo Log Buffer)SGAStreams Pool保留池(Reserved Pool)UGA结果缓存(Result Cache)程序全局区流池(Streams Pool)内存大小评估库缓存(Library Cache)库缓存回收池(Recycle Pool)共享池数据缓冲区
- 简介
- SGA介绍
- (1)共享池(Shared Pool)
- 库缓存(Library Cache)
- 数据字典缓存(Data Dictionary Cache)
- 保留池(Reserved Pool)
- 结果缓存(Result Cache)
- 共享池大小评估
- (2)数据缓冲区(Database Buffer Cache)
- (3)Redo日志缓冲区(Redo Log Buffer)
- (4)大池(Large Pool)
- (5)Java池(Java Pool)
- (6)流池(Streams Pool)
- PGA介绍
- 自动PGA内存管理(Automatic PGA Memory Management)
- UGA介绍
- SHOW SGA和V$SGA的结果区别
- 和内存相关的比较有用的视图
- V$SGASTAT
- V$SGA_DYNAMIC_COMPONENTS
- V$LIBRARYCACHE
- OCP真题
- 如何解决ORA-04030和ORA-04031错误?
- (一)ORA-04030错误
- (二)ORA-04031错误
- 参考
简介
Oracle内存结构主要分共享内存区与非共享内存区,共享内存区主要包含SGA(System Global Area,系统全局区),非共享内存区主要由PGA(Program Global Area,程序全局区)组成,如下图所示:
SGA介绍
SGA(System Global Area,系统全局区)是Oracle实例的基本组成部分,是Oracle为一个实例分配的一组共享内存缓冲区,保存着Oracle系统与所有数据库用户的共享信息,包括数据维护、SQL语句分析、Redo日志管理等。SGA是实例的主要部分,它在实例启动时分配。
SGA是动态的,由参数SGA_MAX_SIZE决定。查看当前系统的SGA可以使用的最大内存大小的命令是:SHOW PARAMETER SGA_MAX_SIZE
。修改SGA大小的命令是:ALTER SYSTEM SET SGA_MAX_SIZE=1200M SCOPE=SPFILE
。因为实例内存的分配是在数据库启动时进行的,所以,要让修改生效,必须重启数据库。当Oracle运行在32位Linux上时,其默认SGA无法超过1.7GB。
在Oracle 10g中引入了ASMM(Automatic Shared Memory Management,自动共享内存管理),DBA只需设置SGA_TARGET,Oracle就会自动地对共享池、数据缓冲区、Redo日志缓冲区、大池、Java池和流池进行自动调配,取消自动调配的方法为设置SGA_TARGET为0。
需要注意的是,Oracle分配内存的单位是granule,即粒度。最小的粒度为4M,设置大小不到一个粒度按一个粒度计算。在32位操作系统的平台上,粒度的最大值为16M。粒度的大小在数据库实例周期内不能被修改。按照粒度为单位分配的组件包括:Shared Pool、Buffer Cache(以及不同大小块的Buffer Cache)、Redo Log Buffer、Java Pool、Streams Pool和Large Pool。粒度的大小参考下表:
通过视图V$SGAINFO可以查询当前SGA分配的粒度大小,如下所示:
1 2 3 4 | SYS@orclasm > SELECT * FROM V$SGAINFO WHERE NAME='Granule Size'; NAME BYTES RES -------------------------------- ---------- --- Granule Size 4194304 No |
下面将对SGA的各个组成部分进行介绍。
缓存了各用户间可共享的各种结构,例如,缓存最近被执行的SQL语句和最近被使用的数据定义。共享池主要包括:库缓存(Library Cache)、数据字典缓存(Data Dictionary Cache)、保留池(Reserved Pool)和结果缓存(Result Cache)。
库缓存(Library Cache)
库缓存(Library Cache)是存放用户SQL命令、解析树和执行计划的区域。对于库缓存来说,具体包含以下几个部分:
共享SQL区(Shared SQL Area):保存了SQL语句文本,编译后的语法分析树及执行计划。查看共享SQL区的使用率命令为:
SELECT (SUM(PINS-RELOADS))/SUM(PINS) "LIBRARY CACHE" FROM V$LIBRARYCACHE;
。私有SQL区(Private SQL Area):包含当前会话的绑定信息以及运行时内存结构。每个发出SQL语句的会话,都有一个Private SQL Area。当多个用户执行相同的SQL语句,此SQL语句保存在共享SQL区。若是共享服务器模式,则Private SQL Area位于SGA的Share Pool或Large Pool中。若是专用服务器模式,则Private SQL Area位于PGA中。
共享PL/SQL区(Shared PL/SQL Area):保存了分析与编译过的PL/SQL块(存储过程、函数、包、触发器和匿名PL/SQL块)。
控制结构区(Control Structure Area):保存锁等控制信息。
数据字典缓存(Data Dictionary Cache)
数据字典缓存(Data Dictionary Cache)存放数据库运行的动态信息,例如,表和列的定义,数据字典表的权限。查看数据字典缓冲区命中率的SQL为:“SELECT ROUND((SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)*100,2) DATA_DICTIONARY_CACHE FROM V$ROWCACHE;”
。
保留池(Reserved Pool)
保留池(Reserved Pool)也叫保留区域(Reserved Area),是指Shared Pool中配置的一个内存保留区域,这个保留区域用做当在普通的Shared Pool列表中的空间不能用来满足Large Request的内存分配请求而分配大块的连续内存块。当一个内存请求大于隐含参数“_SHARED_POOL_RESERVED_MIN_ALLOC
”(默认:4400 bytes,如果系统经常出现ORA-04031错误,基本上都是请求大于4400的内存块,那么就可能需要增加SHARED_POOL_RESERVED_SIZE
参数设置。)的值时就是一个Large Request,反之当内存请求小于“_SHARED_POOL_RESERVED_MIN_ALLOC
”时就是一个Small Request。
另外关于Reserved Pool还有两个参数需要关注一下,一个是SHARED_POOL_RESERVED_SIZE,另外一个是隐含参数“_SHARED_POOL_RESERVED_PCT
”(默认:5%)。通过SHARED_POOL_RESERVED_SIZE可以为Reserved Pool指定一个大小,也可以通过“_SHARED_POOL_RESERVED_PCT
”来为Shared Pool指定一个比例。如果这两个参数同时设置了,那么就会以“_SHARED_POOL_RESERVED_PCT
”为准。参数SHARED_POOL_RESERVED_SIZE的缺省值是SHARED_POOL_SIZE的5%,最小值为5000bytes,最大不得超过SHARED_POOL_SIZE的50%。
通过视图V$SHARED_POOL_RESERVED
可以查到保留池的统计信息。其中字段REQUEST_MISSES记录了没有立即从空闲列表中得到可用的大内存段请求次数,这个值理想状态下要为0。当REQUEST_FAILURES大于0时,则需要增加SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE的空间。当REQUEST_MISS等于0,或是FREE_MEMORY大于等于SHARED_POOL_RESERVED_SIZE的空间时,则增加SHARED_POOL_RESERVED_SIZE的空间。MAX_USED_SPACE字段可以用来判断保留池的大小是否合适。保留区使用Shared Pool的LRU链表来管理内存块。可以通过如下的SQL语句来查询保留池的命中率(Hit Ratio),查询语句如下:
1 2 3 4 | SELECT (REQUEST_MISSES / (REQUESTS + 0.0001)) * 100 "REQUEST MISSES RATIO", (REQUEST_FAILURES / (REQUESTS + 0.0001)) * 100 "REQUEST FAILURES RATIO" FROM V$SHARED_POOL_RESERVED; |
以上结果应该都要小于1%,如果大于1,那么应该考虑加大SHARED_POOL_RESERVED_SIZE。
结果缓存(Result Cache)
结果缓存(Result Cache)是存放SQL查询结果和PL/SQL函数查询结果的区域。
共享池大小评估
共享池的大小由参数SHARED_POOL_SIZE决定。只要将初始化参数STATISTICS_LEVEL设置为TYPICAL(默认值)或ALL,就能启动对Shared Pool的建议功能,如果设置为BASIC,则关闭建议功能。使用如下的SQL语句可以查询到Oracle所建议的Shared Pool的大小:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | SYS@orclasm > SELECT SHARED_POOL_SIZE_FOR_ESTIMATE, 2 ESTD_LC_SIZE, 3 ESTD_LC_MEMORY_OBJECTS, 4 ESTD_LC_TIME_SAVED, 5 ESTD_LC_TIME_SAVED_FACTOR, 6 ESTD_LC_MEMORY_OBJECT_HITS 7 FROM V$SHARED_POOL_ADVICE; SHARED_POOL_SIZE_FOR_ESTIMATE ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_MEMORY_OBJECT_HITS ----------------------------- ------------ ---------------------- ------------------ ------------------------- -------------------------- 180 9 596 15816 .9874 244830 184 13 876 15879 .9913 247845 188 17 996 15910 .9933 249132 192 21 1191 15937 .9949 249697 196 25 1329 15944 .9954 250167 200 29 1447 16018 1 254285 204 33 1630 16033 1.0009 255345 208 37 1810 16041 1.0014 255949 212 40 1999 16068 1.0031 256701 216 43 2209 16069 1.0032 257237 220 46 2428 16083 1.0041 257685 224 50 2549 16088 1.0044 258030 228 53 2639 16089 1.0044 258234 232 57 2833 16092 1.0046 258457 236 61 2985 16095 1.0048 258802 240 65 3061 16097 1.0049 259258 260 85 3709 16118 1.0062 259946 280 104 4411 16142 1.0077 260564 300 124 5084 16157 1.0087 260859 320 143 5990 16166 1.0092 260956 340 162 7155 16174 1.0097 260999 360 162 7155 16174 1.0097 261001 380 162 7155 16174 1.0097 261001 400 162 7155 16174 1.0097 261001 |
第一列表示Oracle所估计的Shared Pool的尺寸值,其他列表示在该估计的Shared Pool大小下所表现出来的指标值。可以主要关注ESTD_LC_TIME_SAVED_FACTOR列的值,当该列值为1时,表示再增加Shared Pool的大小对性能的提高没有意义。对于上例来说,当Shared Pool为200MB时,达到最佳大小。对于设置比200MB更大的Shared Pool来说,就是浪费空间,没有更多的好处了。
(2)数据缓冲区(Database Buffer Cache)
也叫数据库缓冲区高速缓存,用于缓存从数据文件中检索出来的数据块,可以大大提高查询和更新数据的性能,是数据库实例的重要组成部分。参数DB_CACHE_SIZE
可指定数据缓冲区的大小,需要在参数文件中静态修改。Oracle在处理某个查询时,服务器进程会在Buffer Cache中查找它所需的所有数据块。如果未在Buffer Cache中找到所需要的数据块,那么服务器进程会从数据文件中读取所需的数据块,并在Buffer Cache中添加一个副本。因为关于同一数据块的后续请求可能会在内存中找到该数据块,因此,这些请求可能不需要进行物理读操作。Buffer Cache中的内存块有4种状态:
① Pinned:当前块正在被某个进程读取到Cache或正写到磁盘,即当前正在被访问的数据块,可防止多个会话同时对同一数据块进行写操作。此时,其他会话正等待访问该块。
② Clean:服务器进程从数据文件中读入的Block且还没有被其它进程所修改或者后台进程DBWn将Dirty Buffer写入到数据文件中的Buffer,该Buffer中的内容与数据文件中的Block一致。该状态的数据块是可以立即被移出的候选数据块。
③ Free/Unused:Buffer内为空,为实例刚启动时的状态。Buffer Cache初始化时或者在执行alter system flush buffer_cache以后的Buffer,该Buffer中没有存放任何内容。此状态与“clean”状态非常相似,不同之处在于“free/unused”状态的缓冲区尚未使用。
④ Dirty:脏数据,数据块已被修改,需要先被DBWn刷新到磁盘,才能执行过期处理(移出缓冲区)。在该状态下,该Buffer的内容与数据文件中Block的内容不一致。
图 3-6 Database Buffer Cache中数据块的状态转变
数据库高速缓冲区的主要功能是用来暂时存放最近读取自数据库中的数据,也就是数据文件(Data File)内的数据,而数据文件是以数据块(Block)为单位,因此,数据库高速缓冲区中的大小是以块为基数。当用户通过应用程序第一次向Oracle数据库发出查询请求时,Oracle会先在Buffer Cache内寻找该数据,如果有该请求所需要的数据,那么就直接从Buffer Cache传回给用户,这称为缓存命中(Cache Hit),这样就可以减少硬盘上的I/O次数。如果Oracle发现用户要的数据并不在Buffer Cache里,就称为缓存失误(Cache Miss),Oracle会从数据库中读取所需要的数据块,先放入Buffer Cache中,再传送给用户。该区域内的数据块通过LRU(Least Recently Used,最近最少使用)算法管理。LRU将Buffer Cache中的所有的Clean和Free状态的Buffer按照它们被读取的频率连接起来。(冷端:最少使用的;热端:最常被使用的;在服务器进程将磁盘的Block读取到Buffer Cache时,会先覆盖冷端的Buffer。)。
Buffer Cache可以分为多个缓冲池:
① 回收池(Recycle Pool):放到回收池中的数据,只要空间不够用,它们马上就会被释放出来,即回收池中的数据会最先被替换出内存,很少使用的数据放在该区。被放在回收池中的数据块不会被反复使用。也就是说,这些数据块只在事务(Transaction)还存在时才会被用到,一旦事务结束,就会被释放出来。回收池的大小最好是默认池的1/2,通过DB_RECYCLE_CACHE_SIZE参数指定回收池的大小。该缓存不参与ASMM的动态管理,不能自动调整大小。默认未启用,大小为0。手工修改指定值后,Default Pool的空间将被相应的减少。
② 保留池(Keep Pool):当数据被放到保留池里时,就代表这个数据是需要常常被重复使用的。保留池中的数据不会被替换出去,可以将常用的小表放置在该区可以降低I/O操作。可以通过DB_KEEP_CACHE_SIZE参数指定保留池的大小。该区域的大小不会被ASMM自动调节。默认未启用,大小为0,当手工修改指定该值后,Default Pool的空间将被相应的减少。
③ 默认池(Default Pool):当没有指定对象存储的缓冲池时,数据就会放在默认池中,相当于一个没有Keep与Recycle池的实例的Buffer Cache。也就是说,放在默认池的数据利用的是LRU机制。通过DB_CACHE_SIZE参数指定默认池的大小。
BUFFER_POOL子句可以在对象的STORAGE子句中为对象指定使用具体的Buffer Pool。如果现有对象没有明确指定Buffer Pool,那么默认都指定为Default Buffer Pool。可以使用CREATE或ALTER语句指定对象存储的缓冲池:
1 2 3 4 5 | CREATE INDEX CUST_IDX ON TT(ID) STORAGE (BUFFER_POOL KEEP); ALTER TABLE OE.CUSTOMERS STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX OE.CUST_LNAME_IX STORAGE (BUFFER_POOL KEEP); |
在同一个数据库中,支持多种大小的数据块缓存。通过DB_nK_CACHE_SIZE参数指定,如:
DB_CACHE_SIZE(指定标准块(这里为8K)的缓存区)
DB_2K_CACHE_SIZE(指定块大小为2K的缓存区)
DB_4K_CACHE_SIZE(指定块大小为4K的缓存区)
DB_16K_CACHE_SIZE(指定块大小为16K的缓存区)
DB_32K_CACHE_SIZE(指定块大小为32K的缓存区)
标准块缓冲区大小由DB_CACHE_SIZE指定。如标准块为nK,则不能通过DB_nK_CACHE_SIZE来指定标准块缓冲区的大小,应由DB_CACHE_SIZE指定。
当数据库高速缓冲区需要读取或写回数据到数据文件中时,都需要通过DBWn这个后台进程来协助处理,而参数DB_WRITER_PROCESSES主要设置要由几个DBWn来协助处理。在此建议不要超过系统CPU的个数,如果设置的值超过了CPU的个数,那么超过的那些是无法起作用的。
当参数DB_CACHE_ADVICE设置为ON(当STATISTICS_LEVEL为TYPICAL或ALL时,DB_CACHE_ADVICE参数值默认为ON)时,表示开启DB_CACHE_ADVICE功能。当开启参数DB_CACHE_ADVICE后,经过一段时间,Oracle就会自动收集足够的相关统计数据,并预测出DB_CACHE_SIZE在不同大小情况下的性能数据,而这些数据就是通过V$DB_CACHE_ADVICE
视图来显示的,因此,可以根据这些数据对DB_CACHE_SIZE做相关的调整,以达到最佳情况。
对视图V$DB_CACHE_ADVICE的各列介绍如下表所示:
字段名 | 数据类型 | 说明 |
---|---|---|
ID | NUMBER | 不同数据库高速缓冲区的编号,一般来说,DB_CACHE_SIZE的编号是3 |
NAME | VARCHAR2(20) | 数据库高速缓冲区的名称(Default、Keep、Recycle) |
BLOCK_SIZE | NUMBER | 数据块的大小(单位是K) |
ADVICE_STATUS | VARCHAR2(3) | 开启状态:ON代表开启,OFF代表关闭 |
SIZE_FOR_ESTIMATE | NUMBER | 预测性能的Cache大小(以M为单位) |
SIZE_FACTOR | NUMBER | 预测的Cache大小比例,也就是与目前大小的比例 |
BUFFERS_FOR_ESTIMATE | NUMBER | 预测性能数据的数据块个数 |
ESTD_PHYSICAL_READ_FACTOR | NUMBER | 在数据库高速缓冲区里物理读取的因子,也就是说,当数据库高速缓冲区大小为SIZE_FOR_ESTIMATE此字段时,DB_CACHE_ADVICE预测的物理读数与当前物理读数的比率值。如果当前物理读数为0,那么,这个值为空 |
ESTD_PHYSICAL_READS | NUMBER | 当数据库高速缓冲区大小为SIZE_FOR_ESTIMATE时,DB_CACHE_ADVICE预测的实际读数 |
ESTD_PHYSICAL_READ_TIME | NUMBER | 当前物理读取的时间 |
ESTD_PCT_OF_DB_TIME_FOR_READS | NUMBER | 当前物理读取的时间占所有时间的比例 |
查询视图V$DB_CACHE_ADVICE如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SYS@orclasm > SELECT ADVICE_STATUS, 2 SIZE_FOR_ESTIMATE, 3 ESTD_PHYSICAL_READ_FACTOR, 4 ESTD_PHYSICAL_READS 5 FROM V$DB_CACHE_ADVICE 6 WHERE NAME = 'DEFAULT'; ADV SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS --- ----------------- ------------------------- ------------------- ON 4 1.028 4028357 ON 8 1.0223 4006098 ON 12 1.0178 3988551 ON 16 1.0109 3961532 ON 20 1.0068 3945138 ON 24 1.0052 3939208 ON 28 1.0045 3936105 ON 32 1.004 3934171 ON 36 1.0033 3931620 ON 40 1.0023 3927591 ON 44 1.0013 3923578 ON 48 1 3918655 ON 52 .9992 3915665 ON 56 .9988 3913960 ON 60 .9984 3912432 ON 64 .9982 3911506 ON 68 .998 3910840 ON 72 .9979 3910450 ON 76 .9978 3910125 ON 80 .9925 3889182 |
由以上结果可以知道,当ESTD_PHYSICAL_READ_FACTOR为1时,最佳的DB_BUFFER_SIZE是48,因为在这之后再进行调整对降低I/O的影响有限,所以该系统最佳的Buffer Cache的大小为48M。