SOLVED !!! - RE: Need help tuning FTS
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.999')), 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 2 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
RE: Need help tuning FTS
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.999')), 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 2 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:
RE: Need help tuning FTS
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 2 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).
RE: Need help tuning FTS
I would recommend uping the parameter _DB_BLOCK_HASH_LATCHES by default after I believe 2000 block buffers, it defaults to 1024 all the way up to 10 blocks buffers if i remember correctly. This will generate alot of the #66 latch contention. This shouldn't equate to 6 hours compared to 10 minutes, but would certainly slow it down a bit. Most databases with more than 2,000 block buffers should probably increase this parameter or the other parameter _db_block_hash_buckets. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Wednesday, June 06, 2001 2:47 PM To: Multiple recipients of list ORACLE-L 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 2 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: Christopher Spence 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).
RE: Need help tuning FTS
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.999')), 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 2 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
RE: Need help tuning FTS
Chris, Thanks for your input ! One difference between our prod and QA instances is that the QA instance is relatively idle - while average load on the prod instance is about 175 users, with less than 5% hitting the database hard at any given point in time. I am unable to figue out why there is such a big difference in the times between the two instances. The explain plan in both instances is pretty straightforward - SELECT STATEMENT Optimizer=RULE NESTED LOOPS TABLE ACCESS (FULL) OF 'RA_CUSTOMER_TRX_LINES_ALL' TABLE ACCESS (BY INDEX ROWID) OF 'CECO_INTERFACE_KEYS' INDEX (RANGE SCAN) OF 'CECO_INTERFACE_KEYS_PK' (UNIQUE) Srini PS - You are correct in stating that the default value of _DB_BLOCK_HASH_LATCHES is 1024 for values of DB_BLOCK_BUFFERS between 2000 and some higher number ( I think in the region of 13). My understanding about _DB_BLOCK_HASH_BUCKETS in 8.1.6 is that the default value is twice the number of DB_BLOCK_BUFFERS. Christopher Spence [EMAIL PROTECTED]@fatcity.com on 06/06/2001 03:03:17 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: I would recommend uping the parameter _DB_BLOCK_HASH_LATCHES by default after I believe 2000 block buffers, it defaults to 1024 all the way up to 10 blocks buffers if i remember correctly. This will generate alot of the #66 latch contention. This shouldn't equate to 6 hours compared to 10 minutes, but would certainly slow it down a bit. Most databases with more than 2,000 block buffers should probably increase this parameter or the other parameter _db_block_hash_buckets. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Wednesday, June 06, 2001 2:47 PM To: Multiple recipients of list ORACLE-L 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 2 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
RE: Need help tuning FTS
Srini, I am sure you have lots of users in Prod compared to QA :) Anyway, the point is that you are suffering from hot block contention which reflects itself in latch waits on 'cache buffer chain'. I remember that Steve Adams has a SQL at his site that identifies blocks that are 'hot' - I have used a modified version of that to identify objects that are hot. If I know that there are corresponding Conc. Manager jobs that are accessing that part of the Apps, then I know that this is due to contending reports I have managed to reduce this by rebuilding stale indexes as heavy inserts/updates/deletes also add to this. Short of re-scheduling jobs and playing around with _DB_BLOCK_HASH_LATCHES, I don't see any solutions. Do post us if you manage to reduce contention using the _ parameter as we have a similar problem. John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
RE: Need help tuning FTS
John, Thanks for your input ! Unfortunately, this issue seems too weird ! (I know, I know, I shouldn't be saying that as a DBA !!). We have run that process three times today in prod in an effort to try and isolate the issue. We have run it when only one other user was hitting the prod database heavily using a select stmt on unrelated tables - but the problem still persists. There were no concurrent requests running at the time. The issue does not seem to be related to contention of resources. I have tried to figure out what the hot blocks are (via x$bh) but have come up empty-handed. I replied to Chris Spence's suggestions where I posted the query the explain plan - they look pretty simple. I'm trying to figure out why this behavior is not evidenced in QA. Any help would be appreciated !! I will post a solution/cause when I crack this case ! Thanks ! Srini John Kanagaraj [EMAIL PROTECTED]@fatcity.com on 06/06/2001 05:47:24 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Srini, I am sure you have lots of users in Prod compared to QA :) Anyway, the point is that you are suffering from hot block contention which reflects itself in latch waits on 'cache buffer chain'. I remember that Steve Adams has a SQL at his site that identifies blocks that are 'hot' - I have used a modified version of that to identify objects that are hot. If I know that there are corresponding Conc. Manager jobs that are accessing that part of the Apps, then I know that this is due to contending reports I have managed to reduce this by rebuilding stale indexes as heavy inserts/updates/deletes also add to this. Short of re-scheduling jobs and playing around with _DB_BLOCK_HASH_LATCHES, I don't see any solutions. Do post us if you manage to reduce contention using the _ parameter as we have a similar problem. John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
RE: Need help tuning FTS
Hi Srini, isolate the issue. We have run it when only one other user was hitting the prod database heavily using a select stmt on unrelated tables - but the problem still persists. There were no concurrent requests running at the time. The issue does not seem to be related to contention of resources. I have tried to figure out what the hot blocks are (via x$bh) but have come Is it possible to take a snapshot of v$system_event, v$sysstat before and after the program runs and look at what's happening (on both Prod/QA)? That should tell us which resources are being used Basically, what I did was to take Steve's script that looks at v$bh and group/count the hot blocks by object name. John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
RE: Need help tuning FTS
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.999')), 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 2 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
