Seagate ST43401N/ND Guia de Instalação descarregar pdf (Página 56)

If Your Version is 11.5

Aggregate Optimization

Aggregate optimization was introduced in ASE 11.9.x. This allows the

optimizer to factor in an index on the column referenced in an aggregate

function. Sometimes the optimizer can be overly optimistic in costing this

index and may choose it when it is not the best choice. If you notice poor

performance because of this (

showplan will show the index on the aggregate

column being used), you can turn off aggregate optimization with trace flag

370, which will revert the server to a pre-11.9.x handling of aggregates by the

optimizer.

Costing Large I/O

Large I/Os can now be used to access index pages along with data pages. More

accurate costing of large I/Os using cluster ratios was added in 11.9.X. These

new statistics values for tables and indexes are stored in

systabstats.

Cluster ratios are used to determine if large I/O access will be efficient. The

lower the cluster ratio, the less likely a large I/O access will be useful. Look

under the heading “Derived statistics” in

optdiag or trace flag 302 output (Table

Information Block and the Index Selection Block) for the cluster ratios.

The optdiag Utility

Release 11.9.x introduced the new command line utility optdiag. optdiag

enables you to read, write and simulate statistics. Working directly with the

statistics as stored in

sysstatistics and systabstats is difficult because of their

datatypes,

optdiag makes it practical to read and modify statistics. Also, optdiag

allows you to test performance based on simulated statistics.

optdiag output is essential when analyzing optimizer-related problems. It

contains all the information about the data set that you need.

See the Performance and Tuning Guide at

http://manuals.sybase.com:80/onlinebooks/group-

as/asg1200e/aseperf/@Generic__BookView

for information on optdiag.

Trace Flag 302

Trace flag 302 prints the optimizer’s index costing and selection phase. It has

been redesigned to provide information on the new statistics and costing in an

easy-to-read format.

1 2 ... 51 52 53 54 55 56 57 58 59 60 61 ... 145 146

Sem comentários

Seagate ST43401N/ND Guia de Instalação Página 56