The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. Why access 11432983 rows to realize that only 76563 rows are needed? Example 9-1 Looking for Throw-Away in an Explain Plan Rows Execution Planħ6570 TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALLħ6570 INDEX RANGE SCAN (object id 178321)ħ6563 TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALLġ1432983 INDEX RANGE SCAN (object id 186024) Initialization parameters - set globally or at session levelĮxamining an explain plan lets you look for throw-away in cases such as the following:įor example, in the following explain plan, the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows, throws away 99% of them, and retains 76563 rows.Some factors that affect the costs include the following: Schema changes (usually changes in indexes) between the two operations.Įven if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Two users might be pointing to different objects in the same database, resulting in different execution plans. The user explaining the statement is different from the user running the statement.The execution and explain plan happen on different databases.This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.Įxecution plans can differ due to the following: EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained. With the cost-based optimizer, execution plans can and do change as the underlying costs change. For more information on using explain plans, see Database Tuning with the Oracle Tuning Pack. Oracle Performance Manager charts and Oracle SQL Analyze can automatically create and display explain plans for you. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query. The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. Parallel execution, such as the distribution method of join inputs.Partitioning, such as the set of accessed partitions.Optimization, such as the cost and cardinality of each operation.In addition to the row source tree, the plan table contains information about the following: Data operations like filter, sort, or aggregation.A join method for tables affected by join operations in the statement.An access method for each table mentioned in the statement.An ordering of the tables referenced by the statement.The row source tree is the core of the execution plan. A statement's execution plan is the sequence of operations Oracle performs to run the statement. The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. Oracle9i SQL Reference for the syntax of EXPLAIN PLAN
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |