Oracle Windows Sort Memory Usage

From: <mvshelton_at_chartermi.net>
Date: Mon, 23 Sep 2013 09:53:25 -0400 (EDT)
Message-ID: <34fe3a9e.1f957.1414b189b92.Webtop.44_at_chartermi.net>



I have a sql in a 11.2 database that is doing a window sort based on an analytical function. The session is running in parallel 16.

After determining from the following query in my database: SQL> l

   1 SELECT
   2 x.ksppinm name,
   3 y.ksppstvl VALUE,
   4 decode(ksppity,

   5      1,   'BOOLEAN',
   6      2,   'STRING',
   7      3,   'INTEGER',
   8      4,   'PARAMETER FILE',
   9      5,   'RESERVED',
10      6,   'BIG INTEGER',
11      'UNKNOWN') typ,
12    decode(ksppstdf,
13      'TRUE',   'DEFAULT VALUE',
14      'FALSE',   'INIT.ORA') isdefault,
15    decode(bitand(ksppiflg / 256,   1),
16      1,   'IS_SESS_MOD(TRUE)',
17      'FALSE') isses_modifiable,
18    decode(bitand(ksppiflg / 65536,   3),
19      1,   'MODSYS(NONDEFERED)',
20      2,   'MODSYS(DEFERED)',
21      3,   'MODSYS(*NONDEFERED*)',
22      'FALSE') issys_modifiable,
23    decode(bitand(ksppstvf,   7),
24      1,   'MODIFIED_BY(SESSION)',
25      4,   'MODIFIED_BY(SYSTEM)',
26      'FALSE') is_modified,
27    decode(bitand(ksppstvf,   2),
28      2,   'ORA_STARTUP_MOD(TRUE)',
29      'FALSE') is_adjusted,

30 ksppdesc description,
31 ksppstcmnt update_comment
32 FROM x$ksppi x,
33 x$ksppcv y
34 WHERE x.inst_id = userenv('Instance') 35 AND y.inst_id = userenv('Instance') 36 AND X.INDX = Y.INDX
37 AND X.KSPPINM in
('_pga_max_size','_smm_max_size','_smm_px_max_size','_pga_aggregate_target','_smm_isort_cap') 38* order by 1
SQL> / NAME  

VALUE  

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE
-------------- ------------- ----------------- --------------------
IS_MODIFIED          IS_ADJUSTED
-------------------- ---------------------
DESCRIPTION  

UPDATE_COMMENT  

_pga_max_size

2147483648
BIG INTEGER    DEFAULT VALUE FALSE             MODSYS(NONDEFERED)
FALSE                FALSE

Maximum size of the PGA memory for one process

_smm_isort_cap

102400

INTEGER        DEFAULT VALUE IS_SESS_MOD(TRUE) MODSYS(NONDEFERED)
FALSE                FALSE

maximum work area for insertion sort(v1)

_smm_max_size

1048576

INTEGER        DEFAULT VALUE IS_SESS_MOD(TRUE) MODSYS(NONDEFERED)
FALSE                FALSE

maximum work area size in auto mode (serial)

_smm_px_max_size

8388608

INTEGER        DEFAULT VALUE IS_SESS_MOD(TRUE) MODSYS(NONDEFERED)
FALSE                FALSE

maximum work area size in auto mode (global)

I have _smm_px_max_size set to about 8gb so if I am running parallel 16 I should have about 512mb per session but anytime I do a window sort I seem to peek around 150mb.

How do I increase the memory utilized when doing a v1 windows sort in Oracle?

select operation_type, work_area_size, expected_size, actual_mem_used, max_mem_used ,tempseg_size
from v$sql_workarea_active
where sql_id = '8bd3svqtzj5x3'

Operation_type               Work_area_size               Expected_size 
Actual_mem_used         Max_mem_used                TempSeg_size
WINDOW (SORT)             155926528                      155926528 
155746304                           156219392 
24238882816
GROUP BY (HASH)           534250496                      534250496 
0                                              0 
null

Thanks, Matt

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 23 2013 - 15:53:25 CEST

Original text of this message