Hi Liz,
You don't have PGA_AGGREGATE_LIMIT on 11g. Oracle doesn't kill sessions
on 11g. On 12c, a good way of avoiding PGA problems is to use
multi-threaded execution mode. Oracle allocates all the memory at once,
just as with the shared server configuration and manages it
automatically. There are significant differences: with the shared
server, the allocated memory is still shared and latches are semaphores
for which you need a device driver. With multi-threading, the allocated
memory is the normal process memory and latches are implemented as
libpthread mutexes. If you have problems with PGA, I would use thread
based implementation on Linux. The problem is with Oracle, who did not
provide per-process hard limit which would prevent calloc from succeeding.
Regards
On 10/11/2017 03:35 PM, Reen, Elizabeth (Redacted sender elizabeth.reen
for DMARC) wrote:
Sounds all too familiar, I have the same issue on AIX with the same solution.
We can't find out why they are not being killed. I'm running 11 r2.
Liz
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Norman Dunbar
Sent: Monday, October 09, 2017 4:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
On 09/10/17 20:41, Tim Gorman wrote:
> ....
What the documentation is saying is that, when PGA_AGGREGATE_LIMIT isWhat I found recently, with 12c on Windows (in the cloud) was that when the
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.
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
--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=0rZvc54Jlwk3Q8sw3qwAqIvqKrDjbf54s0JFlthL_1Y&s=nlEOhMZVzEeIZWSTJv1vJXGR4oUZPnOJK3G0V2Nw9gc&e=
��i��0���zX���+�� n��{�+i�^l===
