oracle buffer size 设置,【学习笔记】Oracle data buffer cache详细研究笔记

天萃荷净

Oracle研究中心学习笔记：分享一篇关于Oracle数据库的data buffer cache学习研究笔记，通过操作案例进行讲解。

SQL> show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

SQL> show parameter keep

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_keep string

control_file_record_keep_time integer 7

db_keep_cache_size big integer 0

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 160M

sga_target big integer 160M

SQL> alter system set sga_max_size=200m scope=spfile;

系统已更改。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 209715200 bytes

Fixed Size 1248164 bytes

Variable Size 113247324 bytes

Database Buffers 92274688 bytes

Redo Buffers 2945024 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 200M

sga_target big integer 160M

SQL> alter system set db_keep_cache_size=5m;

系统已更改。

SQL> alter table tt storage (buffer_pool keep);

表已更改。

SQL> select table_name,buffer_pool from dba_tables where table_name='TT' and owner='SYS';

TABLE_NAME BUFFER_

------------------------------ -------

TT KEEP

SQL> select tt.*,rowid from tt;

ID SCN ROWID

---------- ---------- ------------------

1 230015 AAACgaAAFAAAACQAAA

2 230127 AAACgaAAFAAAACQAAB

3 230243 AAACgaAAFAAAACQAAC

4 230282 AAACgaAAFAAAACQAAD

4 250729 AAACgaAAFAAAACQAAE

5 250741 AAACgaAAFAAAACQAAF

6 250888 AAACgaAAFAAAACQAAG

7 251493 AAACgaAAFAAAACQAAH

8 251590 AAACgaAAFAAAACQAAI

9 251595 AAACgaAAFAAAACQAAJ

10 251602 AAACgaAAFAAAACQAAK

ID SCN ROWID

---------- ---------- ------------------

1