Oracle學習系列—資料庫優化—效能優化工具

@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語句統計資訊,不輸出結果