Sybase Query Optimizer

What is Sybase query optimizer?

The Sybase query optimizer is in charge of evaluating the available join orders and choosing the best one. You know that we can determine what method it used using showplan:

set showplan on

But did you also know that we can use certain DBA tracing parameters to see the various plans it evaluated, and the estimated costs of each one? Here’s how:

dbcc traceon(3604, 302, 310, 317)

The 3604 sends the output to the monitor, or to a specified file. That prevents it from filling up your error log (3605).

Trace flag 302 prints the optimizer’s index costing and selection phase but 310 and 317 are the trace commands that can tell you which join orders were considered and rejected, and what the costs were. Together they’ll show you ALL the plans, so be prepared for a lot of output. Otherwise use 310 by itself.

There is a way to force the optimizer to choose a certain plan. Specifically, the join order specified by the query itself, in the “from” clause.

set forceplan [on|off]

The advantage is that it saves you the time the optimizer would have spent choosing a join order. The bad news is, that time is negligible anyway.

The disadvantage is that there is usually a reason the optimizer chooses its plan, and it can vary from execution to execution depending on the data and the variables of the query. Instead of forcing a plan you think is best, you may want to consider why the optimizer is choosing the “wrong” plan. That’s where the aforementioned tracing can help.

As a quick fix, though, make sure your statistics and stored procedures are up to date! The optimizer depends on its accuracy for the best results.

update statistics table_name [index_name]

exec proc_name with recompile.

I also found a really good article from Sybase entitled “Analyzing and Resolving Optimizer Problems/Symptoms”:

http://www.sybase.com/detail?id=2602