AW: Reduce latch row cache objects with event 10089
- From: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Mar 2015 06:08:39 +0000
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]" im
Auftrag von "Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx]
Gesendet: Dienstag, 17. März 2015 18:08
An: oracle-l@xxxxxxxxxxxxx
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
@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--
//www.freelists.org/webpage/oracle-l
Other related posts:
