Page 527
If you don't analyze tables and indexes, Oracle will use the rule-based optimizer to determine the best execution plan for each query. You can use the EXPLAIN PLAN statement to obtain the execution plan for a query.
The syntax for EXPLAIN PLAN is as follows:
EXPLAIN PLAN FOR sql-statement
The variable is defined as follows:
sql-statement is the SQL statement for which an execution plan is to be generated.
Before you use the EXPLAIN PLAN statement, you'll need to run a script from SQL*Plus that creates the PLAN_TABLE in your Oracle account. This is displayed in Listing 19.4.
Listing 19.4. Creating the PLAN_TABLE table.
SQL> @c:\orawin95\rdbms73\admin\utlxplan.sql Table created. SQL> desc plan_table Name Null? Type ------------------------------ --------- ---- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER(38) ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) OTHER LONG
After the PLAN_TABLE has been created, you can begin using the EXPLAIN PLAN statement. Whenever the EXPLAIN PLAN statement is executed, Oracle inserts rows into the PLAN_TABLE; as a result, you need to delete the contents of PLAN_TABLE before each use of EXPLAIN PLAN. For example, suppose you create a table that records the day of the year and the maximum temperature in degrees Fahrenheit for each day. As a developer, you want to determine the efficiency of a query's execution plan. Listing 19.5 contains the steps that you would follow to determine a statement's execution plan.
Page 528
Listing 19.5. Using the EXPLAIN PLAN statement.
SQL> delete from plan_table; 0 rows deleted. SQL> explain plan for 2 select day_number, temp_deg_f 3 from day_temp 4 where day_number = 100; Explained. SQL> select operation, options, object_name, id, parent_id, position 2 from plan_table 3 order by id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION ------------------- --------------- ------------ --------- --------- -------- SELECT STATEMENT 0 TABLE ACCESS FULL DAY_TEMP 1 0 1
EXPLAIN_PLAN is looking for the word FULL that is displayed in the OPTIONS column in conjunction with the TABLE ACCESS operation. FULL signifies that the query performs a full table scan to retrieve the data. If a query involving Day_Number is a fairly common operation, you should consider adding an index on the Day_Number column. Listing 19.6 shows how to create the index and rerun EXPLAIN_PLAN.
Listing 19.6. Determining the execution plan after creating an index.
SQL> create index day_temp_day_number_ck 2 on day_temp (day_number); Index created. SQL> delete from plan_table; 2 rows deleted. SQL> explain plan for 2 select day_number, temp_deg_f 3 from day_temp 4 where day_number = 100; Explained. SQL> select operation, options, object_name, id, parent_id, position 2 from plan_table 3 order by id;
Page 529
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION ------------------ ----------- ------------------ ----- --------- -------- SELECT STATEMENT 0 TABLE ACCESS BY ROWID DAY_TEMP 1 0 1 INDEX RANGE SCAN DAY_TEMP_DAY_NUMBER_CK 2 1 1
As you can see, by creating the index, you've changed the optimizer's execution plan for the query. Instead of performing a full table scan, the optimizer performs an index range scan, which is almost always a more efficient operation (although not for a table with a small number of rows). Even though the EXPLAIN PLAN statement supplies useful information about the methods used by the optimizer, it doesn't provide any hard performance numbers. To retrieve performance data, you should use the tkprof utility.
Using tkprof to Analyze a Statement
If you're using Personal Oracle, you'll find an executable file
c:\orawin95\bin\tkprof73.exe (the directory and filename will depend on the Oracle version that you're using). If
you're using an Oracle server, a version of tkprof can be found in the
bin directory beneath the Oracle home directory.
tkprof's command line arguments are the same for any version
of Oracle7 or higher. tkprof processes Oracle trace files to produce a text file that describes
the SQL activity that occurred during a particular Oracle session. A trace file is extremely
useful for performance analysis and tuning for these reasons:
By default, an Oracle database won't produce trace files. To produce a trace file with performance data, you'll need to have your DBA set an initialization parameter named TIMED_STATISTICS to TRUE and restart the Oracle instance. You must stop the database and restart it for this parameter to take effect.
If you wanted to analyze the performance of a group of SQL statements processed by SQL*Plus, you can enable a trace file for the Oracle session in the following way:
SQL> alter session set sql_trace true; Session altered.
You may then process any SQL statements that you were interested in analyzing. If you're using Personal Oracle, you'll find the trace files in c:\orawin95\rdbms73\trace. If you're using an Oracle server, the trace files can be found in the directory named rdbms73/trace and beneath the Oracle home directory. You should end the Oracle session before you try to analyze the trace file.
Page 530
Next, open an MS-DOS window. If you type tkprof73 at the prompt, the program displays a list of its command-line arguments. At a minimum, you should specify the name of the trace file, the name of the output file, and the Oracle user and password whose PLAN_TABLE will be used to determine the statement execution plan.
By default, the output file will have an extension of .prf. You may then use an editor to display and print the contents of the output file. The summary portion of a sample tkprof output file is shown in the following code segment.
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 1.59 2 0 4 0
Execute 8 0.00 0.49 2 13 38 10
Fetch 5 0.00 0.04 2 3 9 35
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 2.12 6 16 51 45
Misses in library cache during parse: 4
Misses in library cache during execute: 1
8 user SQL statements in session.
26 internal SQL statements in session.
34 SQL statements in session.
4 statements EXPLAINed in this session.
********************************************************************************
Trace file: c:\orawin95\rdbms73\trace\ora63171.trc
Trace file compatibilty: 7.02.01
Sort options: execpu
1 session in tracefile.
8 user SQL statements in trace file.
26 internal SQL statements in trace file.
34 SQL statements in trace file.
18 unique SQL statements in trace file.
4 SQL statements EXPLAINed using schema:
FLUGLE.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
382 lines in trace file.
As you can see, the trace file provides a wealth of statistical information about the SQL statements that were processed during the Oracle session. This tool is particularly powerful in helping you solve the so-called 80-20 problem: identifying and improving the 20 percent of the SQL statements that represent 80 percent of the work.
A significant portion of database activity consists of SELECT statements. Accordingly, improving query performance results in better overall application performance. A query generally is processed more quickly if it uses an index to access the qualified rows. A full table scan is a query in which all of a table's rows are read to find the qualified rows. To determine