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.
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
UPDATE_COMMENT
_pga_max_size
2147483648
Maximum size of the PGA memory for one process
maximum work area for insertion sort(v1)
maximum work area size in auto mode (serial)
maximum work area size in auto mode (global)
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 0null
Thanks, Matt
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 23 2013 - 15:53:25 CEST