This document contains basic instruction in interpretting Oracle execution
plans and tuning SQL statements to get the quickest possible response. This
document assumes that you are already able to generate execution plans. QMenu's
Expert Query procedure has the ability to generate execution plans (Explain Plan
button). If you are using Expert Query you have everything you need to proceed.
If, however, you intend to explain SQL statements from Oracle's SQL*Plus
product, please see the SQL*Plus Addendum to this document.
Note: Oracle will not generate an execution plan if the SQL is invalid. If
the SQL is invalid, correct your errors before trying again to explain the SQL.
Sample Execution Plan
(back
to top)
This sample execution plan was generated by Qmenu's Expert Query form which utilizes the SQL statements documented in the SQL*Plus Addendum to this document. Each step of the execution plan is indented zero or more levels to indicate its relative importance and order of execution.
SELECT STATEMENT Cost = 1 (1) SORT(UNIQUE) 2 (1) FILTER 3 (1) NESTED LOOPS(OUTER) 4 (1) FILTER 5 (1) NESTED LOOPS(OUTER) 6 (1) FILTER 7 (1) NESTED LOOPS(OUTER) 8 (1) NESTED LOOPS(OUTER) 9 (1) NESTED LOOPS(OUTER) 10 (1) NESTED LOOPS(OUTER) 11 (1) TABLE ACCESS(FULL) SNAP$_PEBEMPL 12 (2) TABLE ACCESS(BY INDEX ROWID) SNAP$_SPBPERS 13 (1) INDEX(RANGE SCAN) PK_SPBPERS 14 (2) TABLE ACCESS(BY INDEX ROWID) SNAP$_FTVORGN 15 (1) INDEX(RANGE SCAN) PK_FTVORGN 16 (2) TABLE ACCESS(BY INDEX ROWID) SNAP$_FTVORGN 17 (1) INDEX(RANGE SCAN) PK_FTVORGN 18 (2) TABLE ACCESS(BY INDEX ROWID) SNAP$_NBRBJOB 19 (1) INDEX(RANGE SCAN) PK_NBRBJOB 20 (2) TABLE ACCESS(BY INDEX ROWID) SNAP$_NBRJOBS 21 (1) INDEX(RANGE SCAN) NBRJOBS_KEY2_INDEX 22 (2) INDEX(RANGE SCAN) SPRIDEN_KEY_INDEX
Note: The numbers in the left most column are the sequence numbers for lines in the execution plan, not the order in which steps are executed.
When reading an execution plan, you need to first identify which step Oracle would perform first. When formatted as above, the most indented (1) which has an associated (2) at the same indentation level is the first step that Oracle would perform. Steps at the same indentation level are performed in sequence; first the 1 and then the 2. The manner in which records from a subsequent step are related to records from a prior step is indicated by the next most indented (1).
In the sample above, the step labeled "TABLE ACCESS(FULL) SNAP$_PEBEMPL" is the step that Oracle would perform first. After performing the FULL table scan to retrieve all qualifying records, Oracle would then do the step labled "TABLE ACCESS(BY INDEX ROWID) SNAP$_SPBPERS". The statement "INDEX(RANGE SCAN) PK_SPBPERS", which is indented immediately under the step retreiving SPBPERS records, indicates which index will be used and whether or not Oracle is able to do a unique lookup on the index or has to scan the index to find matching records. The statement "NESTED LOOPS(OUTER)" immediately above the first two steps indicates the manner in which Oracle will relate the first two data sets. For every record in PEBEMPL, Oracle will attempt to find a qualifying SPBPERS record. "(OUTER)" indicates that an outer join is being perfomed which in turns means that Oracle does not throw away PEBEMPL records if it can't find qualifying SPBPERS records.
This is the order in which Oracle would perform the steps in the sample explain plan (the left most column indicates the applicable execution plan line numbers):
10,11 scan PEBEMPL table and for every PEBEMPL record 12,13 attempt to find SPBPERS records via index (don't throw out unmatched) 9 for every record in resulting set, 14,15 attempt to find FTVORGN records via index (don't throw out unmatched) 8 for every record in resulting set, 16,17 attempt to find (second copy of) FTVORGN records via index (don't throw out unmatched) 7 for every record in resulting set, 18,19 attempt to find NBRBJOB records via index 6 filter the resulting set 5 for every record in resulting set, 20,21 attempt to find NBRJOBS records via index (don't throw out unmatched) 4 filter the resulting set 3 for every record in resulting set 22 attempt to find SPRIDEN info via index (no need to access table)
When evaluating an execution plan, there are a number of things to look for that can make a big difference in how well your SQL performs.
While there are sometimes good reasons to have more than one FULL table scan in an execution plan, those reasons are not applicable to the majority of SQL statements executed in RPTS.
There are times when it is appropriate to perform a FULL table scan on the first table accessed. For instance, if the table is small or you need the majority of the records from that table or there simply is no (appropriate) index. Otherwise, you should be attempting to utilize an index even when retreiving records from the initial datasource access by your SQL. This applies to all the datasources accessed by your query, even the initial datasource [that is, unless your circustances qualify as "times when it is appropriate to perform a FULL table scan"].
The choice of initial table can have a big impact on how well your SQL statement performs. For instance, it is more efficient to scan a small table containing columns that clearly identify the desired population of records and then utilize indexes to find records in associated tables than it is to scan a large table and then attempt to find associated records in other tables only to throw away most of the records because they don't meet your population criteria.
Outer joins are expensive; they slow down SQL execution. Outer joins are used to insure that important records in the 1st datasource are not lost simply because a matching record is not found in the second datasource (datasource 1 has the budget for the University and datasource 2 has the titles for departments).
You must know your data to best determine when an outer join is necessary. In a Banner database, there are controls that assure that important relationships like $$ to titles are maintained. RPTS, however, is simply a copy of Banner tables. In RPTS, your best indication of whether or not you need to utilize an outer join is the age of the datasources (when they were last refreshed). If refreshed at the same time, you have some assurance that the important relationships are reflected in the data. If not, you have to determine whether or not you can afford to lose records. Know your data!
Tuning SQL is not an exact science; it is also an art. In generating an execution plan Oracle decides which table to utilize as its entry point, whether or not to utilize indexes, which of many possible indexes is most appropriate and the order in which to access tables. Oracle makes its decisions based on what it infers from your SQL statement and other information stored in Oracle's data dictionary. The art to tuning involves what you put in your SQL statement in order to influence Oracle's choices. The two most powerful ways to influence Oracle is the order in which you include datasources in your FROM clause and the condition statements you include in your WHERE clause.
The condition statements you include in your SQL (WHERE this and this or that) have a direct impact on the choice of indexes utilized by Oracle. An index can be utilized only if you write conditions that select for values contained in key fields of an index. Conversely, you can prevent Oracle from utilizing a particular index by writing condition statements that make negative comparisions to key fields in the index.
If a table is indexed on STUDENTID and COURSE#, you can influence Oracle to utilize that index by including in your WEHRE clause conditions comparing STUDENTID to some know value or pattern. If you also include conditions on COURSE# you give Oracle better reason to utilize the index. Table join statements on key indexed columns qualify as the condition statements that influence Oracle to utilize an index.
If you fail to include a condition statement on STUDENTID, or if your comparison is of the negative variety (STUDENTID <> value, STUDENTID not in list, STUDENTID not between value and value, etc.), then Oracle is not able to utilize the index even if you provide conditions on COURSE#.
Before you can explain a SQL statement you must have a table called PLAN_TABLE in which Oracle can store execution plans. To create your own PLAN_TABLE:
sqlplus> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
You only need to execute utlxplan.sql once. After your PLAN_TABLE is created you will simply "explain" your SQL statements and query the PLAN_TABLE to get the Oracle execution plan for that statement.
To "explain" a SQL statement, prefix the statement with the following text:
explain plan set statement_id='some_unique_string' for
Oracle will (attempt to) parse your SQL statement and write the resulting execution plan to your PLAN_TABLE. Query the PLAN_TABLE to see how Oracle expects to execute your statement.
What follows is a SQL statement commonly used for querying the PLAN_TABLE and producing a nicely formatted result:
select decode( id, 0, null, id ) ste,
lpad( ' ', 2*( level-1 ) ) ||
decode( id, 0, null,
'(' || position || ') ' ) ||
operation ||
decode( options, null, null,
'(' || options || ') ' ) ||
object_name ||
' ' ||
decode( id, 0, 'Cost = ' || position ) exe
from plan_table
where statement_id = 'some_unique_string'
start with id = 0
connect by prior id = parent_id;
The way you identify a particular execution plan (PLAN_TABLE can hold many execution plans) is by the STATEMENT_ID. Many people choose to use their Oracle USERID for the value of STATEMENT_ID and overwrite prior execution plans every time they explain a new statement. This is perfectly acceptable.