No, but it's got to be one of the best posts I've read in a long time!!
Regards
HJR
"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message
news:3af916d1.4989094_at_news-server...
> 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.
>
> Incidentaly, 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?
>
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
Received on Wed May 09 2001 - 09:27:01 CDT
