Database Tuning (MOSC)

MOSC Banner

query changing explain plan during index rebuild

edited May 28, 2009 4:58AM in Database Tuning (MOSC) 8 commentsAnswered
 I was rebuilding a very large index (30 Gb) with the following statement

alter index BNI_CC_CF_DT_OD rebuild  parallel 8 online nologging ;

During the index rebuild the query

SELECT * FROM cal_cur_ownr.BOOKING_NAME_ITEM WHERE BOOKING_ID = :1 AND PURGE_DATE = :2

changed from this its normal execution plan

SELECT STATEMENT
PARTITION RANGE SINGLE
 TABLE ACCESS BY LOCAL INDEX ROWID BOOKING_NAME_ITEM TABLE
 INDEX RANGE SCAN IX_BNI_BKG INDEX

to

SELECT STATEMENT
 PARTITION RANGE SINGLE
  TABLE ACCESS FULL BOOKING_NAME_ITEM TABLE

 BOOKING_NAME_ITEM is over 400Gb and a fts causes predictable problems with the application grinding to a halt. To solve the problem I had to kill all the processes doing fts and flush the shared pool after which the correct execution was used. I am not certain but believe the problem arose at the point where the alter index command completed. I am running 10.2.0.3 on a Linux 2-node RAC cluster.  Has anyone run into similar problems ?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center