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
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
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
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
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(
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
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
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
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
ummins.com To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: Need help tuning FTS
10 matches
Mail list logo
