Need help tuning FTS

From: <Srini.Chavali_at_Cummins.com>
Date: Wed, 06 Jun 2001 11:10:17 -0700
Message-ID: <F001.0031EB46.20010606104644@fatcity.com>

All,
I need some help in tuning a select statement that performs a FTS. (The FTS is deliberate !) It takes over 5 hours to run in our prod instance, but takes less than 10 min in our QA instance. The QA instance was copied from prod about 6 weeks ago and is identical to prod, except for db_block_buffers whose value is 20000 in prod and 15000 in QA. The instances run on identical hardware (Compaq TRU 64, 8 Gig RAM & 8 cpus). The table in question has 3 million rows in prod and 2.8 million rows in QA. Explain plans are identical. DB version in both is 8.1.6.0 and both are using RBO.

While running in prod, I took a level 12 trace and here is a snippet form the trace file -

  WAIT #1: nam='latch free' ela= 2 p1=17190174328 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17190304728 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17189692728 p2=66 p3=0
  WAIT #1: nam='dbfile scattered read' ela= 1 p1=12 p2=266267 p3=16
  WAIT #1: nam='latch free' ela=2 p1=17189819928 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 27 p1=17190272728 p2=66 p3=0
  WAIT #1: nam='db file scattered read' ela= 0 p1=12 p2=266269 p3=15
  WAIT #1: nam='latch free' ela= 3 p1=17189935928 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17189917528 p2=66 p3=0
  WAIT #1: nam='db file scattered read' ela= 5 p1=12 p2=266804 p3=16
  WAIT #1: nam='db file scattered read' ela= 1 p1=12 p2=266820 p3=16
  WAIT #1: nam='db file scattered read' ela= 1 p1=12 p2=266836 p3=16
  WAIT #1: nam='db file scattered read' ela= 4 p1=12 p2=266852 p3=16
  WAIT #1: nam='db file scattered read' ela= 1 p1=12 p2=266868 p3=16
  WAIT #1: nam='latch free' ela= 5 p1=17190273528 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17190310328 p2=66 p3=0
  WAIT #1: nam='latch free' ela=1 p1=17189831128 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17189801528 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 2 p1=17189801528 p2=66 p3=0
  WAIT #1: nam='latch free' ela= 3 p1=17190166328 p2=66 p3=0
  WAIT #1: nam='db file scattered read' ela= 1 p1=12 p2=266884 p3=16
  WAIT #1: nam='db file scattered read' ela= 3 p1=12 p2=266900 p3=16
  WAIT #1: nam='latch free' ela= 2 p1=17190259928 p2=66 p3=0

I see a lot of time is spent in waiting for latch #66 (cache buffer chains) - Metalink states that this could be because of a *very* hot block being accessed frequently,
further snooping (via x$bh) shows that there is no such contention.

Can anybody help ?

Thanks much !
Srini Chavali
Oracle DBA
Cummins Inc

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Srini.Chavali_at_Cummins.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). Received on Wed Jun 06 2001 - 13:10:17 CDT