Just a little note on two index operations that you might see in an execution plan that are worth highlighting because it’s so easy to miss just one critical word in the name of the operation.
Index Full Scan: walks down the branch blocks to the first (lowest key) leaf block, and then walks the index in key order, typically reading one leaf block at a time, and using the leaf pointers to get from leaf block to leaf block. This operation can be hinted to work in descending order through the index.
Index Fast Full Scan: goes to the first block of the segment, and does multi-block reads through the segment, picking up branch and leaf blocks, discarding the branches and using the data in the leaf blocks as if they were skinny tables. Does not return the data in order.
The index full scan typically uses db file sequential reads to get data from disk, although newer versions of Oracle can do db file parallel reads which are read requests for multiple Oracle blocks that are not adjacent blocks in the Oracle data files.
The index fast full scan typically uses db file scattered reads to get data from disk and the indexes are then subject to all the details of the special treatment for tablescans.
Oracle Scratchpad 
index full scan typically uses db file sequential reads to get data from disk, although newer versions of Oracle can do db file parallel reads
Interesting – does it mean that Oracle reads the branch blocks also, and requests from disk, in parallel, the leaf/branch blocks pointed by the “current” branch block(s) ? Thanks in advance …
Comment by Alberto Dell'Era — December 15, 2006 @ 10:08 pm GMT Dec 15,2006 |
Alberto, that’s my assumption at present, thought not proved. You can also see this happening with nested loop joins; and you may also see db file scattered reads if some of the leaf blocks are coincidentally adjacent.
Comment by Jonathan Lewis — December 15, 2006 @ 10:32 pm GMT Dec 15,2006 |
What’s the difference between a Index Range Scan and a Index Full Scan?
They both search the lowest key in the non-branch blocks and then go down. The only difference I can imagine: The Range Scan stops when he founds a certain value, as the Full Scan goes until the end. Am I right?
Comment by Wolfgang — December 15, 2006 @ 10:45 pm GMT Dec 15,2006 |
Wolfgang, my best guess matches yours. For a range scan the optimizer calculates a start (frofkks) and stop (frofkke) key – for a full scan it doesn’t need to do any such calculation. Apart from the boundary effect, the run-time mechanics are probably identical.
Comment by Jonathan Lewis — December 15, 2006 @ 10:52 pm GMT Dec 15,2006 |
I wonder if a Range Scan can be interrupted and restart with the next matching value.
For example: 2 tables: A table TSmall with only 3 rows containing the values C,N and X and the second table TBig containing rows with many columns and many million rows.
Now we join the two tables. As both join columns are indexed and have correct histograms the CBO assumes that the result of the join will be, let’s say 1000 rows – so he chosses an Index Range Scan. BUT: The join column of TBig contains also D,M,P etc.
So Oracle starts at the root block and goes down until the first C value, the it reads until the first D – and now it gets interesting. Will oracle stop here, go back to the root block and find the first N, and so doesn’t have to read hundreds of thousands D,E,F,… blocks?
Comment by Wolfgang — December 15, 2006 @ 11:10 pm GMT Dec 15,2006 |
Wolfgang, unless I’ve misunderstood your question, you are talking about a nested loop join; in which case your example has to process three separate range scans on the second index – one for each row in the first row source.
Comment by Jonathan Lewis — December 15, 2006 @ 11:35 pm GMT Dec 15,2006 |
I see a lot of parallel index fast full scans (direct path read) myself, and logically they’re very amenable to that of course.
Comment by David Aldridge — December 15, 2006 @ 11:44 pm GMT Dec 15,2006 |
A readers request: Would it be possible to shine some light upon the usage and interpretation of the IO events such as db file sequential/scattered/parallel reads in a future blog entry or article ?
Comment by Jeroen Wilms — December 16, 2006 @ 2:02 am GMT Dec 16,2006 |
David, I forgot to mention that the fast full scan could go parallel on a non-partitioned index, with the consequential change in read mechanism that you mention. Thanks.
Jeroen, I’m sure I’ll get round to some of them when I start writing more about statspack interpretation.
Comment by Jonathan Lewis — December 16, 2006 @ 7:37 am GMT Dec 16,2006 |
Thanks Jonathan. That was exactly what I meant.
Comment by Wolfgang — December 16, 2006 @ 8:40 am GMT Dec 16,2006 |
JL: “although newer versions of Oracle can do db file parallel reads ”
Do you have an example of this? Is this done via async leaf block reading?
I hate to point this out, a co-worker specialized in SQL Server adminstration showed me this page:
http://msdn2.microsoft.com/en-us/library/ms191475.aspx
Seems like SQL Server is a bit more avanced in terms of read-ahead during index scans and full table scans.
Since Oracle’s index structure does not have information about each leaf node in the branch blocks it’s not really possible. Keeps me wondering if there’s anything else that can be done in that regard.
Comment by Christo Kutrovsky — December 21, 2006 @ 6:30 pm GMT Dec 21,2006 |
Christo, it looks like Microsoft is describing a “db file scattered read” as if it were something exciting. Note how it says the read-ahead is for contiguous blocks. Oracle started to record these as ‘prefetched blocks’ some time ago, and also reports a statistic like ‘prefetched blocks aged out before use’.
Branch blocks contain pointers to leaf blocks, so a non-contiguous (db file parallel) read is possible in a large index range scan – possibly by Oracle reading a branch block and fetching all the relevant leaf blocks indicated by the branch. It is also possible (though I have not proved it yet) that in a nested loop join, Oracle stops at the index level in the nested loop (joining first table to second index) and then pre-fetches many of the blocks from the second table – as indicated by all the rowids. This, too, can be a non-contiguous pre-fetch.
For the index effect, simply create a large table, then force Oracle to count non-indexed column with a primary key index hint. Check the 10053 trace and you will probably see a line like “index prefetching enabled” somewhere.
Comment by Jonathan Lewis — December 21, 2006 @ 7:43 pm GMT Dec 21,2006 |
There’s 2 things I particulary liked about how MS does it.
1. Full table scans share the output – that sounds interesting
2. About index scans, I notice the following things that MS *seems* to be doing, but not Oracle
– async reads of table blocks during index range scan (is this prefetched that you refer?)
– it does seem that it’s doing async read aheads of the leaf blocks too !
Enought about MS. I just wish Oracle used async reads a bit more in basic operations such as index range scans or even full table scans. I think async read-ahead is better then large scattered reads.
It’s exciting that at least some areas are starting to use it.
Comment by Christo Kutrovsky — December 22, 2006 @ 7:07 pm GMT Dec 22,2006 |
[…] easy it is to overlook one important feature when trying to comment on another. In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment […]
Pingback by Parallel Execution « Oracle Scratchpad — January 3, 2007 @ 4:41 pm GMT Jan 3,2007 |
Jonathan,
I’m getting a INDEX FULL SCAN on a Primary Key index on a large table in this query;
select sys_connect_by_path(prd.description,’.’)
from product_tree_lnk tree,
products prd
where prd.id = tree.child_product_id
connect by tree.child_product_id = prior tree.parent_product_id
I have to show the full tree path of where the product resides in the hierarchy. The primary key on products is ID, joining to that without the connect by is fine and I get unique scan, the moment connect by is used, it does a INDEX FULL SCAN. Any ideas why?
Peter
Comment by Peter — July 2, 2007 @ 1:03 pm BST Jul 2,2007 |
Peter, Sorry about the delay, I failed to spot this one when it appeared. Bear in mind that some plans show lines that may not be executed at runtime. (See https://jonathanlewis.wordpress.com/2006/10/30/non-execution-plans/ ).
To check what’s actually happening, if you’re running 10g you could use the hint /*+ gather_plan_statistics */ and check the rowsource execution stats. (See https://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ ).
For 9i you can query v$sql_plan_statistics_all after setting one of two possible parametere (see https://jonathanlewis.wordpress.com/2007/04/26/heisenberg/ ).
Apart from that, this is something I don’t happen to have an immediate answer to and I don’t have the time to set up a test case and work out what’s going on, sorry.
Comment by Jonathan Lewis — July 8, 2007 @ 10:48 am BST Jul 8,2007 |
Dear Jonathan
We have a batch process where we delete approx 2 millions of rows from a set of 80 millions through index . Because of huge db file sequential read it takes about 800 to 900 seconds .
The index fast full scan typically uses db file scattered reads
This made me to try out something in my idle development instance.
DELETE FROM RC_DTL
WHERE HDR_NBR IN ( SELECT hdr_nbr
FROM rc_hdr
WHERE run_nbr = -9200705
)
This takes about 850 seconds to delete 2010561 rows.
NEXT I tried for a different run_nbr just avoid in cache read
DELETE FROM RC_DTL
WHERE ROWID IN (SELECT d.ROWID
FROM rc_dtl d, rc_hdr h
WHERE h.run_nbr = -9200704
AND h.hdr_nbr = d.hdr_nbr
)
This took about 370 seconds and deleted 2003709 rows.
I traced the event and found the second one has done more logical then first one but still completed much faster then the first one. I believe it is because of the db file scattered rather than sequential read of index.
Following are the tkprof
First Statement
In the execute phase
CPU time 109.99
elapsed time 848.32
disk 139311
query 53940
current 8469403
rows 2010561
Rows Row Source Operation
——- —————————————————
0 DELETE RC_DTL (cr=33615 pr=112005 pw=31617 time=662796664 us)
2010561 NESTED LOOPS (cr=32863 pr=2501 pw=0 time=24127017 us)
13850 INDEX RANGE SCAN RC_HDR_NDX_4 (cr=64 pr=61 pw=0 time=1440484 us)(object id 239182)
2010561 INDEX RANGE SCAN RC_DTL_NDX2 (cr=32799 pr=2440 pw=0 time=10960085 us)(object id 239179)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
db file sequential read 107690 1.23 664.63
log file switch completion 22 0.97 1.34
log buffer space 89 0.98 23.25
latch free 1 0.00 0.00
direct path read temp 31422 0.00 0.10
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
log file switch (checkpoint incomplete) 48 0.98 45.34
latch: cache buffers lru chain 2 0.00 0.00
log file sync 11 0.97 10.17
**********************************************************************
For second statement
In the execute phase
CPU time 185.92
elapsed time 371.15
disk 228794
query 2325784
current 16635836
rows 2003709
Rows Row Source Operation
——- —————————————————
0 DELETE RC_DTL (cr=2325784 pr=228794 pw=0 time=371155531 us)
2003709 NESTED LOOPS (cr=2325012 pr=218262 pw=0 time=206012971 us)
2003709 VIEW VW_NSO_1 (cr=185494 pr=149273 pw=0 time=79763625 us)
2003709 SORT UNIQUE (cr=185494 pr=149273 pw=0 time=69745071 us)
2003709 HASH JOIN (cr=185494 pr=149273 pw=0 time=26094059 us)
13807 INDEX RANGE SCAN RC_HDR_NDX_4 (cr=60 pr=58 pw=0 time=41677 us)(object id 239182)
78332749 INDEX FAST FULL SCAN RC_DTL_NDX2 (cr=185434 pr=149215 pw=0 time=313331158 us)(object id 239179)
2003709 TABLE ACCESS BY USER ROWID RC_DTL (cr=2139518 pr=68989 pw=0 time=147045392 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
db file sequential read 86210 1.36 163.91
db file scattered read 2487 0.80 18.83
latch: object queue header operation 1 0.00 0.00
latch free 8 0.00 0.00
log file switch completion 17 0.97 2.71
buffer exterminate 1 0.14 0.14
log buffer space 21 0.97 5.70
**********************************************************************
In production the first statement takes like about 20 to 23 minutes in the busy hours.I am wondering is it right approach to change the statement to second one as it is faster inspite of huge logical I/O.Also any thoughts on the physics of operation would be helpful.
Thank you very much
CT
P.S Jonathan This time I have formatted all white space. hope it should publish right If not I will try again.
Comment by CT — July 27, 2007 @ 5:39 pm BST Jul 27,2007 |
CT,
It’s impossible to say what’s likely to happen on the production system. But there are clues that it’s worth trying.
Note that your first option uses less CPU but more elapsed time – possibly because it does a lot of sorting to disk on the delete. (So a much larger memory allowance for sorting may be an alternative “fix”).
The second option uses a lot more CPU, but no disk writes – which may be the time advantage. On the other hand it is doing a big fast full scan, which may complete quickly but cause a lot more trouble to other processes by impeding their disc activity.
The problem is deciding how the different use of resources will affect what’s happening concurrently in the production system.
Comment by Jonathan Lewis — August 2, 2007 @ 3:59 pm BST Aug 2,2007 |
Hello Jonathan,
Thank you very much for taking time to respond. I am going to try out this.
Thank you
CT
Comment by CT — August 7, 2007 @ 4:21 pm BST Aug 7,2007 |
Hi Jonathan,
This is a query on Indexing Strategy. I have a query that uses an index on (n1,n2,n3). All these columns are used as an equality in a query. The query is similar to
select n4 from t1 where n1=:b1 and n2=:b2 and n3=:b3;
The query is executed from multiple sessions and most of the times waits for Buffer Busy waits or Latch Free (CBC). Also, the Index is an Unique Index. Now, the columns n1 and n2 have very less distinct values where n3 has 100% distinct values, hence, I feel the column ordering is causing high waits as the index has to scan multiple leaf blocks.
I dropped and recreated that Index as an Unique index on (n3, n2, n1), the waits seems to be eliminated but also since, the load is less, will only come to know during month ends. Do you feel that the index on (n3,n2,n1) will eliminate the waits as the Index Scan will stop immediately when it finds a matching value and n3 is 100% distinct.
The I/Os done (logical) by the query with index on (n1,n2,n3) and (n3,n2,n1) is same, but I feel that the waits should be eliminated.
Index Scan pins the branch and leaf blocks till the time the index is scanned, hence, it can cause BBW and LF waits. Need your opinion on this.
Regards
Comment by viveshar — August 30, 2007 @ 1:40 am BST Aug 30,2007 |
I don’t think the waits are caused by the column ordering. The latch activity for a unique scan of a unique index should be identical irrespective of the column ordering.
In fact, given the buffer busy waits, I think your problem is caused by concurrent changes to blocks – possibly delayed block cleanout rather than genuine updates – not by concurrent access.
A buffer busy wait appears when you have acquired the cache buffers chains latch, attempt to pin the buffer, and find that it is pinned in an incompatible mode. If all you are doing is querying through the index, all your sessions will be requesting in share mode, so buffer busy waits could not happen (except for the “read by other session” cases when the block wasn’t already in the buffer).
I don’t think the change in column order would affect the cache buffers chains latch waits either, as Oracle would still have to traverse the index to the leaf block – which it seems to do with ‘gets – examination’ when the index is clean. In other words, it’s always going to do one latch hit per block to get from root to leaf whatever the column order.
It is possible, though, that by rebuilding the index you’ve cleaned all the blocks so that temporarily you don’t get so many pins and cleanouts going on, which means less latch activity.
Comment by Jonathan Lewis — August 31, 2007 @ 3:49 pm BST Aug 31,2007 |
Thanks Jonathan for your reply. One point that I missed in my above posting was that the due to the nature of business, this table is dropped and recreated daily night before the End of day process starts.
Secondly, Only Insert, Select and Delete Statements are executed against this table and no updates.
The Select query used to be on top, not in terms of buffer gets but in terms of number of Latch Frees and Buffer Busy Waits. Now, after changing the column ordering, the select statement is no more on top, but I have noticed that the buffer gets of the Delete Statement is incrementing as the day passes. This could be probably due to the size of the index that is changing since now we have a 100% distinct value as the first column.
Regards
Comment by viveshar — August 31, 2007 @ 4:13 pm BST Aug 31,2007 |
Having read Viveshar’s last point – why have a multicolumn index – surely your 100% selective key should do the trick ?
If a lot of other queries filter on the original cols 1 and 2 won’t they have been adversely affected by the change to the leading edge ?
Finally – if the table is dropped and recreated once a day and is static during the day – why not compress the index to level 2 and keep the original order of columns – wouldn’t this reduce the i/o needed for branch/leaf traversal but retain the selectivity advantages for other queries ?
Comment by C Page — November 30, 2007 @ 6:37 pm GMT Nov 30,2007 |
Hi
I do have similar issue in my db that went live this week, except that the table is a partitioned table partitioned by a TIMESTAMP column (10G Rel 2) called TIME_IN .
I use the order by and “rownum” to fetch the first 10 rows. It works fine and gives me the result in less than a mille second if I keep a global index on the TIME_IN column. When I convert it into a local index, it takes one full minute. It is not performing index walking anymore.
The table has about 27 million rows in the table and we expect about 35 million per week. Our system went live just this week
Is there any explanation for this?
Thanks
Sundar R
Comment by Sundar — July 2, 2008 @ 10:44 pm BST Jul 2,2008 |
Sundar R,
You describe this as a table partitioned on a timestamp column. The “obvious” guess (based on the column type)would be that you mean range-partitioned – but is that correct, or is the table actually hash-partitioned ?
Comment by Jonathan Lewis — July 5, 2008 @ 6:28 am BST Jul 5,2008 |
Sorry for the delay,
Yes, this is partitioned by range.
Thank You
Sundar R
Comment by Sundar — July 12, 2008 @ 2:38 am BST Jul 12,2008 |
ORDERS_TEMP has a global index on time_in where as ORDERS has a local index on time_in.
In other words, index walk for top 10 rows works only if we have the global index .
I am curious as why it was not performing the index walk in this case.
Thanks
Sundar R
PS: This is our test env and so we have very less # of rows. I am comparing the LIO
SQL> select * from (select * from orders_temp order by time_in desc) where rownum < 11 ; 10 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3379657680 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 6350 | 12 (0)| 00:00:01 | | | |* 1 | COUNT STOPKEY | | | | | | | | | 2 | VIEW | | 10 | 6350 | 12 (0)| 00:00:01 | | | | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORDERS_TEMP | 18M| 3793M| 12 (0)| 00:00:01 | ROWID | ROWID | | 4 | INDEX FULL SCAN DESCENDING | ORDERS_TEMP_GLOBAL | 10 | | 3 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM select * from (select * from orders order by time_in desc) where rownum < 11; 10 rows selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 2253467059 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 6350 | | 374 (1)| 00:00:05 | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | PARTITION RANGE ALL | | 6922 | 4292K| | 374 (1)| 00:00:05 | 20 | 1 | | 3 | VIEW | | 6922 | 4292K| | 374 (1)| 00:00:05 | | | |* 4 | SORT ORDER BY STOPKEY| | 6922 | 1331K| 4120K| 374 (1)| 00:00:05 | | | | 5 | PARTITION LIST ALL | | 6922 | 1331K| | 73 (0)| 00:00:01 | LAST | 1 | | 6 | TABLE ACCESS FULL | ORDERS | 6922 | 1331K| | 73 (0)| 00:00:01 | 320 | 1 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<11) 4 - filter(ROWNUM<11) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 915 consistent gets 0 physical reads 0 redo size 4756 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 17 sorts (memory) 0 sorts (disk) 10 rows processedComment by Sundar — July 12, 2008 @ 3:17 am BST Jul 12,2008 |
It is even worse when I force it to use the LOCAL index
SQL> select * from (select /*+INDEX(ORDERS ORDERS_PK_IX) */ * from orders order by time_in desc) where rownum < 11; 10 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1424745003 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 6350 | | 3505 (1)| 00:00:43 | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | PARTITION RANGE ALL | | 6922 | 4292K| | 3505 (1)| 00:00:43 | 20 | 1 | | 3 | VIEW | | 6922 | 4292K| | 3505 (1)| 00:00:43 | | | |* 4 | SORT ORDER BY STOPKEY | | 6922 | 1331K| 4120K| 3505 (1)| 00:00:43 | | | | 5 | PARTITION LIST ALL | | 6922 | 1331K| | 3204 (1)| 00:00:39 | LAST | 1 | | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| ORDERS | 6922 | 1331K| | 3204 (1)| 00:00:39 | 320 | 1 | | 7 | INDEX FULL SCAN | ORDERS_PK_IX | 6922 | | | 80 (0)| 00:00:01 | 320 | 1 | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<11) 4 - filter(ROWNUM<11) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1935 consistent gets 0 physical reads 0 redo size 4700 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 17 sorts (memory) 0 sorts (disk) 10 rows processedComment by Sundar — July 12, 2008 @ 3:25 am BST Jul 12,2008 |
Sundar,
Your table is not ‘range-partioned’, it is “range/list composite partitioned”.
To use an indexed access path for your query, the optimizer would have to descend a little way on every index subpartition of the highest partition until it had picked up enough data to ensure that it could meet your rownum requirement.
For example it could pick the first N rows from each subpartition, then sort and keep only the top N rows. Alternatively, it could jump round the subpartitions constantly cross-checking each row.
Of course, this type of thing gets very complicated when there isn’t enough data in the subpartitions of the top partition – so you’re thinking of a path that is probably too complex to be handled by generic code — at present.
If this query is very important, but you want to use partitioned indexes, you could create a globally partitioned index on the timestamp, carefully defining it as a range-partitioned index where the ranges are an exact match for the ranges in the range/list partitioning on the table.
The optimizer won’t be aware of exactly what’s going on (it’s a trick I call ‘semi-local’ partitioning) and the maintenance job will be a little messy, but it might do what you want without imposing too much of a penalty.
Comment by Jonathan Lewis — July 12, 2008 @ 10:52 am BST Jul 12,2008 |
Hi
Thank you for the response. Yes, it is range partitioned by the time stamp and list subpartitioned. I did not expect the list sub partition would be the bottle neck here.
You suggestion did work.
As a matter of fact I did the following comparisons –
here are all results…
1. With a global index and list sub partition “consistent get” is 14 -> Best for performance, bad for storage as the entire index needs to be placed in one tablespace
2. With global partitioned index and with list sub partition “consistent get” is 22 -> Optimum strategy
3. With local index and list sub partition , “consistent get” is 1965 -> Worst LIO and so the response time
4. With local index and no list sub partition “consistent get” is 149 -> not so great load distribution across multiple nodes of a RAC
Thanks again
Comment by Sundar R — July 15, 2008 @ 1:23 am BST Jul 15,2008 |
> David, I forgot to mention that the fast full scan could go parallel on a non-partitioned index, with the consequential change in read mechanism that you mention. Thanks
Jonathan, is there any reason that you mentioned “non-partitioned index” here? I believe that Oracle supports parallel index fast full scan on partitioned(local, global) index.
Comment by Dion Cho — August 14, 2008 @ 8:01 am BST Aug 14,2008 |
Dion Cho,
I’m afraid I have to answer your question with a double negative.
My comment wasn’t intended to suggest that index fast full scans didn’t work with partitioned indexes. The detail I wanted to highlight from David’s post was that even simple (i.e. no special licence) systems might see parallel index fast full scans.
Things can get a little entertaining, of course, when you start adding options as there are more variations in the arithmetic to consider when you mix parallelism and partitioning.
Comment by Jonathan Lewis — August 19, 2008 @ 6:31 pm BST Aug 19,2008 |
I have two identical databases with same configuration and hardware at same patch level on all fronts that I could check. One is test and other production. While they are complaining about performance issues on test (fairly new setup), I tried to take one simple query to count number of rows in a table. On production it is doing INDEX FAST FULL SCAN on PK and taking 9 min. and on Test, it is doing INDEX FULL SCAN and taking about 44 min. I tried even import stats from production for that table, behavior didn’t change. I checked db file multiblock read count and other related paramters, they are same on both systems. Any idea what other thing is influencing this behavior?
Comment by SReddy — January 6, 2009 @ 5:11 pm GMT Jan 6,2009 |
Hi Jonathan,
Would like to know the formula behind the index fast full scan especially with OICA & OIC value for the Global & Local Partitioned Index. Trying to correlate with 10053 trace but I still couldnt get thro’..
Appreciate your inputs..
Version : 10.2.0.3 / No Histograms / Bind Variables / 2 Tables Joins
Thanks.
Comment by Nataraj — March 8, 2009 @ 2:33 pm GMT Mar 8,2009 |
Nataraj,
I haven’t checked for changes in 10g, but the basic arithmetic used to be the same as that used for tablescans, except for using the leaf_blocks figure from dba_indexes instead of the blocks figure from dba_tables. This is documented in Cost Based Oracle – Fundamentals.
Comment by Jonathan Lewis — March 22, 2009 @ 12:48 am GMT Mar 22,2009 |
Jonathan sir,
I have posted a question about fast full scans of local nonprefixed index, but surprisingly (for me) nobody has replied yet.
Could I request you to take a quick look and give your opinion (even if it is to flame me for a daft question)?
http://forums.oracle.com/forums/message.jspa?messageID=9794705#9794705
Many thanks,
CJ
Comment by CJ — August 12, 2011 @ 3:37 pm BST Aug 12,2011 |
CJ,
The index fast full scan is only available to the optimizer if all columns referenced in the query are in the index.
Even though it’s obvious to the human eye that your predicate is always true and therefore redundant, and even though the optimizer eventually eliminates it (which we would be able to see from the predicate section if you had printed it in the OTN posting), the optimizer has already discarded the index_ffs before it considers the predicate elimination.
I don’t know if everyone is affected, but I get an error page when logging on to OTN at present (but not when logging on to MOS) – so perhaps someone else has been waiting to answer the questioon for you,but has been able to log on.
Comment by Jonathan Lewis — August 14, 2011 @ 8:12 am BST Aug 14,2011 |
Sir,
My point is that if the CBO can do an ‘Index Full scan’ without touching the table, then it should be able to do an ‘Index Fast Full scan’ as well. If it discards that path, then I think that this is a shortcoming of the CBO (unless there is a sound reason which I am not getting).
I have included the predicate section in the plans.
Greg Rahn has commented that he will check if the restriction can be lifted.
Many thanks,
CJ
Comment by CJ — August 15, 2011 @ 12:56 pm BST Aug 15,2011 |
CJ,
There are all sorts of options available to the human that the optimizer code cannot see – this just happens to be a simple and obvious example of the principle. You are perfectly correct that this is a shortcoming of the CBO, and with a little luck it might be one that can be removed relatively easily.
Comment by Jonathan Lewis — August 16, 2011 @ 9:56 am BST Aug 16,2011 |
[…] using the B-tree index as it seemed that the only way the index could be used was with an index fast full scan. I was curious, so I said I’d take a look at the query, the object definitions the plan, and […]
Pingback by Bitmap Index « Oracle Scratchpad — August 24, 2011 @ 6:20 pm BST Aug 24,2011 |
[…] wasn’t using the B-tree index as it seemed that the only way the index could be used was with an index fast full scan. I was curious, so I said I’d take a look at the query, the object definitions the plan, and the […]
Pingback by Bitmap Index « Ukrainian Oracle User Group — August 25, 2011 @ 6:43 am BST Aug 25,2011 |
Hi Jonathan,
after reading and reading, and trying to find an explanation, i find myself in the position of addressing this question:
We have an ORACLE 10g standard edition 1
I have a HEAP organized table of ~ 6 million records. Doing a select count with full table scan i get:
consistent gets: 29511, and the duration is somewhere about 5 seconds
After moving the table to IOT ( nevermind the reasons behind the decision) the same select count will need:
consistent gets: 120 000
and will need somewhere about 13 seconds.
The number of CG is normal ( analyzing the index returns about 120 000 blocks)
interesting is that the wait event on the active session doing the IOT query is “db sequential read”, instead of “db scattered read”, which leads me to the following question:
full table scans are known to do scattered reads, taking advantage of the multiblock read.
index organized tables, due to their organization, will always follow an index fast full scan to process the query, but: it is also said that only oracle enterprise edition can do parallel index scans.
Does that mean that standard edition can do scattered read on full table scan, but not on fast full scan of indexes ?
I have not found a concrete answer to this question yet, and it would be really helpful (for the community also, i am sure) to know this. Would really appreciate your time on this one.
Thank you
Alex
Comment by Alexandru Ersenie — September 12, 2011 @ 10:57 am BST Sep 12,2011 |
[…] summary – Spendius asks why an index_ss() hint is making the optimizer pick an index full scan, and I confirm that I can create a (slightly unusual) example where a query does a full tablescan […]
Pingback by Hints and Guesses | Oracle Scratchpad — July 30, 2020 @ 11:11 am BST Jul 30,2020 |