Great gap between Est. Temp Space Used and Act. Temp Space Used

Hi all,

I have a question about the Est. Temp Space Used and Act. Temp Space Used, 
from the query plan, I found the estimated temp space is 200% of the actual 
temp space

Est. Temp Space Used (Mb) 4722.6

Act. Temp Space Used (Mb) 1948.6

This make it difficult to set QUERY_TEMP_SPACE_LIMIT as the resources is 
very tight, do you have any idea to make the estimation of the temp space 
used more accurate?

Version: 12.7.0/080707/P/ESD 5/MS/Windows 2003/64bit


0
Keith
3/9/2009 6:06:20 AM
sybase.iq 1656 articles. 1 followers. Follow

1 Replies
1432 Views

Similar Articles

[PageSpeed] 40
Get it on Google Play
Get it on Apple App Store

Keith Ma wrote:
> Hi all,
> 
> I have a question about the Est. Temp Space Used and Act. Temp Space Used, 
> from the query plan, I found the estimated temp space is 200% of the actual 
> temp space
> 
> Est. Temp Space Used (Mb) 4722.6
> 
> Act. Temp Space Used (Mb) 1948.6
> 
> This make it difficult to set QUERY_TEMP_SPACE_LIMIT as the resources is 
> very tight, do you have any idea to make the estimation of the temp space 
> used more accurate?
> 
> Version: 12.7.0/080707/P/ESD 5/MS/Windows 2003/64bit
> 
> 

Hi

I think we should found why the difference between the Actual and 
Estimated is so high. Do you have HG/LF/fast-FP indexes on all columns 
participating in the WHERE clause(s) of your query? Are these indexes 
really used by the query? If not - then the optimizer has to guess - 
which may lead to incorrect estimations. Also, joins and aggregations, 
especially when there are no HG indexes on join columns may affect 
optimizer calculations. Skewing of data distribution of join columns may 
also contribute to the problem.

I would suggest checking estimated vs. actual number of rows returned by 
leafs in the detailed execution plan. If you see differences also on 
this level then it may be easier to start the investigation here then to 
analyze just one figure at the very top of the plan. Since you are using 
IQ 12.7, you may play with optimizer hints (selectivity etc.) and see if 
estimation of returned rows for leafs and joins and total Temp space 
used estimation are affected by it.

Hope it helps
Leonid Gvirtz

0
Leonid
3/10/2009 5:21:05 PM
Reply:

Similar Artilces:

TEMP space used by DBMLSYNC
Consolidated: Oracle 9i/Oracle 10gR2 MobiLink: dbmlsrv 8.02/ mlsrv 10.0.1 (windows 32 bit) ASA: dbeng & dbmlsync 8.02/ dbeng & dbmlsync 8.02 A few questions around TEMP space usage: DBMLSYNC leaves TEMP files '_T#' On large syncs) and sometimes fails to clean them even when no issue has been encountered and the sync process completes sucessfully as expected. I have not found a pattern as to when it cleans them and when it does not upon sucessful completion of the sync. Is this a known issue? Does dbmlsync follow the same Temp space rules as the dbeng? Can I...

temp space used by user
How do I calculate the temp used by a user? Occasionally we have a problem of temp space being filled up by a users. What I want to do is to find which users are using most of the temp space and take action accordingly. Please let me know if anyone have done this before. Thanks. ...

UNIX file system free space, used space, total space
Anyone have an idea about getting UNIX file system free space, used space, total space numbers without having to resort to something like the following ( which gets the percent of used space for a command-line supplied file system name stored in the variable ${file_sys} ): $per_used = `df -k | grep " ${file_sys}\$" | awk '{print \$5}'`; > Anthony (Tony) Esposito > Senior Technical Consultant > Inovis(tm), formerly Harbinger and Extricity > 2425 N. Central Expressway, Suite 900 > Richardson, TX 75080 > (972) 643-3115 > tony.esposito@ino...

