This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 27, 2013 5:10 AM by user12016307 Go to original post RSS
  • 15. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
    Dave Rabone Journeyer
    Currently Being Moderated
    Something to consider:

    In Oracle 10 and above the all_synonyms view was enhanced to recursively follow synonyms to synonyms ( ... to synonyms to synonyms to ... )

    If you have a lot of synonyms then that can have a huge performance hit, because it was implemented as a function call within the view (and of course it's just doing more work too).

    There was a MOS note which spoke about this (it might be the one Dom Brooks refers to, I don't have MOS access right now to check).

    One solution suggested there was to revert to the Oracle 9 definition of all_synonyms.

    I hit this when moving from 9 to 10 a long time ago ... I can't really comment whether it's still appropriate for 11.

    Dave
  • 16. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
    user12016307 Newbie
    Currently Being Moderated

    Hello, i have the same performance problems regarding statements with ALL_SYNONYMS.

     

    I have 11.2.0.3 PSU6 - nothing changes....

     

    My solution to fix this is to use a hint - I know, it is dirty - but it works for me  :

     

    CURSOR C_SYNO (PI_OWNER IN VARCHAR2, PI_TABLE IN VARCHAR2) IS

    SELECT /*+ optimizer_features_enable('9.2.0') */

    TABLE_OWNER, TABLE_NAME

    FROM ALL_SYNONYMS

    WHERE OWNER=PI_OWNER

    AND   SYNONYM_NAME=UPPER(PI_TABLE);

     

    So back to 9.2.0 works fine : From 9 minutes to 0.8 seconds. Lookning to the access plane is still  awfull....

    I have tried with optimizer to 10.2.0 : It does not work...

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points