RE: Reduce latch row cache objects with event 10089

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
_at_jloracle

________________________________________
From: Petr Novak [Petr.Novak_at_trivadis.com]
Sent: 17 March 2015 15:29
To: Jonathan Lewis; oracle-l_at_freelists.org
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_at_freelists.org [oracle-l-bounce_at_freelists.org]" im Auftrag von "Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk]
Gesendet: Dienstag, 17. März 2015 15:41
An: oracle-l_at_freelists.org
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
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Petr Novak [Petr.Novak_at_trivadis.com]
Sent: 17 March 2015 14:16
To: oracle-l_at_freelists.org
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--
http://www.freelists.org/webpage/oracle-l


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