This is a very critical error message that many applications started seeing since 12c. Prior to 12c, PGA of a server process could grow unabatedly. After the advent of PGA_AGGREGATE_LIMIT, PGA usage for all connected sessions could be limited to avoid enormous growth of PGA memory due to bad PL/SQL code (or due to an Oracle bug causing a process memory leak). Whenever total PGA usage by all sessions tries to go beyond the value set in this parameter, ORA-04036 is returned to the session and also logged in the alert log file (and trace file is also generated) . Alert log file could log error message similar to the following.
Errors in file /u02/app/oracle/diag/rdbms/mydb/MYDB1/trace/MYDB1_ora_15278.trc (incident=476204) (PDBNAME=MYPDB): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT MYPDB(3):Incident details in: /u02/app/oracle/diag/rdbms/mydb/MYDB1/incident/incdir_476204/MYDB1_ora_15278_i476204.trc |
If you look into the trace file, and search for “SID”, you can find the session SID, as well as SQL in execution by the session that was returned this error. This will help you investigate further.
I usually investigate by querying active session history (V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY) during the time error was reported to find out session/SQL that was returned this error message. The session that was returned this error may not be a culprit, but a victim, if another session had occupied most of the memory that a normal session could not get some PGA space.
There could be cases where no session is occupying comparatively huge PGA space and total PGA requirement by all sessions is more than the value currently set for this parameter. In that case you may search of sessions sitting idle and kill/exit them so that they release memory that could be used by other sessions.
ollowing is a query you can use to query active session history views to find out a session that was occupying huge PGA during the time error was reported. As can be seen below, there is one SQL (PL/SQL block in my case) grew to 10GB and thus caused the trouble. Further investigation is duty of the development team about why a PL/SQL block is having a memory leak or why it is growing so much. Normally some infinite loops or recursive calls to procedures/functions is the root cause of such issue.
In case this session is from an internal oracle process, contact MOS for further investigation.
If there is no specific session that grew in PGA, and all sessions during that time were using a similar or low amount of PGA, it would mean that you need to adjust PGA_AGGREGATE_LIMIT, as current value could be low to support the usage of the database.
select distinct sample_time,session_id,session_serial#,max_pga_mb from ( select sample_time,session_id,session_serial#,max(PGA_ALLOCATED/1024/1024) max_pga_mb from dba_hist_active_sess_history where sample_time between to_date('24-jan-24 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('24-jan-24 00:10:00','DD-mon_yy hh24:mi:ss') group by session_id,session_serial#,sample_time) order by sample_time;
SAMPLE_TIME SQL_ID TOP_LEVEL_SQL PGA_GB ----------------------------- ------------- ------------- ---------- 24-JAN-24 03.30.17.406 AM 09ft6yhdt665d 09ft6yhdt665d .188482285 24-JAN-24 03.30.27.646 AM 09ft6yhdt665d 09ft6yhdt665d 1.03613853 24-JAN-24 03.30.37.886 AM 09ft6yhdt665d 09ft6yhdt665d 1.94238853 24-JAN-24 03.30.48.126 AM 09ft6yhdt665d 09ft6yhdt665d 2.81574059 24-JAN-24 03.30.58.366 AM 09ft6yhdt665d 09ft6yhdt665d 3.69074059 24-JAN-24 03.31.08.606 AM 09ft6yhdt665d 09ft6yhdt665d 4.56574059 24-JAN-24 03.31.18.846 AM 09ft6yhdt665d 09ft6yhdt665d 5.47199059
SAMPLE_TIME SQL_ID TOP_LEVEL_SQL PGA_GB ----------------------------- ------------- ------------- ---------- 24-JAN-24 03.31.29.086 AM 09ft6yhdt665d 09ft6yhdt665d 6.34699059 24-JAN-24 03.31.39.326 AM 09ft6yhdt665d 09ft6yhdt665d 7.22199059 24-JAN-24 03.31.49.566 AM 09ft6yhdt665d 09ft6yhdt665d 8.12824059 24-JAN-24 03.31.59.806 AM 09ft6yhdt665d 09ft6yhdt665d 9.00324059 24-JAN-24 03.32.10.046 AM 09ft6yhdt665d 09ft6yhdt665d 9.53449059 24-JAN-24 03.32.20.286 AM 09ft6yhdt665d 09ft6yhdt665d 9.97199059 24-JAN-24 03.32.30.526 AM 09ft6yhdt665d 09ft6yhdt665d 10.2844906
SAMPLE_TIME SQL_ID TOP_LEVEL_SQL PGA_GB ----------------------------- ------------- ------------- ---------- 24-JAN-24 03.32.41.150 AM 09ft6yhdt665d 09ft6yhdt665d 10.5657406
15 rows selected. |
No comments:
Post a Comment