Oracle逻辑读的示例分析

这篇文章给大家分享的是有关Oracle逻辑读的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

1.物理读(physical read)

当数据块第一次读取到,就会缓存到buffer cache 中,而第二次读取和修改该数据块时就在内存buffer cache 了 以下是例子:

1.1 第一次读取:

C:"Documents and Settings"Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Feb 28 09:32:04 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> set autotrace traceonly SQL> select * from test;

Execution Plan----------------------------------------------------------   0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)1   0  TABLE ACCESS (FULL) OF TEST (Cost=2 Card=4 Bytes=8)

Statistics----------------------------------------------------------        175 recursive calls          0 db block gets         24 consistent gets9 physical reads            --9个物理读         0 redo size        373 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          2 sorts (memory)          0 sorts (disk)          1 rows processed

1.2 第二次读取

SQL> select * from test;

Execution Plan----------------------------------------------------------   0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)1   0  TABLE ACCESS (FULL) OF TEST (Cost=2 Card=4 Bytes=8)

Statistics----------------------------------------------------------          0 recursive calls          0 db block gets          7 consistent gets0 physical reads    --没有发生物理读了,直接从buffer cache 中读取了         0 redo size        373 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)1 rows processed

1.3 数据块被重新读入buffer cache ,这种发生在

如果有新的数据需要被读入Buffer Cache中，而Buffer Cache又没有足够的空闲空间，Oracle就根据LRU算法将LRU链表中LRU端的数据置换出去。当这些数据被再次访问到时，需要重新从磁盘读入。

SQL> alter session set events immediate trace name flush_cache;--清空数据缓冲区

Session altered.

SQL> select * from test;

Execution Plan----------------------------------------------------------   0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)1   0  TABLE ACCESS (FULL) OF TEST (Cost=2 Card=4 Bytes=8)

Statistics----------------------------------------------------------          0 recursive calls          0 db block gets          7 consistent gets6 physical reads  --又重新发生了物理读         0 redo size        373 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)          1 rows processed

2.逻辑读(buffer read)

逻辑读指的就是从（或者视图从）Buffer Cache中读取数据块。按照访问数据块的模式不同，可以分为即时读（Current Read）和一致性读（Consistent Read）。注意：逻辑IO只有逻辑读，没有逻辑写。

即时读

即时读即读取数据块当前的最新数据。任何时候在Buffer Cache中都只有一份当前数据块。即时读通常发生在对数据进行修改、删除操作时。这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。

SQL> select * from test for update;

Execution Plan ----------------------------------------------------------0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)   1   0  FOR UPDATE2   1    TABLE ACCESS (FULL) OF TEST (Cost=2 Card=4 Bytes=8)

Statistics ----------------------------------------------------------0 recursive calls  1 db block gets        14 consistent gets          0 physical reads        252 redo size386 bytes sent via SQL*Net to client        503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)          1 rows processed

SQL>

一致性读

Oracle是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致性读就是为了保证数据的一致性。在Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。当其他进程读取数据块时，会先比较数据块上的SCN和自己的SCN。如果数据块上的SCN小于等于进程本身的SCN，则直接读取数据块上的数据；如果数据块上的SCN大于进程本身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。

下面这个例子帮助大家理解一下一致性读：

会话1中：

SQL> select * from test;

       ID----------          1000

SQL> update test set id=2000;

1 row updated.

会话2中:

SQL> set autotrace on   SQL> select * from test;

       ID       ----------          1000

Execution Plan----------------------------------------------------------0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)   1   0  TABLE ACCESS (FULL) OF TEST (Cost=2 Card=4 Bytes=8)

Statistics----------------------------------------------------------          0 recursive calls          0 db block gets9 consistent gets   没有事物做update时 是 7 consistent gets 说明多了2个 consistent gets    这2个是要从回滚段中获取的          0 physical reads         52 redo size        373 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)1 rows processed

SQL>

Oracle性能调优中，逻辑读是个很重要的度量值，它不仅容易收集，而且能够告诉我们许多关于数据库引擎工作量的信息。逻辑读是在执行SQL语句的时候从高速缓存中读取的块数。

逻辑读在Oracle调优中有四个好处：

（1）逻辑读是受制于CPU能力的操作，因而，很好的反映了CPU的使用情况。

（2）逻辑读可能导致物理读，因而，通过减少逻辑读的数量，很可能会降低I/O操作次数。

（3）逻辑读是受制于串行的操作，既然经常要考虑多用户负载的优化，最小化逻辑读将有利于避免扩展性问题。

（4）逻辑读的数量可以通过SQL跟踪文件和动态性能视图在SQL语句以及执行计划级别获得。

