Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unhappy Unanswered: Strange Index usage

    Hi,
    I have a Baseline Explain Plan for a batch procedure which should be followed by the optimizer to have the best performance for that procedure.
    (The baseline was taken for the procedure when performing best.)

    Today when I saw the procedure was running abnormally slow, I checked the Explain Plan being followed by the optimizer in the current scenario.

    Every step of the Explain Plan matched with that of the baseline except one wherein instead of the intended normal index , some other bitmap index was being used . I dropped that bitmap index just to see if there was any difference but I found the optimizer went for some other bitmap index.

    I gave a hint to use teh intended index but still it is not getting used.
    I dropped and recreated teh intended index but no help.
    I analyzed all the associated tables but still no help.

    Can anyone advise what is happening.
    Thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A couple of things which might help

    1) Be careful with hints as it is very easy to specify them incorrectly and thus they get ignore i.e. specify the table alias not the table name etc.

    2) Has the data distribution changed in the table dramatically? Try doing a more detailed analysis i.e. for all indexed column size <n>.

    3) Has the init.ora changed which might affect the optimizer.

    4) Is your index optimal for your query i.e. does it have all the associated columns required for the criteria specified in the query

    5) If you still cant get it working try looking up plan stability

    Alan

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unhappy Strange Index Usage

    Thanks Alan.
    1. init.ora has a new parameter "star_transformation_enabled" set to true while this was false earlier.
    This is the only change in init.ora

    2. Hint had table alias specified
    3. Indexes the optimizer is trying to look for are always bitmap and they are sole column indexes.
    4. Data change is not at all significant.
    The next Explain Plan being followed has abnormally high cost and it may take days to complete the execution.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    do

    alter session set STAR_TRANSFORMATION_ENABLED=false;

    and see if the query runs properly. If it does, change your init.ora if appropriate or put 'alter session set STAR_TRANSFORMATION_ENABLED=false;' line in your app/procedure.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •