Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with OPTIMIZER

Re: Help with OPTIMIZER

From: Mark Malakanov <markmal_at_sprint.ca>
Date: Mon, 27 Sep 1999 21:31:45 -0300
Message-ID: <ASUH3.3617$xJ4.177194@newscontent-01.sprint.ca>


Interesting things.

I already interested about where (and how) Oracle stored the exec plans for parsed queries.
Only things, I can get from static/dynamic tables, are tables/clasters used for
query. But no info about indexes!
I wanted to know what objects (including indexes) are used by Oracle for particular SQL.

I try to write home-made tool to distribute objects (tables and indexes) among the drives.
And I partly made this by getting SQL from V$SQL/V$SQLAREA/V$SQLTEXT views and making EXPLAIN PLAN for this statements. But now I see it doesn't work sometime. What to do? How to know a truth about execution plan and objects used?

Mark

Alex Daman <alex_damanakis_at_exe.com.au> wrote in message news:_MEH3.22331$1E2.172245_at_ozemail.com.au...
> Hello All,
>
> I am fairly new in the ORACLE world and need some input with a problem we
> are facing.
>
> Oracle Version : 7.3.4
> Platform : IBM AIX UNIX 4.3
>
> The Oracle optimizer does not choose the index path that is reported in
the
> query plan unless the SQL statement contains hints. I know this is
happening
> because the statement without the hints takes 2minutes to run as compared
to
> 3 seconds with the hints.
> You might say then, "Use Hints!" but I really need to understand what is
> going on here because I dont want to go and modify every program thats
> querying the database.
> I also do not believe that a RDBMS product like Oracle has an optimizer
that
> behaves this way.
>
> OPTIMIZER_MODE is set to CHOOSE and we run ANALYZE every night.
>
> I have also read somewhere that this can be caused by type casting. I have
> checked the application and this is not the case.
>
> Can anyone please give me any other reasons for the optimizer to behave
this
> way.
>
> Thanks in advance.
>
>
> Alex
>
>
>
>
>
>
Received on Mon Sep 27 1999 - 19:31:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US