|
|
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.
- Access path: The means by which the database retrieves data from a database. For example, a query using an index and a query using a full table scan use different access paths.
-
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).
- Remove unnecessary large-table full-table scans - Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.
- Cache small-table full-table scans - In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows. In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.
- Verify optimal index usage - Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.
- Materialize your aggregations and summaries for static tables - Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily.
|
|
|
|
|
There are three major decitions that the SQL oprimizer must make during the optimization of a SQL statement:
- Access path: Oracle has seeveral choices of the best way to access data.
- Join method: Oracle must decode between nested loops joins, hash join, etc.
- Join order: The database has choices about the best table join order.
|
|
Access paths
The following table lists out the common type of access paths which are used by Oracle to get to the data sets.
| Acess Path |
Explanation |
| Full table scan |
Reads all rows from table and filters out those that do not meet the where clause predicates. Used when there are no indexes, DOP set, and so forth. |
| Table access by rowid |
Rowid specifies the data file and data block containing the row and the location of the row in that block. Used if rowid is supplied by the index or in WHERE clause. |
| Index unique scan |
Only one row will be returned. Used when the statement contains a unique or a primary key constraint that guarantees that only a single row is accessed. |
| Index range scan |
Accesses adjacent index entries returns rowid values. Used with equality on nonunique indexes or range predicate on unique indexes. |
| Index skip scan |
Skips the leading edge of the index and uses the rest. Advantageous if there are few distinct values in the leading column and many distinct values in the nonleading column. |
| Full index scan |
Processes all leaf blocks of an index but only enough branch blocks to find the 1st leaf block. Used when all necessary columns are in an index and order by clause matches index structure or if sort merge join is done. |
| Fast full index scan |
Scans all blocks in an index; used to replace a full table scan when all necessary columns are in the index. |
| Index joins |
Hash joins of several indexes that together contain all the table columns that are referenced in the query. |
| Bitmap indexes |
Uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Can efficiently merge indexes that correspond to several conditions in a WHERE clause. |
|
|
Optimizer Join methods
The following table lists the various types of join types used by the Oracle optimizer when generating access plans.
| Join Type |
Explanation |
| Nested Loop Joins |
For every row in the outer table, Oracle accesses all the rows in the inner table. Useful when joining small subsets of data and there is an efficient way to access the second table, for example, using index lookups. |
| Hash Joins |
The smaller of the two tables is scanned and the resulting rows are used to build a hash table on the join key in memory. The larger table is then scanned, the join column of the resulting rows are hashed, and the values used to probe the hash table to finish the matching rows. Useful for larger tables and if equality predicates. |
| Sort Merge Joins |
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.
|
| Cartesian Joins |
Joins every row from one data source with every row from the other data source, creating the Cartesian Product of the two sets. Only good if tables are very small. The only choice if there is no join condition specified in the query. |
| Outer Joins |
Returns all rows that satisfy the join condition and also returns all of the rows from the table without the (+) for which no rows from the other table satisfy the join condition. |
|
|