Toad World® Forums

Restarting a paused activity

This is probably a really basic question, but I’ve had to pause long-running jobs in Inspect SGA and Optimize SQL, but then when I reopen those jobs, I don’t know how to restart from the paused location without completely restarting from scratch. What’s the trick?

I am afraid you may mix up. Only Optimize Indexes supports the pause and resume function. For Inspect SGA and Optimize SQL, all you can do is re-inspect or optimize the job again.

That’s a shame. I thought SQL Optimizer was going to be useful for “deep dives,” analysis that takes more than 8 hours. The developers probably tested it by loading two or three queries into the SGA and analyzing them. In the real world that does no good, because the recommendation of what it would take to fix those three queries might mess up the other 10,000 queries that are in a production SGA. The developers should have written this with the assumption that the users would let it run for days, and that those users would have laptops, which means that the session needs to be paused and restarted on a regular basis. I have a 45-minute query that I can’t run through Opimize SQL because it generates 500 alternatives and can only run the first 10 in a workday.

For Optimize SQL, there is a workaround. You may stop the optimization and save the session. Then load it later and continue the test run. Please look for the save and load session button just below the “Optimize SQL” tab. Hope this help.

That’s the part I didn’t understand. I get that the optimization session can be saved and named, and that that session can be reloaded. Then what do I do to restart the optimization from where it left off? If I hit the auto-optimize button or the batch run button, it starts from the beginning and re-runs queries that it had already executed.

If you have alternatives produced, you can select specific alternatives and choose to Batch Run->Selected. So the idea might be something like, I’ve produced
alternatives and executed a few selected alternatives. I choose to save my session. When I’m ready, I re-open my session, and choose additional alternatives that I wish to execute. Repeat as necessary.

From: Eric Gan [mailto:bounce-Eric_Gan@toadworld.com]

Sent: Thursday, June 05, 2014 9:22 AM

To: sqloptoracle@toadworld.com

Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Restarting a paused activity

RE: Restarting a paused activity

Reply by Eric Gan

That’s the part I didn’t understand. I get that the optimization session can be saved and named, and that that session can be reloaded. Then what do I do to restart the optimization from where
it left off? If I hit the auto-optimize button or the batch run button, it starts from the beginning and re-runs queries that it had already executed.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Which really means we need a “resume” button :slight_smile:

Thank you for you guys suggestion, we are thinking to add a resume function after abort of a test running session or a restored session.

Perfect! Thank you very much!

Hi guys, just checking in. Has any progress been made on adding a resume function after an Inspect SGA session is paused or an Optimize SQL session is aborted?

We do have a plan to provide a Resume feature in Optimize SQL. However, this feature would require re-architecture of certain part of the product and we are not able to give any concrete time frame at this moment.

As for the Inspect SGA, we actually are not able to provide such a Resume feature. The SGA is dynamic and changes every minute. If you stopped the process and try to resumed later, you won’t get you want have in the SGA as before.

Glad to hear there is a plan for a Resume feature in Optimize SQL.

It seems like the Inspect SGA portion needs a redesign; with the current configuration, it’s only useful for inspecting very small applications, and who would buy a product to repair small applications? In my environment, that means that I can only run Inspect SGA for a maximum of 8 hours, assuming my SQLNet session doesn’t time out because of inactivity. Production applications will like mine will take more than 8 hours to inspect. Perhaps the resume feature can be built with the assumption that not much has changed since the prior execution, and pick up “near” where it left off. The restart point doesn’t have to be 100% precise.

Could you please describe how you use the Inspect SGA, particularly if you are using it to collect the executed SQL or the currently running SQL? Thanks.

Oh no! I did not mean to say Inspect SGA! I meant to say Optimize Indexes. I apologize for any misunderstanding. I have not used Inspect SGA.

My problem with Optimize SQL is that I can only use the product for a few hours at a time (8 hours at the office, about 16 at home). That means I can only run the first few alternatives for a 1-hour query. That wouldn’t be a problem if I could hit a resume button and continue the analysis each day for the next few days.

