RE: high "latch: cache buffers chains" waits in 10.2.0.3 DB

>> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of krish.hariharan_at_quasardb.com
Sent: Thursday, February 28, 2008 12:31 AM To: bkaltofen_at_gmx.de; 'Greg Rahn'
Cc: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]

On Behalf Of bkaltofen_at_gmx.de

Sent: Wednesday, February 27, 2008 6:15 AM

To: Greg Rahn

Cc: oracle-l_at_freelists.org

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.

...

--
http://www.freelists.org/webpage/oracle-l