1 2 Previous Next 16 Replies Latest reply: Jun 27, 2013 7:10 AM by user12016307 Go to original post RSS
      • 15. Re: SLOW SQL ON ORACLE 11r2 fast on 9i
        Dave Rabone
        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

          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