Toad World® Forums

sql optimizer crashes ...


#1

hello all,

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.
440x100_hatima_palaphone_010.jpeg


#2

Hi,

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.

Thanks,

Rene


#3

when i click on store session, it save a file. But it in .sts format and when i try to attach the file here…it says FILE IS TOO LARGE…


#4

Hi,

If you can zip the file so that it is under 10 mg, you can email it to me.

rene.woody@quest.com

Rene


#5

Please decrease the intellegent level to 2 or 3 to reduce the size of alternative SQL generation, may be your SQL is too complex.

Richard


#6

Hi abdul.irfan2_1234376198750,

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.

  1. What’s the version of QSO you are runing?
  2. Did problem happen when you run these alternatives one by one manually?
  3. Which execution mode (Run on client/Run on Server) is in use?
  4. Did the problem repeat all the time?
  5. Did the problem happen at other SQL?

Thanks,

Tony Ng


#7
  1. What’s the version of QSO you are runing?
  • SQL optimizer 7.4
  1. Did problem happen when you run these alternatives one by one manually?
  • It gives me about 25 alts. And i just select few of them for execution…maybe 2 or 3
    including the original…and thats when it crashes
  1. Which execution mode (Run on client/Run on Server) is in use?
  • NOT SURE ON THIS ONE…what exactly are you asking ?
  1. Did the problem repeat all the time?
  • Yes
  1. Did the problem happen at other SQL?
  • It actually happens with 2 selects that i am trying to tune, which is going
    to be used in 2 of the views I am creating.

– and oh by the way i did try movin the intel level to 2 or 1…same thing…


#8

Hi,

To find the answer to question number 3, go to the Options.
Look under

Tuning Lab
Execution
Execution Method

REne


#9

the execution method is clicked on RUN ON SERVER. is that a correct setting ?


#10

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.

  1.  Turn on the supple bundle from Help manual -> Support Bundle
    
  2.  Repeat the same steps to reproduce the bug
    
  3.  Send me back the support bundle file to tony.ng@quest.com
    

Thanks,

Tony Ng


#11

sent bundle to tony.ng@quest.com

Message was edited by: crazy


#12

Thanks for the support bundle, but how’s the “Run On Client” mode? It’s the same problem with executing SQL?


#13

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.


#14

The problem may be SQL statement dependence; so we need your help to define what’s the exactly SQL may cause the application crashes.

  1.  Using “Run On Client” mode
    
  2.  Change the setting of ‘Retrieve run time executing’ to ‘All SQL twice using second run time’. (Options -> Tuning Lab -> Execution)
    
  3.  Execute only one SQL each time
    

Thanks for helping us,

Tony Ng


#15

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…


#16

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…


#17

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


#18

THANK YOU AND EVERYONE ELSE FOR ALL THE HELP…what is the next release due ?? just curious…as 7.4 came out…


#19

The next release of version QSO 75 should be completed at middle of this year.

Tony Ng


#20

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.)

Thanks,

Tony Ng