| Multi-block disk reads for index range scans? [message #473822] |
Tue, 31 August 2010 21:28  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
My understanding of DB_FILE_MULTIBLOCK_READ_COUNT parameter is that it affects only Full Table Scans and Fast Full Index Scans - all other disk retrieval is single block.
If so, then maybe I'm reading this trace incorrectly:
select /*+ first_rows */ pk
from test_join_tgt
where pk >= 0
and rownum > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.48 27.77 22368 22134 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.49 27.78 22368 22134 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 140
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT (cr=22134 pr=22368 pw=0 time=27778270 us)
0 FILTER (cr=22134 pr=22368 pw=0 time=27778261 us)
10000000 INDEX RANGE SCAN TEST_JOIN_TGT_PK (cr=22134 pr=22368 pw=0 time=20175517 us)(object id 142723)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file scattered read 699 0.63 7.96
SQL*Net message from client 1 21.96 21.96
We can see that:- The only data read is via an index range scan (single block reads)
- 22368 blocks were read from disk.
- There were only 699 round-trips to disk to retrieve those 22368.
Divide 22368 by 699 and you get EXACTLY 32. ie. My Range Scan is returning 32 blocks per visit to disk.
Not that it should matter, my DB_FILE_MULTIBLOCK_READ_COUNT is 16, not 32.
Here's a alternate example. I've changed the query to use a Fast Full Scan so that it WILL use the DB_FILE_MULTIBLOCK_READ_COUNT.
select pk
from test_join_tgt
where pk >= 0
and rownum > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.27 29.04 22167 22204 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.27 29.04 22167 22204 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 140
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT (cr=22204 pr=22167 pw=0 time=29041877 us)
0 FILTER (cr=22204 pr=22167 pw=0 time=29041869 us)
10000000 INDEX FAST FULL SCAN TEST_JOIN_TGT_PK (cr=22204 pr=22167 pw=0 time=20043860 us)(object id 142723)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 1 0.01 0.01
db file scattered read 1397 0.39 9.09
SQL*Net message from client 1 14.03 14.03
1397 visits to disk to retrieve 22167 blocks, which is pretty close to 16 blocks per round-trip. i.e. Exactly what I would expect from a full scan.
What the heck is going on with the Multi-block reads in the Range Scan?
Ross Leishman
|
|
|
|
|
|
| Re: Multi-block disk reads for index range scans? [message #473844 is a reply to message #473822] |
Wed, 01 September 2010 01:56  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Yep, that looks like it.
This after a different SQL exhibiting similar results:
select name,value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv('sid')
and n.name like '%prefetch%'
order by name;
index crx upgrade (prefetch) 0
physical reads cache prefetch 28202
physical reads prefetch warmup 28172
prefetch clients - 16k 0
...
Looks like that physical reads prefetch warmup is the key.
I hadn't noticed that my blocks were showing up as SCATTERED reads instead of SEQUENTIAL - as would be normal for an index scan.
It seems that there is some internal Oracle cleverness that identifies that the Buffer Cache is empty (I flushed it) and that my SQL will use a large proportion of the index, and pre-fetches the entire index into the Buffer Cache.
I wonder if there are any undocumented hints that allow you to control this caching manually...
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
