Les07-干预优化器.ppt

Influencing the Optimizer Objectives After completing this lesson, you should be able to do the following: Influence the optimizer behavior at the following levels: Instance and session level Statement level Specify access-path hints Specify hints on and in views Setting the Optimizer Mode At the instance level, set the following parameter: For a session, use the following SQL command: Full Notes Page Some Additional Parameters OPTIMIZER_FEATURES_ENABLE Set to current release to enable the latest optimizer features Defaults to current release of database OPTIMIZER_INDEX_COST_ADJ Optimizer Hint Syntax Rules for Hints Place hints immediately after the first SQL keyword of a statement block. Each statement block can have only one hint comment, but it can contain multiple hints. Hints apply only to the statement block in which they appear. If a statement uses aliases, hints must reference aliases rather than table names. Hint Recommendations Use hints carefully because they imply a high maintenance load. Be aware of the performance impact of hard-coded hints when they become less valid. Optimizer Hint: Syntax Example Hint Categories Hints for optimization approaches and goals Hints for access paths Hints for query transformations Hints for Join Orders Hints for Join Operation Hints for Parallel Execution Other hints Optimization Goals and Approaches Full Notes Page Hints for Access Paths Full Notes Page Hints for Access Paths Full Notes Page Hints for Query Transformation  Full Notes Page Hints for Query Transformation Full Notes Page Hints for Join Orders Full Notes Page Hints for Join Operations Full Notes Page Hints for Join Operations Full Notes Page Other Hints Full Notes Page Hints and Views Do not use hints in views. Use view-optimization techniques: Statement transformation Results accessed like a table Hints can be used on mergeable views and nonmergeable views. Full Notes Page Hints for View Processing Summary In this lesson, you should have learned how t