« Oracle Database12c新特性:多线程模式- Multi-threaded Model | Blog首页 | 恩墨学院:OCM之路微小说 - 洪日华(红叶DBA) »
Oracle flashback buffer 的分配与 Granule 限制
链接:https://www.eygle.com/archives/2013/07/oracle_flashback_buffer_granule.html
自Oracle 10g开始,Oracle引入了Flashback Database的功能,当启用该功能后,需要在Shared Pool中分配Flashback buffer , 该内存的分配与Granule的大小有关,Oracle限制该内存的分配不超过1个Granule的大小。
修改如下参数,重启数据库,开启Flashback功能:
SQL> alter system set db_recovery_file_dest='/home/oracle/fra' scope=spfile;
System altered.SQL> alter system set db_recovery_file_dest_size=1024M scope=spfile;
System altered.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
在Shared Pool会分配一块内存区域:flashback generation buffer,该内存最大为 1 个Granule 。缺省的数据库分配了如下内存:
SQL> select * from v$sgastat where name like '%flash%';
POOL NAME BYTES CON_ID
------------ -------------------------- ---------- ----------
shared pool flashback_marker_cache_si 9200 0
shared pool flashback generation buff 7428272 0
修改隐含参数 _flashback_generation_buffer_size 可以增加该内存分片,但是在 16M Granule大小的设置下,flashback buffer的分配不会超过16M,修改_ksmg_granule_size参会可以调整Granule的大小,但是由于该参数影响广泛,通常不建议修改:
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: granule_size
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%granule_size%'
NAME VALUE DESCRIB
------------------------------ -------------------- -----------------------------------------------------------
_ksmg_granule_size 16777216 granule size in bytesSQL> alter system set "_flashback_generation_buffer_size"=33554432 scope=spfile;
System altered.
重启数据库后可以看到Flashback Buffer增加到16M左右:
SQL> select * from v$sgastat where name ='flashback generation buff';
POOL NAME BYTES CON_ID
------------ ------------------------------ ---------- ----------
shared pool flashback generation buff 15937344 0
内存的分配过程也可以从告警日志文件中观察得到:
Mon Jul 15 12:17:39 2013
Successful mount of redo thread 1, with mount id 927646639
Mon Jul 15 12:17:39 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
这就是关于Flashback Buffer的分配与参数关系。
历史上的今天...
>> 2020-07-17文章:
>> 2014-07-17文章:
>> 2012-07-17文章:
>> 2011-07-17文章:
>> 2008-07-17文章:
>> 2006-07-17文章:
>> 2005-07-17文章:
By eygle on 2013-07-17 09:11 | Comments (0) | Oracle12c/11g | 3111 |