PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server

18 01 2010

January 18, 2010

(Forward to the Follow-Up Post)

Here is a fun test where you might be able to bring down the server in one of several ways (warning, you might not want to try this with anything less than Oracle Database 11.1.0.6 – if you want to try the test with Oracle 9i or 10g, add NOT NULL constraints to the columns C1 and C2 in each table):

  • Filling up the last bit of available space in the datafiles.
  • Causing the Temp tablespace to madly expand until it reaches its maximum size.
  • Stealing all of the memory on the server, so much for setting the PGA_AGGREGATE_TARGET parameter.
  • Swamping the disk subsystem

We start out with three innocent looking tables created by the following script:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('R'||TO_CHAR(ROWNUM),30,'B') C2,
  RPAD('A',100,'A') C3
FROM
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000) V1,
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000) V2;

CREATE TABLE T2 AS
SELECT
  ROWNUM*10 C1,
  RPAD('R'||TO_CHAR(ROWNUM*10),30,'B') C2,
  RPAD('A',255,'A') C3
FROM
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000) V1,
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

CREATE TABLE T3 AS
SELECT
  (ROWNUM*10)+2 C1,
  RPAD('R'||TO_CHAR((ROWNUM*10)+2),30,'B') C2,
  RPAD('A',255,'A') C3
