Re: Slow data dictionary query from all_synonyms fixsyn script

From: Shakespeare <>
Date: Sat, 29 Mar 2008 13:18:41 +0100
Message-ID: <47ee33ab$0$14353$>

"Kirmo Uusitalo" <> schreef in bericht
> 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.

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?


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

Original text of this message