oracle学习11g性能调优官方less_10.ppt

Influencing the Optimizer Objectives After completing this lesson, you should be able to: Describe the optimizer’s behavior Adjust parameters to influence the optimizer Adjust data structures to influence the optimizer Functions of the Query Optimizer Selectivity Selectivity represents a fraction of rows from a row source. Selectivity affects the estimates of I/O cost. Selectivity affects the sort cost. Selectivity lies in a value range from 0.0 to 1.0. When statistics are available, the estimator uses them to estimate selectivity. When statistics are not available the estimator uses default values or dynamic sampling. With histograms on columns that contain skewed data, the results are good selectivity estimates. Cardinality and Cost Cardinality represents the number of rows in a row source. Cost represents the units of work or resource that are used. Changing Optimizer Behavior The optimizer is influenced by: SQL statement construction Hints Statistics Data structure System parameters Session parameters SQL Plan Management options Using Hints Hints Are directives (suggestions) to the optimizer Require changing the code Are useful to test specific access paths Must be maintained through upgrades Optimizer Statistics The optimizer depends on various statistics to determine an optimal execution plan for an SQL statement. Some statistics are gathered at various times and are controlled by statistic preferences. Object statistics Dictionary statistics Other statistics are only gathered manually. Statistics on fixed objects Operating system statistics Extended Statistics Optimizer needs additional statistics in some cases. Multicolumn statistics for related columns (column groups) Collect statistics over a group of columns For complex predicates For equality predicates only Expression statistics for function-based indexes Collect statistics over an expression For expressions used in predicates Creates a virtual column Controlling the Behavior of the Optimizer with Pa