RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load (bind peeking?)
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Oct 2012 17:22:44 +0000
Chris,
Did you resolve this problem? I saw a response explaining when "peeks" happen
on binds, but if it helps here's a query to check the plan in question as to
whether or not binds were peeked and what those peeked values are:
SELECT *
FROM TABLE(dbms_xplan.display_cursor('&sql_id',&child,'+PEEKED_BINDS'));
Is the default stats gathering job enabled and running each night? Because if
it is AND the table you're working on is frequently becoming "stale", it's
stats could be getting gathered nightly which would invalidate xplans and cause
new ones to be generated, possibly peeking at bind values that may not be
optimal for most cursors.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring@xxxxxxxxxx
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Saturday, October 13, 2012 12:09 PM
To: Christopher.Taylor2@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence
during HEAVY load (bind peeking?)
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Saturday, October 13, 2012 11:49 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