Space in Use Negative with Space Restrictions
Using Console 1.3.6 I seem to get quite a few users who have a Space in Use statistic with a negative value therefore allowing them to save a lot more info in their home directories than the restriction should allow. Is there an issue in Console One or is it that the space restrictions on the volume are not working properly? Thanks for any enlightenment on this subject. Ben. ----------------------------------------------------------------------------------------- Ben Davis - Computer Systems Manager Christian Outreach College Brisbane email: ben@brisbane.coc.edu.au WWW: http://www....

space restriction (negative space in use)
I have NW6.0 SP4 and have space restriction enabled on NSS volume. The "space in use column" show negative numbers, any ideas how to fix this issue? Thanks. TID10074366 ? Cheers Dave -- Dave Parkes [NSCS] Occasionally resident at http://support-forums.novell.com/ Dave Parkes wrote: > 10074366 This TID takes me to http://www.novell.com/coolsolutions/netware/qna/nss.html There is a link "Wierd NSS Stats" which tells me to click on a link to 10074366. That link tells me: The document /10074366.htm is no longer available at support.novell...

Reserve Table Space / Use All Space Questions
XE3 version 11.0.4.820 Will using a database with the *Reserve Table Space* set to ‘No’ on a large database (10 GB) with lots of activity / updating would cause issues with performance with Interbase? Will using the XE3 database with the *Reserve Table Space* set to ‘No’ cause any long term issues? Gbak has the *–use_all_space* which I imagine the existence of the parameter is same as IBConsole’s ‘Use All Space’ set to true? IBConsole has the Use All Space (false is default or true). Are there any differences between a default restore using gbak compared to ibconsole? Jeff Cope ...

Space used
Hi Does anyone know of how to calculate the spaceused for an non clustered index in a table. Cheers vinodh In article <EnF2kLsYCHA.294@forums.sybase.com>, vinodh.sadagopan@btfinancialgroup.com says... > Hi > Does anyone know of how to calculate the spaceused for an non clustered > index in a table. > > Cheers > vinodh > > > sp_spaceused <tablename>,1 -- Roland van Veen <http://www.sybase.nl/solutions/consulting.html> ...

Temp Space
I want to disconnect a special user who let the temp space grow to the limit. Maybe with a SQL Querry which Result is a crossproduct. I tried this with a event handler but it was impossible to write the Trigger with drop connection. I got always an syntax error. Maybe anyone had the same problem. Create a separate event handler for diconnecting a user, and then in a trigger use a "TRIGGER EVENT" statement. And best of all, post the trigger, event source code. Artur On 2 Mar 2004 01:02:20 -0800, rochus.leinert@bahn.de wrote: >I want to disconnect a special user...

