Re: Slow data dictionary query from all_synonyms fixsyn script
Date: Sat, 29 Mar 2008 13:18:41 +0100
"Kirmo Uusitalo" <not_at_exists.com.invalid> schreef in bericht
> 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)
> 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