The execution plan for a SQL operation tells you step-by-step exactly how the Oracle optimizer will execute your SQL operation. Using AUTOTRACE, let’s get an explain plan for the following query:
Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
1. The PK_DEPT index is scanned (ID 3).
2. All EMP table rows are scanned (ID 5).
3. Rows are retrieved from the DEPT table based on the matching entries in the
PK_DEPT index (ID 2).
4. Resulting data from the EMP table is sorted (ID 4).
5. Data from the EMP and DEPT tables are then joined via a MERGE JOIN (ID 1).
6. The resulting data from the query is returned to the user (ID 0).
How It Works
When first looking at an explain plan and wanting to quickly get an idea of the steps in which the query will be executed, do the following:
1. Look for the most indented rows in the plan (the right-most rows). These will
be executed first.
2. If multiple rows are at the same level of indentation, they will be executed in
top-down fashion in the plan, with the highest rows in the plan first moving downward in the plan.
3. Look at the next most indented row or rows and continue working your way
outward.
4. The top of the explain plan corresponds with the least indented or left-most
part of the plan, and usually is where the results are returned to the user.
Once you have an explain plan for a query, and can understand the sequence of how the query will be processed, you then can move on and perform some analysis to determine if the explain plan you are looking at is efficient. When looking at your explain plan, answer these questions and consider these factors when determining if you have an efficient plan:
By looking at our original explain plan, we determined that the EMP table is larger in size, and also that there is no index present on the DEPTNO column, which is used within a join condition between the DEPT and EMP tables. By placing an index on the DEPTNO column on the EMP table and gathering statistics on the EMP table, the plan now uses an index:
Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
1. The PK_DEPT index is scanned (ID 3).
2. All EMP table rows are scanned (ID 5).
3. Rows are retrieved from the DEPT table based on the matching entries in the
PK_DEPT index (ID 2).
4. Resulting data from the EMP table is sorted (ID 4).
5. Data from the EMP and DEPT tables are then joined via a MERGE JOIN (ID 1).
6. The resulting data from the query is returned to the user (ID 0).
How It Works
When first looking at an explain plan and wanting to quickly get an idea of the steps in which the query will be executed, do the following:
1. Look for the most indented rows in the plan (the right-most rows). These will
be executed first.
2. If multiple rows are at the same level of indentation, they will be executed in
top-down fashion in the plan, with the highest rows in the plan first moving downward in the plan.
3. Look at the next most indented row or rows and continue working your way
outward.
4. The top of the explain plan corresponds with the least indented or left-most
part of the plan, and usually is where the results are returned to the user.
Once you have an explain plan for a query, and can understand the sequence of how the query will be processed, you then can move on and perform some analysis to determine if the explain plan you are looking at is efficient. When looking at your explain plan, answer these questions and consider these factors when determining if you have an efficient plan:
- What is the access path for the query (is the query performing a full table scan or is the query using an index)?
- What is the join method for the query (if a join condition is present)?
- Look at the columns within the filtering criteria found within the WHERE clause of the query, and determine if they are indexed.
- Get the volume or number of rows for each table in the query. Are the tables small, medium-sized, or large? This may help you determine the most appropriate join method.
- When were statistics last gathered for the objects involved in the query?
- Look at the COST column of the explain plan to get a starting cost.
By looking at our original explain plan, we determined that the EMP table is larger in size, and also that there is no index present on the DEPTNO column, which is used within a join condition between the DEPT and EMP tables. By placing an index on the DEPTNO column on the EMP table and gathering statistics on the EMP table, the plan now uses an index:
No comments:
Post a Comment