Skip to Content
Former Member
Apr 20, 2016 at 04:07 PM

hands on 'optimizer', optdiag statistics...


--- sybase version ---

Adaptive Server Enterprise/15.7.0/EBF 22639 SMP SP52 /P/Sun_svr4/OS 5.10/ase157sp5x/3293/64-bit/FBO/Wed May 21 02:49:14 2014

--- end version ---

Hi community,

I'm starting reading/reading again some chapters of 'ASE 15.7 Performance and Tuning Series: Query Processing and Abstract Plans'.

I think I get the 'big picture' of the need of the optimizer but I'm looking for way to better understand it and the information it needs to provide the best plan to the execution engine.

Is there any document/presentation that will present a kind of 'hands-on understanding the optimizer'?

For example, I get a rough image when I read this


The query optimizer processes join predicates the same way it processes search

arguments, in that it uses statistics, number of rows in the table, index heights,

and the cluster ratios for the index and data pages to determine which index and

join method provides the cheapest access. In addition, the query optimizer also

uses join density estimates derived from join histograms that give accurate

estimates of qualifying joining rows and the rows to be scanned in the outer and

inner tables. The query optimizer also must decide on the optimal join ordering

that will yield the most efficient query plan. The next sections describe the key

techniques used in processing joins.


Those words would be meaningfull to me if I can see in practice how the output of 'optdiag statistics' helps the optimizer choosing the right plan, how some dbcc traceon(traceflags) can help me understanding the optimizer work.

Thanks in advance for your input.