| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Rebuilds - Cost based optimizer on 8.1.6
--0__=IZqzlf3gjrssNz1Xt8Ypjqa3OsCwywj0fswEZhLim51x1m85waY0ieIf
Content-type: text/plain; charset=us-ascii
Content-Disposition: inline
We're using cost based optimization, and have been for some time. The primary schema is analyzed nightly - estimated for the development environments and computed for production. Have noticed serious reductions in query speed when tables and indexes are analyzed less frequently, and have also noticed that the nodes have to work really, really hard when the jobs are running. But downtime is not required. I just stagger the runs, and schedule as best I can. (Difficult when you have users on three continents, but it can be done.)
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)
Subject: Re: Index Rebuilds
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.
=E9crit=A0: >
>
> Hello!
>
> I am 'really surprised' that the solution
> 'commonly' provided for 'such
> problems' is analyze the table..
>
> Do people really analyze tables? ( I mean do u get
> so much down time! if
> not how do u manage it!)
> Do people really run their applications in COST
> BASED mode...
> cos our organisation has been using Oracle6 onwards
> and since then the
> applications have been tuned
> for rule based and recently we tried to move to
> COST BASED and since all
> the programs gave a huge problem
> we cried off...
>
> Has anyone gone from RULE BASED to COST BASED.. can
> u help!
>
>
> --
> Author: cyril
> INET: cyril_at_stockholding.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
-- Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D INET: stephane_paquette_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Jun 09 2000 - 09:20:25 CDT
 |
 |