All,
Thanks for your help - the issue is now resolved.
The culprit was not the 3 million row table but the smaller table
(CECO_INTERFACE_KEYS). The selectivity of the PK index on this table in
prod was very poor (the PK has 3 columns whereas the select is using just
the first column) and the result was almost like a cartesian product. The
selectivity is much much better in our QA instance (the data is slightly
older) and hence the process runs much faster.
We solved it by creating another index with better selectivity and ran the
process in under two minutes in prod.
Thanks again !
Srini Chavali
Oracle DBA
Cummins Inc
Mohammad Rafiq <[EMAIL PROTECTED]>@fatcity.com on 06/07/2001 01:37:45
PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Can you compare indexes on both the boxes for tables involved...
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 06 Jun 2001 13:07:29 -0800
Yosi,
Thanks for your input !
No, there is no reference to a sequence. The offending statement is
SELECT
SUBSTR(INTERFACE_LINE_ATTRIBUTE6,1,LENGTH(INTERFACE_LINE_ATTRIBUTE6)-4),
SUBSTR(INTERFACE_LINE_ATTRIBUTE7,1,LENGTH(INTERFACE_LINE_ATTRIBUTE7)-4),
LTRIM( TO_CHAR( RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,
'S0.999999999999999EEEE')),
RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE14
FROM
AR.RA_CUSTOMER_TRX_LINES_ALL,
FNDC.CECO_INTERFACE_KEYS
WHERE
RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE14 = 'ADJUSTMENT'
AND
SUBSTR(INTERFACE_LINE_ATTRIBUTE6,1,LENGTH(INTERFACE_LINE_ATTRIBUTE6)-4) =
CECO_INTERFACE_KEYS.ATTRIBUTE1
AND
SUBSTR(INTERFACE_LINE_ATTRIBUTE7,1,LENGTH(INTERFACE_LINE_ATTRIBUTE7)-4) =
CECO_INTERFACE_KEYS.ATTRIBUTE2
AND INTERFACE_PGM_NAME = 'CEP-SUBLEDGER'
AND STATUS = 'PENDING_ENGINE_INFORMATION_CREATION'
[EMAIL PROTECTED]@fatcity.com on 06/06/2001 02:47:28 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Wild guess, way out of left field, is there a sequence in
the select statement, that might have a high cache value
in QA, but a low cache value in prod?
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 06, 2001 2:47 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Need help tuning FTS
>
>
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
