[20210301]为什么逻辑读这么多.txt

来源：这里教程网时间：2026-03-03 16:30:04 作者：

[20210301]为什么逻辑读这么多.txt --//重新看链接http://blog.itpub.net/267265/viewspace-2152011/=>[20180319]直接路径读特例12c.txt --//我以为自己终于知道为什么当时rowid between的逻辑读是5.主要在于读段头多次. --//实际上的测试还是颠覆我对该问题的看法,或者讲还是不理解为什么oracle要这样操作. 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 create table t as select * from all_objects,(select 1 from dual connect by level<=4) ; create index i_t_object_id on t(object_id); --//分析略 SYS@book> alter system set "_trace_pin_time"=1 scope=spfile; System altered. --//重启数据库. 2.测试: SCOTT@book> alter session set statistics_level=all; Session altered. SCOTT@book> alter session set "_serial_direct_read"=never; Session altered. SCOTT@book> select rowid ,owner from t where object_id =42; ROWID OWNER ------------------ ------ AAAWGSAAEAAAAIlAAo SYS AAAWGSAAEAAAAIlAAp SYS AAAWGSAAEAAAAIlAAq SYS AAAWGSAAEAAAAIlAAr SYS SCOTT@book> select rowid ,owner,object_id from t where rowid between 'AAAWGSAAEAAAAIlAAo' and 'AAAWGSAAEAAAAIlAAp'; ROWID OWNER OBJECT_ID ------------------ ------ ---------- AAAWGSAAEAAAAIlAAo SYS 42 AAAWGSAAEAAAAIlAAp SYS 42 Plan hash value: 280204748 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 1391 (100)| | 2 |00:00:00.01 | 10 | |* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 56 | 2352 | 1391 (1)| 00:00:17 | 2 |00:00:00.01 | 10 | ------------------------------------------------------------------------------------------------------------------------------ --//共10个逻辑读,看看跟踪文件读了那些文件呢? --//跟踪看到的情况: *** 2021-03-01 09:30:07.740 pin ktewh25: kteinicnt dba 0x1000222:4 time 2396795371 pin ktewh26: kteinpscan dba 0x1000222:4 time 2396795454 pin ktewh27: kteinmap dba 0x1000222:4 time 2396795474 pin kdswh11: kdst_fetch dba 0x1000225:1 time 2396795503 pin kdswh11: kdst_fetch dba 0x1000225:1 time 2396795978 pin ktewh27: kteinmap dba 0x1000222:4 time 2396796042 pin ktewh27: kteinmap dba 0x1000222:4 time 2396796065 pin ktewh27: kteinmap dba 0x1000222:4 time 2396796080 pin ktewh27: kteinmap dba 0x1000222:4 time 2396796094 pin ktewh27: kteinmap dba 0x1000222:4 time 2396796107 --//0x1000222 = set dba 4,546 = alter system dump datafile 4 block 546 = 16777762 --//kteinmap 读了6次. SCOTT@book> select * from dba_segments where owner=user and segment_name='T' 2 @ prxx ============================== OWNER : SCOTT SEGMENT_NAME : T PARTITION_NAME : SEGMENT_TYPE : TABLE SEGMENT_SUBTYPE : ASSM TABLESPACE_NAME : USERS HEADER_FILE : 4 HEADER_BLOCK : 546 BYTES : 41943040 BLOCKS : 5120 EXTENTS : 55 INITIAL_EXTENT : 65536 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 MAX_SIZE : 2147483645 RETENTION : MINRETENTION : PCT_INCREASE : FREELISTS : FREELIST_GROUPS : RELATIVE_FNO : 4 BUFFER_POOL : DEFAULT FLASH_CACHE : DEFAULT CELL_FLASH_CACHE : DEFAULT PL/SQL procedure successfully completed. SYS@book> alter system dump datafile 4 block 546; System altered. Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x01000220 Data dba: 0x01000223 Extent 1 : L1 dba: 0x01000220 Data dba: 0x01000230 Extent 2 : L1 dba: 0x010002a0 Data dba: 0x010002a1 Extent 3 : L1 dba: 0x010002a0 Data dba: 0x010002a8 Extent 4 : L1 dba: 0x010002b0 Data dba: 0x010002b1 Extent 5 : L1 dba: 0x010002b0 Data dba: 0x010002b8 Extent 6 : L1 dba: 0x010002c0 Data dba: 0x010002c1 Extent 7 : L1 dba: 0x010002c0 Data dba: 0x010002c8 Extent 8 : L1 dba: 0x010002d0 Data dba: 0x010002d1 Extent 9 : L1 dba: 0x010002d0 Data dba: 0x010002d8 Extent 10 : L1 dba: 0x010002e0 Data dba: 0x010002e1 Extent 11 : L1 dba: 0x010002e0 Data dba: 0x010002e8 Extent 12 : L1 dba: 0x010002f0 Data dba: 0x010002f1 Extent 13 : L1 dba: 0x010002f0 Data dba: 0x010002f8 Extent 14 : L1 dba: 0x01000d88 Data dba: 0x01000d89 Extent 15 : L1 dba: 0x01000d88 Data dba: 0x01000d90 Extent 16 : L1 dba: 0x01000300 Data dba: 0x01000302 Extent 17 : L1 dba: 0x01000380 Data dba: 0x01000382 Extent 18 : L1 dba: 0x01000400 Data dba: 0x01000402 Extent 19 : L1 dba: 0x01000480 Data dba: 0x01000482 Extent 20 : L1 dba: 0x01000500 Data dba: 0x01000502 Extent 21 : L1 dba: 0x01000580 Data dba: 0x01000582 Extent 22 : L1 dba: 0x01000600 Data dba: 0x01000602 Extent 23 : L1 dba: 0x01000680 Data dba: 0x01000682 Extent 24 : L1 dba: 0x01000700 Data dba: 0x01000702 Extent 25 : L1 dba: 0x01000780 Data dba: 0x01000782 Extent 26 : L1 dba: 0x01000800 Data dba: 0x01000802 Extent 27 : L1 dba: 0x01000880 Data dba: 0x01000882 Extent 28 : L1 dba: 0x01000900 Data dba: 0x01000902 Extent 29 : L1 dba: 0x01000a00 Data dba: 0x01000a02 Extent 30 : L1 dba: 0x01000a80 Data dba: 0x01000a82 Extent 31 : L1 dba: 0x01000b00 Data dba: 0x01000b02 Extent 32 : L1 dba: 0x01000b80 Data dba: 0x01000b82 Extent 33 : L1 dba: 0x01000c00 Data dba: 0x01000c02 Extent 34 : L1 dba: 0x01000c80 Data dba: 0x01000c82 Extent 35 : L1 dba: 0x01000d00 Data dba: 0x01000d02 Extent 36 : L1 dba: 0x01000e00 Data dba: 0x01000e02 Extent 37 : L1 dba: 0x01000e80 Data dba: 0x01000e82 Extent 38 : L1 dba: 0x01000f00 Data dba: 0x01000f02 Extent 39 : L1 dba: 0x01000f80 Data dba: 0x01000f82 Extent 40 : L1 dba: 0x01001000 Data dba: 0x01001002 Extent 41 : L1 dba: 0x01001080 Data dba: 0x01001082 Extent 42 : L1 dba: 0x01001100 Data dba: 0x01001102 Extent 43 : L1 dba: 0x01001180 Data dba: 0x01001182 Extent 44 : L1 dba: 0x01001200 Data dba: 0x01001202 Extent 45 : L1 dba: 0x01001280 Data dba: 0x01001282 Extent 46 : L1 dba: 0x01001300 Data dba: 0x01001302 Extent 47 : L1 dba: 0x01001380 Data dba: 0x01001382 Extent 48 : L1 dba: 0x01001400 Data dba: 0x01001402 Extent 49 : L1 dba: 0x01001480 Data dba: 0x01001482 Extent 50 : L1 dba: 0x01001500 Data dba: 0x01001502 Extent 51 : L1 dba: 0x01001580 Data dba: 0x01001582 Extent 52 : L1 dba: 0x01001600 Data dba: 0x01001602 Extent 53 : L1 dba: 0x01001680 Data dba: 0x01001682 Extent 54 : L1 dba: 0x01001700 Data dba: 0x01001702 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01000221 --//你可以发现读了6次Extent,与我前面分析正确,分6次读取Auxillary Map,每次读10个Extent. --//kteinicnt,kteinpscan 2次我就不清楚了. 3.继续测试: --//前面忘了分析表.分析后测试: SCOTT@book> select rowid ,owner,object_id from t where rowid between 'AAAWGSAAEAAAAIlAAr' and 'AAAWGSAAEAAAAIlAAs'; ROWID OWNER OBJECT_ID ------------------ ------ ---------- AAAWGSAAEAAAAIlAAr SYS 42 AAAWGSAAEAAAAIlAAs SYS 55 Plan hash value: 280204748 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 1391 (100)| | 2 |00:00:00.01 | 9 | |* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 848 | 19504 | 1391 (1)| 00:00:17 | 2 |00:00:00.01 | 9 | ------------------------------------------------------------------------------------------------------------------------------ --//分析后少了1次. SCOTT@book> @ rowid AAAWGSAAEAAAAIlAAs OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 90514 4 549 44 0x1000225 4,549 alter system dump datafile 4 block 549 ; *** 2021-03-01 09:51:47.692 pin ktewh26: kteinpscan dba 0x1000222:4 time 3696747000 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747079 pin kdswh11: kdst_fetch dba 0x1000225:1 time 3696747113 pin kdswh11: kdst_fetch dba 0x1000225:1 time 3696747527 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747578 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747598 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747612 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747626 pin ktewh27: kteinmap dba 0x1000222:4 time 3696747640 --//少了kteinpscan --//本来想查询kteinicnt,kteinpscan函数,链接https://www.modb.pro/oraFunc查询没有结果. --//http://orafun.info/ 站点好像无法使用. 4.继续测试: create table t1 as select * from all_objects ; create index i_t1_object_id on t1(object_id); insert into t1 select * from t1; commit ; --//分析略. SCOTT@book> select rowid ,owner,object_id from t1 where object_id between 1 and 4; ROWID OWNER OBJECT_ID ------------------ ------ ---------- AAAWGUAAEAAABebAAw SYS 2 AAAWGUAAEAAAB/wAAw SYS 2 AAAWGUAAEAAABebAAF SYS 3 AAAWGUAAEAAAB/wAAF SYS 3 AAAWGUAAEAAABebAAx SYS 4 AAAWGUAAEAAAB/wAAx SYS 4 6 rows selected. SCOTT@book> select rowid ,owner,object_id from t1 where rowid between 'AAAWGUAAEAAABebAAx' and 'AAAWGUAAEAAABebAAy'; ROWID OWNER OBJECT_ID ------------------ ------ ---------- AAAWGUAAEAAABebAAx SYS 4 AAAWGUAAEAAABebAAy SYS 31 *** 2021-03-01 09:56:14.070 pin ktewh26: kteinpscan dba 0x100179a:4 time 3963125446 pin ktewh27: kteinmap dba 0x100179a:4 time 3963125514 pin kdswh11: kdst_fetch dba 0x100179b:1 time 3963125547 pin kdswh11: kdst_fetch dba 0x100179b:1 time 3963125916 pin ktewh27: kteinmap dba 0x100179a:4 time 3963125964 pin ktewh27: kteinmap dba 0x100179a:4 time 3963125984 pin ktewh27: kteinmap dba 0x100179a:4 time 3963125999 pin ktspfwh8: ktspScanInit1 dba 0x1002581:8 time 3963126015 pin ktspswh6: ktspInitScan dba 0x1001799:9 time 3963126064 pin ktspfwh13: ktspGetNextL1ForScan dba 0x1002680:8 time 3963126079 pin ktspfwh13: ktspGetNextL1ForScan dba 0x1002681:8 time 3963126094 pin ktspfwh21: ktspfhsd dba 0x100179a:4 time 3963126108 Plan hash value: 1216763554 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 691 (100)| | 2 |00:00:00.01 | 12 | |* 1 | TABLE ACCESS BY ROWID RANGE| T1 | 1 | 2 | 46 | 691 (1)| 00:00:09 | 2 |00:00:00.01 | 12 | ------------------------------------------------------------------------------------------------------------------------------ --//逻辑读反而增加,可以发现我前面没有遇到的函数. --//我前面建立的表是ctas插入的,而该表T1 有部分信息采用insert插入. --//一些细节不再探究.