oracle 索引快速全扫描,[20210220]全索引扫描快速索引扫描的逻辑读.txt

最新推荐文章于 2021-04-14 17:28:39 发布

瑶质

最新推荐文章于 2021-04-14 17:28:39 发布

阅读量100

点赞数

文章标签： oracle 索引快速全扫描

[20210220]全索引扫描快速索引扫描的逻辑读.txt

--//昨天测试了表扫描逻辑读问题,今天测试看看全索引扫描快速索引扫描的逻辑读.

1.环境:

SYS@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

SYS@book> alter system set "_trace_pin_time"=1 scope=spfile;

System altered.

--//重启数据库.

SYS@book> @ hide _trace_pin

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD

--------------- ------------------------------------ ------------- ------------- ------------ ----- ---------

_trace_pin_time trace how long a current pin is held FALSE 1 1 FALSE FALSE

2.测试INDEX FULL SCAN:

SCOTT@book> alter session set "_serial_direct_read"=never;

Session altered.

SCOTT@book> alter session set statistics_level = all;

Session altered.

SCOTT@book> select count(*) from emp;

COUNT(*)

----------

Plan hash value: 2937609675

-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1 (100)| | 1 |00:00:00.01 | 1 |

| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 1 |

| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 |

-------------------------------------------------------------------------------------------------------------

--//跟踪发现如下:

*** 2021-02-20 08:27:27.735

pin qeilwhrp: qeilbk dba 0x100009b:1 time 2720838425

3.测试INDEX FAST FULL SCAN:

SCOTT@book> select /*+ index_ffs(emp) */count(*) from emp;

COUNT(*)

----------

Plan hash value: 1006289799

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 2 (100)| | 1 |00:00:00.01 | 3 |

| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 3 |

| 2 | INDEX FAST FULL SCAN| PK_EMP | 1 | 14 | 2 (0)| 00:00:01 | 14 |00:00:00.01 | 3 |

------------------------------------------------------------------------------------------------------------------

--//跟踪发现如下:

*** 2021-02-20 08:28:53.366

pin ktewh26: kteinpscan dba 0x100009a:4 time 2806469226

pin ktewh26: kteinpscan dba 0x100009a:4 time 2806469311

pin kdiwh100: kdircys dba 0x100009b:1 time 2806469343

4.gdb无法跟踪的问题:

(gdb) b kdircys

Function "kdircys" not defined.

Make breakpoint pending on future shared library load? (y or [n]) y

Breakpoint 1 (kdircys) pending.

(gdb) b qeilbk

Function "qeilbk" not defined.

Make breakpoint pending on future shared library load? (y or [n]) y

Breakpoint 2 (qeilbk) pending.

--//出现Make breakpoint pending on future shared library load?,如果选择Y.也无法跟踪到断点.

5.看看唯一索引的情况:

SCOTT@book> select * from emp where empno=7369;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

Plan hash value: 2949544139

--------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 2 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 38 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 |

--------------------------------------------------------------------------------------------------------------------------------

--//跟踪没有任何信息.

6.看看rowid探查的情况:

SCOTT@book> select * from emp where rowid='AAAVREAAEAAAACXAAA';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

Plan hash value: 1116584662

-----------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 |

| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 1 | 38 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |

-----------------------------------------------------------------------------------------------------------------------------

*** 2021-02-20 08:37:31.503

pin kdswh05: kdsgrp dba 0x1000097:1 time 3324606823

7.最后看看快速索引扫描的情况.

SCOTT@book> create table t as select * from all_objects;

Table created.

SCOTT@book> create index i_t_owner_obj_name on t (owner,object_name) ;

Index created.

SCOTT@book> @ seg i_t_owner_obj_name

SCOTT@book> @prxx

==============================

SEG_MB : 4

SEG_OWNER : SCOTT

SEG_SEGMENT_NAME : I_T_OWNER_OBJ_NAME

SEG_PARTITION_NAME :

SEG_SEGMENT_TYPE : INDEX

SEG_TABLESPACE_NAME : USERS

BLOCKS : 512

HDRFIL : 4

HDRBLK : 1986

PL/SQL procedure successfully completed.

--//分析略.

SCOTT@book> select count(*) from t;

COUNT(*)

----------

84774

Plan hash value: 1196567590

------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 135 (100)| | 1 |00:00:00.06 | 501 |

| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.06 | 501 |

| 2 | INDEX FAST FULL SCAN| I_T_OWNER_OBJ_NAME | 1 | 84774 | 135 (0)| 00:00:02 | 84774 |00:00:00.06 | 501 |

------------------------------------------------------------------------------------------------------------------------------

$ grep ^pin /tmp/ab.txt |wc

