Re: Seeking understanding of my "gc cr multi block request" waits

On Thu, 19 Jul 2007 09:36:32 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>Would they let you go to 10g if you set optimizer_level to 1?

I am afraid they don't know what 'optimizer_level 1' means. They already don't know what a database link is, and what it could do to merge 2 databases on 2 different sites.
I am aware optimizer_level is already at 1 in the 9i database, I don't know whether it changes meaning in 10g.
Frankly, this is the worst piece of Oracle software I ever saw. It fully qualifies to be included in Tom Kyte's upcoming 'Worst practices' web seminar.
They use ODBC as driver, and they always execute only 1 transaction per session. There currently only 42 transactions per minute. They use 31-byte varchar2 surrogate keys for everything, and as those key include the date and a sequence number those keys only increase (which is disastrous for index volume).

Right now, I've tested a few real statements, and it appears even without appropiate statistics CBO is providing better execution plans. Needless to say many columns are indexed which shouldn't have been indexed, and many columns which should have been indexed are not indexed. IOTs are not being used, and there are quite a few candidates.
I seriously doubt whether they performed any statement tuning at all.

I would like to avoid to disclose any more details in public, however I'm quite certain this is going to end up in disaster, and fingerpointing to an innocent DBA will start very soon.

--
Sybrand Bakker
Senior Oracle DBA