[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.總結:
--//這些都是一些細節問題,可能在實際的工作中不不是太重要.