Re: Performance Problem after Migration

Denny,

    BINGO! You hit it right on the head. By halving the db_file_multiblock_read_count, performance returned to normal, if not better. I thought I had done that when I changed the block size, but I must have been interrupted, or just plain forgot. Thank you to everyone that responded!

groups_at_koovakattu.com wrote:

> Hi Scott,
>
> I wouldn't worry about the hit ratios. Have you tried to find badly
> performing SQL. The chances are the execution plan may have changed for some of
> the frequently used SQL. When you doubled the block size, did you halve the
> db_file_multiblock_read_count ? The optimizer may be prefering FTS now.
>
> Also
>
> - How much free memory does the new machine have ? 52G free memory looks odd.
> - Did the Oracle version change ?
> - Since the block size doubled, have you ruled out contention for blocks and
> ITLs ?
> - Look at v$session_event and see if anything odd shows up. You may also want
> to run sql_trace on a few sessions.
>
> Regards,
> Denny
>
> Quoting Scott Canaan <srcdco_at_ritvax.rit.edu>:
>
> > I have an interesting problem. I recently migrated a database
> > from
> > a Digital Unix system to a Sun Solaris system, with an EMC disk array.
> > Since I was going to be migrating the database, I decided to double
> > the
> > block size from 4k to 8k. I also created the tablespaces on the new
> > box
> > as locally managed, with fixed extent sizes. Then I did a full
> > database
> > export, ftp'd the file, and imported it. It went well, or so I
> > thought. The application works, but it is much slower than it was on
> > the original (Digital) system.
> > One side effect was that I didn't change the db_block_buffers, so
> > that part of the SGA essentially doubled in size. The library cache
> > hit
> > rate was always around 99%, but the data cache hit rate used to only
> > be
> > about 85%, now it is 95 - 99%. All of the sorts are being done in
> > memory, with memory to spare (52G yesterday, not used). According to
> > the statistics, the database should be screaming. But the users are
> > complaining that the online screens are taking much longer to come up.
> > They say that the screens used to come up in 1 - 2 seconds, now it's
> > taking about 10.
> > Just for fun, I tried deleting the statistics and changing the
> > optimizer_mode from choose to rule. That made things worse, which was
> > what I expected, but it was worth a try. I have tried to capture a
> > session, but I need to get a repository up to look at the trace that
> > was
> > generated. Until then, I'm pretty baffled. I'd appreciate any ideas
> > that anyone has on this.
> >
> > Thank you.
> >
> > --
> > Scott Canaan (srcdco_at_rit.edu)
> > (585) 475-7886
> > "Life is like a sewer, what you get out of it depends on what you put
> > into it" - Tom Lehrer
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: groups_at_koovakattu.com
>
> 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_at_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).

--
Scott Canaan (srcdco_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it" -
Tom Lehrer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Canaan
  INET: srcdco_at_ritvax.rit.edu

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_at_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).