AW: Reduce latch row cache objects with event 10089
Date: Wed, 18 Mar 2015 06:08:39 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B6407C88B2E91_at_smxc002.trivadis.com>
Hallo Jonathan,
that is the problem - the queries are usually based on 2 tables, both with at least 30 indexes. Current application enhancement added kind of ACL , so one table is 2times in the query, second 7times (from about 3times before). I did small test, set on '7times' table all but 4 needed indexes to invisible, which reduced the parse time from 0.29 to 0.17s I check continually index usage in shared pool and AWR, there are only 1-2 index candidates for elimination. We try to execute ACL separately and add the result to the query as list of constants
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 18:08
An: oracle-l_at_freelists.org
Betreff: 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 -- http://www.freelists.org/webpage/oracle-l-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 18 2015 - 07:08:39 CET