Re: Re: query performance following 12c upgrade
From: <l.flatz_at_bluewin.ch>
Date: Thu, 24 Sep 2015 09:18:00 +0000
Message-ID: <33059127.20989.1443086279974.JavaMail.webmail_at_bluewin.ch>
Hi Steve,
what we can see is that the time is spent not in running the query. The slight difference in plans should be no issue. Thus it must be somewhere else. The best guess seems to be parsing which the 10046 would reveal. ( I am not a 100% convinced that the runtime stats exclude all parse time, but it seems reasonable.) I think the 10053 would be good to find the root cause, but the next step would be IMHO to find out how the time is spent. Thus I vote for the 10046. Can be tkprofed for a start. BTW: The runtime stats show that the index skip scan is indeed inefficient. However timewise that is no real issue. Regards
Lothar
----Ursprüngliche Nachricht----
Von : sjb1970_at_gmail.com
Datum : 24/09/2015 - 11:06 (GMT)
An : jonathan_at_jlcomp.demon.co.uk
Cc : contact_at_soocs.de, oracle-l_at_freelists.org Betreff : Re: query performance following 12c upgrade Hi,
Which traces? I have a 10053 and 10046 from yesterday morning. My team lead has set the optimizer_features_enable parameter to 11.2.0.3 as a temporary fix as we've had a lot of complaints from users about performance. With my optimizer set to 12.1.0.2, I've just tried setting _optimizer_ads_use_result_cache to false, and re-ran the query and it was still slow. Steve
On Wed, Sep 23, 2015 at 3:57 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote: Competition for the result cache sounds viable - though 12 seconds seems a little extreme. If the OP can't get at trace file easily, or query own v$session_event or V$active_session_history then testing 12c after executing alter session set "_optimizer_ads_use_result_cache" = FALSE; might be an indicator - it should disable the use of the result cache for dynamic stats activity. 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 Stefan Koehler [contact_at_soocs.de] Sent: 23 September 2015 15:13
To: sjb1970_at_gmail.com
Cc: ORACLE-L
Subject: Re: query performance following 12c upgrade Hi Steve,
thanks for the requested data.
The execution plan (especially the important predicate section) and the amount of work is identical - expect the ROWID BATCHED part, but there is an important hint with OPTIMIZER_FEATURES_ENABLE = 12.1.0.2.
Note
Date: Thu, 24 Sep 2015 09:18:00 +0000
Message-ID: <33059127.20989.1443086279974.JavaMail.webmail_at_bluewin.ch>
Hi Steve,
what we can see is that the time is spent not in running the query. The slight difference in plans should be no issue. Thus it must be somewhere else. The best guess seems to be parsing which the 10046 would reveal. ( I am not a 100% convinced that the runtime stats exclude all parse time, but it seems reasonable.) I think the 10053 would be good to find the root cause, but the next step would be IMHO to find out how the time is spent. Thus I vote for the 10046. Can be tkprofed for a start. BTW: The runtime stats show that the index skip scan is indeed inefficient. However timewise that is no real issue. Regards
Lothar
----Ursprüngliche Nachricht----
Von : sjb1970_at_gmail.com
Datum : 24/09/2015 - 11:06 (GMT)
An : jonathan_at_jlcomp.demon.co.uk
Cc : contact_at_soocs.de, oracle-l_at_freelists.org Betreff : Re: query performance following 12c upgrade Hi,
Which traces? I have a 10053 and 10046 from yesterday morning. My team lead has set the optimizer_features_enable parameter to 11.2.0.3 as a temporary fix as we've had a lot of complaints from users about performance. With my optimizer set to 12.1.0.2, I've just tried setting _optimizer_ads_use_result_cache to false, and re-ran the query and it was still slow. Steve
On Wed, Sep 23, 2015 at 3:57 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote: Competition for the result cache sounds viable - though 12 seconds seems a little extreme. If the OP can't get at trace file easily, or query own v$session_event or V$active_session_history then testing 12c after executing alter session set "_optimizer_ads_use_result_cache" = FALSE; might be an indicator - it should disable the use of the result cache for dynamic stats activity. 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 Stefan Koehler [contact_at_soocs.de] Sent: 23 September 2015 15:13
To: sjb1970_at_gmail.com
Cc: ORACLE-L
Subject: Re: query performance following 12c upgrade Hi Steve,
thanks for the requested data.
The execution plan (especially the important predicate section) and the amount of work is identical - expect the ROWID BATCHED part, but there is an important hint with OPTIMIZER_FEATURES_ENABLE = 12.1.0.2.
--------------8<----------------
Note
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
--------------8<----------------
Is it possible that the query is suffered by latches? How long does the parse itself take? SPDs are based on the result cache and this can have nasty
side effects. You can verify this by running a SQL trace on the slow SQL as previously suggested.
By the way here are some good references about the result cache and SPD:
- http://berxblog.blogspot.de/2015/06/sql-plan-directives-and-result-cache.html
- https://dban00b.wordpress.com/2015/04/21/311/ Best Regards Stefan Koehler Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de Twitter: _at_OracleSK > Steve Bradshaw <sjb1970_at_gmail.com> hat am 23. September 2015 um 15:53 geschrieben: > > Hi, > > Please see the attached. opti12 is the results when run with optimizer_featured_enable=12.1.0.2, and opti11 it is 11.2.0.3 > > I've had to anonymyse the data/columns/tables etc so the formatting may be a little out. > > Thanks > Steve -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 24 2015 - 11:18:00 CEST