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

On 2/27/08, bkaltofen_at_gmx.de <bkaltofen_at_gmx.de> 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
--
http://www.freelists.org/webpage/oracle-l