|
疑问：Buffer cache 的大小，能存放多少数据？会对consistent gets、physical reads 的影响。他们之间有什么关系？有没有计算公式？下面是产生上述疑问的实验过程：
数据库系统环境信息： Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, DataMining and Real Application Testing options ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: Linux Release: 2.6.18-194.el5 Machine: i686 Instance name: fish
SGA信息： 04:24:22 SCOTT # fish >selectname,bytes/1024/1024 MB from v$sgainfo; NAME MB ------------------------------------------ Fixed SGA Size 1.27499771 Redo Buffers 5.8203125 Buffer Cache Size 44 Shared Pool Size 192 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 4 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 399.097656 Startup overhead in Shared Pool 140 Free 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 ---------------------------------------------------------- 305 recursive calls 0 db block gets 57 consistent gets 3 physical 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 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical 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.04 Statistics ---------------------------------------------------------- 251 recursive calls 0 db block gets 1111 consistent gets 1127 physical 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.01 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1038 consistent gets 1035 physical 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.27499771 Redo Buffers 5.8203125 Buffer Cache Size 140 Shared Pool Size 192 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 4 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 399.097656 Startup overhead in Shared Pool 144 Free 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 1113 consistent gets 1038 physical 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 1040 consistent gets 0 physical 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
发现物理读变为零了。
| 
发表于 2014-12-18 13:26:26
发表于 2014-12-18 13:51:41
楼主