Space being used
I have NW 6.5 latest SP, last week I gave a volume 25 gig and it's gone already.... I know about Nsure audit and file system factory but I don't have any of those installed... Is there a way I can track who is abusing this volume.... I'm seeking when/who/and why all this space is gone already... Also, I would like to see if I can find a specific folder on that volume that may be being hit... Thank you Hi, I'd start with TreeSize Professional (http://www.jam-software.com/treesize/) or look at the inventory reports you can do in Remote Manager... Regards, ...

space used
Hi, Is there a way in ASA to invoke something like the ASE sp_spaceused function to determine space used for tables/indexes? Thanks! What version of SQL Anywhere are you running? Glenn Ellen wrote: > Hi, > Is there a way in ASA to invoke something like the ASE > sp_spaceused function to determine space used for > tables/indexes? > Thanks! -- Glenn Paulley Director, Engineering (Query Processing) iAnywhere Solutions Engineering EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all ...

Using Using
I have just completed reading a chapter in my book on performance. The books says to speed up performance on your web site you should use the using statement when opening connections. My question is how do you catch errors if you are using this as apposed to a try catch block.thanksBryan  Why would you use it as an alternative to a try/catch statement. Couldn't you just do something like  public void UsingSomeMethod() { try { SomeMethod(); } catch(SomeException e) { /// do something useful }}public void SomeMethod() { using (Something) { ...

When to use ( and when to use ((?
I'm just not getting it. When do you use ( in a statement, when do you need to use ((, and is the space after either or both mandatory? For example, I coded this statement: if (( $_ eq $bad_guys_ip )) { more_stuff_here }; Why the two (( in an if? And why does it seem to not work when I miss the space after the ((? Thanx! Mark me as... /Corn-fused|Dense/ > I'm just not getting it. >=20 > When do you use ( in a statement, when do you need to use ((, and is > the space after either or both mandatory? >=20 > For example, I coded this sta...

Can no longer use your product
Name: Trevor Dance Email: 106305dot2117atompuservedotcom Product: Firefox Summary: Can no longer use your product - new release uses too much space Comments: Since your new release these last few days, you have added some large system files. Because these are held in the Microsoft user profile area (irrespective of the directory that I choose to install Firefox), it exceeds the use profile space allocated by my company. This causes constant warning messages while I am working and prevents me shutting down the machine. The company will not change it’s IT policy to suite Firef...

Not all free space in a device fragment is used before using the next device for the segment ?
32-bit ASE 12.5.0.3 Solaris 8 As a result of having a free-space threshold, I have a segment that I have extended by adding another device to that segment. Now what I was expecting was that the free space on the device have to be 0 or something so low ( < 1 extent ) before ASE uses the new device that I added / extended to the segment. From the sp_helpdb output below, it is the index_segment that I have extended ( originally having only sgmaster_index device, then added segment_index02 device ). I noticed that ASE started using the new device ( sgmaster_index02 ) e...

Web resources about - Great gap between Est. Temp Space Used and Act. Temp Space Used - sybase.iq


Donald Trump and the Politics of Resentment
As you surely know by now, the latest round of Republican campaign cretinism came a few days ago when Donald Trump mocked a reporter with chronic ...

These New Star Wars: The Force Awakens Posters Are The Ultimate Nostalgia Play
Han, Leia, Luke! Practical effects! X-Wings and TIE Fighters! All that classic music! Empires and Rebels! A lot of the impetus of The Force Awakens ...

Russia-Turkey war of words escalates over downed warplane
Moscow slapped sanctions on Ankara on Friday as the war of words over a downed Russian warplane escalated, with Turkish strongman Recep Tayyip ...

LG investment adds new fuel to reports that Apple is planning an OLED iPhone
Remember all the recent reports saying that while the iPhone 7 won’t have an OLED display, it’s likely that Apple will come out with an OLED ...

Polanski won't face extradition from Poland after prosecutors say they won't ...
OCRegister Polanski won't face extradition from Poland after prosecutors say they won't ... OCRegister In this Oct. 30, 2015 file photo filmmaker ...

Pope Francis makes his first trip to Africa and everyone is excited
Pope Francis's first ever trip to Africa began on Wednesday when his plane touched down in the Kenyan capital, Nairobi. To highlight the need ...

Obama: Syrian refugees are like pilgrims on the Mayflower
**Written by Doug Powers Did everybody have a good Thanksgiving? You’re probably still finishing up leftovers so here’s an open thread for those ...

David Bowie, Björk, Thom Yorke, & More Sign Letter Urging Climate Change Deal
Dazed reports that David Bowie, Björk, Thom Yorke, Damon Albarn, Phil Selway, Courtney Barnett, Robert Plant, Chrissie Hynde, Bobby Gillespie, ...

‘RHONJ’ Teresa Giudice’s Daughter Gia Posts Happy Family Thanksgiving Photos, Melissa Gorga Shares Photo ...
Despite the fact that their mom is in prison, it seems that Teresa Giudice’s four daughters were still able to have a happy Thanksgiving. On ...

Resources last updated: 11/28/2015 1:48:21 AM