tailieunhanh - SQL Statement Tuning

After completing this lesson, you should be able to do the following: Control optimizer options Use optimizer hints Employ plan stability Use stored outlines Use SQL Trace and TKPROF | SQL Statement Tuning Objectives After completing this lesson, you should be able to do the following: Control optimizer options Use optimizer hints Employ plan stability Use stored outlines Use SQL Trace and TKPROF Overview The purpose of this lesson is: To provide methods to determine the resources used by SQL statements: Oracle Enterprise Manager Statspack Explain plan SQL Trace and TKPROF Autotrace To determine which SQL statements possibly require tuning Not to tune the actual SQL statements Overview In this lesson you will examine the different methods that can be used to determine the resources used by a SQL statement. If you know the resources used by a SQL statement, then you can determine which statements are the best candidates for tuning. This course does not cover SQL tuning. For further information on this topic, the Oracle9i: SQL Tuning Workshop course is recommended. Optimizer Modes There are two types of optimizer modes: Rule-based: Uses a ranking system Syntax- and data dictionary-driven Cost-based: Chooses the path with lowest cost Statistics-driven Optimizer Modes Rule-Based Optimization In this mode, the server process chooses its access path to the data by examining the query. This optimizer has a complete set of rules for ranking access paths. The rule-based optimizer (RBO) is syntax-driven, in that it uses the statement syntax in combination with data dictionary information about the data structures to determine which execution plan to use. This optimizer mode is supported for backward compatibility. Cost-Based Optimization In this mode, the optimizer examines each statement and identifies all possible access paths to the data. The resource cost of each access path is calculated and the least expensive one is chosen. The cost is based mainly on the number of logical reads. The cost-based optimizer (CBO) is statistics-driven in that it uses statistics generated for the objects involved in the SQL statement to determine the most effective . | SQL Statement Tuning Objectives After completing this lesson, you should be able to do the following: Control optimizer options Use optimizer hints Employ plan stability Use stored outlines Use SQL Trace and TKPROF Overview The purpose of this lesson is: To provide methods to determine the resources used by SQL statements: Oracle Enterprise Manager Statspack Explain plan SQL Trace and TKPROF Autotrace To determine which SQL statements possibly require tuning Not to tune the actual SQL statements Overview In this lesson you will examine the different methods that can be used to determine the resources used by a SQL statement. If you know the resources used by a SQL statement, then you can determine which statements are the best candidates for tuning. This course does not cover SQL tuning. For further information on this topic, the Oracle9i: SQL Tuning Workshop course is recommended. Optimizer Modes There are two types of optimizer modes: Rule-based: Uses a ranking system Syntax- and .