Buffer cache 的大小能存放多少数据？的一些疑问！
疑问：Buffer cache 的大小，能存放多少数据？会对consistent gets、physical reads 的影响。他们之间有什么关系？有没有计算公式？下面是产生上述疑问的实验过程：数据库系统环境信息：Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, DataMining and Real Application Testing optionsORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1System name:LinuxNode name: LinuxRelease: 2.6.18-194.el5Machine: i686Instance name: fish
SGA信息：04:24:22 SCOTT # fish >selectname,bytes/1024/1024 MB from v$sgainfo; NAME MB------------------------------------------Fixed SGA Size 1.27499771Redo Buffers 5.8203125Buffer Cache Size 44Shared Pool Size 192Large Pool Size 4Java Pool Size 4Streams Pool Size 4Shared IO Pool Size 0Granule Size 4Maximum SGA Size 399.097656Startup overhead in Shared Pool 140Free SGA Memory Available 144 12 rows selected. Elapsed: 00:00:00.01正如我们熟知的，BUFFERCACHE 用于存放最近访问的数据块信息 通下面实验可以知晓：第一次运行SQL 语句04:25:14 SCOTT # fish >selectcount(*) from emp; COUNT(*)---------- 14 Statistics---------------------------------------------------------- 305recursive calls 0db block gets 57consistent gets 3physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed第二次运行SQL语句04:25:45 SCOTT # fish >selectcount(*) from emp; COUNT(*)---------- 14 Elapsed: 00:00:00.00 Statistics---------------------------------------------------------- 0recursive calls 0db block gets 1consistent gets 0physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed通过两次运行发现consistent gets从57 降到了 1， physicalreads 由3降到了0。数据被加载到了buffer cache 中。减少了一致性读和物理读。 创建实验表：04:24:15 SCOTT # fish >createtable tony as select * from dba_objects; 第一次执行：04:24:55 SCOTT # fish >selectcount(*) from tony; COUNT(*)---------- 72636 Elapsed: 00:00:00.04Statistics---------------------------------------------------------- 251 recursive calls 0 db block gets 1111consistent gets 1127physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed第二次执行：04:25:05 SCOTT # fish >selectcount(*) from tony; COUNT(*)---------- 72636 Elapsed: 00:00:00.01Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1038consistent gets 1035physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed通过两次比较发现：consistent gets 由1111 降到了1038 只降了 73 physicalreads 由 1127 降到了 1035 只降了 92 。 增大BUFFER CACHE 大小：增加100MB。在运行上述语句。05:51:05 SYS # fish >selectname,bytes/1024/1024 MB from v$sgainfo; NAME MB------------------------------------------Fixed SGA Size 1.27499771Redo Buffers 5.8203125Buffer Cache Size 140Shared Pool Size 192Large Pool Size 4Java Pool Size 4Streams Pool Size 4Shared IO Pool Size 0Granule Size 4Maximum SGA Size 399.097656Startup overhead in Shared Pool 144Free SGA Memory Available 48 05:54:10 SCOTT # fish >selectcount(*) from tony; COUNT(*)---------- 72636 Elapsed: 00:00:00.10 Statistics---------------------------------------------------------- 251 recursive calls 0 db block gets 1113consistent gets 1038physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed 05:54:24 SCOTT # fish >selectcount(*) from tony; COUNT(*)---------- 72636 Elapsed: 00:00:00.01 Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1040consistent gets 0physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
发现物理读变为零了。
Buffer cache 的大小，能存放多少数据？会对consistent gets、physical reads 的影响。他们之间有什么关系？有没有计算公式？
你这个第一次执行有很多递归调用，第二次没有递归调用
所以buffer cache里面存储的数据，逻辑读，物理读也不光是你执行的单条sql导致，还有部分是因为递归抵用导致，例如：
251 recursive calls 想问的是：buffer cache 大小决定了能够缓存的数据。说白了就是100MB的空间能存放多少的磁盘数据。有些低级，自己没有验证出来问一下。
页:
[1]