[20210224]fetch r=0算逻辑读吗.txt

[20210224]fetch r=0算逻辑读吗.txt

--//我一直以为fetch r=0时依旧算1次逻辑读.测试发现我理解错了.通过测试说明问题.

1.环境:
scott@book> @ ver1
port_string         version        banner
------------------- -------------- --------------------------------------------------------------------------------
x86_64/linux 2.4.xx 11.2.0.4.0     oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

scott@book> create table empx as select * from emp;
table created.

--//分析略.该表共14条记录.

2.测试:
scott@book> set arraysize 13
scott@book> @ seg empx
scott@book> @ prxx
==============================
seg_mb                        : 0
seg_owner                     : scott
seg_segment_name              : empx
seg_partition_name            :
seg_segment_type              : table
seg_tablespace_name           : users
blocks                        : 8
hdrfil                        : 4
hdrblk                        : 554
pl/sql procedure successfully completed.

scott@book> alter session set statistics_level=all;
session altered.

scott@book> @ 10046on 12
session altered.

scott@book> select * from empx;
     empno ename      job              mgr hiredate                   sal       comm     deptno
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 smith      clerk           7902 1980-12-17 00:00:00        800                    20
      7499 allen      salesman        7698 1981-02-20 00:00:00       1600        300         30
      7521 ward       salesman        7698 1981-02-22 00:00:00       1250        500         30
      7566 jones      manager         7839 1981-04-02 00:00:00       2975                    20
      7654 martin     salesman        7698 1981-09-28 00:00:00       1250       1400         30
      7698 blake      manager         7839 1981-05-01 00:00:00       2850                    30
      7782 clark      manager         7839 1981-06-09 00:00:00       2450                    10
      7788 scott      analyst         7566 1987-04-19 00:00:00       3000                    20
      7839 king       president            1981-11-17 00:00:00       5000                    10
      7844 turner     salesman        7698 1981-09-08 00:00:00       1500          0         30
      7876 adams      clerk           7788 1987-05-23 00:00:00       1100                    20
      7900 james      clerk           7698 1981-12-03 00:00:00        950                    30
      7902 ford       analyst         7566 1981-12-03 00:00:00       3000                    20
      7934 miller     clerk           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

scott@book> @ 10046off
session altered.

scott@book> select * from empx;
plan hash value: 722738080

--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       3 |
|   1 |  table access full| empx |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

--//看一下转储文件:
$ grep fetch /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_14841.trc
fetch #140627174044680:c=0,e=72,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=722738080,tim=1614150824077788
fetch #140627174044680:c=0,e=77,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=722738080,tim=1614150824078538
fetch #140627174044680:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=722738080,tim=1614150824079938
--//第1次 r=1,第2次r=13,第3次 r=0.
--//如果算第3次的fetch,执行计划的逻辑读应该是4,而现在是3.

--//可以使用我建立的gdb脚本确定,脚本参考链接:http://blog.itpub.net/267265/viewspace-2757990/=>[20210220]gdb跟踪逻辑读2.txt
breakpoint 1 at 0x994df72
breakpoint 2 at 0xfcafda
breakpoint 3 at 0x947da78
breakpoint 4 at 0xfc97f0
breakpoint 5 at 0x95ed0ca
breakpoint 6 at 0x94471a4
(gdb) c
continuing.
2021/02/24 15:16:42.990936245 :kteinpscan 0x100022a
2021/02/24 15:16:42.996402852 :kteinmap 0x100022a
2021/02/24 15:16:43.016412801 :kdst_fetch 0x100022b
2021/02/24 15:16:43.035717000 :kdst_fetch 0x100022b
--//kteinmap 不算1次逻辑读.这样逻辑读3次.

3.再换一种测试,使用12c的客户端:

scott@78> show sqlpluscompatibility
sqlpluscompatibility 12.2.0
scott@78> set rowprefetch 14
--//这样1次fetch全部记录,这样逻辑读可以减少1个.

scott@78> alter session set statistics_level=all;
session altered.

scott@78> select * from empx;
scott@78> @ dpc '' ''
plan_table_output
-------------------------------------
sql_id  4ag7sc82kdhh4, child number 0
-------------------------------------
select * from empx
plan hash value: 722738080
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       2 |
|   1 |  table access full| empx |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
   1 - sel$1 / empx@sel$1
--//逻辑读为2.

--//gdb跟踪显示:
(gdb) c
continuing.
2021/02/24 15:21:32.389548656 :kteinpscan 0x100022a
2021/02/24 15:21:32.395058076 :kteinmap 0x100022a
2021/02/24 15:21:32.406183568 :kdst_fetch 0x100022b

4.总结:
--//这些都是一些细节问题,可能在实际的工作中不不是太重要.