| Full Index Scan issue [message #591603] |
Tue, 30 July 2013 03:29  |
 |
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
Hi Experts,
We have 3 tables TABLE1 ,TABLE2, TABLE3
TABLE1 A have (col_A1,col_A2,one other column) as the Primary key.===>IDX_TALE1
TABLE2 B have (col_BC1) as the Primary key=======> IDX_TABLE2
TABLE3 C have (col_BC1,col_C7) as the Primary key======>IDX_TABLE3
Below is the TKPOF for a sql join query on above three tables
SELECT C.col_C1,
C.col_C2,
C.col_C3,
C.col_C4,
C.col_C5,
C.col_C6
FROM TABLE1 A,
TABLE2 B,
TABLE3 C
WHERE A.col_A1 = :1
AND A.col_A2 = :2
AND A.col_AB1 = B.col_AB1
AND B.col_BC1 = C.col_BC1
ORDER BY C.col_BC1,
C.col_C7
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 247.39 328.83 189826 22845997 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 247.39 328.83 189826 22845997 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=11 us)
14672576 NESTED LOOPS (cr=8173421 pr=189826 pw=0 time=249627600 us cost=1893664 size=6480000000 card=48000000)
7336288 NESTED LOOPS (cr=8173403 pr=189825 pw=0 time=188332370 us cost=1891263 size=5088000000 card=48000000)
7439075 TABLE ACCESS BY INDEX ROWID TABLE3 C (cr=808438 pr=150575 pw=0 time=78080414 us cost=1875182 size=4320000000 card=48000000)
7439075 INDEX FULL SCAN IDX_TABLE3 (cr=19262 pr=19260 pw=0 time=7321394 us cost=65346 size=0 card=48000000)(object id 22123)
7336288 TABLE ACCESS BY INDEX ROWID TABLE2 (cr=7364965 pr=39250 pw=0 time=98804581 us cost=1 size=16 card=1)
7336288 INDEX UNIQUE SCAN IDX_TABLE2 (cr=28677 pr=2335 pw=0 time=20860357 us cost=1 size=0 card=1)(object id 22119)
14672576 INDEX RANGE SCAN IDX_TABLE1 (cr=18 pr=1 pw=0 time=46719539 us cost=1 size=0 card=6592996)(object id 22128)
0 TABLE ACCESS BY INDEX ROWID TABLE1 (cr=14672576 pr=0 pw=0 time=64330368 us cost=1 size=29 card=1)
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 189826 0.45 86.95
Disk file operations I/O 1 0.00 0.00
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
If we look at the above explain plan to access data from TABLE3 "INDEX FULL SCAN IDX_TABLE3" is used , but when we run the same query in our environment it uses a "INDEX RANGE SCAN" and elapsed time is also very less
So because of FULL INDEX scan it will perform single block i/o's and so becasue of which we have high db file sequential read i.e. 189826
Doubts:-
1. Why in Production environment above sql uses a INDEX FULL SCAN but in development env. it uses a INDEX range scan?
|
|
|
|
|
|
| Re: Full Index Scan issue [message #591612 is a reply to message #591607] |
Tue, 30 July 2013 04:19  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's using the full scan on Table3 to avoid the ORDER BY. It thinks this is a superior plan because it doesn't think that the index scan on Table1 is possible - or perhaps it will return a large number of rows.
My guess is one of 2 things:
- There really is no index on Table1 (col_A1,col_A2,one other column). Check to make sure it exists and is not invalid.
- The data types of bind variable :1 and :2 are not the same as the data types of COL_A1 and COL_A2. Check the data types in the table and compare to the data types of the variables in the host program.
Ross Leishman
[Updated on: Tue, 30 July 2013 04:19] Report message to a moderator |
|
|
|
|
|
| Re: Full Index Scan issue [message #591634 is a reply to message #591603] |
Tue, 30 July 2013 07:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Per the Oracle docs -
In a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.
As Ross also pointed in his first line, it is trying to avoid ORDER BY the columns specified - C.col_BC1, C.col_C7 which are also the candidates for - TABLE3 C have (col_BC1,col_C7) as the Primary key======>IDX_TABLE3.
So a perfect match of conditions for the optimizer to go for a full index scan. Oracle reads the root block, and goes till the leaf block, reading a block at a time. So it results in high single block I/Os.
|
|
|
|
| Re: Full Index Scan issue [message #591673 is a reply to message #591633] |
Tue, 30 July 2013 16:45 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
rishwinger wrote on Tue, 30 July 2013 22:18
I have a doubt, why are we targeting TABLE1 ?
That would be because of the two WHERE predicates of TABLE1 that filter the results down from 14M rows to 0
Ross Leishman
|
|
|
|
|
|
| Re: Full Index Scan issue [message #592230 is a reply to message #592138] |
Mon, 05 August 2013 15:26 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
michael_bialik wrote on Sun, 04 August 2013 17:45IMHO - there is no index to make a join from TABLE 1 A to TABLE 2 B.
Try:
CREATE INDEX ... ON TABLE2 ( col_AB1, col_BC1 ) ...
HTH
No, that's not the issue here.
1. OP's 1st question : why index full scan on table3?
INDEX FULL SCAN IDX_TABLE3 --> because of the ORDER BY C.col_BC1, C.col_C7
2. OP's 2nd question : why is it targeting table1?
INDEX RANGE SCAN IDX_TABLE1 --> because of the WHERE A.col_A1 = :1 AND A.col_A2 = :2
|
|
|
|
| Re: Full Index Scan issue [message #592355 is a reply to message #591603] |
Tue, 06 August 2013 22:04 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
I would like to point out :
INDEX FULL SCAN IDX_TABLE3 (cr=19262 pr=19260 pw=0 time=7321394 us
It read 19,262 blocks and took 7.321seconds of elapsed time. So this particular step itself is not the "issue".
Hemant K Chitale
|
|
|
|
| Re: Full Index Scan issue [message #592363 is a reply to message #592355] |
Wed, 07 August 2013 01:50 |
|
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
Thanks all for the reply
We looked at the stats for Index IDX_TALE1 on Table1 ,we found stats were missing for this index
NUM_ROWS=0,clustering_factor=0 and other values also 0
so once we fixed the stats , query started using optimal explain plan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
