Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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 130000). 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 <cspence_at_FuelSpot.com>@fatcity.com on 06/06/2001 03:03:17 PM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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 100000 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 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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
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 Liststo: 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 - 15:52:40 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message