Finally, after all of these
years and releases (1990 Version 6), Oracle Corporation has written
two SQL scripts used to extract and interpret information from the
table PLAN_TABLE after using the EXPLAIN PLAN command. The two
scripts are named utlxpls.sql
and utlx-plp.sql.
On UNIX platforms, the scripts are found in $ORACLE_HOME/rdbms/admin.
After all this time of struggling
with LPAD, CONNECT BY, and START WITH, it is now much easier to use Explain
Plan. However, you must still create the table
plan_table.
SQL> @f:\v8i\rdbms\admin\utlxplan
Table created.
Next, you are ready to run Explain Plan on queries.
SQL> EXPLAIN PLAN FOR
2
SELECT *
3
FROM EMP
4
WHERE SAL = 5000;
Explained.
Now you can use one of the SQL scripts written by Oracle
to extract meaningful information from the table
plan_table. The following example
uses utlx-pls.sql. Oracle would
use a full table scan to retrieve all employees who earn $5000 per month. On
a large table with only a few employees earning $5000 per month, you would
want to create an index on the SAL column of the EMP table, and use the
index to improve performance.
SQL> @f:\v8i\rdbms\admin\utlxpls
Plan Table
-------------------------------------------------------------
|Operation
|Name|Rows|Bytes|Cost|Pstart|Pstop|
-------------------------------------------------------------
|SELECT STATEMENT
| |
| |
|
| |
| TABLE ACCESS FULL |EMP |
| |
|
| |
-------------------------------------------------------------
The example in Exhibit 9
shows the result of an Explain Plan on a Top N query. Notice the "STOPKEY"
in the OPERATION column of the table
plan_table. Oracle's new script is robust and easy to read. Thanks
Oracle!
SQL> SET
VERIFY OFF
SQL> EXPLAIN PLAN
FOR
2
SELECT ENAME,
SAL
3
FROM (SELECT
ENAME, SAL
4
FROM EMP
5
ORDER BY SAL DESC)
6* WHERE
ROWNUM< &TOP;
Enter value for top: 3
Explained.
SQL> @C:\ORACLE8I\RDBMS\ADMIN\UTLXPLS
Plan Table
---------------------------------------------------------------
|Operation
|Name|Rows|Bytes|Cost|Pstart|Pstop|
---------------------------------------------------------------
|SELECT STATEMENT
| |
| |
|
| |
|
COUNT STOPKEY
| |
| |
|
| |
|
VIEW
| |
| |
|
| |
|
SORT ORDER BY STOPKEY|
| |
| |
| |
|
TABLE ACCESS FULL | EMP|
| |
|
| |
---------------------------------------------------------------
8 rows selected.
Exhibit 9.Result
of an Explain Plan on a Top N Query
The
Old Way
Previous to Oracle8i, you had to write a query similar to
the following. For someone new to the Oracle world, it was always a
traumatic experience trying to determine the functionality of LPAD, LEVEL,
CONNECT BY, START WITH, and recursion.
SQL> get old_explain
1
COL
"Query Plan" FORMATA40
2
SELECT
LPAD(' ',2*LEVEL-1) || OPERATION || ' ' ||
3
OPTIONS || '
' || OBJECT_NAME || ' ' ||
4
OPTIMIZER "Query Plan",
COST
5
FROM
PLAN_TABLE
6
START
WITH ID= 0
7* CONNECT
BY PRIOR ID= PARENT_ID
SQL> @old_explain
Query Plan
COST
----------------------------------------- -----
SELECT STATEMENT CHOOSE
3
COUNT
STOPKEY
VIEW
3
SORT ORDER BY STOPKEY
3
TABLE ACCESS FULL EMP ANALYZED
1
Exporting Selected
Rows from a Table
Thanks to the new export parameter,
query, you can now export
particular rows from a table. The example in Exhibit 10
shows an
export of all employees in department number 10 from the EMP table.
C:\>exp help=yes
Keyword
Description (Default)
Keyword
Description (Default)
----------- -------------------------- ------------
-------------------------
USERID
username/password
FULL
export entire file (N)
BUFFER
size of data buffer
OWNER
list of owner usernames
FILE
output files (EXPDAT.DMP)
TABLES
list of table names
COMPRESS
import into one extent (Y) RECORDLENGTH length of IO record
GRANTS
export grants (Y)
INCTYPE
incremental export type
INDEXES
export indexes (Y)
RECORD
track incr. export (Y)
ROWSexport
data rows (Y)
PARFILE
parameter filename
CONSTRAINTS export constraints (Y)
CONSISTENT
cross-table consistency
LOG
log file of screen output
STATISTICS analyze
objects (ESTIMATE)
DIRECT
direct path (N)
TRIGGERS
export triggers (Y)
FEEDBACK
display progress every x rows (0)
FILESIZE
maximum size of each dump file
QUERY
select clause used to export a subset of a table
The following keywords only apply to transportable
tablespaces
TRANSPORT_TABLESPACE export transportable tablespace
metadata (N)
TABLESPACES list of tablespaces to transport
Export terminated successfully without warnings.
C:\>exp query='where deptno=10' file=emp10.dmp tables=emp
userid=system/manager
Export: Release 8.1.5.0.0—Production on Sun Jan 23 09:55:22 2000
(c) Copyright 1999 Oracle Corporation. All rights
reserved.
Connected to: Oracle8i Enterprise Edition Release
8.1.5.0.0—Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0—Production
Export done in WE8ISO8859P1 character set and
WE8ISO8859P1 NCHAR character
Set
About to export specified tables via Conventional Path
...
. . exporting tableEMP3 rows exported
Export terminated successfully without warnings.
Exhibit 10.All
Employees in Department Number 10 from the EMP Table
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
