逻辑IO只有逻辑读,没有逻辑写
ORACLE物理读和逻辑读(SET AUTOTRACE ON 部分参数解释)
set autotrace 产生部分信息解读的官方文档基本的定义为如下:
recursive calls: Number of recursive calls generated at both the user and system level.
[url=]Oracle[/url]
maintains tables used for internal processing. When Oracle needs to make a change to
these tables, it internally generates an internal [url=]SQL[/url] statement, which in turn generates a
recursive call.
DB block gets: The number of accesses to the current image of a block
当前模式块意思就是在操作中正好提取的块数目,而不是在一致性读的情况下而产生的块数。正常的情况下,一个查询提取的块是在查询开始的那个时间点上存在的数据块,当前块是在这个时刻存在的数据块,
而不是在这个时间点之前或者之后的数据块数目。
Consistent gets: The number of accesses to a read-consistent image of a block(单位:块次,即读每块的次数)
数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块
Physical reads: The number of blocks read from disk(单位:块次,即读每块的次数)
逻辑读是指读内存,物理读是指读磁盘,在set autotrace on下,他们关系如下:
LOGICAL IO = db_block_gets + consistent_gets
PHYSICAL IO = physical_reads
由此,自然也就得出了cache命中率的公式:
Hit Ratio = (db block gets + consistent gets - physical reads) / (db block gets + consistent gets)
或者Hit Ratio = 1 – (physical reads/(db block gets + consistent gets))
逻辑读的理解(摘自晶晶小妹文章):
我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕,就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.
但Oracle并不会在一次逻辑读中,读块中所有的行,而是根据用户的设定,一次读一部分行。为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的cache buffer 链.找到之后,需要在这个链上加一个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表中定位块的这个过程。
如果一次Fetch,访问了N个块,就是N个逻辑读。如果一个块中的行,花费N次Fetch才抓取完,也是N个逻辑读。
几个问题:
1. 每次逻辑读所读取的行的数量默认是几条?
2. 如何在抓取命令中设置?
3. 只对当前的session有效吧?
4. 如果采用普通的fetch游标(不用bulk collect),那么每次逻辑读所读取的行的数量是?
问题1:set arraysize 可以设置系统当前的值,查看的语句是:show arraysize,默认值15,有效值1到5000
补充:Arraysize by default is 15 in sqlplus,10 in JDBC,2 in pro*c,1 in OCI
(参考tomkyte:http://asktom.oracle.com/pls/ask ... ION_ID:880343948514)
问题2:fetch 游标 bulk collect into 变量limit N;
问题3. 只针对当前会话有效.
问题4. 普通的fetch,一次抓取一行,每读一行在不考虑行迁移行链接的情况下,是一个逻辑读.
在sqlplus下利用命令set autotrace on会产生一些统计信息,其中包括Consistent gets,
从官网文档中看不出到底这个值是如何产生的,
如果对一个表进行全表扫描并取出所有记录(select * from table),可以把算法写为:
Consistent gets=rows/arraysize + N (N为读表时在cache中占用的总的块数,参考asktom,ORACLE8i)
或者Consistent gets=rows/arraysize + N + recursive calls(自己[url=]实验[/url]结果,ORACLE10g)
实验如下:
SQL> drop table test; Table dropped. Elapsed: 00:00:00.26 SQL> create table test( a int); Table created. Elapsed: 00:00:00.04 SQL> begin 2 for i in 1..10000 loop 3 insert into test values (i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.68 --并未执行commit SQL> set autotrace traceonly SQL> select count(0) from test; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3467505462 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 10000 | 6 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 23 consistent gets --此时说明需要从内存读取23次,即占用23个block(不一定就真的占用23个,但至少涉及23个块) 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> show arraysize --查看arraysize,这是我之前设置的值1000 arraysize 1000 SQL> select * from test; 10000 rows selected. Elapsed: 00:00:00.13 Execution Plan ---------------------------------------------------------- Plan hash value: 217508114 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 126K| 6 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 10000 | 126K| 6 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 60 consistent gets --发现其值=23(上面结果)+27(recursive calls)+ 10000(总行数)/1000(arraysize)=60 0 physical reads 0 redo size 56026 bytes sent via SQL*Net to client 591 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> / --再执行一次上面的语句,不同的是recursive calls 显示为0,相应的consistent gets也减少 10000 rows selected. Elapsed: 00:00:00.12 Execution Plan ---------------------------------------------------------- Plan hash value: 217508114 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 126K| 6 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 10000 | 126K| 6 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 56026 bytes sent via SQL*Net to client 591 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> set arraysize 20 --设置arraysize为20时, SQL> / 10000 rows selected. Elapsed: 00:00:00.23 Execution Plan ---------------------------------------------------------- Plan hash value: 217508114 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 126K| 6 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 10000 | 126K| 6 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 523 consistent gets --发现其值=23(上面结果)+0(recursive calls)+ 10000(总行数)/20(arraysize)=523 0 physical reads 0 redo size 143246 bytes sent via SQL*Net to client --发现设arraysize=20时网络传输字节少比设arraysize=1000时多了很多 5981 bytes received via SQL*Net from client 501 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)
|
疑问:Arraysize by default is 15 in sqlplus,10 in JDBC,2 in pro*c,1 in OCI 未经过实验,另外,在这相应的环境下如何修改arraysize?