We have tried that and more. All have had some undesirable side
effect. Connection pooling got filled up and the world hanged. Dedicated is
the only methodology which keeps it manageable. We have an idle process killer.
Some days it's very active. Other days are not very active. It's third party
stuff so we can't see what they are doing.
Elizabeth Reen
-----Original Message-----
From: Mladen Gogala [mailto:gogala.mladen@xxxxxxxxx] ;
Sent: Thursday, October 12, 2017 3:30 PM
To: Reen, Elizabeth [ICG-IT]; oracle-l@xxxxxxxxxxxxx
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
Hi Liz,
Have you tried DRCP ("Database Resident Connection Pooling")? Here is a page
about it:
https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_manproc002.htm-23ADMIN12348&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=Ij6AC3BhNTda8GrJSqRuifN0PfQqER-k4omj4zan89k&s=WvDtvw3ggnrq9lSGe6CHFGx74vhLYAEYPfrSUe5dlug&e=
There is also a good explanation on AskTom:
https://urldefense.proofpoint.com/v2/url?u=https-3A__asktom.oracle.com_pls_asktom_f-3Fp-3D100-3A11-3A0-3A-3A-3A-3AP11-5FQUESTION-5FID-3A22140261281764&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=Ij6AC3BhNTda8GrJSqRuifN0PfQqER-k4omj4zan89k&s=iW94ZRdXEpdnWfZqVkRHkUld-GQ4MFvb8ZaJQIUO2rc&e=
You get the best of both worlds with DRCP.
Regards
On 10/12/2017 03:23 PM, Reen, Elizabeth wrote:
Multithreaded is what is killing me. The app servers are creating sessions
and losing track of them.
Liz
Elizabeth Reen
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Wednesday, October 11, 2017 7:14 PM
To: oracle-l@xxxxxxxxxxxxx
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@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_LIMITWhat I found recently, with 12c on Windows (in the cloud) was that when the
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.
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=yWMFosURAngbt8
V
LeJtKLVJGefQxustAZ9UxecV7xpc&m=0rZvc54Jlwk3Q8sw3qwAqIvqKrDjbf54s0JFlt
h 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=yWMFosURAngbt8V
LeJtKLVJGefQxustAZ9UxecV7xpc&m=iSdlqqZGEvKSPVkyx9mqxJp40Tao4O4t7xR_RUR
v-rE&s=42rns7bxkka_XObc3rNyI0XhS-EZe5V-gCTgl3oJj9o&e=