501 3507 26557

$ grep -n -C1 kteinmap /tmp/ab.txt

2-pin ktewh26: kteinpscan dba 0x10007c2:4 time 148494304

3:pin ktewh27: kteinmap dba 0x10007c2:4 time 148499972

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4-pin ktewh26: kteinpscan dba 0x10007c2:4 time 148505557

5:pin ktewh27: kteinmap dba 0x10007c2:4 time 148511168

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

6:pin ktewh27: kteinmap dba 0x10007c2:4 time 148516498

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

7-pin kdiwh100: kdircys dba 0x10007c3:1 time 148516571

79-pin kdiwh100: kdircys dba 0x1000d97:1 time 148520913

80:pin ktewh27: kteinmap dba 0x10007c2:4 time 148526353

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

81-pin kdiwh100: kdircys dba 0x1000d01:1 time 148526407

--//一共4次,有点与全表扫描不同的是集中在开始要调用2次kteinpscan.kteinmap.

--//另外你查看select /*+ index_ffs(emp) */count(*) from emp;

*** 2021-02-20 08:28:53.366

pin ktewh26: kteinpscan dba 0x100009a:4 time 2806469226

pin ktewh26: kteinpscan dba 0x100009a:4 time 2806469311

pin kdiwh100: kdircys dba 0x100009b:1 time 2806469343

--//猜测这个跟全表扫描类似,如果Auxillary Map仅仅1个Extent时,全索引快速扫描不会将出现kteinmap计入逻辑读.

--//0x10007c2 = set dba 4,1986 = alter system dump datafile 4 block 1986 = 16779202

SCOTT@book> alter system dump datafile 4 block 1986 ;

System altered.

--//INDEX FAST FULL SCAN类似将索引当作表的全扫描.开始连续2次kteinpscan,kteinmap.

--//转储 Auxillary Map部分如下:

Auxillary Map

--------------------------------------------------------

Extent 0 : L1 dba: 0x010007c0 Data dba: 0x010007c3

Extent 1 : L1 dba: 0x010007c0 Data dba: 0x010007c8

Extent 2 : L1 dba: 0x010007d0 Data dba: 0x010007d1

Extent 3 : L1 dba: 0x010007d0 Data dba: 0x010007d8

Extent 4 : L1 dba: 0x010007e0 Data dba: 0x010007e1

Extent 5 : L1 dba: 0x010007e0 Data dba: 0x010007e8

Extent 6 : L1 dba: 0x010007f0 Data dba: 0x010007f1

Extent 7 : L1 dba: 0x010007f0 Data dba: 0x010007f8

Extent 8 : L1 dba: 0x01000d88 Data dba: 0x01000d89

Extent 9 : L1 dba: 0x01000d88 Data dba: 0x01000d90

------------------------------------------------->读到0x1000d97后出现kteinmap.

Extent 10 : L1 dba: 0x01000d00 Data dba: 0x01000d01

Extent 11 : L1 dba: 0x01000d00 Data dba: 0x01000d08

Extent 12 : L1 dba: 0x01000d10 Data dba: 0x01000d11

Extent 13 : L1 dba: 0x01000d10 Data dba: 0x01000d18

Extent 14 : L1 dba: 0x01000d20 Data dba: 0x01000d21

Extent 15 : L1 dba: 0x01000d20 Data dba: 0x01000d28

Extent 16 : L1 dba: 0x01000e00 Data dba: 0x01000e02

Extent 17 : L1 dba: 0x01000e80 Data dba: 0x01000e82

Extent 18 : L1 dba: 0x01000f00 Data dba: 0x01000f02

--------------------------------------------------------

--//1次调用kteinmap读10个Extent.

8.最后看看索引全扫描的情况.

SCOTT@book> select /*+ INDEX_ASC(t) */ count(*) from t;

COUNT(*)

----------

84774

Plan hash value: 2053645701

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 495 (100)| | 1 |00:00:00.03 | 494 |

| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.03 | 494 |

| 2 | INDEX FULL SCAN| I_T_OWNER_OBJ_NAME | 1 | 84774 | 495 (1)| 00:00:06 | 84774 |00:00:00.03 | 494 |

-------------------------------------------------------------------------------------------------------------------------

$ grep ^pin /tmp/ac.txt |wc

492 3444 25585

--//奇怪存在2个差距.索引全扫描是单块读,cost有点高.

--//后记:我估计跟索引root节点以及叶子节点cbc采用共享拴锁模式有关.

$ head /tmp/ac.txt ; echo ;tail /tmp/ac.txt

*** 2021-02-20 09:13:46.572

pin qeilwhnp: qeilbk dba 0x10007c4:1 time 1204708362

