I'm running my DB2 8.1 on AIX. I'm running into a memory problem and here is the error details, what i get from db2diag.log


2004-02-24-12.15.43.676561 Instance:db2inst Node:000
PID:565416(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OE5C.0E0334200118
sort/list services sqlsAllocateSortMemory Probe:35 Database:CLFY81

Not enough memory available for a (shared) sort heap of size 8
Trying smaller size...


Can someone please help me here. My application is hanging and we can not proceed.

Thanks,

Can you verify your sort related paramters like sortheap, sheapthres have correct values.
Plus do you have INTRA_PARALLEL yes? It affects what type of sort is used - shared vs private.

Hello,

Here are my Sort parameters:

Sortheap : 50000
Sheapthres_shr : 5000
Stat_heap_size : 5000
STMT_heap: 512
UTIL_heap_size : 5000

Please advice

The values are wrong. You should have sheapthres_shr at least two times bigger than sortheap.
Also check sheapthres at the instance level.

Thank you for your help.

So the value for sheapthres_shr should be 100,000? should I make the change?

Also at the instance level, If the value is low. Can I make it to 100,000?

Thanks for all your help.

I suggest you read the documentation related to the mentioned parameters. It's difficult to recommend without knowing the requirements and monitoring database performance.

In general you need to consider the number of concurrent sorts you expect, size of the sorted data, available memory and code base (32 or 64 bit).

The value of 50k sort pages(~200MB) may be too big, but again I do not know your environment.
If you have a lot of relatively small sorts, just switch the values for sortheap and sheapthres_shr.

As for instance level, please consider the number of databases you have in the instance. If it's only one, just make instance and database sheapthres_shr equal.

You may try IBM's configuration adviser (from control center). It'll give you good values as a starting point for the parameters optimization.

Hello,

To tell you about my environment: My database is a 1GB size database. When we run a single or couple users against this database. The performance and operations run fine.

But when we run Win-runner with 100 users. The database reports the below listed error: Well when this 100 users connect to the database, they are doing update, insert and some select. Thats all they are doing. My application is a CRM application.

Any suggestionss?

Thanks:



Error:



2004-02-25-11.23.54.512751 Instance:db2inst Node:000
PID:708832(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OFF4.0404D5185000
sort/list services sqlsAllocateSortMemory Probe:35 Database:CLFY81

Not enough memory available for a (shared) sort heap of size 7
Trying smaller size...

2004-02-25-11.23.54.530693 Instance:db2inst Node:000
PID:708832(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OFF4.0404D5185000
sort/list services sqlsCalculateSortMemory Probe:75 Database:CLFY81

DIA8311C No memory available in the sort heap.
ZRC=0x8B0F0033

I'm afraid, I don't have the 'direct' answer to your question ...

But let me try ...

Did you change the SORTHEAP and SORTHEAPTHRES_SHR as recommended earlier ? If you did, what are the values now ?

1 GB does not seem to be a big database, espcially given that it is a CRM application ... Is it a third party software (eg . Siebel) or is it develped in house ... In the former case, get in touch with the vendor .. For the latter, you may have to do one or both of the following :

1) Check if appropriate indexes are defined ? (You should do this even if your application is from a vendor)

2) Tune your SQL to ensure it does not do unnecessary sorts ...

About the second point :
Developers normally tend to put in order by, UNION where UNION ALL will also do the same job etc (which require sort space), primarily for testing purposes .. . Do not include more columns than necessary in the SELECT clause and/or in the ORDER BY Clause .. More the columns in the ORDER BY Clause, more sort space will be needed and more CPU Time is consumed ...

HTH

Sathyaram

Hello,

I did changed the sort heap and now the values are:

Sort_heap : 3000
Stat_heap_size : 5000
Stmt_heap: 1000
Util_heap_size : 5000
Sheapthres_size : 0
DB_heap : 4000

I still have the problem, where it reports " No memory available in the sort heap".

dont know, whats going on. As i said earlier. My application is a CRM and we are doing a Win-runner test with 100 users.

Does my above parameters needs any modification? MY database is 1 GB in size and has 4 GB physical memory avaiable.

any help?

What is your SHEAPTHRES value in dbm cfg? Why donot you increase this, and monitor server page_in/out(before and after change).

My SHEAPTHRES DBM value is 19000. But for Database its 0.