RE: Index Rebuilds - Cost based optimizer on 8.1.6

I just tried it on a 420,000 row table and had no errors.

On the other hand we have an even more serious error with 8.1.6 involving the PQO. When we had degrees > 1 on some tables we would get erroneous SQL results with NO error message. Setting degrees=1 gave us the correct results again.

Oracle says to try applying the 8.1.6.1.1 patch and see if it helps which we hope to do this weekend...

-----Original Message-----
From: rsands_at_lendleaserei.com [mailto:rsands_at_lendleaserei.com] Sent: Friday, June 09, 2000 12:02 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Index Rebuilds - Cost based optimizer on 8.1.6

But I also have a question. Had to upgrade to get off of 8.0.5, and had the option of going to 8.0.6 or 8.1.6. One of the other dbas told me she had discovered a bug in the optimizer for 8.1.6. Apparently, if there are statistics on the table, and a query using distinct and order by is run, it fails with an ora-600. Once the statistics are removed, the query was fine.

Has anyone else has seen this or heard anything from Oracle on the subject? Checked Metalink - don't see it in the bug database. Seems like this would be a show stopper for many.

Robyn

paquette stephane <stephane_paquette_at_yahoo.com> on 06/09/2000 10:41:14 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Robyn Sands/US1/Lend Lease)

On all new projects I've worked on since Oracle 8, I've choosed cost base. Anyway all new stuff only works with cost base.
I've tested cost base on Oracle 7.x and it was pretty unstabled, especially on 7.2

Analyze table are done on a daily or weekly basis depending on the applications and tables.

Analyze do not lock table.

I've not switched from rule base to cost base but I'm sure there is a big tuning effort involve in that. Received on Fri Jun 09 2000 - 13:59:10 CDT