Home » RDBMS Server » Performance Tuning » Full Index Scan issue (11.2.0.3.0)
Full Index Scan issue [message #591603] Tue, 30 July 2013 03:29 Go to next message
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 #591607 is a reply to message #591603] Tue, 30 July 2013 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Different data
Different clustering factor
Different disk access
Different CPU
Different memory
Different workload
...

Regards
Michel
Re: Full Index Scan issue [message #591612 is a reply to message #591607] Tue, 30 July 2013 04:19 Go to previous messageGo to next message
rleishman
Messages: 3689
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 #591633 is a reply to message #591603] Tue, 30 July 2013 07:18 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks for the reply

Rleishman,


Quote:
i thinks this is a superior plan because it doesn't think that the index scan on Table1 is possible

I have a doubt, why are we targeting TABLE1 ?
To access TABLE3 data Oracle does a FULL Index SCAN which is not good
"We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort."

But in my environment Oracle chooses Range scan so is it because of the statistics or some other reason?
Re: Full Index Scan issue [message #591634 is a reply to message #591603] Tue, 30 July 2013 07:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1859
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 Go to previous messageGo to next message
rleishman
Messages: 3689
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 #592138 is a reply to message #591603] Sun, 04 August 2013 07:15 Go to previous messageGo to next message
michael_bialik
Messages: 601
Registered: July 2006
Senior Member
IMHO - 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
Re: Full Index Scan issue [message #592230 is a reply to message #592138] Mon, 05 August 2013 15:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1859
Registered: May 2013
Location: World Wide on the Web
Senior Member
michael_bialik wrote on Sun, 04 August 2013 17:45
IMHO - 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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Analyze stats running long
Next Topic: Optimize Query using Explain Plan
Goto Forum:
  


Current Time: Tue Jul 29 10:12:49 CDT 2014

Total time taken to generate the page: 0.09182 seconds