Oracle 8.0.5 optimizer error?

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 Tue Dec 07 1999 - 06:42:43 CST