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.
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.
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.
>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.
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.
Having PAT=1800m, don’t you think it’s quite reasonable to limit single process PGA by 756MB?
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
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
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?
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.
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
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.
[...] 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 [...]