pin kdiwh15: kdifxs dba 0x10007c5:1 time 1204708513

pin kdiwh15: kdifxs dba 0x10007c6:1 time 1204708586

pin kdiwh15: kdifxs dba 0x10007c7:1 time 1204708650

pin kdiwh15: kdifxs dba 0x10007c8:1 time 1204708720

pin kdiwh15: kdifxs dba 0x10007c9:1 time 1204708785

pin kdiwh15: kdifxs dba 0x10007ca:1 time 1204708849

pin kdiwh15: kdifxs dba 0x10007cb:1 time 1204708956

pin kdiwh15: kdifxs dba 0x10007cc:1 time 1204709022

pin kdiwh15: kdifxs dba 0x1000f74:1 time 1204741750

pin kdiwh15: kdifxs dba 0x1000f75:1 time 1204741818

pin kdiwh15: kdifxs dba 0x1000f76:1 time 1204741885

pin kdiwh15: kdifxs dba 0x1000f77:1 time 1204741959

pin kdiwh15: kdifxs dba 0x1000f78:1 time 1204742031

pin kdiwh15: kdifxs dba 0x1000f79:1 time 1204742105

pin kdiwh15: kdifxs dba 0x1000f7a:1 time 1204742178

pin kdiwh15: kdifxs dba 0x1000f7b:1 time 1204742250

pin kdiwh15: kdifxs dba 0x1000f7c:1 time 1204742331

pin kdiwh15: kdifxs dba 0x1000f7d:1 time 1204742422

--//扫描叶子节点使用的函数是kdifxs,与快速全索引扫描的函数kdircys不同.

9.gdb跟踪kdifxs的问题:

--//有点奇怪的是如果跟踪kdifxs,会发现每取一条记录调用1次kdifxs.

break kdifxs

set pagination off

commands

silent

shell echo -n $( date +"%Y/%m/%d %T.%N : ")

printf "kdifxs 0x%x\n", *($rdi+28)

continue

end

SCOTT@book> select /*+ INDEX_ASC(t) */ count(*) from emp;

COUNT(*)

----------

--//跟踪文件:

*** 2021-02-20 09:35:56.279

pin qeilwhrp: qeilbk dba 0x100009b:1 time 2534414893

--//gdb

2021/02/20 09:35:56.283527260 :kdifxs 0x100009b

2021/02/20 09:35:56.288943445 :kdifxs 0x100009b

2021/02/20 09:35:56.294140527 :kdifxs 0x100009b

2021/02/20 09:35:56.299311991 :kdifxs 0x100009b

2021/02/20 09:35:56.304559641 :kdifxs 0x100009b

2021/02/20 09:35:56.309751711 :kdifxs 0x100009b

2021/02/20 09:35:56.315070138 :kdifxs 0x100009b

2021/02/20 09:35:56.320309307 :kdifxs 0x100009b

2021/02/20 09:35:56.325567607 :kdifxs 0x100009b

2021/02/20 09:35:56.330755128 :kdifxs 0x100009b

2021/02/20 09:35:56.336053584 :kdifxs 0x100009b

2021/02/20 09:35:56.341249687 :kdifxs 0x100009b

2021/02/20 09:35:56.346460461 :kdifxs 0x100009b

2021/02/20 09:35:56.351713932 :kdifxs 0x100009b

2021/02/20 09:35:56.356859085 :kdifxs 0x100009b

--//一共15行.

--//我修改如下:

set var a=0x0

break kdifxs if *($rdi+28) != a

set pagination off

commands

silent

shell echo -n $( date +"%Y/%m/%d %T.%N : ")

printf " kdifxs 0x%x\n", *($rdi+28)

set var a= *($rdi+28)

continue

end

--//注意这样不能作为逻辑读统计.注我不熟悉gdb的调式.

SCOTT@book> set arraysize 2

SCOTT@book> select /*+ INDEX_ASC(t) */ empno from emp;

*** 2021-02-20 10:01:02.141

pin qeilwhrp: qeilbk dba 0x100009b:1 time 4040276637

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040278254

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040279670

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040280965

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040282210

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040283446

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040284689

pin kdiwh16: kdifxs dba 0x100009b:1 time 4040285942

--//逻辑读8.

--//gdb下仅仅看到1行.

(gdb) c

Continuing.

2021/02/20 10:04:23.791914398 :kdifxs 0x100009b

10.最后看看索引范围扫描:

SCOTT@book> set arraysize 400

SCOTT@book> select /*+ index(t) */ CREATED from t where owner='SCOTT';

CREATED

-------------------

2013-08-24 12:04:21

...

2017-01-03 11:41:11

22 rows selected.

Plan hash value: 3696261165

