Re: Performance Problem after Migration

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