本次实验是基于winxp,所有sql脚本存储于...\rdbms\admin\
有几种方式评估sql语句和pl/sql模块的性能
一.STATSPACK
详见: http://blog.chinaunix.net/u/25142/showart.php?id=324955
二.EXPLAIN PLAN
1.执行脚本生成plan_table表
sql>@D:\oracle\ora92\rdbms\admin\utlxplan.sql;
2.创建explain plan(可是指定某个table)
sql>explain plan for select * from dba_tables;
3.生成脚本
sql>@D:\oracle\ora92\rdbms\admin\utlxpls.sql;
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | | |
| 3 | NESTED LOOPS OUTER | | | |
| 4 | NESTED LOOPS OUTER | | | |
| 5 | NESTED LOOPS OUTER | | | |
| 6 | NESTED LOOPS OUTER | | | |
| 7 | NESTED LOOPS | | | |
| 8 | TABLE ACCESS FULL | OBJ$ | | |
|* 9 | TABLE ACCESS CLUSTER | TAB$ | | |
|* 10 | INDEX UNIQUE SCAN | I_OBJ# | | |
| 11 | TABLE ACCESS BY INDEX ROWID| OBJ$ | | |
|* 12 | INDEX UNIQUE SCAN | I_OBJ1 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | |
|* 14 | INDEX UNIQUE SCAN | I_OBJ1 | | |
| 15 | TABLE ACCESS CLUSTER | USER$ | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 16 | INDEX UNIQUE SCAN | I_USER# | | |
| 17 | TABLE ACCESS CLUSTER | SEG$ | | |
|* 18 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | | |
| 19 | TABLE ACCESS CLUSTER | TS$ | | |
|* 20 | INDEX UNIQUE SCAN | I_TS# | | |
| 21 | TABLE ACCESS CLUSTER | USER$ | | |
|* 22 | INDEX UNIQUE SCAN | I_USER# | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(BITAND("T"."PROPERTY",1)=0)
10 - access("O"."OBJ#"="T"."OBJ#")
12 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
14 - access("T"."BOBJ#"="CO"."OBJ#"(+))
16 - access("CX"."OWNER#"="CU"."USER#"(+))
18 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND "T"."BLO
#"="S"."BLOCK#"(+)
20 - access("T"."TS#"="TS"."TS#")
22 - access("O"."OWNER#"="U"."USER#")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: rule based optimization
43 rows selected
--------------------------------------------------------------------------------
三.SQL TRACE and TKPROF
1.sql trace 有基于instance和session两种方式;
alter system set sql_trace=true ; --实例方式
alter session set sql_trace=true; --会话方式
下面这个方式与会话方式能够达到相同效果
dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
生成的用户日志文件位于参数user_dump_dest指定的位置
2.利用tkprof生成标准文件
eg
c:\tkprof D:\oracle\admin\ivandb1\udump\ivandb1_j000_1124.trc d:\output.txt
下面就是output.txt内容:
----------------------------------------------------------------------------------
TKPROF: Release 9.2.0.1.0 - Production on 星期二 6月 19 08:44:44 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: ivandb1_ora_520.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 obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and
:5 is null)and(subname=:6 or subname is null and :6 is null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 3 0.00 0.03 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.04 0 6 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select name
from
v$statname order by statistic#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.24 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.02 0 0 0 248
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.26 0 0 0 248
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
********************************************************************************
select value
from
v$sesstat where sid = :sid order by statistic#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.04 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 496
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.04 0 0 0 496
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.29 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.02 0 0 0 744
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.01 0.31 0 0 0 744
Misses in library cache during parse: 2
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 3 0.00 0.03 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.04 0 6 0 0
Misses in library cache during parse: 1
2 user SQL statements in session.
2 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
Trace file: ivandb1_ora_520.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
2 internal SQL statements in trace file.
4 SQL statements in trace file.
3 unique SQL statements in trace file.
61 lines in trace file.
--------------------------------------------------------------------------------
四.UTLBSTAT.SQL和UTLESTAT.SQL脚本
1.创建统计时的各种表
SQL>@D:\oracle\ora92\rdbms\admin\utlbstat.sql;
2.输出统计
SQL>@D:\oracle\ora92\rdbms\admin\utlestat.sql;
<输出统计略>
