Sql Optimizer 8.0.0 memory issue

Toad for Oracle
1

Hi, I’m using Sql Optimizer 8.0.0 and trying to optimize a fairly
complex statement. I let it run for about an hour and it had tested
about 1600 alternatives. Then I noticed my system was slowing and I was
having problems switching between windows. Checking task manager, I see
that the optimizer is taking about 25%+ of my CPU and the memory usage
is over 1.4 million K. I tried to abort the running process in the
optimizer by going to the sql details tab and clicking ‘abort process’.
I waited about 10 minutes and the status still says aborting… I tried
closing Sql Opt but get an error message…a process is currently
running in the following module(s): Optimize SQL Please wait until the
process is complete or abort it before closing the application again.
So I finally killed it from the task manager, and that did fix my
performance issues…really would have like to look at some of that
optimizer data though…

2

1600 alternatives is a lot for the tool to process. Can you try setting the intelligence level down a notch? This will cause it to find less alternatives, but I reckon even if it only generates 200, you’ll still have a pretty good spread to evaluate.

You can also post your questions to the SQL Optimizer Community, as it is developed separate from Toad
http://sqloptimizeroracle.inside.quest.com/index.jspa

Jeff

3

I’ve forwarded this to the sql optimizer dev team – I too have seen
some slowdowns even on what I consider rather average sql statements ……

4

Hi,

Optimizer currently keeps the alternatives in memory to compare for unique plans
and to test run. That data it used can be huge as we keep more than just the SQL
text but also all kinds of parsing information of your SQL so we can generate
different alternatives. We know that it is not an efficient way to keep them in
memory and we are going to revise that in the next release.

Thanks,

Alex

5

Is there a way to force it to abort when asked? Clicking on “abort process”
didn’t seem to work. I’ll send future questions to the optimizer list…sorry.

On 9/27/2010 6:22 AM, Alex Luk wrote:

Hi,

 

Optimizer currently keeps the alternatives in memory to compare for unique
plans and to test run. That data it used can be huge as we keep more than
just the SQL text but also all kinds of parsing information of your SQL so
we can generate different alternatives. We know that it is not an efficient
way to keep them in memory and we are going to revise that in the next
release.

 

Thanks,

Alex
6

Unfortunately no. The abort process is one of the things that starts failing when the memory consumption is too high. Fixing the memory consumption problem should fix this problem as well. Nevertheless, we will look at the abort process when we revise the memory problem.

Regards,
Alex