Oracle Windows Sort Memory Usage
- From: mvshelton@xxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 23 Sep 2013 09:53:25 -0400 (EDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Oracle Windows Sort Memory Usage - mvshelton
