|
|
|
Oracle: Run a SQL execution plan report
Run the
plan9i.sql script and view the distribution of
data access methods in your database. This
script is the Oracle9i equivalent of the
access.sql script in the Burleson book, and it
will interrogate the library cache and produce a
report showing all internal tables access
methods.
In your
database, you might want to “exercise” your
personal database by running numerous
queries against the pubs database.
Your output
should look something like this:
SQL> @plan9i
Owner # of SQL
selects
------------
----------------
SYS
504
AURORA$JIS$U
8
TILITY$
SYSTEM
6
PUBS
4
Sat May 18
page
1
Index range scans
and counts
OWNER TABLE_NAME INDEX_NAME
TBL_BLOCKS NBR_SCANS
--------- --------------------
-------------------- ------------
------------
SYS OBJ$
I_OBJ2 778
873
SYS ACCESS$
I_ACCESS1 478
748
SYS DEPENDENCY$
I_DEPENDENCY1 878
748
SYS IDL_SB4$
I_IDL_SB41 1,103
413
SYS IDL_UB1$
I_IDL_UB11 26,653
391
SYS IDL_CHAR$
I_IDL_CHAR1 653
390
SYS IDL_UB2$
I_IDL_UB21 2,503
390
SYS ARGUMENT$
I_ARGUMENT2 828
90
SYS OBJAUTH$
I_OBJAUTH1 131
36
SYS SYSAUTH$
I_SYSAUTH1 6
21
SYS PROFILE$ I_PROFILE
3 17
11 rows selected.
Sat May
18
page 1
Index unique scans
and counts
OWNER TABLE_NAME INDEX_NAME
NBR_SCANS
--------- --------------------
--------------------
------------
SYS OBJ$
I_OBJ1
857
SYS C_OBJ# I_OBJ#
282
SYS JAVASNM$
I_JAVASNM1
127
SYS PROCEDUREJAVA$
I_PROCEDUREJAVA$
74
SYS C_COBJ# I_COBJ#
72
SYS C_USER#
I_USER#
64
SYS C_FILE#_BLOCK# I_FILE#_BLOCK#
56
SYS SMON_SCN_TO_TIME
SMON_SCN_TO_TIME_IDX
28
SYS SYN$
I_SYN1
17
SYS C_TS#
I_TS#
13
SYS PROCEDURE$
I_PROCEDURE1
13
SYS VIEW$
I_VIEW1
12
SYS USER$
I_USER1 11
Script
source:
Plan9i.sql
|
|
|
|
|
|
