Re: Slow data dictionary query from all_synonyms fixsyn script
Date: Fri, 28 Mar 2008 10:15:10 +0200
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
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