FROM
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000) V1,
  (SELECT
    ROWNUM C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3')

We can see how much disk space is in use by the three tables with the following SQL statement:

SELECT
  SEGMENT_NAME SEGMENT,
  SUM(BYTES/1048576) TOTAL_MB
FROM
  DBA_EXTENTS
WHERE
  OWNER=USER
  AND SEGMENT_NAME IN ('T1','T2','T3')
GROUP BY
  SEGMENT_NAME
ORDER BY
  SEGMENT_NAME;

SEGMENT   TOTAL_MB
------- ----------
T1           15684
T2            3269
T3            3266

Looks like about 21.7GB is in use by the three tables.  Next, we need a script that we will name PGAMemoryFill.sql:

DECLARE
CURSOR C_MEMORY_FILL IS
SELECT
  T1.C1,
  T1.C2,
  T1.C3
FROM
  T1
WHERE
  T1.C1 NOT IN (
    SELECT
      C1
    FROM
      T2)
  AND T1.C2 NOT IN (
    SELECT
      C2
    FROM
      T3)
ORDER BY
  T1.C2 DESC,
  T1.C1 DESC;

TYPE TYPE_MEMORY_FILL IS TABLE OF C_MEMORY_FILL%ROWTYPE
INDEX BY BINARY_INTEGER;

T_MEMORY_FILL  TYPE_MEMORY_FILL;

BEGIN
  OPEN C_MEMORY_FILL;
  LOOP
    FETCH C_MEMORY_FILL BULK COLLECT INTO  T_MEMORY_FILL  LIMIT 10000000;

    EXIT WHEN T_MEMORY_FILL.COUNT = 0;

    FOR I IN T_MEMORY_FILL.FIRST..T_MEMORY_FILL.LAST LOOP
      NULL;
    END LOOP;

    DBMS_LOCK.SLEEP(20);
  END LOOP;
END;
/

Yes, the script is performing bulk collection (2 DBAs stand up and clap, the rest start shaking their heads side to side).

Let’s check the PGA_AGGREGATE_TARGET:

SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
pga_aggregate_target                 big integer 1800M

OK, the PGA_AGGREGATE_TARGET is just less then 1.8GB, and the server has 12GB of memory.

Now for the test, we will need two sessions, session 1 will be the session that executes the above script, and session 2 will execute various queries to see what is happening in the database.

Session 1:

SELECT SID FROM V$MYSTAT WHERE ROWNUM<=1;

  SID
-----
  335

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  T1.C1,
  T1.C2,
  T1.C3
FROM
  T1
WHERE
  T1.C1 NOT IN (
    SELECT
      C1
    FROM
      T2)
  AND T1.C2 NOT IN (
    SELECT
      C2
    FROM
      T3)
ORDER BY
  T1.C2 DESC,
  T1.C1 DESC;

Plan hash value: 2719846691

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|Time      |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |   174 |       |  2351K  (1)| 07:50:19 |
|   1 |  SORT ORDER BY            |      |     1 |   174 |       |  2351K  (1)| 07:50:19 |
|*  2 |   HASH JOIN RIGHT ANTI NA |      |     1 |   174 |   171M|  2351K  (1)| 07:50:19 |
|   3 |    TABLE ACCESS FULL      | T2   |    10M|    57M|       |   113K  (1)| 00:22:39 |
|*  4 |    HASH JOIN RIGHT ANTI NA|      |    99M|    15G|   410M|  1382K  (1)| 04:36:25 |
|   5 |     TABLE ACCESS FULL     | T3   |    10M|   295M|       |   113K  (1)| 00:22:39 |
|   6 |     TABLE ACCESS FULL     | T1   |   100M|    12G|       |   543K  (1)| 01:48:42 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C1"="C1")
   4 - access("T1"."C2"="C2")

SET AUTOTRACE OFF

SELECT 1 FROM DUAL;

From the above, Oracle is planning to perform a NULL aware hash join between table T1 and T3 (predicted to consume 410MB of space in the TEMP tablespace… is this the true unit of measurement, keep reading), and then join that row source to table T2 using a NULL aware hash join (Oracle 10.2.0.4 and lower will not use a NULL aware hash join – you have been warned) – the SQL statement involving tables T1, T2, and T3 is the SQL statement that will be executed in the PGAMemoryFill.sql script.

In session 2 we will take a look at the optimizer parameters in effect for the last SQL statement executed by Session 1:

SET PAGESIZE 1000
COLUMN CN FORMAT 99
COLUMN NAME FORMAT A37
COLUMN VALUE FORMAT A14
COLUMN DEF FORMAT A3

SELECT
  CHILD_NUMBER CN,
  NAME,
  VALUE,
  ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV SOE,
  V$SESSION S
WHERE
  SOE.SQL_ID=S.SQL_ID
  AND SOE.CHILD_NUMBER=S.SQL_CHILD_NUMBER
  AND S.SID=335
ORDER BY
  NAME;

 CN NAME                                  VALUE          DEF
--- ------------------------------------- -------------- ---
  0 _pga_max_size                         368640 KB      NO
  0 active_instance_count                 1              YES
  0 bitmap_merge_area_size                1048576        YES
  0 cell_offload_compaction               ADAPTIVE       YES
  0 cell_offload_plan_display             AUTO           YES
  0 cell_offload_processing               true           YES
  0 cpu_count                             8              YES
  0 cursor_sharing                        exact          YES
  0 db_file_multiblock_read_count         128            YES
  0 hash_area_size                        131072         YES
  0 is_recur_flags                        0              YES
  0 optimizer_capture_sql_plan_baselines  false          YES
  0 optimizer_dynamic_sampling            2              YES
  0 optimizer_features_enable             11.1.0.7       YES
  0 optimizer_index_caching               0              YES
  0 optimizer_index_cost_adj              100            YES
  0 optimizer_mode                        all_rows       YES
  0 optimizer_secure_view_merging         true           YES
  0 optimizer_use_invisible_indexes       false          YES
  0 optimizer_use_pending_statistics      false          YES
  0 optimizer_use_sql_plan_baselines      true           YES
  0 parallel_ddl_mode                     enabled        YES
  0 parallel_degree                       0              YES
  0 parallel_dml_mode                     disabled       YES
  0 parallel_execution_enabled            true           YES
  0 parallel_query_default_dop            0              YES
  0 parallel_query_mode                   enabled        YES
  0 pga_aggregate_target                  1843200 KB     YES
  0 query_rewrite_enabled                 true           YES
  0 query_rewrite_integrity               enforced       YES
  0 result_cache_mode                     MANUAL         YES
  0 skip_unusable_indexes                 true           YES
  0 sort_area_retained_size               0              YES
  0 sort_area_size                        65536          YES
  0 star_transformation_enabled           false          YES
  0 statistics_level                      typical        YES
  0 transaction_isolation_level           read_commited  YES
  0 workarea_size_policy                  auto           YES 

Notice in the above that _pga_max_size was set to 368640KB (360MB – 20% of the PGA_AGGREGATE_TARGET – note that this value does not seem to decrease as hard parses are forced when a lot of PGA memory is in use), and even though the ISDEFAULT column shows that this is not the default value, the value was set automatically based on the PGA_AGGREGATE_TARGET value.  To further demonstate that _pga_max_size has not been adjusted, here are two screen shots from one of my programs that shows all of the initialization parameters that are in effect, note Is Default is set to TRUE for this parameter (to output all of the hidden parameter values, see http://www.jlcomp.demon.co.uk/params.html):

The 368640 KB value reported for the _PGA_MAX_SIZE in the V$SQL_OPTIMIZER_ENV view exactly matches the value for _PGA_MAX_SIZE returned by the query of  X$KSPPI and X$KSPPSV.

Before we start causing damage, let’s check the documentation for the V$SQL_WORKAREA_ACTIVE view:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/memory.htm#i48705
http://download-west.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3058.htm

The second of the above links defines the columns in the view.  A couple of those column definitions follow:

  • OPERATION_TYPE: Type of operation using the work area (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE)
  • WORK_AREA_SIZE: Maximum size (in bytes) of the work area as it is currently used by the operation
  • ACTUAL_MEM_USED: Amount of PGA memory (in bytes) currently allocated on behalf of this work area. This value should range between 0 and WORK_AREA_SIZE.
  • NUMBER_PASSES: Number of passes corresponding to this work area (0 if running in OPTIMAL mode)
  • TEMPSEG_SIZE: Size (in bytes) of the temporary segment used on behalf of this work area.  This column is NULL if this work area has not (yet) spilled to disk.

While session 1 is busy executing the PGAMemoryFill.sql script, session 2 will periodically query the V$SQL_WORKAREA_ACTIVE view to see what is happening.

In Session 1:

ALTER SESSION SET STATISTICS_LEVEL=ALL;

@PGAMemoryFill.sql

In Session 2 starts repeatedly executing the following SQL statement after a short delay (note that I could have selected the OPERATION_ID column to make it easy to tie the memory used to a specific operation in the execution plan that was displayed earlier):

SELECT
  SQL_ID,
  OPERATION_TYPE,
  WORK_AREA_SIZE,
  ACTUAL_MEM_USED,
  NUMBER_PASSES,
  TEMPSEG_SIZE
FROM
  V$SQL_WORKAREA_ACTIVE;

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv HASH-JOIN                  29298688        20712448             0    121634816

So, Session 1 is using about 19.75MB of PGA memory for a hash join, and according to the definition of the NUMBER_PASSES column, the hash join is currently an optimal execution, yet that seems to conflict with the definition of the TEMPSEG_SIZE definition and the output in that column.  Session 2 will continue to re-execute the above SQL statement, pausing after each execution:

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv HASH-JOIN                  40738816        33011712             0     45088768
0k5pr4rx072sv HASH-JOIN                 189427712        20740096             1    130023424

Now there are two hash joins active for the SQL statement with a total of 51.26MB of PGA memory in use.  One of the hash joins is still an optimal execution, while the second has become a 1 pass execution.

 
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  78364672        28554240             1   1293942784
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1    814743552
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1    470810624

Now both of the hash joins are reporting a 1 pass execution.  A V2 sort operation has joined the output, and it too is executing as a 1 pass operation.  The session is now using just over 274MB of PGA memory based on the output of this view.

 
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  78304256        43396096             1   1749024768
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1    968884224
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1    591396864

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  80089088         5712896             1   5366611968
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1   2097152000
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   1509949440

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  73031680        67003392             1   8383365120
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1   3050307584
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2283798528

The session has made it up to 310.77MB of PGA memory, and the TEMPSEG_SIZE column values continue to grow.

--

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  67550208        37590016             1   9634316288
0k5pr4rx072sv HASH-JOIN                  23760896        13456384             1   3338665984
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2607808512

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  67550208        47077376             1   1.0252E+10
0k5pr4rx072sv HASH-JOIN                  23760896        13456384             1   3338665984
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2770337792

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                 188743680       188285952             1   1.1250E+10
0k5pr4rx072sv HASH-JOIN                  18951168        17658880             1   2839543808

One of the hash join operations has completed, must be about done now.

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  90914816        91714560             1   1.1966E+10

The final hash join finished, and the TEMPSEG_SIZE is now 1.1966E+10, which indicates that the temporary segment size in the TEMP tablespace is about 11.14GB.  That is kind of big – remember that number.  When just the sort operation is returned by the above query, session 2 executes this SQL statement:

COLUMN VALUE FORMAT 999,999,999,990

SELECT
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.SID=335
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%';

NAME                             VALUE
------------------------ -------------
session pga memory       3,391,500,272
session pga memory max   3,391,500,272

Based on the above, Session 1 is not consuming about 90MB of PGA memory, but instead roughly 3234.39MB of PGA memory (the 2 DBAs still standing and clapping should sit down now).  Let’s hope that the DBA responsible for this database did not consider the 1800MB value for the PGA_AGGREGATE_TARGET parameter as a hard upper limit, and set the other parameters to take full advantage of the 12GB of memory in the server.

Once the script ends, the above SQL statement returns the following values:

NAME                             VALUE
------------------------ -------------
session pga memory          10,039,280
session pga memory max   3,391,500,272 

The session is still consuming 9.57MB of PGA memory just sitting idle – remember this number.

Now just to make sure that 0k5pr4rx072sv, as output by the query of the V$SQL_WORKAREA_ACTIVE view, is the SQL_ID for our SQL statement:

SELECT
  SQL_TEXT
FROM
  V$SQL
WHERE
  SQL_ID='0k5pr4rx072sv';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT T1.C1, T1.C2, T1.C3 FROM T1 WHERE T1.C1 NOT IN ( SELECT C1 FROM T2) AND T
1.C2 NOT IN ( SELECT C2 FROM T3) ORDER BY T1.C2 DESC, T1.C1 DESC

Good, now let’s check the execution plan for the SQL statement:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0k5pr4rx072sv',0,'ALLSTATS LAST'));

SQL_ID  0k5pr4rx072sv, child number 0
-------------------------------------
SELECT T1.C1, T1.C2, T1.C3 FROM T1 WHERE T1.C1 NOT IN ( SELECT C1 FROM
T2) AND T1.C2 NOT IN ( SELECT C2 FROM T3) ORDER BY T1.C2 DESC, T1.C1
DESC

Plan hash value: 2719846691

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |     80M|00:24:55.23 |    2833K|   5024K|   2190K|       |       |          |         |
|   1 |  SORT ORDER BY            |      |      1 |      1 |     80M|00:24:55.23 |    2833K|   5024K|   2190K|    12G|    35M|  179M (1)|      11M|
|*  2 |   HASH JOIN RIGHT ANTI NA |      |      1 |      1 |     80M|00:12:08.70 |    2833K|   3563K|    730K|   269M|    14M|  105M (1)|    2708K|
|   3 |    TABLE ACCESS FULL      | T2   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|      0 |       |       |          |         |
|*  4 |    HASH JOIN RIGHT ANTI NA|      |      1 |     99M|     90M|00:08:25.72 |    2416K|   2811K|    394K|   521M|    19M|  141M (1)|    3184K|
|   5 |     TABLE ACCESS FULL     | T3   |      1 |     10M|     10M|00:00:20.12 |     416K|    416K|      0 |       |       |          |         |
|   6 |     TABLE ACCESS FULL     | T1   |      1 |    100M|    100M|00:03:20.37 |    2000K|   1999K|      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C1"="C1")
   4 - access("T1"."C2"="C2") 

Let’s see, almost 25 minutes to execute the SQL statement, a total of roughly 425MB of memory was used during one pass workarea executions (but from our earlier output, not all of that memory was in use at the same time) and the SORT ORDER BY operation used 11M of TEMP tablespace space… but is that 11MB, or is it 11 million KB, or is it 11,534,336 KB (2^20 * 11 KB)?  Remember earlier we found “that the temporary segment size in the TEMP tablespace is about 11.14GB”, so that 11M means 11,534,336 KB, or about 11GB.  OK, that was slightly confusing, but we are not done yet.  (Side note: the author of the book “Troubleshooting Oracle Performance” commented on the Used-Tmp column here.)

Let’s have some fun and burn memory (or have some fun until something breaks).  Now, we will run the PGAMemoryFill.sql script in 4 sessions, with a fifth session to monitor the progress (if you want to have more fun, modify the WHERE clause on the T1 table so that all workarea executions are optimal, rather than spilling to disk in a one-pass or multi-pass operation).  In 4 sessions, execute the script:

@PGAMemoryFill.sql

After a short pause, session 5 (the monitoring session) should periodically submit the following query:

SELECT
  SN.NAME,
  SUM(SS.VALUE) VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME='session pga memory'
GROUP BY
  SN.NAME;

NAME                            VALUE
-------------------- ----------------
session pga memory        144,351,120

Roughly 137.66MB of PGA memory in use – I wonder if we will hit 3,391,500,272 * 4 = 12.63GB of PGA memory in use – 4 times the value seen for the single session?)  Well, let’s keep executing the above query with brief pauses between each execution:

NAME                            VALUE
-------------------- ----------------
session pga memory        144,351,120

NAME                            VALUE
-------------------- ----------------
session pga memory        285,902,000

NAME                            VALUE
-------------------- ----------------
session pga memory      1,191,920,144

NAME                            VALUE
-------------------- ----------------
session pga memory      1,296,843,280

NAME                            VALUE
-------------------- ----------------
session pga memory      1,379,306,720

NAME                            VALUE
-------------------- ----------------
session pga memory      1,401,504,272

NAME                            VALUE
-------------------- ----------------
session pga memory      1,465,467,408

NAME                            VALUE
-------------------- ----------------
session pga memory      1,473,207,536

NAME                            VALUE
-------------------- ----------------
session pga memory      1,484,283,120

Let’s check one of the sessions to see how it is doing:

SELECT
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.SID=335
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%';

NAME                              VALUE
---------------------- ----------------
session pga memory          357,904,368
session pga memory maz      357,904,368

This one session is using roughly 341.32MB of PGA memory, now back to the other query:

NAME                            VALUE
-------------------- ----------------
session pga memory      1,476,418,800

NAME                            VALUE
-------------------- ----------------
session pga memory      4,517,252,992

NAME                            VALUE
-------------------- ----------------
session pga memory      4,518,556,832

The PGA memory usage seems to have stabilized at 4,309.23MB (4.21GB), so we didn’t bring down the server by exceeding the 12GB of memory in the server, but this is 2.4 times the value of the PGA_AGGREGATE_TARGET parameter.  Let’s check on the progress of our 4 sessions:

SELECT
  SS.SID,
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.VALUE>=300*1024*1024
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%'
ORDER BY
  SS.SID,
  SN.NAME;

SID NAME                              VALUE
--- ---------------------- ----------------
297 session pga memory        3,322,442,384
297 session pga memory max    3,384,832,656
304 session pga memory          368,734,864
304 session pga memory max      373,518,992
305 session pga memory          368,734,864
305 session pga memory max      368,734,864
335 session pga memory          357,904,368
335 session pga memory max      357,904,368

The above seems to show that one of the sessions is still using 3,168.53GB of PGA memory, while the other three have each retreated to roughly 352MB of PGA memory.  Let’s check on the sessions…  The script in 3 of the 4 sessions crashed with this error:

ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at line 33

Quick math time: is 11.14GB * 4 greater than the maximum size of a SMALLFILE datafile in a database with an 8KB block size?  128 * 8KB = 1MB, which is the extent size in the TEMP tablespace.  OK, if the script crashed in 3 of the 4 sessions, why are each of those sessions still consuming about 352MB of PGA memory when they are just sitting there waiting for the next SQL statement?  This would certainly drive someone mad trying to figure out what Jimmy the Developer has done.  So, how do you get the memory back from the session so that it can be returned to the operating system?  You must execute this specially crafted SQL statement in each session:

SELECT
  42
FROM
  DUAL
WHERE
  1=2;

OK, it does not need to be that SQL statement, but until another SQL statement is executed, the 352MB acquired by each of the three sessions cannot be used for anything else.  And that, my friends, is the developer’s secret weapon for stealing all of the memory in the server.  Now try to modify the SQL statement in the PGAMemoryFill.sql script so that all three workarea executions are optimal executions to see how high the memory usage can be pushed while executing the SQL statement.


Actions

Information

10 responses

18 01 2010
Timur Akhmadeev

Hello Charles,

>And that, my friends, is the developer’s secret weapon for stealing all of the memory in the server.

The DBA’s secret weapon to stop yet another crazy developer is event 10261. Not a panacea, of course, but the only way to hard limit PGA usage per process.

18 01 2010
Charles Hooper

Interesting suggestion, thanks Timur

From the documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/e9858.htm

“ORA-10261: Limit the size of the PGA heap Cause: the limit is one kilobyte times the level of the event. If the pga grows bigger than this signal an internal error.
Action: none”

But then the question becomes, what is the appropriate value (in your database, not necessarily the test database that I used) for event 10261 so that the necessary queries themselves do not fail, but silly settings for the bulk collect limit are not permitted to consume a significant amount of memory? Then you have to deal with problems like the second of the above problems that involved 4 sessions, in which 3 of the sessions returned an ORA-01652 (cannot extend the TEMP tablespace) during the sort operation without returning the roughly 352MB of PGA memory consumed by each of the sessions.

While the session for setting event 10261 is a good one, there probably is not a right/correct answer for the level of the event.

Note that there is a follow-up blog article that is scheduled to appear 24 hours after this blog article appeared.

18 01 2010
Timur Akhmadeev

>While the session for setting event 10261 is a good one, there probably is not a right/correct answer for the level of the event.
Having PAT=1800m, don’t you think it’s quite reasonable to limit single process PGA by 756MB? ;) It won’t help in all cases, but will provide some protection against running ~PGAMemoryFill.sql – that is the main purpose of the event 10261, I think.

