RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load (bind peeking?)
Date: Fri, 19 Oct 2012 17:22:44 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4010442354C_at_LITIGMBCRP02.Corp.Acxiom.net>
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_at_acxiom.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Saturday, October 13, 2012 12:09 PM To: Christopher.Taylor2_at_parallon.net; oracle-l_at_freelists.org 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_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
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 19 2012 - 19:22:44 CEST