RE: high "latch: cache buffers chains" waits in 10.2.0.3 DB
- From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
- To: <krish.hariharan@xxxxxxxxxxxx>, <bkaltofen@xxxxxx>, "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Feb 2008 08:46:25 -0600
>> We identified one select statement accessing the table with hot
blocks, that consumed over 90% of CPU time during high load and max
processes.
RS: Amdahl's law states that by tuning or removing remaining 10%
bottleneck, you would only gain 10% benefit. Simply put, if 90% cpu time
spent in one SQL and if cpu time is what you are trying to reduce, this
SQL need to be researched further. Everything else you do, will improve
performance only by 10%. Further more, higher cpu usage is a typical
symptom of latch contention due to spinning.
BTW, if there is no statstpack or AWR report, what method was used to
conclude 90% of cpu used by this CPU. Any sql trace files?
Hot blocks in question, Are they table blocks or index blocks? If Index
block, are they root block?
>> Again, I can not test anything, as we have found no way to reproduce
the problem by will and we have no test database.
RS: Why not? Why can't you take the SQL, take bunch of those tables to
any database, write couple of scripts, ramp up the concurrency with many
parallel executions (or scale it depending upon server capacity) and
reproduce that?
Amdahl's law: http://en.wikipedia.org/wiki/Amdahl's_law
{Sorry, I had to remove much lines from this thread to avoid over
quating}
Thanks
Riyaj "Re-yas" Shamsudeen
ERP Financials DBA, New AT&T
OakTable Member - www.oaktable.net
(469) 229 7769 (W)
(469 )426 7637 (C)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
krish.hariharan@xxxxxxxxxxxx
Sent: Thursday, February 28, 2008 12:31 AM
To: bkaltofen@xxxxxx; 'Greg Rahn'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: high "latch: cache buffers chains" waits in 10.2.0.3 DB
I still maintain that divide and conquer and partitioned problem solving
...
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of bkaltofen@xxxxxx
Sent: Wednesday, February 27, 2008 6:15 AM
To: Greg Rahn
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: high "latch: cache buffers chains" waits in 10.2.0.3 DB
Hello,
thanks for all the responses.
I will try to respond to all suggestions.
..
We identified one select statement accessing the table with hot blocks,
that consumed over 90% of CPU time during high load and max processes.
And there is only one insert statement on the mentioned table.
..
What you think about moving the table to a tablespace without ASSM and
increasing the freelists and freelist_groups? As far as I know
freelist-changes are not possible in ASSM tablespaces.
...
Other related posts:
