Niall's Oracle Pages

I note today that another reference to pga_memory_management has appeared on Don Burleson's Oracle News site. Its a reprint of a tip submitted to dba-village by Franck Pachot.

Presumably its intended as yet more support for the Undocumented Secrets article on SearchOracle - though perhaps its a replacement for the various comments on Burleson Consulting related websites on the subject that have disappeared after being criticised.

Whatever its intention, I'm a little bemused by the whole disussion now. The current thrust of Don's arguments seems to be that you can increase the memory actually available to Oracle for sorts (presumably to avoid disk sorts) by setting some documented and undocumented parameters so that you can use really large sort areas, that is sort areas in the hundreds of megabytes or gigabytes. To me this begs the question as to whether you really wouldn't be better off concentrating on tuning the queries that create the requirements for these monster sorts. Typically you will see these in two situations.

First when the developer has missed, or wrongly coded, joins. Tuning wrong code is rather pointless here.

Second in large datawarehouse environments - in these I can't help but wonder if looking at materialized views, bitmap indexes and star transformations might be of more use. However as I don't do proper datawarehouses I could be off beam here.

In other words, whatever the merits of the various parameters, wouldn't it be better to ask why I think I need such large sorts in the first place.

Finally I attach some interesting output from a production 9206 database. You'll see that the pga_aggregate_target is set to 120m and the undocumented parameter untouched. There are no parallel queries (its OLTP as well). Somehow I still managed 16 optimal work area operations in the 256-512mb range. I don't have an explanation for this, but just wanted to add observation to all the words.


SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
2 (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
3 optimal_executions, onepass_executions, multipasses_executions
4 FROM v$sql_workarea_histogram
5 WHERE total_executions != 0;

LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
---------- ---------- ------------------ ------------------ ----------------------
16 32 9077203 0 0
32 64 81237 0 0
64 128 14718 48 0
128 256 9252 0 0
256 512 8479 24 0
512 1024 34921 40 0
1024 2048 18942 48 0
2048 4096 65558 120 0
4096 8192 2715 478 0
8192 16384 153 1061 0
16384 32768 88 758 0
32768 65536 32 362 0
65536 131072 88 345 16
131072 262144 12 24 16
262144 524288 16 0 0
524288 1048576 0 0 1

16 rows selected.

SQL> SELECT
2 a.ksppinm parameter,
3 a.ksppdesc description,
4 b.ksppstvl session_value,
5 c.ksppstvl instance_value
6 FROM
7 x$ksppi a,
8 x$ksppcv b,
9 x$ksppsv c
10 WHERE
11 a.indx = b.indx
12 AND a.indx = c.indx
13 AND substr(a.ksppinm,1,1) = '_'
14 and a.ksppinm like '%pga%'
15 /

PARAMETER DESCRIPTION
---------------------------------------------------------------- -------------------------------------------------------
---------
SESSION_VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
INSTANCE_VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
_pga_max_size Maximum size of the PGA memory for one process
209715200
209715200


1 row selected.

SQL> select name,value
2 from v$parameter
3 where name like '%pga%';

NAME
------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
pga_aggregate_target
125829120


1 row selected.

SQL>