RE: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence during HEAVY load
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <kellyn.potvin@xxxxxxxxx>
- Date: Sat, 13 Oct 2012 12:01:25 -0500
Hey Kellyn,
How would I be able to determine if that is happening? I'm sort of backing my
way into this I guess.
Chris
-----Original Message-----
From: Kellyn Pot'Vin [mailto:kellyn.potvin@xxxxxxxxx]
Sent: Saturday, October 13, 2012 11:59 AM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence
during HEAVY load
Hey Chris,
I see you've included PGA settings in your title but, nowhere in your email do
you discuss if you have concerns that you may be over loading your pga and
"swapping" to temp, which would explain the extra IO as well...
Have you looked into this avenue at all?
Kellyn
Christopher.Taylor2@xxxxxxxxxxxx wrote:
>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:
