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: Oracle 8.0.5 optimizer error?

Re: Oracle 8.0.5 optimizer error?

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 11 Dec 1999 23:40:26 +0200
Message-ID: <3852C4CA.3373C74A@0800-einwahl.de>


Hi Ivan,

perhaps you try "optimizer_mode = rule" in init.ora. Helped me.

In Oracle8i there is the same bug when you export (if you start your instance with sql_trace = true you will see that this is the faulty statement).

Martin

Ivan Bajon wrote:
>
> I've got this going with Oracle Support as well but I figured someone might
> be interested in this "feature". Could save people a lot of trouble working
> on performance problems which they might not be able to solve. I've got a
> Oracle 8.0.5.2.1 running on AIX. The optimizer mode is first_rows. I first
> ran into problems trying to make an export of the database. On our similar
> 7.3.4 an export of approx. the same amount of data would take an hour or so
> to complete. After 5 hours on the 8.0.5 I killed it. It wasn't even half way
> through. I had a look at what was running and I saw that a recursive SQL
>
> select name, proterty from sys.exu8ico
> where tobjid = :1
> order by colid;
>
> had been executed around 300 times, generating a total of 3/4 BILLION (yes,
> that's a B alright) buffer gets. Later we experienced very poor performance
> whenever we queried data dictionary objects. Oracle support suggested that I
> altered the catalog.sql script by putting in a rule-hint. This did get us
> further but that doesn't help on recursive sql. According to Oracle, when
> you used first_rows with Oracle7, recursive sql would be executed using
> rule. That has changed. In Oracle8 it will execute recursive sql using
> choose when your optimizer mode is first_rows. I believe that it's really
> using first_rows on recursive sql too though. This is backed up by the fact
> that when I do an explain plan on a recursive sql I get a cost! SYS' objects
> aren't analyzed so if it was using choose, it would have picked rule
> instead.
> I'm having a hard time seing how to solve the performance problems this
> gives us. I was told that in Oracle8i you can analyze SYS' objects but we're
> obligated to have our application running on 8.0.5 as well. We could use
> choose and then hardcode a first_rows hint into each of our sqls (we're
> bound to use first_rows since choose gives us other problems). Maybe we
> should just use rule all the way through. Or maybe one of you guys out there
> have a tip? In any case, I find it worrying that Oracle wants us all to use
> the cost-based optimizer when its very own core sucks badly.....unless it's
> allowed to use rule.
>
> End of my 2 cents,
> Ivan Bajon, ocp
Received on Sat Dec 11 1999 - 15:40:26 CST

Original text of this message

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