Re: high "latch: cache buffers chains" waits in 10.2.0.3 DB
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: bkaltofen@xxxxxx
- Date: Wed, 27 Feb 2008 14:04:13 -0800
On 2/27/08, bkaltofen@xxxxxx <bkaltofen@xxxxxx> wrote:
> 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.
You need to find that select that is being frequently executed. It
possibly is doing a table scan and run by every session that logs on
to the database. Take a look at the app source code if available. If
you cant determine it from the statspack report, try a logon trigger
to reverse engineer the sql it executes. Or look at v$sql filter by
whatever and order by executions.
Given that waiting for a cache buffers chains latch is an event for
read operations and free lists have to do with writes, I'd say the
chances of any benefit from moving to MSSM from ASSM are about zero.
If it was an issue you would see 'buffer busy waits'.
Sounds like you might have that statement already. Good luck.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
