Re: Slow data dictionary query from all_synonyms fixsyn script

From: joel garry <joel-garry_at_home.com>
Date: Wed, 26 Mar 2008 17:28:45 -0700 (PDT)
Message-ID: <b67b54bf-2fc2-48b9-88f4-097e62124c69@s37g2000prg.googlegroups.com>


On Mar 26, 12:54 am, Kirmo Uusitalo <n..._at_exists.com.invalid> wrote:
> Hello,
>
> I've developed a script file which fixes (creates or drops) synonym
> statements for all users which have been granted access for current
> user's objects. It can even trace the privileges inherited through
> roles.
>
> It works OK but the performance is way too slow if the database is
> big.
>
> 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.

jg

--
@home.com is bogus.
Hey, that's what I started on:  http://www.nytimes.com/2008/03/23/technology/23digi.html?_r=1&oref=slogin
Received on Wed Mar 26 2008 - 19:28:45 CDT

Original text of this message