RE: Full table scan -- uniform extent allocation - extent allocation map issue?
Date: Tue, 26 Nov 2019 17:19:05 +0000
Message-ID: <MWHPR19MB0141D61A9E649B92A269C4909B450_at_MWHPR19MB0141.namprd19.prod.outlook.com>
WAIT #139711646963136: nam='db file scattered read' ela= 1941 file#=5 block#=44736 blocks=64 obj#=75252 tim=248445270874 WAIT #139711646963136: nam='db file scattered read' ela= 1788 file#=5 block#=44802 blocks=62 obj#=75252 tim=248445273696 WAIT #139711646963136: nam='db file scattered read' ela= 1902 file#=5 block#=44864 blocks=64 obj#=75252 tim=248445276573 WAIT #139711646963136: nam='db file scattered read' ela= 1756 file#=5 block#=44930 blocks=62 obj#=75252 tim=248445279335....
WAIT #139711646963136: nam='db file scattered read' ela= 3078 file#=5 block#=132 blocks=128 obj#=75252 tim=248445887737 WAIT #139711646963136: nam='db file scattered read' ela= 3045 file#=5 block#=260 blocks=124 obj#=75252 tim=248445893065 WAIT #139711646963136: nam='db file scattered read' ela= 3399 file#=5 block#=384 blocks=128 obj#=75252 tim=248445898641....
...
WAIT #139711646963136: nam='db file scattered read' ela= 12269 file#=5 block#=97540 blocks=124 obj#=75252 tim=248446182677 WAIT #139711646963136: nam='db file scattered read' ela= 17440 file#=5 block#=97664 blocks=128 obj#=75252 tim=248446202405 WAIT #139711646963136: nam='db file scattered read' ela= 3282 file#=5 block#=97792 blocks=128 obj#=75252 tim=248446208027...
...
WAIT #139711646963136: nam='db file scattered read' ela= 3279 file#=5 block#=61058 blocks=128 obj#=75252 tim=248446281696 WAIT #139711646963136: nam='db file scattered read' ela= 3115 file#=5 block#=61186 blocks=126 obj#=75252 tim=248446287141 WAIT #139711646963136: nam='db file scattered read' ela= 2990 file#=5 block#=61312 blocks=128 obj#=75252 tim=248446292359 WAIT #139711646963136: nam='db file scattered read' ela= 3109 file#=5 block#=61440 blocks=128 obj#=75252 tim=248446297711....
...
WAIT #139711646963136: nam='db file scattered read' ela= 3125 file#=5 block#=7812 blocks=128 obj#=75252 tim=248446303109 WAIT #139711646963136: nam='db file scattered read' ela= 3246 file#=5 block#=7940 blocks=124 obj#=75252 tim=248446309728 WAIT #139711646963136: nam='db file scattered read' ela= 3180 file#=5 block#=8064 blocks=128 obj#=75252 tim=248446315169
the L1 DBA extent map:
Auxillary Map
Extent 0 : L1 dba: 0x01411880 Data dba: 0x01411883 Extent 1 : L1 dba: 0x01411880 Data dba: 0x01411888....
Extent 35 : L1 dba: 0x0140ae80 Data dba: 0x0140ae82 Extent 36 : L1 dba: 0x0140af00 Data dba: 0x0140af02 Extent 37 : L1 dba: 0x0140af80 Data dba: 0x0140af82...
Extent 38 : L1 dba: 0x0140b000 Data dba: 0x0140b002 Extent 39 : L1 dba: 0x0140b080 Data dba: 0x0140b082 Extent 40 : L1 dba: 0x0140b100 Data dba: 0x0140b102 Extent 41 : L1 dba: 0x0140b180 Data dba: 0x0140b182 Extent 42 : L1 dba: 0x0140b200 Data dba: 0x0140b202 Extent 43 : L1 dba: 0x0140b280 Data dba: 0x0140b282...
...
Extent 92 : L1 dba: 0x01417c80 Data dba: 0x01417c84 Extent 93 : L1 dba: 0x01418080 Data dba: 0x01418084 ...
Extent 94 : L1 dba: 0x0140ee80 Data dba: 0x0140ee82 Extent 95 : L1 dba: 0x01401e80 Data dba: 0x01401e84 Extent 96 : L1 dba: 0x01402280 Data dba: 0x01402284 Extent 97 : L1 dba: 0x01402680 Data dba: 0x01402684 Extent 98 : L1 dba: 0x01402a80 Data dba: 0x01402a84
When it comes to index range scan things are as expected, as the rowids are sorted it reads all the blocks in one arc (i am not talking about prefetching, or batched (db file parallel read which exploits IOPS at a storage layer but a very simplistic case with all prefetching etc turned off) but for full table scans things appear to be different as it simply following the extent map and not in an order of their L1 DBA,
maybe this can be explained by something, but I am still not be able to figure out any other valid reason as to why we should not perform a full table scan based on the storage order extent map with the exception of last extent. Please let me know if I have missed anything?
Thanks,
Vishnu
--
http://www.freelists.org/webpage/oracle-l<https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7Cclay.jackson%40quest.com%7C5f05abd5c0894eb59b0108d7728e1318%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637103826849761206&sdata=gFpqKs1cKbH7KPEQPvX41RzD%2BtvxBcqpDXhH6wy%2BK3w%3D&reserved=0>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 26 2019 - 18:19:05 CET
- image/png attachment: image001.png