Toad World® Forums

no alternative produced for sql at level 10 (with some exception error)


#1

hello all,

 Seems like QSO is not producing any alt for me.  i understand it is a complex query.  But after waiting for it to process for over 27 hours (done that 4 times) i get no results.  All it says is all process compelete.  See screen shots.  Also when i close QSO i get some exception error with the application.  screenshot attached.  the level i used was 10.  I was thinking atleast couple of alt must have been produced.  But nothing happens.  Any idea why this is happening or workaround for this ??  as i said this has happen well over 4 times.  It took over 24+ hours and then it didnt produce any alt's

image001.jpeg


#2

You can try Intelligence leve 2 or 3 first, since there are too many alternative will be generated for complex SQL, level 10 will cause memory problem. We will fix this problem in coming release.


#3

i had started this out at level 3. It said it was not able to find any alt. This was using Perdefined settings of " Use oracle optimization hints" … i then tried levl5…same thing…thats why i jumped at level 10…which gives no results…just says all processing compelete.

When is the the next release comming out ?? and will that support 64bit ??


#4

Hi,

Our next release will be in October. 64-bits support is one of the major goal for this release.

As for the memory consumption problem, we have a hotfix for it. Though it is not a complete fix, it has better memory management. If you could contact Support to open a case, they should be able to get you the hotfix.

Thanks.


#5

hello,

   I dont think this might be our problem(memory consumption).  because all those times i have monitored the memory consuption.  And we actully have a special desktop build just to run this quest optimizer program.  Its a quad core running at 2.3Ghz with 8GB of memory.  And as i said we monitored the memory consuption for it and that didnt seem to be a problem.  But will contact Support for it.  Am i asking for HOTFIX or is there a patch/bug # for the thing you are referring to.

Also another question i had was. Once it produce all the alts. Then select execute all. Let the original statement run and do its tmiing…and for other alts cut them off under 5mins timing … as i know there is a option of …cancel execution of user defined time … as the original sql run for about an hour or so…i want to give an hour of that time and then 5 mins of others alt for execution…can that be done ?? As i tried that…it canceled the orignal at 5 mins as well…


#6

Hi,

The memory issue I mentioned is not a problem on your machine but in the product to correctly detect if there is enough memory. So it would be a case that the product misunderstood that there is not enough memory and hence terminated your optimization. To check if there is a termination occurred, you may check the Messages window to see if there is a line saying “User Cancel”. And when you contact Support, you may quote Case 714970 or simply ask for escalating your problem to the dev.

Regarding setting the options to terminate, both the original and alternative SQL will be affected by the options. A workaround would be to execute the original in one setting and then execute the other alternatives in another settings. However, I will suggest you start by executing only the alternatives by setting a 5 mins termination time. That is, skip running the original SQL, as you already know its run time. Doing this would help shorten your overall execution time. After finding an alternative that runs in 5 mins, you may then run the original SQL to compare. And in case you don’t find any alternative running faster than 5 mins, you can change the termination time to 10 mins (or 30 mins) to run the alternatives again.

Thanks.