Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 9 Oct 2017 20:59:44 +0100
On 09/10/17 20:41, Tim Gorman wrote:
> ....
What the documentation is saying is that, when PGA_AGGREGATE_LIMIT is
exceeded, the RDBMS will first "attack" sessions using lots of
"untunable" PGA, because there is nothing else that the Oracle kernel
can do to control that. Tunable memory can be adjusted, but untunable
memory can only be killed, and heap memory is always released with the
calling operation or process is killed.
What I found recently, with 12c on Windows (in the cloud) was that when
the PGA_AGGREGATE_LIMIT was exceeded only runnable/running sessions wre
getting killed by Oracle.
There were a number of sessions, submitted by an application server, to
run reports/calculations etc. When the reports were done, the sessions
should have logged out, but didn't for some as yet, unknown reason - the
vendor is unable to explain why. So there were lots of sessions sitting
on event "SQL*NET: message from client" for hours and hours and days and
weeks (occasionally) gradually building up the amount of PGA in use.
I saw over 650 of these idle sessions on occasions and when the PGA ran
out, there were "screams of horror" from the users as their running
sessions were killed leaving these damned idle sessions untouched, and
consuming PGA. The users were not happy! (And neither was the DBA!)
In the end, and in the inability of the vendor to come up with (a) a
reason and (b) a solution, I created a scheduled job to run every half
hour, until the universe ends, checking for these sessions very
carefully, and killing any that were previously running reports, that
were now idle, that were idle on the above event, had been idle for over
30 minutes and were executing as a particular OS User while logged in as
a specific database user. That "solved" our particular problem.
I finished that contract before the vendor had worked out what the
problem was, and as far as I know from recent conversations with my now
ex colleagues, they have simply said that my solution is the fix! Go figure.
This implies, to my ageing mind, that the algorithm used by Oracle that
selects the sessions to be killed is somewhat flawed perhaps? At least,
on Windows.
Just my £0.02.
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG
Company Number: 05132767
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Ravi Teja Bellamkonda
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Ram Raman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Franky Weber Faust
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Franky Weber Faust
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Tim Gorman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY - Norman Dunbar
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Tim Gorman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Tim Gorman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Norman Dunbar
- » RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mark W. Farnham
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Tim Gorman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Juan Carlos Reyes Pacheco
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Ram Raman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Reen, Elizabeth
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Ram Raman
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala
- » RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Reen, Elizabeth
- » RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Reen, Elizabeth
- » Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY- Mladen Gogala