RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load

The tuning I was doing was *only* on the SELECT portion of the INSERT statement in question.

I failed to consider that the SQL execution plans for the original INSERT SQL might still be cached (which they were), so Oracle wasn't generating a new plan (as far as I can tell).

Following http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/ I was able to extract the different execution times for the INSERT SQL and implement a SQL profile using the best elapsed time. It seems to be working so far.

I'll find out tonight if it works in the batch process.

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 Received on Sat Oct 13 2012 - 21:17:35 CEST