5 02 2010
Anand

Hi Charles,

In which case can the “total PGA allocated” be greater than “aggregate PGA target parameter”? The parameter pga_aggregate_target is set to 1628M and this is what is shown in “aggregate PGA target parameter” but the “total PGA allocated” is showing 4250M.The database is 2-Node RAC and 10.2.0.4 version on sun solaris.

Regards,
Anand

5 02 2010
Charles Hooper

Anand,

Good question.

Oracle will try to not exceed the PGA_AGGREGATE_TARGET. To help processing efficiency, when little of the PGA memory is in use, a single session may use up to the amount of memory specified by the _SMM_MAX_SIZE hidden parameter per workarea before needing to use the temp tablespace for a one-pass or multi-pass workarea execution. As demonstrated above, more than one workarea could be in use at one time. In Oracle 10.1.0.x and below, this _SMM_MAX_SIZE limit was set to 5% of the value for PGA_AGGREGATE_TARGET. If you look at the two screen captures in this article, you will see that the 5% limit has increased in recent releases. Assume that a couple of sessions are executing, and each is using two workareas simultaneously for an optimal in-memory execution, and then 10 more sessions start processing. As the PGA_AGGREGATE_TARGET value is approached, the 10 new sessions might be forced to perform multi-pass operations when performing the same task that the other sessions are performing completely in memory – this is an effort by Oracle to try to adhere to the PGA_AGGREGATE_TARGET value, but the sessions will still need some PGA memory. Now, assume that another session starts that performs a bulk collect, or another PL/SQL feature that must requires a lot of physical memory – Oracle has a choice at this point, but failure is not one of the choices, so it exceeds the PGA_AGGREGATE_TARGET value to permit the session to continue executing.

