simple sortheap question

[ChrisHadley]

My simple sortheap configuration question is:

the sortheap parameter specifies the amount of memory used by agents
for sorts. Is this value the total for all agents or for each?
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR ) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 3000
SQL statement heap (4KB) (STMTHEAP) = 3000

Does this setting mean that each agent will get 12mb memory pool for
sorts, or that for this entire database there is a total of 12mb
available for private sorts by agents on this database?

Thanks in advance,
Chris

[Ian]

ChrisHadley wrote:

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR ) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 3000
SQL statement heap (4KB) (STMTHEAP) = 3000

Does this setting mean that each agent will get 12mb memory pool for
sorts, or that for this entire database there is a total of 12mb
available for private sorts by agents on this database?

Sort heap is allocated per sort, not per connection. SORTHEAP
is a limit on the memory a single sort can allocate -- meaning
that a sort allocates what it needs, not the maximum. The total
memory for a single database is controlled by SHEAPTHRES_SHR.

See the docs for more information on these config parameters.
Google is a good place to start.

[ChrisHadley]

I believe you mean the db cfg parameter SHEAPTHRES. The document that
I have:

http://www-128.ibm.com/developerwork...cle/dm-0406qi/

says:

"For private sorts, the database manager configuration parameter
sheapthres specifies an instance-wide soft limit on the total amount of
memory that can be consumed by private sorts at any given time."

In any case the sortheap parameter is for each agent's private sorts.

Thanks for pointing me in the right direction.

Chris

[Pierre Saint-Jacques]

SORTHEAP is a db cfg parm. and is assigned ON DEMAND up to 3000 pages as per
your parm values.
It is for each/any statement. It is determined at stmt. compile time as to
how big the optimizer thinks it will need. That's what it requests at
execute time.
SHEAPTHRESH is a dbm cfg parm that is instance wide. It is a soft limit
meaning that the instance will monitor all db's belonging to it that have
stmts using sortheaps. Their sum is controlled by SHEAPTHRESH in that the
instance will not allocate the requested size for new sortheaps but a bit
less so that it stays within that limit.
You can see this when you turn on the monitor switch for sorts and take
snapshots. It will tell you when you have post threshold sorts requested.

SHEAPTHRESH_SHR is a hard limit that has to be considered if you are using
multi processors and/or partioned faciltity. Then that parm. needs to be
taken into account as it requests real memory area for holding sort results
from the agents involved servicing that specific query statement.

STMTHEAP has no bearing on sorts. It is used to determine the amount of
work area the client can request when it has to compile a statement not
execute it.

HTH, Pierre

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"ChrisHadle y" <ch*********@gm ail.com> a écrit dans le message de
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

My simple sortheap configuration question is:

the sortheap parameter specifies the amount of memory used by agents
for sorts. Is this value the total for all agents or for each?
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR ) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 3000
SQL statement heap (4KB) (STMTHEAP) = 3000

Does this setting mean that each agent will get 12mb memory pool for
sorts, or that for this entire database there is a total of 12mb
available for private sorts by agents on this database?

Thanks in advance,
Chris