The prior article in this series finished the discussion on hard parsing. This article will take a look at the explain plan and discuss the order of processing and what the steps actually mean. Please be sure to read these articles in the proper order as they each build on the prior article!
Let’s review what we’ve learned so far, using the very likely familiar A-B-C example SQL explain plan example shown below.
Figure 1. Example Explain Plan.
First, we can review the indentation. Oracle processes SQL from the inner most steps outward. I call this going up the explain plan tree.
The first step is the Table Access on ID line 5 of the Execution Plan. This has an indented related step underneath (ID line 6) that is an Index Access. Indexes store key data from the table columns the index is created on, and the ROWID, the phsical location of that particular row where that data item can be found in the underlying table. In this explain plan, the Index Access at ID line 6 is passing these ROWIDs to ID line 5 and ID line 5 will do a physical read on the table to get the data block that contains one or more of the items being selected by the where clause (in this case…B.STATUS = ‘OPEN’ from the ID line 6 index access).
Each step creates a rowset. This rowset is passed up the execution tree.
A rowset is like a temporary table and is used just to process the SQL in memory. IF the rowset gets larger than 64K, then it is written to the temporary tablespace. These queries take longer to execute, because of the larger rowset being written to the temporary tablespace. An index produces a rowset that contains rowids, being passed to the related table. A table access will contain data perhaps being selected by the SQL.
Sometimes the table doesn’t get referenced. This kind of depends on what the SQL is looking for. For example a SELECT COUNT(*) can count the entries in the index. Since indexes are typically quite a bit smaller than tables, there will be far fewer phsical I/O operations to read all the leaf entries and count them than to read all the rows in the table. Another example is if the SQL only makes reference to columns that are in an index, then the CBO knows it doesn’t have to access the table for the data item, because it already has it. Again, getting information from indexes requires far fewer physical I/O operations than reading blocks from the table.
In this example, Table B is being joined with Table A with a Hash Join operation. This Hash Join will create a rowset. This rowset is then passed to the next step up, which also happens to be a join condition at ID line 2. This is where Table C is brought into the picture.
Now that all the tables have been accessed, if there are any more where clause items to be processed, they will appear in this part of the explain plan.
This particular SQL has addressed all the where clause items as part of the join process so it goes into the sort for the aggregate function (the count) then ID line 0 formats the data and it is returned to the program that submitted the SQL in the first place.
Leave a Reply