下面就来详细的讲述下逻辑读相关的知识，以作为自己学习的一个总结。我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不是整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的buffercache  链.找到之后,需要在这个链上加一个cache buffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache buffer chains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffer cache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cache buffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.逻辑读和Cache buffer chains闩关系密切，TOM曾有文章提到，进程每申请一次Cache buffer chains闩，就是一次逻辑读。但是，逻辑读并不等同于Cache buffer chains闩，每次逻辑读，在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。 下面是我的测试： 步1：建立测试表： create table jj_one(id number(5),name char(40)); 步2：插入100行 beginfor i in 1..100 loop      insert into jj_one values(i,aaa);    end loop; end; /或：insert into jj_one select rownum,aaa from dba_objects where rownum<=100; 步3：显示一下表中行的分布sid=10 pid=11> select bk,max(id),min(id) from (select dbms_rowid.rowid_block_number(rowid) bk,id from jj_one) group by bk;         BK    MAX(ID)    MIN(ID)---------- ---------- ----------      42594         81          1      42595        100         82可以看到，表共占两个块，ID从1到81的行在块42594中，ID从82到100的行在42595中。 步4：设备批量读取参数为15 sid=10 pid=11> set arraysize 15因为9i或10g中的默认值都是15，如果并没有更改过这个设置，此步也可省去。 步5：查看1行： sid=11 pid=12> set autot trace statsid=11 pid=12> select * from jj_one where id<=1; 统计信息 ----------------------------------------------------------           0  recursive calls6  consistent gets           0  physical reads         458  bytes sent via SQL*Net to client         372  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client           1  rows processed （省略无关行） 逻辑读为6 步6：查询15行以内：sid=11 pid=12> select * from jj_one where id<=2; 统计信息 ----------------------------------------------------------0  recursive calls           6  consistent gets           0  physical reads         493  bytes sent via SQL*Net to client372  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           2  rows processed 在抓取行数小于15的情况下，逻辑读始终为6。步7：查询16行以上： sid=11 pid=12> select * from jj_one where id<=16; 已选择16行。 统计信息----------------------------------------------------------           0  recursive calls           7  consistent gets           0  physical reads699  bytes sent via SQL*Net to client         383  bytes received via SQL*Net from client           3  SQL*Net roundtrips to/from client16  rows processed 逻辑读已经变成7次。注意，在10G中，对块读的算法有改进。以同样的谓词条件，访问同样的行时，第一次访问时的逻辑读要比以后再行访问时多的多。因此，在10G中，同样的命令，多执行几次，这样看到的结果比较全面。 还有一点，访问15行以内时，为什么会有6次逻辑读？不应该是1次吗？这里，我相信Set autot trace stat命令本身有一定的原因，如果用下面的静态游标：sid=10 pid=11> alter session set events 10046 trace name context forever ,level 14; 会话已更改。 declaretype mid is table of jj_one.id%type;   mid1 mid;   cursor c is select id from jj_one where id>=1 and id<=15; beginopen c;   fetch c bulk collect into mid1 limit 15;   dbms_output.put_line(c%rowcount);   close c; end; /sid=10 pid=11> alter session set events 10046 trace name context off; 会话已更改。 用Tkprof格式化跟踪结果：E:/oracle/admin/mytwo/udump>tkprof mytwo_ora_756.trc m3.txt 查看M3.txt文件：call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        1      0.00       0.00          0          3          0          15------- ------  -------- ---------- ---------- ---------- ----------  ----------total        3      0.00       0.00          0          3          0          15 逻辑读只有3次。这3次逻辑读，有一次是针对行所在块的，其余两次是针对段头的。 实验完毕从上面的实验中可以看出，“成批读取”中，批大小的设定，可以影响逻辑读的数量。批大小越大，读相同数量的行，逻辑读就越少。而且服务端和客户端交互的次数也越少，由网络传输的数据也可以减少，下面看一下测试： 批大小为1： sid=11 pid=12> set arraysize 1sid=11 pid=12> select * from jj_one; 已选择100行。 统计信息 ----------------------------------------------------------54  consistent gets        7206  bytes sent via SQL*Net to client         911  bytes received via SQL*Net from client51  SQL*Net roundtrips to/from client         100  rows processed 批大小为100： sid=11 pid=12> set arraysize 100sid=11 pid=12> select * from jj_one; 已选择100行。 统计信息 ----------------------------------------------------------6  consistent gets        1277  bytes sent via SQL*Net to client         372  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client         100  rows processed 差别是很明显的，bytes sent via SQL*Net to client的数值，相差了6倍左右。但这并不代表将批大小设置的越高，速度就越快，否则，Oracle直接将它设置为一个最大的值，不就行了，干吗还要让我们去自己调节呢！行从Buffer cache中读出来后，会先缓存在PGA中（具体是在游标的运行时区），然后再传给客户端。如果批大小过大，在PGA、客户端占用的内存也会增大。而且，如果渐歇性的在网络上传输大量数据，对网络也会有一定影响。下面来观察一下批大小对PGA的影响：在会话11中执行如下过程： declare   type mid is table of t1.id%type;   mid1 mid;   cursor c is select id from t1; begin   open c;   loopfetch c bulk collect into mid1 limit 5000;   exit when c%notfound;   end loop;dbms_output.put_line(c%rowcount);   close c; end; / 在另一会话中观察会话11的内存占用情况： sid=10 pid=11> @pga   --此脚本下面有说明 输入 user 的值:  11原值    7: and b.sid= &user 新值    7: and b.sid= 11   PGA Used  PGA Alloc    PGA Max ---------- ---------- ----------561508     779492     779492 然后将会话11中过程的批大小改为1：fetch c bulk collect into mid1 limit 5000; 再试一次在另一会话观察会话11的PGA占用情况： sid=10 pid=11> @pga 输入 user 的值:  11 原值    7: and b.sid= &user 新值    7: and b.sid= 11PGA Used  PGA Alloc    PGA Max ---------- ---------- ----------     184388     250668     250668批大小为5000时的内存占用，是批大小为1时的3倍左右。另外，测试表一定要大一些，我的测试表是1000000行，否则不容易看到结果。在10G中，可以得到基本相同的结果。 PGA.SQL脚本如下： --pga_by_process.sql： SELECTa.pga_used_mem "PGA Used", a.pga_alloc_mem "PGA Alloc", a.pga_max_mem "PGA Max" FROM v$process a,v$session b where a.addr = b.paddr and b.sid= &user

感谢各位的阅读！关于“Oracle逻辑读的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！