Re: Slow data dictionary query from all_synonyms fixsyn script
Date: Fri, 28 Mar 2008 15:22:56 -0700 (PDT)
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)
> 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!
But it's never going to be faster if you are processing 3 full table scans of sys.obj$ one row at a time.
-- @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