Shared Memory

Hi all, I need help on how to identify what process and users trying to allocate more shared memory segments (onstat -g seg) ? and how to releases this memory segment (virtual portion) ? TQ

Tham Huei Hwan wrote: > > Hi all, > > I need help on how to identify what process and users trying to > allocate more shared memory segments (onstat -g seg) ? I assume you are trying to identify database sessions that are being "virtual memory hogs" (and forcing the server to create extra virtual memory segments). Virtual shared memory segments are allocated as required (i.e. when a thread needs another block of shared memory and there is not enough free). So any session, no matter how small, can cause the server to allocate another segment of shared memory. Of course, sessions requiring large pools of VSM are more likely to generate new segments. To find the big sessions :- database sysmaster; select * from syspools where po_class = 2 order by po_usedamt desc po_class - 1=Resident, 2=Virtual, 3=Message You may want to filter out most of the "non user stuff" :- select * from syspools where po_class = 2 and po_name < "99999999" order by po_usedamt desc po_id po_address po_name po_class po_freeamt po_usedamt 116 278315040 24 2 18288 170128 257 280928288 24_SORT_0 2 5848 18728 36 278368288 7 2 3328 8960 33 278200352 4 2 3328 8960 37 278499360 8 2 4568 7720 35 278319136 6 2 4568 7720 32 278134816 3 2 4568 7720 30 278077472 2 2 4568 7720 199 279126048 27 2 4568 7720 po_name is the session ID. Note the separate pool grabbed for the "order by" in session 24. > and how to > releases this memory segment (virtual portion) ? onmode -? | grep -i segment -a <kbytes> Increase shared memory segment size. -F Free unused memory segments onmode -F HTH Brett Randall > > TQ

To Brett's excellent response let me just add that the most common culprits are sessions performing large ORDER BY clauses and archives. Art S. Kagel Tham Huei Hwan wrote: > > Hi all, > > I need help on how to identify what process and users trying to > allocate more shared memory segments (onstat -g seg) ? and how to > releases this memory segment (virtual portion) ? > > TQ