--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 2237 (100)| | 22 |00:00:00.01 | 18 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2923 | 40922 | 2237 (0)| 00:00:27 | 22 |00:00:00.01 | 18 |

|* 2 | INDEX RANGE SCAN | I_T_OWNER_OBJ_NAME | 1 | 2923 | | 19 (0)| 00:00:01 | 22 |00:00:00.01 | 4 |

--------------------------------------------------------------------------------------------------------------------------------------------

*** 2021-02-20 10:08:47.319

pin qeilwhnp: qeilbk dba 0x1000e8f:1 time 210487381

pin kdswh05: kdsgrp dba 0x100074c:1 time 210487471

pin kdiwh16: kdifxs dba 0x1000e8f:1 time 210487812

pin kdswh05: kdsgrp dba 0x1000755:1 time 210487858

pin kdswh05: kdsgrp dba 0x100074c:1 time 210487892

pin kdswh05: kdsgrp dba 0x1000755:1 time 210487914

pin kdswh05: kdsgrp dba 0x100074c:1 time 210487932

pin kdswh05: kdsgrp dba 0x1000755:1 time 210487951

pin kdswh05: kdsgrp dba 0x100074d:1 time 210487971

pin kdswh05: kdsgrp dba 0x1000755:1 time 210487988

pin kdswh05: kdsgrp dba 0x100074c:1 time 210488006

pin kdswh05: kdsgrp dba 0x1000755:1 time 210488026

pin kdswh05: kdsgrp dba 0x100074c:1 time 210488044

pin kdswh05: kdsgrp dba 0x100074d:1 time 210488069

pin kdswh05: kdsgrp dba 0x100074c:1 time 210488089

pin kdswh05: kdsgrp dba 0x1000755:1 time 210488107

--//逻辑读16.还是少2个.

--//后记:我估计跟索引root节点以及叶子节点cbc采用共享拴锁模式有关.

SCOTT@book> alter system reset "_trace_pin_time";

System altered.

--//重启,不然下面的跟踪信息太乱了.

SCOTT@book> @ treedump i_t_owner_obj_name

old 1: select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX'

new 1: select object_id from user_objects where object_name = upper('i_t_owner_obj_name') and object_type = 'INDEX'

OBJECT_ID

----------

90544

old 1: alter session set events 'immediate trace name treedump level &m_index_id'

new 1: alter session set events 'immediate trace name treedump level 90544'

Session altered.

----- begin tree dump

branch: 0x10007c3 16779203 (0: nrow: 2, level: 2)

branch: 0x1000efb 16781051 (-1: nrow: 363, level: 1)

--//应该是漏掉这2个块的逻辑读.检索找不到这两块的读操作.

leaf: 0x10007c4 16779204 (-1: nrow: 157 rrow: 157)

leaf: 0x10007c5 16779205 (0: nrow: 159 rrow: 159)

leaf: 0x10007c6 16779206 (1: nrow: 148 rrow: 148)

leaf: 0x10007c7 16779207 (2: nrow: 157 rrow: 157)

leaf: 0x10007c8 16779208 (3: nrow: 154 rrow: 154)

leaf: 0x10007c9 16779209 (4: nrow: 158 rrow: 158)

...

leaf: 0x1000f78 16781176 (122: nrow: 187 rrow: 187)

leaf: 0x1000f79 16781177 (123: nrow: 195 rrow: 195)

leaf: 0x1000f7a 16781178 (124: nrow: 185 rrow: 185)

leaf: 0x1000f7b 16781179 (125: nrow: 227 rrow: 227)

leaf: 0x1000f7c 16781180 (126: nrow: 221 rrow: 221)

leaf: 0x1000f7d 16781181 (127: nrow: 61 rrow: 61)

----- end tree dump

11.总结:

--//测试有点乱,基本想到哪里做到哪里.

--//使用"_trace_pin_time"=1探究全表扫描以及快速全索引扫描逻辑读还是很准确的,索引一些逻辑读存在一些误差.

--//kteinmap 调用实际上跟全表扫描类似.不知道是为什么快速全扫描开始出现2次kteinpscan,extents多的话还会出现2次kteinmap.

--//gdb无法跟踪qeilbk,kdircys, 不知道是为什么快速全扫描开始出现2次.

瑶质

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 索引快速全扫描,[20210220]全索引扫描快速索引扫描的逻辑读.txt

[20210220]全索引扫描快速索引扫描的逻辑读.txt--//昨天测试了表扫描逻辑读问题,今天测试看看全索引扫描快速索引扫描的逻辑读.1.环境:SYS@book> @ver1PORT_STRING VERSION BANNER------------------------------ -------------- ----------...
复制链接

扫一扫