[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.总结: --//这些都是一些细节问题,可能在实际的工作中不不是太重要.