I am trying to run a query on the tuning lab and not the batch optimizer. and for some
odd reason sql optimizer closes. Once i click on optimize button, it applies all the
hints and give me all the alternative query. But when i go and click on EXECUTE ALL.
SQL optimizer closed and give me the below error…first error is the screenshot1
and the screenshot2 is another error that i get after screenshot1. I thought it was
running out of memory on my laptop. But then i opened up task manager. and the load
look fine. I have 3gigs of ram on my laptop. Any reason why this is happenin
and how can i fix it ?? As i mentioned i only to get the ALTERNATIVE query part
and then when i try to execute all of them to get the timing and other stats…it
closes on me…any help will be greatful.
Could you optimize this SQL statement again and then click the Store Session button to save the optimized SQL statements? Would you then attached the file so that we can take a look at the SQL statements to see if we can find out why this may be happening.
I can sure it is nothing about not engouh menory. However, I can’t tell what’s the exactly problem is at the monment, so there are a few questions which may help to define.
What’s the version of QSO you are runing?
Did problem happen when you run these alternatives one by one manually?
Which execution mode (Run on client/Run on Server) is in use?
Thanks for the quick response. ‘Run On Server’ mode is just an option for executing and it should be fine in any setting.
This execution option (Server or Client side) will take difference script to execute the SQL. If you choice Server Side, QSO will generate a PL/SQL script which is including the counting of timer to mark the response time, in other word, the Client side execution will take timer at the local client PC. So, I may suggest you to try the Run On Client testing and setting the limitation of rows retrieved to minimize the usage of resource.
Actually, it is not a solution or workaround of your problem. It is a simple test to help us. And than I will be very appreciated, if you can send me the support bundle.
Turn on the supple bundle from Help manual -> Support Bundle
Repeat the same steps to reproduce the bug
Send me back the support bundle file to tony.ng@quest.com
still the same on both modes…i am thinking maybe its just a complex select statement and cant execute it. As i said earlier, It does produces alternatives. Just when it comes to executing them it crashes.
are you asking to break the sql down into part ?? and then run it ?? its a complex select statement being used to create a view…it it joining 3 different tables with a union to get some more data out…and there is not a lot we can do with the veiw…(select statement)…as its a 3rd part vendor…we can re-write the query but the data retrived needs to be the same…
after chagin the below setting…its working fine now…
" Changethe setting of ‘Retrieve run time executing’ to ‘All SQL twice usingsecond run time’. (Options -> Tuning Lab -> Execution) "
THANK YOU…could you explain what the setting is doing and how it all of a sudden started working fine after changing that. Thanks again for all your help…
Sorry for late reply. It is most possibly a program bug. We are so sorry about what happened, but I sure it will be fixed in next version. By now, we are rewritten the whole tuning module for improving the look and feel. And we almost rewrite everything at the UI side, which means the bug here no longer exists. Thanks for your help Tony Ng
The new version of QSO will include better UI control to support UNICODE. It also allow user to save the whole tuning lab (not just save and restore alternatives).
And then the optimization engine has a great change. The SQL alternatives will be returned back during the optimizing. The benefit is that user may abort the optimizing anytime when better alternative found. (In current version, all alternatives will be returned just after the optimizing completed, so there is nothing show when the process cancel.)
