1 / 14

SQL Tuning

SQL Tuning. Optimizer Tasks. Oracle optimizer performs the following functions when evaluating a SQL statement: Evaluates the expressions and conditions contained in the SQL statement Transforms complex queries into an equivalent join query

amalie
Download Presentation

SQL Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Tuning

  2. Optimizer Tasks • Oracle optimizer performs the following functions when evaluating a SQL statement: • Evaluates the expressions and conditions contained in the SQL statement • Transforms complex queries into an equivalent join query • Chooses an optimization goal, either the Cost-based Optimizer (CBO) or the Rule-based Optimizer (RBO) • Determines how to access each table to retrieve data • Determines the order of table joins for statements involving more than two tables • Chooses the type of join to be performed for each pair

  3. Optimizer Modes

  4. Optimizer Goal

  5. Rule-based Optimizer Ranks

  6. Cost-based Optimizer Process • CHOOSE: Oracle chooses the best and most efficient path to execute and retrieve data. Optimizer can select a cost-access method when statistics are available or a rule-based access method if it is faster. This setting is the most common mode used. • ALL_ROWS: Oracle uses cost based access methods regardless of statistics availability. This optimizer priority is to retrieve all rows with the minimum amount of resource consumption. This option often used in Web applications. • FIRST_ROWS: Oracle uses a mix of cost-based access methods and heuristics to retrieve the first few rows fast. This option is often used in FORMS client/server applications. • FIRST_ROWS_N: Where N is 1, 10, 100, or 1000. Uses the cost-based access method to retrieve rows the most efficient way.

  7. Cost-based Optimizer Process (continued)

  8. Cost-based Optimizer Access Paths

  9. Cost-based Optimizer Access Paths (continued)

  10. Execution Plan Interpretation

  11. Optimizer Parameters • Optimizer Performance: • OPTIMIZER_DYNAMIC_SAMPLING • OPTIMIZER_FEATURES_ENABLE • OPTIMIZER_INDEX_CACHING • OPTIMIZER_INDEX_COST_ADJ • OPTIMIZER_MAX_PERMUTATIONS • Optimizer Behavior: • CURSOR_SHARING • DB_FILE_MULTIBLOCK_READ_COUNT • HASH_AREA_SIZE • HASH_JOIN_ENABLED • PARTITION_VIEW_ENABLED • QUERY_REWRITE_ENABLED • SORT_AREA_SIZE • STAR_TRANSFORMATION_ENABLED

  12. Optimizer Hints

  13. Optimizer Hints Syntax

  14. Classification of V$SQL Columns

More Related