Re: Slow data dictionary query from all_synonyms fixsyn script


"Kirmo Uusitalo" <not@xxxxxxxxxxxxxxxxxx> schreef in bericht
news:t2apu3l6duef2i18s6p0jvio9qavh9b36k@xxxxxxxxxx
On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
<joel-garry@xxxxxxxx> wrote:

On Mar 26, 12:54 am, Kirmo Uusitalo <n...@xxxxxxxxxxxxxxxxxx> 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.

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?

Shakespeare


Thank you all for these!

jg

Kirmo Uusitalo


.

Relevant Pages

  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Store procedure vs Direct statement ???
    ... Try opening up query analyzer and checking out the query plan or ... advantages of using stored procedures over not using stored procedures is the ... the better the overall performance of your SQL ... dynamic sql versus the execution plan for a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)