Re: Slow data dictionary query from all_synonyms fixsyn script

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 29 Mar 2008 13:18:41 +0100
Message-ID: <47ee33ab$0$14353$e4fe514c@news.xs4all.nl>

"Kirmo Uusitalo" <not_at_exists.com.invalid> schreef in bericht news:t2apu3l6duef2i18s6p0jvio9qavh9b36k_at_4ax.com...
> 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.

But why would you want it any faster? How many times will you run this query? How often do you create synomyms or grant objects?

Shakespeare

>
> Thank you all for these!
>
>>jg
>
> Kirmo Uusitalo
Received on Sat Mar 29 2008 - 07:18:41 CDT

Original text of this message