I try to do deep dives with Optimize Indexes, analyzing all the queries in SGA to find new indexes. The problem has been that the Stop and Start functions in Optimize Indexes don’t always work (the timer keeps ticking but no progress is being made, then stop and restart the program and it starts getting immediate results; and stopping has to be done from Task Manager since the program won’t respond, and many times the progress from that session is lost). This might be a case of the SQL Net session timing out because of a long-running query with no response, but I have no way of knowing for sure.

Thanks for the details. The problem you described seems to be a problem in the Stop/Start button not functioning sometimes. This has been improved in the latest release of version 8.9.1 so if you are not using this latest version, I suggest you try upgrading. We will also take a look again in the coming release to make the Stop/Start function more reliable.

Thank you again for your suggestions.

Thanks. I downloaded 8.9.1 and it does appear to have improved the situation. I ran three Optimize Index sessions since. The previous release used to hang during the Collect SQL phase, when the Stop button is grayed out, which is the same as not having a stop/restart feature since the session would have to be killed and the only option was to start over, and then it would hang in the same place. In all three sessions with the new release, it successfully made it through Collect SQL, but it hung on all three in either the Initialize Table or Initialize Index steps. In those cases, the Stop button was not grayed out, but it still didn’t work since the session was hung. The only recourse was to shut down SQL Optimizer from the Task Manager and restart it. In all three cases, the session did restart. At least the product is now usable; I couldn’t use Optimize Indexes for 6 months because of the earlier issue.

The Start/Stop problem still exists in 9.2.1 and is VERY irritating for longe-running queries…those are the ones that seem to need the most optimization.

I’m not impressed with the stability of the tool, but when it works, it is useful…

Hi Peter,

Sorry to hear that something is not working fine here. In version 9.2.1, you can stop a running process in Optimize SQL and later click on one of the action buttons (Auto Optimize, Rewrite, Test Run, etc.) to start/continue the process. If you are restarting an optimization process, you do not need to start from scratch again.

Would you please provide more information about the problem you have encountered? It would help us understand better the problem if you can provide steps of what you were performing and the details of the error occurred. I will have a closer look for you then.

Thank you in advanced for the information.

Regards,

Alex

I chose to optimize for duration and I’m running two(2) tabs against the same data connection, but when I restart using Test Run – Non-Tested( ) the tab either loses track of the original scenario run-time, or it assumes the run-time of the slowest tab(longest run-time).

If I choose to restart using Clear Optimization Results( ), either Keep/Clear Original Scenario and with two(2) tabs against the same connection, then I get unpredictable results: always I lose track of the original run-time and sometimes I also lose the program and sometimes I get a blue-screen of death.

Since the scenarios I’m testing involve >1 hr test cycles for each run and the program stays open for days at a time, I’ve found it can get unstable quite easily as I swap between the tabs, hide and show the window, or lock and unlock my desktop. Even switching between the tabs in quick succession can cause instability, so I’ve tried to minimize that by switching only when absolutely necessary.

The SQL I’m testing is usually >100 lines of code, returns >1,000,000 rows of data, and I’ve customized the optimization options to use almost all the hints possible (I just leave out anything having to do with star schemas, fact tables, and models).

Thanx,

Peter.

From: Alex Luk [mailto:bounce-AlexLuk@toadworld.com]

Sent: Wednesday, November 23, 2016 12:14 AM

To: sqloptoracle@toadworld.com

Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Restarting a paused activity

RE: Restarting a paused activity

Reply by Alex Luk

Hi Peter,

Sorry to hear that something is not working fine here. In version 9.2.1, you can stop a running process in Optimize SQL and later click on one of the action buttons (Auto Optimize, Rewrite, Test Run, etc.) to start/continue the process. If you are restarting an optimization process, you do not need to start from scratch again.

Would you please provide more information about the problem you have encountered? It would help us understand better the problem if you can provide steps of what you were performing and the details of the error occurred. I will have a closer look for you then.

Thank you in advanced for the information.

Regards,

Alex

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Peter,

Thank you for providing the details of what you were performing and what happened. I will log this information into our task list and will also pay closer attention to the stability issues around the areas/features you described. This information will be helpful for us to improve the product and your help is very much appreciated.

Thanks,

Alex