Re: Slow data dictionary query from all_synonyms fixsyn script

From: Kirmo Uusitalo <>
Date: Fri, 28 Mar 2008 10:15:10 +0200
Message-ID: <>

On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry <> wrote:

>On Mar 26, 12:54 am, Kirmo Uusitalo <> wrote:

>> I have traced the problem to this sql satement:
>> SELECT owner
>>              FROM all_synonyms
>>              WHERE table_name = UPPER ('EMP')
>>                AND synonym_name = UPPER ('EMP')
>>                AND table_owner = 'SCOTT'
>>                AND (owner = 'SYSTEM' OR owner = 'PUBLIC');
>> The explain plan for this rather simple query looks like this!
>> (I am using Oracle on Windows platform:
>> Am I doing something wrong as the explain plan looks rather complex
>> for this simple query?
>Remember, all_synonyms is a view, with exists and unions. You may
>have more luck dealing with the underlying tables. Or maybe David's
>suggestion will be good enough for the optimizer to sort through all
>that stuff.

Yes I am aware of this. But as far as I know the views stay the same between Oracle versions but the underlying table structures may change. This is why I prefer using the view instead.

I tried David Fitzjarrell's suggestion, running this) dbms_stats.gather_fixed_objects_stats(NULL).

It seemed to improve running time a little, but still running this fixsyn.sql against one table in a small database (around 50 users) takes about 2,5 seconds which seems to me quite a long time for such a simple task.

Thank you all for these!


Kirmo Uusitalo Received on Fri Mar 28 2008 - 03:15:10 CDT

Original text of this message