ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20210301]为什么逻辑读这么多.txt

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

原创 Oracle 作者：lfree 时间：2021-03-01 10:08:37 1 删除 编辑

[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插入.
--//一些细节不再探究.

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2760150/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210301]第2行插入Y.txt
下一篇： [20210301]延迟显示输出.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4014
  • 访问量
    7991010

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593