Two other things to check, then: a) can you get rid of any indexes - the more you have the more work it takes to optimise a statement. b) can you check v$active_session_history to see if there are any statements which spend a lot of CPU time in parsing ? It's possible that a small class of statements is responsible for the problem without standing out. There are two columns (in_parse, and in_hard_parse, I think) that would be particularly relevant grouped by time and sql_id; and you might be able to do something with checking for SQL_IDs that are blocked by statements that are in_hard_parse. You may find (if you're lucky) that there's some particular transformation that introduces a lot of extra work in a few cases. Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ________________________________________ From: Petr Novak [Petr.Novak@xxxxxxxxxxxx] Sent: 17 March 2015 15:29 To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx Subject: AW: Reduce latch row cache objects with event 10089 Hallo Jonathan, thank you very much for your answer.We already use the previous options (all_rows,binds, cursor_sharing=force) I have also tried to set the optimizer_features for single statement to 10.2.0.4, which resulted in bad plan. Best Regards, Petr ________________________________________ Von: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]" im Auftrag von "Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx] Gesendet: Dienstag, 17. März 2015 15:41 An: oracle-l@xxxxxxxxxxxxx Betreff: RE: Reduce latch row cache objects with event 10089 Take note that it's the last option on the list - there's a good reason for that. You may find that some current execution plans change (which may be for the worse) if you set this event, especially if you've set the optimizer_mode to a low first_rows(N). This is because the alphabetic tie-break will no longer apply for matching costs, and you may find Oracle changing its choice of index for accessing a table, and the order of accessing rows can make a big difference to the efficiency. Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Petr Novak [Petr.Novak@xxxxxxxxxxxx] Sent: 17 March 2015 14:16 To: oracle-l@xxxxxxxxxxxxx Subject: Reduce latch row cache objects with event 10089 Hallo, according Metalink Note 1485410.1 event 10089 could be set. Does anybody has experience with it ? Best Regards, Petr-- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l