Re: how 8.1.7.4 calculates the # of 'library cache' latch children ?

Sybrand Bakker <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message
> The number of latches is determined by the size of the shared pool.

Great! Thank you.

> There should be no need to increase the cpu_count, or you must be in
> want of to create havoc. Oracle automatically determines the number of
> cpu's correctly for most platforms, and setting an artificial number
> may have adverse results.

Yes, sorry, i didn't explained myself clearly enough - what we did is to hot-plug 4 cpus and bounce the database during the night. As I expected, Oracle saw the new cpus on restart and automatically re-set cpu_count to 14 - we didn't manually set the parameter.

> To reduce the number of parses you should set cursor_sharing to force.
> This can be done on session, system and instance level.

Already tried, unfortunately that "fine" application has invented a new type of cursor-sharing-resistant sql statement, i.e.

select ... from t t123 where ...;
select ... from t t124 where ...;
select ... from t t125 where ...;
select ... from t t126 where ...;

Where the alias t<number> changes at every parse (!!)

The purpose if that alias is unknown, anyway it sabotages the instance nicely (100% of active sessions waiting on 'latch free', where the latches waited for are the parsing-related 'shared pool','library cache','row cache objects' and 'latch wait list'). Upping the library cache latches from 11 to 17 has brought some relief - until the next traffic spike, obviously.

Thank you for your time!
Alberto Received on Wed Nov 26 2003 - 06:54:09 CST