System and Application Softwares
Oracle SQL Tuning: Execution Plan
An execution plan is the combination of the steps that Oracle Database uses to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them for the user issuing the statement. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.
Join method: A method of joining a pair of row sources. The possible join methods are nested loop, sort merge, and hash joins. A Cartesian join requires one of the preceding join methods
Join order: The order in which multiple tables are joined together. For example, for each row in the employees table, the database can read each row in the departments table. In an alternative join order, for each row in the departments table, the database reads each row in the employees table.
The goals of SQL tuning focus on improving the execution plan to fetch the rows with the smallest number of database "touches" (LIO buffer gets and PIO physical reads).
There are three major decitions that the SQL oprimizer must make during the optimization of a SQL statement:
The following table lists out the common type of access paths which are used by Oracle to get to the data sets.
The following table lists the various types of join types used by the Oracle optimizer when generating access plans.
Consists of two steps.
Both the inputs are sorted on the join key.
The sorted lists are merged together.
This is useful when the join condition between two tables is an inequality condition or oneof the tables is already ordered, e.g., index access.
© 2019 Digcode.com. All rights reserved.