Re: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load (bind peeking?)

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Sat, 13 Oct 2012 21:03:44 +0200
Message-ID: <CAJu8R6jxcAApyofL5FiBwbgHtxz1BBGoHEkL5v=UVKSi3BxJDQ_at_mail.gmail.com>

Bind variable peeking occurs during the hard parse phase. The execution plan optimized during this phase using the bind variable of this phase will be shared by subsequent equivalent queries (using different bind variable values) until this shared execution plan is thrown away from the cache or until a change in the optimizer environment occurs. This Is the disadvantage of using bind variables.

As per regards to your remark concerning the good execution time of a query when you execute it manually compared to the bad execution time of the same query executed by the overnight job when concurrent process are active in the same database, you have to consider two things in my opinion

   (1) if the concurrent process are modifying the data your query is selecting you will find that Oracle will do more than a simple select that you are thinking of. Particularly, a read consistent image of your data could necessitate more time when compared to a select on stable data.

 (2) I suppose that when you execute your query manually you are using literal values which is something different for the CBO when the same query is run using bind variables

Best Regards

Mohamed Houri
www.hourim.wordpress.com

2012/10/13 <Christopher.Taylor2_at_parallon.net>

> It just struck me that this query could be a victim of bind peeking.
>
> We loop through multiple ORG_IDS and we see this perf problem for
> different orgs every night - each org may or may not have data to process
> for that particular night.
>
> I can't remember: Does Oracle peek the binds for each execution, or only
> for the first execution when the SQL is not in the cache?
>
> If Oracle is peeking at the bind when the SQL is first loaded (and not
> after that) then the first ORG_ID could be causing subsequent executions to
> be suboptimal - or am I going down the wrong path?
>
> Chris
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Christopher.Taylor2_at_parallon.net
> Sent: Saturday, October 13, 2012 11:49 AM
> To: oracle-l_at_freelists.org
> Subject: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence
> during HEAVY load
>
> This is more of a curiosity question really.
> If the database is under high load (many sessions, lots of IO, multiple
> statements accessing the same tables) would it be reasonable to think that
> the optimizer might choose a different execution plan based on how the
> workload area is being used versus when the database is under light load?
>
> I have a particular query (same query I've been working on the last 3
> days) that runs well when I test it, but at night its running like a dog.
>
> Some of the concurrent processing we have running at the same time is:
>
> a.) Expdp (UDE) (node 1)
> b.) Batch Processing running across multiple sessions (all/most on node1)
> c.) Batch processing generates a lot of IO
>
> It appears (and I'm having trouble verifying) that this particular insert
> statement (from SELECT) is choosing a poor plan at night but runs well when
> I examine it.
>
> I've run it with and without tracing (to rule out tracing giving me a
> better plan).
>
> Chris
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


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

Received on Sat Oct 13 2012 - 21:03:44 CEST