Re: Slow data dictionary query from all_synonyms fixsyn script

From: joel garry <joel-garry_at_home.com>
Date: Fri, 28 Mar 2008 15:22:56 -0700 (PDT)
Message-ID: <b5a88ac4-7d4a-49eb-943d-5b19e61550d4@h11g2000prf.googlegroups.com>


On Mar 28, 1:15 am, Kirmo Uusitalo <n..._at_exists.com.invalid> wrote:
> On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
>
>
>
>
>
> <joel-ga..._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!

You might start with http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:677074900346092274

But it's never going to be faster if you are processing 3 full table scans of sys.obj$ one row at a time.

jg

--
@home.com is bogus.
 Access denied for user 'aquarist_yanton'@'localhost' (using password:
YES) in /home/tomsiebe/public_html/phrase_tracer.php on line 54
I cannot connect to the database because: Access denied for user
'aquarist_yanton'@'localhost' (using password: YES)
Received on Fri Mar 28 2008 - 17:22:56 CDT

Original text of this message