Oracle 参数之_flashback_generation_buffer_size

我们知道在Oracle 10g中，如果数据库启用flashback功能，Oracle即在shared pool中分配flashback buffer，以下是Oracle部分启动日志，可以清楚的看到flashback buffer在shared pool分配了3981204 bytes。

[quote]Wed Dec 30 15:20:40 2009
Allocated 3981204 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=16, OS id=724[/quote]

同样在数据字典中也可以看到flashback buffer的大小：
[quote]SQL> select name,bytes from V$sgastat
2 where pool='shared pool'
3 and name like '%flash%';

NAME BYTES
-------------------------- ----------
flashback generation buff 3981204[/quote]

当flashback buffer空间紧张时，可能会出现flashback buf free by RVWR等待事件，Oracle并没有提供参数调节flashback buffer，对于高吞吐量，并发量系统初始大小并不能满足系统要求，[color=red]有人说调节log_buffer，可以间接调节flashback buffer。[/color]
目前系统中log_buffer大小为7012352 bytes，将其改为10M，重启数据库，观察flashback buffer变化。
[quote]SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 7012352

SQL> alter system set log_buffer=10000000 scope=spfile;

System altered.

SQL> select name,bytes from V$sgastat
2 where pool='shared pool'
3 and name like '%flash%';

NAME BYTES
------------------------------ ----------
flashback generation buff 3981204

SQL> startup force
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1262764 bytes
Variable Size 155192148 bytes
Database Buffers 369098752 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL> select name,bytes from V$sgastat
2 where pool='shared pool'
3 and name like '%flash%';

NAME BYTES
------------------------------ ----------
flashback generation buff 3981204[/quote]
可以看到flashback generation buff大小并没有发生变化。
进一步，观察和flashback相关的隐含参数
[quote]SQL> set linesize 120
SQL> col name for a40 trunc
SQL> col value for a20
SQL> col pdesc for a50 trunc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: flashback
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%flashback%'

NAME VALUE PDESC
---------------------------------------- -------------------- --------------------------------------------------
db_flashback_retention_target 1440 Maximum Flashback Database log retention time in m
_validate_flashback_database FALSE Scan database to validate result of flashback data
_flashback_allow_noarchivelog FALSE Allow enabling flashback on noarchivelog database
_verify_flashback_redo TRUE Verify that the redo logs needed for flashback are
_flashback_verbose_info FALSE Print verbose information about flashback database
_flashback_logfile_enqueue_timeout 600 flashback logfile enqueue timeout for opens
_flashback_barrier_interval 1800 Flashback barrier interval in seconds
_flashback_standby_barrier_interval 1800 Flashback standby barrier interval in seconds
_flashback_fuzzy_barrier TRUE Use flashback fuzzy barrier
_flashback_log_size 1000 Flashback log size
_flashback_log_min_size 100 Minimum flashback log size

NAME VALUE PDESC
---------------------------------------- -------------------- --------------------------------------------------
_flashback_copy_latches 10 Number of flashback copy latches
_flashback_n_log_per_thread 128 Desired number of flashback logs per flashback thr
_flashback_max_n_log_per_thread 2048 Maximum number of flashback logs per flashback thr
_flashback_generation_buffer_size 134193152 flashback generation buffer size
_flashback_max_log_size 0 Maximum flashback log size in bytes (OS limit)
_flashback_log_io_error_behavior 0 Specify Flashback log I/O error behavior
_flashback_hint_barrier_percent 20 Flashback hint barrier percent
_percent_flashback_buf_partial_full 50 Percent of flashback buffer filled to be considere
_flashback_write_size_qm 4 Desired flashback write size in quarter MB

20 rows selected.
[/quote]
调节隐含参数_flashback_generation_buffer_size，可以看到flashback generation buff 并没有发生变化。
[quote]SQL> alter system set "_flashback_generation_buffer_size"=4200000 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1262428 bytes
Variable Size 146803876 bytes
Database Buffers 318767104 bytes
Redo Buffers 70037504 bytes
Database mounted.
Database opened.
SQL> select name,bytes from V$sgastat
2 where pool='shared pool'
3 and name like '%flash%';

NAME BYTES
---------------------------------------- ----------
flashback generation buff 3981204[/quote]

难道_flashback_generation_buffer_size不起作用？再进一步研究，我们知道Oracle内存分配是以granule为单位的，查看当前系统granule大小
[quote]SQL> select * from v$sgainfo
2 where name='Granule Size';

NAME BYTES RES
---------------------------------------- ---------- ---
Granule Size 4194304 No[/quote]

同时该参数是受隐含参数_ksmg_granule_size控制的。
[quote]SQL> set linesize 120
SQL> col name for a40 trunc
SQL> col value for a20
SQL> col pdesc for a50 trunc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: ksmg_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 '%ksmg_granule_size%'

NAME VALUE PDESC
---------------------------------------- -------------------- --------------------------------------------------
_ksmg_granule_size 4194304 granule size in bytes[/quote]
修改_ksmg_granule_size大小到8M
[quote]SQL> alter system set "_ksmg_granule_size"=8388608 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261080 bytes
Variable Size 142606824 bytes
Database Buffers 318767104 bytes
Redo Buffers 74235904 bytes
Database mounted.
Database opened.[/quote]
可以看到granule已经变成8M。
[quote]SQL> select * from v$sgainfo
2 where name='Granule Size'
3 ;

NAME BYTES RES
---------------------------------------- ---------- ---
Granule Size 8388608 No[/quote]

查看flashback buffer大小，发现已经和_flashback_generation_buffer_size相匹配。
[quote]SQL> select name,bytes from V$sgastat
2 where pool='shared pool'
3 and name like '%flash%';

NAME BYTES
---------------------------------------- ----------
flashback generation buff 4200448

SQL> set linesize 120
SQL> col name for a40 trunc
SQL> col value for a20
SQL> col pdesc for a50 trunc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: _flashback_generation_buffer_size
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%_flashback_generation_buffer_size%'

NAME VALUE PDESC
---------------------------------------- -------------------- --------------------------------------------------
_flashback_generation_buffer_size 4200448 flashback generation buffer size
[/quote]

总结：Oracle flashback buffer大小设置不仅和隐含参数_flashback_generation_buffer_size有关而且和Granule 大小（其大小不仅和隐含参数有关而且Oracle内存大小有关）有关。
1、Granule大于_flashback_generation_buffer_size时，_flashback_generation_buffer_size生效。
2、对于大内存高并发生产库建议将log_buffer设置8m以上