There are many variations of how to query the plan table. I would recommend for the moment that Oracle DBA begin
with the Oracle supplied 'utlxpls.sql' which is designed to return the plan for the last serial query (ie
non parallel execution) in the plan table. Even the SQL to query the plan table looks complex, but we do
not need to know any of the detail - just the result.
UTLXPLS.SQL
In SQLPlus issuing the following command will return the results for the last explain plan (which was against
the last SQL statement above).
@utlxpls.sql;
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3K| 11K| 8 | | |
| INDEX RANGE SCAN |LOTS_OF_N | 3K| 11K| 8 | | |
--------------------------------------------------------------------------------
That's a fairly simple plan, we can see that Oracle is going to scan a range of values from the an index,
unfortunately the formatting is a little of and we can't quite see the whole index name. Oracle DBA can modify
the SQL in utlxpls.sql if you wish to avoid this.
We can see that as a result of the index range scan, Oracle will fetch 3000 rows, a total of 11000 bytes
with a total cost (Disk I/O) of 8 (at 8K per block that's 64K of disk reads).
Here's a slightly more complex plan...
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 4 | 116 | 3 | | |
| FILTER | | | | | | |
| HASH JOIN OUTER | | 4 | 116 | 3 | | |
| TABLE ACCESS FULL |C_CURRENC | 3 | 48 | 1 | | |
| TABLE ACCESS FULL |C_CURRENC | 4 | 52 | 1 | | |
| SORT AGGREGATE | | 1 | 10 | | | |
| FIRST ROW | | 2 | 20 | 1 | | |
| INDEX RANGE SCAN (MIN/|C_CURRENC | 2 | | 1 | | |
--------------------------------------------------------------------------------
We don't need to see the actual SQL but starting with the innermost statements (the bottom) we can
see that Oracle is going to use an index range scan to find a minimum or maximum value. It also knows
it only needs the first result of this.
Both tables are tiny, so full table scans have been opted for even though indexes exist, the tables will be
read into memory and hash joined (Oracle will hash the primary keys to perform the join).
The result will then be sorted to apply the lower join and the entire result set filtered
to obtain the required row.
Total bytes worked with is 116, and the cost in disk I/O is 3 blocks (24K).
The intricacies of each row in the plan table are far too detailed to go into here. In fact I'm not sure
I would be able to adequately explain each type of row you might see. All I can suggest is that Oracle DBA
look for...
|
|
- Full table scans (except very small tables)
- Index scans which return a large number of rows
- In memory sorts or hash joins which use a large amount of memory
- A high cost (Disk I/O)
|
If you see any of these you should stop and consider whether there is a better way to perform your SQL.
Much earlier on we discussed the age/gender table. Consider the plans for the following statements...
SELECT *
FROM (
SELECT *
FROM PEOPLE
WHERE GENDER = 'M'
)
WHERE AGE = 34;
SELECT *
FROM (
SELECT *
FROM PEOPLE
WHERE AGE = 34
)
WHERE GENDER = 'M';
I know both statements are never going to be found in the real world but they show the point. The first
statement would cause an index range scan resulting in 10000 records being filtered in memory (and the
relevant amount Disk I/O and memory usage. The second statement would cause an index range scan resulting
in 200 records being filtered in memory. The Disk I/O of the second statement would also be subsequently
lower.
Both statements achieve the same result, but with the first statement being 50 times more resource hungry.
Learn Oracle - Doing it the TOAD way
TOAD Is becoming more and more popular and I see it at many more customer sites by the day. As it is also
my tool of choice I though it only fair to include a nice feature of TOAD which simplifies obtaining a
plan.
TOAD also requires a plan table to be present and supplies it's own script for this purpose. If it has
not already been executed, Oracle DBA can find this script in your TOAD directory under the 'Temps' subdirectory.
Under some versions of TOAD the 'TOADPREP.SQL' script builds the TOAD plan tables (along with other bits
and pieces) and other versions have it in a separate script called 'TOADPLAN.SQL'.
Once this script has been executed you can obtain a plan quite simply at any SQL Editor window by placing
your cursor on the statement and pressing CTRL-E.
If all is configured correctly Oracle DBA should see the following....
