Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Optimizer and block size changes = trouble...

Optimizer and block size changes = trouble...

From: Nuno Souto <>
Date: Wed, 09 May 2001 07:59:15 -0700
Message-ID: <>

Another spiky one. Situation is this:

V8.0.6.0, HP/UX 11, Peoplesoft Financials 7.52(heck, does it sound like I'm involved with this mob? <g>)

Block size in DB was 16K. Totally inappropriate for the type of database this is, with huge memory use and horrendous rollback segment size overhead and performance hit. DB_FILE_MULTIBLOCK_READS at 8. Optimizer mode was CHOOSE, all tables/indexes analyzed. Yeah I know, this is the same as ALL_ROWS. Stay with me, it's not that simple!

But the explain plans of critical join SQL was OK: using NL because the result set was small, as opposed to using hash joins. This was surprising, given the well known penchant of the optimizer to use hash joins all over the place when ALL_ROWS is active. But I let it go at that.

So, "dirk the daring" here decides to change the block size to 8K. After checking with multiple other sites running same software and making sure 8K is by far the most used block size for this combination, with a few on 4K and NOT A SINGLE ONE on 16K. Peoplesoft recommends 2K, but what do they know?

Export/re-create/import/analyze. Set DB_FILE_MULTIBLOCK_READS to 16. All hell breaks loose. Suddenly, all critical join SQL becomes HASH JOIN, no matter what! Hints, no hints, you name it! Heavy disk activity on the TSs with the tables being hash joined, as expected.

"dirk the daring" is beginning to look like "dirk the stoopid ass"...

Nothing else changed. Optimizer is still CHOOSE, tables have been analyzed and properly sized with uniform extent allocations of suitable sizes. Same for indexes. This was also with 16K, so that's not the problem.

First things first: Set DB_FILE_MULTIBLOCK_READS lower to try and reduce the optimizer picking full table scans. No joy. Absolutely no change whatsoever!

So, I decide to play with HASH_AREA_SIZE and jack it up to see if I can reduce somewhat the overhead of all these hash joins.

Great! Now I get MERGE JOIN CARTESIAN thrown in together with HASH JOIN in my explain plans. Did I mention "hell breaking loose" before? You should see the I/O and CPU use in this box by now. The EMC was throwing a fit...

"dirk the daring" is now feeling like someone is standing right behind
him, doing rude things to his ass...

OK, time for a coffee break and some serious thinking! No way I'm gonna go back to 16K, I know the problems I was having. Let's fix this.

First, set OPTIMIZER_MODE=FIRST_ROWS. No effect. **NO EFFECT????** What the heck is going on here? OK, re-size HASH_AREA_SIZE. The smaller I go, the more I get rid of MERGE JOIN CARTESIAN and the more it goes back to HASH JOIN only. Hmm, that's an unexpected result. But I'll live with that.

So, drop HASH_AREA_SIZE completely. Throw it away. Shoo. Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ.

Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?...

Anyways, I digress. Check, adjust, shutdown/restart, back to check, repeat. Eventually, I find a combination that makes the vast majority of HASH joins go away and I get my NL back for short result sets and the HASH to work only where I want it.

All is well. Throw in lots of users, lots of batch. System is responding well, no I/O blow outs, all is even, CPU being used where I want it, even load in the system, good memory use, rollback segs back to normal size, great throughput and response time, yadda-yadda-ho-hum-back-to-normal.

"dirk the daring" is now "dirk the hero".

However, "dirk" is an arsehole who doesn't like to be kicked in the teeth by just another piece of software, regardless of who makes it. He wants to know what the heck went wrong here and why did the optimizer behave like a temperamental spoiled little brat.

Any ideas greatly appreciated.

Nuno Souto

Please see the official ORACLE-L FAQ:
Author: Nuno Souto

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: (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 Wed May 09 2001 - 09:59:15 CDT

Original text of this message