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