减小字体
增大字体- ·上一篇文章：oracle em中按钮乱码解决办法
- ·下一篇文章：Oracle数据库服务器参数文件管理技巧
Oracle flashback buffer参数研究
atabase 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
难道_flashback_generation_buffer_size不起作用？再进一步研究，我们知道Oracle内存分配是以granule为单位的，查看当前系统granule大小
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
难道_flashback_generation_buffer_size不起作用？再进一步研究，我们知道Oracle内存分配是以granule为单位的，查看当前系统granule大小
引用
SQL> select * from v$sgainfo
2 where name=’Granule Size’;
NAME BYTES RES
—————————————- ———- —
Granule Size 4194304 No
同时该参数是受隐含参数_ksmg_granule_size控制的。
引用
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
修改_ksmg_granule_size大小到8M
引用
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.
可以看到granule已经变成8M。
引用
SQL> select * from v$sgainfo
2 where name=’Granule Size’
3 ;
NAME BYTES RES
—————————————- ———- —
Granule Size 8388608 No
查看flashback buffer大小，发现已经和_flashback_generation_buffer_size相匹配。
引用
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
总结：Oracle flashback buffer大小设置不仅和隐含参数_flashback_generation_buffer_size有关而且和Granule 大小（其大小不仅和隐含参数有关而且Oracle内存大小有关）有关。
1、Granule大于_flashback_generation_buffer_size时，_flashback_generation_buffer_size生效。
2、对于大内存高并发生产库建议将log_buffer设置8m以上
Oracle flashback buffer参数研究
