Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 859A61961097 for ; Fri, 11 Nov 2016 11:54:38 +0100 (CET) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Fri, 11 Nov 2016 11:54:38 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3FC1161DFB; Fri, 11 Nov 2016 05:54:37 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JbuSnoqaTLVy; Fri, 11 Nov 2016 05:54:37 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4FFC661E24; Fri, 11 Nov 2016 05:54:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 11 Nov 2016 05:53:02 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 79D1661C37 for ; Fri, 11 Nov 2016 05:53:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Uol9kC2FMWHr for ; Fri, 11 Nov 2016 05:53:02 -0500 (EST) Received: from mail-ua0-f180.google.com (mail-ua0-f180.google.com [209.85.217.180]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 35A8A61C34 for ; Fri, 11 Nov 2016 05:53:01 -0500 (EST) Received: by mail-ua0-f180.google.com with SMTP id 51so10292384uai.1 for ; Fri, 11 Nov 2016 02:53:01 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=gc4cbqJQWQuKVyl4SmtRQxua7arlaKWhQDJOCNpcA4g=; b=c6cOL9HNaiQHNVQ4uRXiYTEjRyKM7Da7CtzbGp4UZBJzVFSO5kT527+vs+1fNXcNl0 wIGhGLQZPAqVoXW65w8i2tO6zMQj0nJ/bvR707ieycm6SNlIJDxXXv+vZ4jmORIJizN1 cDzOO7PVlzRCFUGWLetS3IIAetTlFMDWa9rSLCJ9cWOqTYwGP6GDjlkcBAFOVLsC06W4 Ak+20ikZVQe0PsHCiOccNpwKz4c8wVjuX9G0bVRPnGH+thPfJA9nZQW+7f8V0vQfyHjE a/2JMt4CJ+/ins39iLb2/gmpAIQVVc8kfxuaGBivChvphs6zo6vvkqS4t4+s8JzyKcv1 FXHQ== X-Gm-Message-State: ABUngvfyXBJsWMvkciybeu8mBksax9S1OHRk57cCn9nrh2WhzJjK3s7mHkfQog84IkoyQE/C6oSEZAGY7WKNxw== X-Received: by 10.159.38.133 with SMTP id 5mr1427719uay.102.1478861581211; Fri, 11 Nov 2016 02:53:01 -0800 (PST) MIME-Version: 1.0 Received: by 10.159.38.129 with HTTP; Fri, 11 Nov 2016 02:52:40 -0800 (PST) In-Reply-To: References: From: Eriovaldo Andrietta Date: Fri, 11 Nov 2016 08:52:40 -0200 Message-ID: Subject: Re: How to force Oracle generate a new execution plan To: Mohamed Houri Cc: Jonathan Lewis , "info@www.sqltools-plusplus.org" , "oracle-l@freelists.org" Content-Type: multipart/alternative; boundary=94eb2c04791c38652f0541044bd6 X-archive-position: 66810 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ecandrietta@gmail.com Precedence: normal Reply-To: ecandrietta@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --94eb2c04791c38652f0541044bd6 Content-Type: text/plain; charset=UTF-8 Hello, I will suggest to development the use of a temporary table in order to get the same plan for all executions. For my scenary it seems to be the best way. Thanks for answers. Regards Eriovaldo 2016-11-11 6:44 GMT-02:00 Mohamed Houri : > Randolf, > > Yes you're right. And I have been very often confronted to such a > situation in the last couple of years. > > I have optimized an overnight query with a varying IN-LIST and fixed a SQL > Profile over it. A couple of weeks later > there was a performance issue within the same job but using a different > sql_id. I have recognized that big query and asked > the end user whether it is the same query as the one I have SQL Profiled > the last couple of weeks; and the answer was yes with an addition of a > couple of few new clients in the varying IN-LIST. > > I have then transferred the plan of the SQL Profiled query to the new > sql_id and the performance was good again. > > I have asked, at the same time, the development team to replace the > IN-LIST with a select from a dedicated table so that the sql_id will not > change and the SQL Profile will be used. > > Best regards > Mohamed Houri > > 2016-11-11 9:28 GMT+01:00 Jonathan Lewis : > >> >> Randolf, >> >> I had read the question differently, viz: there are always 1000 bind >> variables of which a limited number are set. >> Re-reading the original posting, though, I see that your interpretation >> looks like a better match for the description. >> >> Under my interpretation, the abrupt (and dramatic) switch from fast to >> slow on the 20/21 value break point would simply be due to the optimizer >> producing a different plan (the typical indexed access/NLJ vs. full >> tablescan/HJ type of thing) if if had to optimize for a slightly larger >> predicted data set - making the day's workload dependent on the size of the >> first set of values used. >> >> >> Regards >> Jonathan Lewis >> >> ________________________________________ >> From: oracle-l-bounce@freelists.org on >> behalf of Randolf Geist >> Sent: 11 November 2016 08:13:13 >> To: ecandrietta@gmail.com >> Cc: oracle-l@freelists.org >> Subject: Re: How to force Oracle generate a new execution plan >> >> Hi Eriovaldo, >> >> you've got already answers to your specific questions, however the way I >> you understand your description these answers won't necessarily help you >> with your problem. >> >> If your application issues the dynamic query using a variable length IN >> list of bind variables (so e.g. uses IN (:b1, :b2, :b3) in case of three >> members, IN (:b1, :b2, :b3, :b4) in case of four members etc.) then you >> effectively end up with different SQL texts / SQL_IDs for each of these >> variations - so you actually don't re-use any of the plans potentially >> already available in the Shared Pool, except for those cases where you >> issue the statement with the same number of bind variables but >> different/same bind values. >> >> Hence your problem is very likely related to different execution plans >> being generated for the different variations / SQL_IDs, but it could >> also be a optimization / hard parse issue - sometimes with a higher >> number of bind variables the optimizer might take very long to come up >> with an execution plan. >> >> So as a first step you need to identify what the problem actually is >> (bad execution plan, hard parse, something else) by measuring where the >> time goes e.g. via SQL trace, or maybe via Active Session History if you >> have an Enterprise Edition plus Diagnostic Pack license, then you can >> decide what measures you have at your disposal. >> >> Randolf >> >> > 1.) How can I do to force Oracle always generate a new execution plan >> and >> > not reuse the plan that is in cache ? >> > 2.) Is there a way to clear a execution plan for a specific sql_id ? (I >> > have the sql_id that supose is with the bad execution plan). >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> > > > -- > > Houri Mohamed > > Oracle DBA-Developer-Performance & Tuning > > Member of Oraworld-team > > Visit My - Blog > > Let's Connect - *Linkedin > Profile * > > My Twitter - MohamedHouri > > > > --94eb2c04791c38652f0541044bd6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,=C2=A0

I will suggest to development the use of a temporary table =C2=A0in o= rder to get the same plan for all executions.
For my s= cenary it seems to be the best way.


Regards
Eriovaldo

2016-11-11 6:44 GMT-02:00 Mohamed Houri <mohamed.= houri@gmail.com>:
Randolf,

Yes you're right. And = =C2=A0I have been very often confronted to such a situation in the last cou= ple of years.

I have optimized an overnight query = with a varying IN-LIST and fixed a SQL Profile over it.=C2=A0 A couple of w= eeks later
there was a performance issue within the same job but = using a different sql_id. I have recognized that big query and asked
<= div>the end user whether it is the same query as the one I have SQL Profile= d the last couple of weeks; and the answer was yes with an addition of a co= uple of few new clients in the varying IN-LIST.

I = have then transferred the plan of the SQL Profiled query to the new sql_id = and the performance was good again.

I have asked, = at the same time, the development team to replace the IN-LIST with a select= from a dedicated table so that the sql_id will not change and the SQL Prof= ile will be used.

Best regards
Mohamed H= ouri

<= div>
2016-11-11 9:28 GMT+01:00 Jonathan Lewis <= jonathan@jlcomp.demon.co.uk>:

Randolf,

I had read the question differently, viz: there are always 1000 bind variab= les of which a limited number are set.
Re-reading the original posting, though, I see that your interpretation loo= ks like a better match for the description.

Under my interpretation, the abrupt (and dramatic) switch from fast to slow= on the 20/21 value break point would simply be due to the optimizer produc= ing a different plan (the typical indexed access/NLJ vs. full tablescan/HJ = type of thing) if if had to optimize for a slightly larger predicted data s= et - making the day's workload dependent on the size of the first set o= f values used.


Regards
Jonathan Lewis

________________________________________
From: or= acle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> on b= ehalf of Randolf Geist <info@www.sqltools-plusplus.org>
Sent: 11 November 2016 08:13:13
To: ecandrietta@= gmail.com
Cc: oracle-l@fr= eelists.org
Subject: Re: How to force Oracle generate a new execution plan

Hi Eriovaldo,

you've got already answers to your specific questions, however the way = I
you understand your description these answers won't necessarily help yo= u
with your problem.

If your application issues the dynamic query using a variable length IN
list of bind variables (so e.g. uses IN (:b1, :b2, :b3) in case of three members, IN (:b1, :b2, :b3, :b4) in case of four members etc.) then you
effectively end up with different SQL texts / SQL_IDs for each of these
variations - so you actually don't re-use any of the plans potentially<= br> already available in the Shared Pool, except for those cases where you
issue the statement with the same number of bind variables but
different/same bind values.

Hence your problem is very likely related to different execution plans
being generated for the different variations / SQL_IDs, but it could
also be a optimization / hard parse issue - sometimes with a higher
number of bind variables the optimizer might take very long to come up
with an execution plan.

So as a first step you need to identify what the problem actually is
(bad execution plan, hard parse, something else) by measuring where the
time goes e.g. via SQL trace, or maybe via Active Session History if you have an Enterprise Edition plus Diagnostic Pack license, then you can
decide what measures you have at your disposal.

Randolf

> 1.) How can I do to force Oracle always generate a new execution plan = and
> not reuse the plan that is in cache ?
> 2.) Is there a way to clear a execution plan for a specific sql_id ? (= I
> have the sql_id that supose is with the bad execution plan).
--
http://www.freelists.org/webpage/oracle-l


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





--

Houri Mohamed=C2=A0

Oracle DBA-Developer-Performance & Tuning=C2=A0

Member of=C2=A0Oraworld-team

Visit My =C2=A0 =C2=A0 =C2=A0 =C2=A0 -=C2=A0Blog

Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -=C2=A0MohamedHouri



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