Detecting full-table scans using EXPLAIN PLAN
- With the Oracle Enterprise Manager open, click the Oracle SQL Analyzer button.
- Read the opening screen, and click OK.
- Now we are ready to begin. Select the bottommost database under the Databases folder.
- Select Top SQL from the mon1 database.
- The TopSQL Options box will allow you to control the number of disk reads as well as apply various filters. Click OK.
- SQL disk reads appear in ascending order. The first row, which begins select/*+first_rows*/ has already been selected for you. Click the SQL Text tab.
- The SQL text now appears in the SQL Text Tab window. Place your cursor anywhere in the SQL text field to highlight the text.
- In an actual Oracle environment, you would press Ctrl+C to copy this text and Ctrl+N to move to a new screen. For the purposes of this simulation, press Enter to move to a new SQL screen.
- For the purposes of this simulation, place your cursor in the SQL statement window and hit Enter to paste your SQL text. You would normally accomplish this by pressing Ctrl+V.
- Typically, you would press F9 to see the rule-based EXPLAIN PLAN, but for the purposes of this simulation press Enter.
- Here we see a full-table scan.
Viewing the Execution Plan
Execution plans are hierarchical in nature. That is, the task of executing a query is one large step, which can be broken down into one or more smaller steps. Each of those steps might then be broken down further into even smaller steps. This division continues until the bottom is reached. To look at such an execution plan, you need to execute a hierarchical query such as the one shown here:
SELECT LPAD(' ', 2*(level-1)) ||
operation || ' ' || options
|| ' ' || object_name || ' ' ||
DECODE(id, 0, 'Cost = ' || position)
START WITH id = 0 AND statement_id = 'statement_id'
CONNECT BY PRIOR id = parent_id
AND statement_id = 'statement_id';
The START WITH and CONNECT BY clauses in this query are used to organize the results in a hierarchy. The ID and PARENT_ID fields in the plan table determine that hierarchy and each step is indented underneath its parent.
The LPAD expression in the query's select list is used to indent the results to reflect the hierarchy. Each plan table record contains an ID and a PARENT_ID column. The results of each step are fed as input into the parent step,
which is identified by the PARENT_ID. If this query is used to view the plan for the emp_report statement that was explained in the previous section, the following results will be displayed:
SELECT STATEMENT Cost = 9
SORT ORDER BY
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL SALGRADE
TABLE ACCESS FULL EMP
The step that is indented the most is executed first. In this example, the first step is a full table scan of the salgrade table. The results from this step are then fed as input into the parent step.