[20210301]為什麼邏輯讀這麼多.txt
[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函式,連結查詢沒有結果.
--// 站點好像無法使用.
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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2760150/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- [20180410]為什麼2個邏輯讀不一樣.txt
- 邏輯迴歸為什麼使用sigmod邏輯迴歸
- 在Oracle中，什麼是物理讀和邏輯讀？Oracle
- 01-什麼是邏輯？
- Scrapy中傳送請求的固定邏輯?為什麼要這樣寫?
- [20200326]為什麼選擇這個索引.txt索引
- [20231124]奇怪的高邏輯讀4.txt
- [20230216]奇怪的高邏輯讀3.txt
- [20210224]fetch r=0算邏輯讀嗎.txt
- [20210220]gdb跟蹤邏輯讀2.txt
- 為什麼會有這麼多的程式語言？
- 02-邏輯學有什麼用？
- java短路邏輯運算子是什麼Java
- [20220216]為什麼出現這樣的情況.txt
- [20210219]全表掃描邏輯讀問題.txt
- 為什麼“敏捷”會浪費這麼多時間？ - Reddit敏捷
- 幽默：什麼是業務邏輯程式碼？
- 哪有那麼多為什麼？
- 為什麼讀書？
- ICLR 2020 | 模型引數這麼多，泛化能力為什麼還能這麼強？ICLR模型
- 程式語言這麼多，為什麼建議選擇Python?Python
- MySQL:為什麼lsof會看到這麼多臨時檔案MySql
- Nginx 為什麼這麼快？Nginx
- Redis為什麼這麼快？Redis
- 為什麼 Python 這麼慢？Python
- 為什麼Python這麼慢？Python
- 為什麼Julia這麼快？
- 為什麼前端這麼多人前端
- 文字編輯工具那麼多，運維為什麼要學vi/vim？運維
- Python是什麼?為什麼這麼搶手?Python
- python有什麼特性？為什麼這麼火？Python
- [20210902]為什麼會使用多個共享記憶體段.txt記憶體
- 計算機程式的思維邏輯 (20) – 為什麼要有抽象類？計算機抽象
- 什麼是物理畫素和邏輯畫素？
- [20220331]為什麼不使用索引.txt索引
- [20201203]為什麼不使用索引.txt索引
- [20181015]為什麼是3秒.txt