David,
The QA instance was refreshed by copying the datafiles, not via exp/imp.
The output from DBA_TABLES for the two tables in both instances are
identical except for the number of rows (and blocks etc).
Srini
[EMAIL PROTECTED]@fatcity.com on 06/06/2001 06:56:28 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Srini,
How did you refresh your development instance, with an export and import?
Is it possible that in the process you've eliminated some chained/migrated
rows and/or rebuilt your index - essentially done a reorg? What comes out
of dba_tables on the two instances for these two tables?
David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
Srini.Chavali@C
ummins.com To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: Need help
tuning FTS
m
06/06/2001
04:07 PM
Please respond
to ORACLE-L
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).
--
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).
