不胜人生一场醉

@C:UserDefineoracleora92rdbmsadminutlxplan.sql;

SQL> explain plan

2 set statement_id='test' for

3 select * from testindex where object_type='JAVA CLASS';

Explained

SQL> @C:UserDefineoracleora92rdbmsadminutlxpls.sql;

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 895 | 76075 | 27

| 1 | TABLE ACCESS BY INDEX ROWID| TESTINDEX | 895 | 76075 | 27

|* 2 | INDEX RANGE SCAN | OBJECTTYPEINDEX | 895 | | 2

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("TESTINDEX"."OBJECT_TYPE"='JAVA CLASS')

13 rows selected

SELECT lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM plan_table
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'test'
ORDER BY id;

Plan

--------------------------------------------------------------------------------

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID TESTINDEX

INDEX RANGE SCAN OBJECTTYPEINDEX

SQL> show parameters TIMED_STATISTICS;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_statistics boolean TRUE

SQL> show parameters MAX_DUMP_FILE_SIZE;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size string UNLIMITED

SQL> show parameters USER_DUMP_DEST;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest string C:UserDefineoracleadminwbqudump

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered

SQL> select owner,object_name from testindex where object_type='CONTEXT';

OWNER OBJECT_NAME

------------------------------ --------------------------------------------------------------------------------

SYS LT_CTX

SYS WK$CONTEXT

C:>CD C:UserDefineoracleadminwbqudump

C:UserDefineoracleadminwbqudump>TKPROF wbq_ora_4868.trc output.txt

TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

C:UserDefineoracleadminwbqudump>more |output.txt

TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: wbq_ora_4868.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

select *

from

testindex where object_type='CONTEXT'

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 8 0 4

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 6 0.00 0.00 0 8 0 4

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 61

Rows Row Source Operation

------- ---------------------------------------------------

2 TABLE ACCESS BY INDEX ROWID TESTINDEX

2 INDEX RANGE SCAN OBJECTTYPEINDEX (object id 30527)

Autotrace Setting

结果

SET AUTOTRACE OFF

没有自动跟踪报告(系统缺省)

SET AUTOTRACE ON EXPLAIN

输出执行结果和优化器执行计划

SET AUTOTRACE ON STATISTICS

输出执行结果和SQL语句统计信息

SET AUTOTRACE ON

输出执行结果,优化器执行计划和SQL语句统计信息

SET AUTOTRACE TRACEONLY

仅仅输出优化器执行计划和SQL语句统计信息,不输出结果