RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load (bind peeking?)
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Oct 2012 12:08:31 -0500
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
Other related posts:
