RE: [DB2EUG] Shared sort & private sort

[Vilas, Ajith (USPC.PCT.Hopewell)]

-----Original Message-----
From: Vilas, Ajith (USPC.PCT.Hopewell) 
Sent: Friday, May 17, 2002 9:23 AM
To: 'Raghu Raman'
Subject: RE: [DB2EUG] Shared sort & private sort


Raghu,
  Main difference between these two are that they are allocated out of two separate 
memory areas. Private sort heap is allocated from agents private memory and the space 
required for shared sort is
allocated from database shared memory. If you have a SQL that could exploit 
parallelism, coordinating database agent will fork multiple sub agents and these sub 
agents will work parallel on
subsections allocated for them. If there are no possible parallelism detected by 
optimizer, it will be done by the coordinating agent itself (even if INTRA_PARALLEL is 
ON). For instance, your SQL
query requires sorting and if they can be pushed down, optimizer can decide on various 
types of parallel sort strategies.
  Based on the type of parallel sort strategy, one or more shared sort heap will be 
allocated from database manager shared memory and each subagents will insert data into 
this area which is then
processed by the coordinating agent. 
  SORTHEAP parameter applies to both private and shared sort. If your sort is private, 
then this is the maximum no of sort memory pages (in agent private heap) that can be 
allocated by a single
coordinating agent. if your sort is shared this is the maximum pages that can be 
allocated by a coordinating agent in database shared memory (used by sub agents).

 SHORTHEAPTHRES is a soft limit for private sort, meaning, sum of the total pages of 
private sort allocated by all coordinating agents, hit this limit, any new private 
sort might get less pages than
it really requires (more paging/spilling). This doesn't mean that all these 
coordinating agents allocated SORTHEAP size of private sort. It could be in smaller 
chunks.
  But for shared sort, if the total sort heap allocated by coordinating agents (will 
be used by subagents, again in chunks <= SORTHEAP) in database shared memory hits this 
parameter, no more sort
parallelism is possible and the optimizer will not choose a parallel sort strategy.
Regards,
Ajith


-----Original Message-----
From: Raghu Raman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 16, 2002 4:55 PM
To: [EMAIL PROTECTED]
Subject: [DB2EUG] Shared sort & private sort


Hi all,

In DB2 what does it mean by shared sort and private sort.  We have a Websphere 
application connecting to DB2 6.1 FP7 on an AIX machine.

How to determine what type of sort my application is using.  Also in the manual it 
says SORTHEAP and SHEAPTHRES parameter varies depending whether its a shared or 
private sort.  So we need to
determine when an application will use shared sort and when it will use private sort.

Any help would be appretiated.

Thnx.

Regards,
Ragu


________________________________________________________
Outgrown your current e-mail service?
Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS.
http://login.mail.lycos.com/brandPage.shtml?pageId=plus
-
:::  When replying to the list, please use 'Reply-All' and make sure
:::  a copy goes to the list ([EMAIL PROTECTED]).
***  To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
***  For more information, check http://www.db2eug.uni.cc

-
:::  When replying to the list, please use 'Reply-All' and make sure
:::  a copy goes to the list ([EMAIL PROTECTED]).
***  To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
***  For more information, check http://www.db2eug.uni.cc