Re: Slow data dictionary query from all_synonyms fixsyn script

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

>On Mar 26, 12:54 am, Kirmo Uusitalo <n..._at_exists.com.invalid> 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 10.2.0.2.0 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!

>jg

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