From Alex.Hillman@usmint.treas.gov Wed, 09 May 2001 10:18:57 -0700 From: "Hillman, Alex" Date: Wed, 09 May 2001 10:18:57 -0700 Subject: RE: Optimizer and block size changes = trouble... Message-ID: MIME-Version: 1.0 Content-Type: text/plain Default value of hash_area_size is sort_area_size. What was your sort_area_size, and also what values of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ did you finally use? Alex Hillman -----Original Message----- Sent: Wednesday, May 09, 2001 10:51 AM To: Multiple recipients of list ORACLE-L 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? ) 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. Cheers Nuno Souto nsouto@nsw.bigpond.net.au http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: nsouto@nsw.bigpond.net.au 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@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: Alex.Hillman@usmint.treas.gov 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@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).