"a tale of two optimizers"

Greetings, all,

   I'm supporting a couple of databases on a IBM RS/6000 S70, with    eight (8) 125MHz processors, 2GB RAM and about 72GB of SSA disks.    The application is PeopleSoft 6.10.20, Fixed Assets and AP/GL, with    AIX 4.3.1 and Oracle 7.3.4.4.

   I recently reorganized the databases, and changed the blocksize    from 4K to 8K. I also changed the OPTIMIZER_MODE from CHOOSE to    RULE, and adjusted parameters such as LOG_BUFFER, SORT_AREA_SIZE,    etc.

   The first week after the reorganization, Crystal Reports that had    been running for an hour went down to two minutes; some of the    nVision reports improved as well. Others still ran over an hour.    I attributed this to the defragmentation that the reorg took care of    the more efficient blocksize, and the rebuilding of indexes.

   The second week, which began our month-end close, the system went    to pieces - all eight (8) of the processors on the S70 were _pegged_    at 85-90%, and complaints rolled in about s-l-o-w response time.    I kept getting pulled into meetings and asked what the problem was.

   I changed the OPTIMIZER_MODE back to CHOOSE, ran an ANALYZE TABLE...    COMPUTE STATISTICS, and the next day, everything was great!    The users were happy, they were able to get most of their reports    done - the nVision reports were still running in about an hour,    and the Crystal Reports were taking about 2-10 minutes (there were    a lot more people on the system).

   The other thing I noticed was that when the OPTIMIZER_MODE was    set to RULE, the individual buffer hit ratios were about 99-100%,    overall about 98%. CONSISTENT_GETS were in the hundreds of millions.    When I changed it back to CHOOSE, individual buffer hit ratios    went down a bit, with most being between 97-100%, but the overall    hit ratio went down to about 80% (it had never gone below 90% when    the mode was RULE). CONSISTENT_GETS went down to the hundreds of    thousands.

   It seems that RULE caused everything to be in memory, which    inflated the buffer hit ratio (making me thing everything was    great).

   I would appreciate any insights on this - I can't ever get a    straight answer from PeopleSoft when it comes to tuning Oracle.

   Thank you (I know this is rather long)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 04 1999 - 15:42:10 CST