RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

Multithreaded is what is killing me. The app servers are creating sessions and losing track of them.

Liz

Elizabeth Reen
CPB Database Group Manager
718.248.9930  (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Wednesday, October 11, 2017 7:14 PM To: oracle-l_at_freelists.org
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
> Sent: Monday, October 09, 2017 4:00 PM
> To: oracle-l_at_freelists.org
> 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 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
> --
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_
> webpage_oracle-2Dl&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8V
> LeJtKLVJGefQxustAZ9UxecV7xpc&m=0rZvc54Jlwk3Q8sw3qwAqIvqKrDjbf54s0JFlth
> L_1Y&s=nlEOhMZVzEeIZWSTJv1vJXGR4oUZPnOJK3G0V2Nw9gc&e=
>
>
> i 0 zX + n { +i ^l===

--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=iSdlqqZGEvKSPVkyx9mqxJp40Tao4O4t7xR_RURv-rE&s=42rns7bxkka_XObc3rNyI0XhS-EZe5V-gCTgl3oJj9o&e=

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu Oct 12 2017 - 21:23:06 CEST