The above explanation might not be detailed enough. Tom Kyte wrote a couple of detailed explanations about how the PGA_AGGREGATE_TARGET value might be exceeded. See the following links:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4473773580447

http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA124#v=onepage&q=&f=false

6 02 2010
Anand

HI Charles

“Oracle has a choice at this point, but failure is not one of the choices, so it exceeds the PGA_AGGREGATE_TARGET value to permit the session to continue executing.”

Is there some limit to which the PGA can exceed?Does it depend on the space avaliable in RAM?

6 02 2010
Charles Hooper

I believe that an error will be returned to the client once all of the physical memory in the server is consumed – this likely assumes that the operating system will not be able free additional memory fast enough by swapping out infrequently used memory contents to the page file. At least that was the result of an experiment that I performed eight months ago on the 64 bit version of Oracle 11.1.0.7 running on Windows. If you would like to test to determine what happens on your server (hopefully a test server), modify the PGAMemoryFill.sql script that is shown above – remove the “ORDER BY T1.C2 DESC, T1.C1 DESC” portion of the SQL statement, then execute the script using several sessions.

6 02 2010
Anand

Hi Charles,

Thanks for the input.Its not a test server so can’t try.One more question is popping up in my mind…Does this “total allocated PGA” size decrease if not used means when the processing sessions end ??or does it remain the same,it doesn’t decrease??

Anand

6 02 2010
Charles Hooper

Based on my testing, if the SQL statement executed by the session completed successfully, the memory should be returned to the operating system after the SQL statement finishes (when the PGA_AGGREGATE_TARGET is used). When the SQL statement failed to finish executing (when the temp tablespace reached its maximum size), the memory was not released to the operating system until the session executed another SQL statement or disconnected.

The behavior might be different on other platforms and other Oracle versions.

12 02 2010
Blogroll Report 15/01/2009 – 22/01/2010 « Coskan’s Approach to Oracle

[...] 7-How does session PGA usage behaves when session gives error? Charles Hooper-PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: