![]() 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 |