- explain plan，autotrace，tkprof，执行计划和静态统计信息的解读
-
-
-
- 1、 执行计划
-
- 执行计划的设定
- conn sys/pwd@gx as sysdba;
CREATE
USER
TOOL
IDENTIFIED
BY
tool
DEFAULT
TABLESPACE EXAMPLE
TEMPORARY
TABLESPACE
TEMP
PROFILE
DEFAULT
ACCOUNT UNLOCK;
GRANT
RESOURCE
TO
TOOL;
GRANT
CONNECT
TO
TOOL;
ALTER
USER
TOOL
DEFAULT
ROLE NONE;
GRANT
CREATE
SESSION
TO
TOOL;
GRANT
CREATE
TABLE
TO
TOOL;
GRANT
UNLIMITED TABLESPACE
TO
TOOL;
CREATE
GLOBAL
TEMPORARY
TABLE
tool.PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30 BYTE),
PLAN_ID NUMBER,
TIMESTAMP
DATE
,
REMARKS VARCHAR2(4000 BYTE),
OPERATION VARCHAR2(30 BYTE),
OPTIONS VARCHAR2(255 BYTE),
OBJECT_NODE VARCHAR2(128 BYTE),
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ALIAS VARCHAR2(65 BYTE),
OBJECT_INSTANCE
INTEGER
,
OBJECT_TYPE VARCHAR2(30 BYTE),
OPTIMIZER VARCHAR2(255 BYTE),
SEARCH_COLUMNS NUMBER,
ID
INTEGER
,
PARENT_ID
INTEGER
,
DEPTH
INTEGER
,
POSITION
INTEGER
,
COST
INTEGER
,
CARDINALITY
INTEGER
,
BYTES
INTEGER
,
OTHER_TAG VARCHAR2(255 BYTE),
PARTITION_START VARCHAR2(255 BYTE),
PARTITION_STOP VARCHAR2(255 BYTE),
PARTITION_ID
INTEGER
,
OTHER LONG,
OTHER_XML CLOB,
DISTRIBUTION VARCHAR2(30 BYTE),
CPU_COST
INTEGER
,
IO_COST
INTEGER
,
TEMP_SPACE
INTEGER
,
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE),
PROJECTION VARCHAR2(4000 BYTE),
TIME
INTEGER
,
QBLOCK_NAME VARCHAR2(30 BYTE)
)
ON
COMMIT
PRESERVE
ROWS
;
grant
all
on
TOOL.PLAN_TABLE
to
public
;
CREATE
PUBLIC
SYNONYM PLAN_TABLE
FOR
TOOL.PLAN_TABLE;
使用方法：
truncate
table
PLAN_TABL;
explain plan
select
*
from
emp;
select
plan_table_output
from
table
(dbms_xplan.display(
'plan_table'
,
null
,
'serial'
));
演示:
conn scott/tiger
SQL> explain plan
for
select
*
from
dept
where
deptno=10;
Explained
SQL>
select
plan_table_output
from
table
(dbms_xplan.display(
'plan_table'
,
null
,
'serial'
));
PLAN_TABLE_OUTPUT
Plan hash value: 3383998547
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
| 0 |
SELECT
STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |
|* 1 |
TABLE
ACCESS
FULL
| DEPT | 1 | 16 | 4 (0)| 00:00:01 |
Predicate Information (identified
by
operation id):
1 - filter(
"DEPTNO"
=10)
13
rows
selected
执行计划解读:
表v$sql_plan
cost概念
cardinality
查询路径—估算树
create
table
e
as
select
*
from
emp
create
table
d
as
select
*
from
dept
Explain plan
for
select
ename,dname
from
d,e
where
e.deptno=d.deptno
select
*
from
table
(dbms_xplan.display());
Plan hash value: 1127375450
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
| 0 |
SELECT
STATEMENT | | 15 | 630 | 7 (15)| 00:00:01 |
|* 1 | HASH
JOIN
| | 15 | 630 | 7 (15)| 00:00:01 |
| 2 |
TABLE
ACCESS
FULL
| D | 4 | 88 | 3 (0)| 00:00:01 |
| 3 |
TABLE
ACCESS
FULL
| E | 15 | 300 | 3 (0)| 00:00:01 |
Predicate Information (identified
by
operation id):
1 - access(
"E"
.
"DEPTNO"
=
"D"
.
"DEPTNO"
)
Note
-
dynamic
sampling used
for
this statement
Explain plan
for
select
ename,dname
from
d, (
select
ename,deptno
from
e
where
rownum<2) e
where
e.deptno=d.deptno
select
*
from
table
(dbms_xplan.display());
Plan hash value: 1791846393
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
| 0 |
SELECT
STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 42 | 5 (0)| 00:00:01 |
| 2 |
VIEW
| | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 |
COUNT
STOPKEY | | | | | |
| 4 |
TABLE
ACCESS
FULL
| E | 15 | 300 | 2 (0)| 00:00:01 |
|* 5 |
TABLE
ACCESS
FULL
| D | 1 | 22 | 3 (0)| 00:00:01 |
Predicate Information (identified
by
operation id):
3 - filter(ROWNUM<2)
5 - filter(
"E"
.
"DEPTNO"
=
"D"
.
"DEPTNO"
)
Note
-
dynamic
sampling used
for
this statement
驱动表概念
估算树
从左到右 从下到上
autotrace
oracle_home\sqlplus\admin\
conn sys/pwd@gx
as
sysdba;
drop
role plustrace;
create
role plustrace;
grant
select
on
v_$sesstat
to
plustrace;
grant
select
on
v_$statname
to
plustrace;
grant
select
on
v_$mystat
to
plustrace;
grant
plustrace
to
dba
with
admin
option
;
grant
plustrace
to
public
;
grant
select
on
v_$sesstat
to
public
;
grant
select
on
v_$statname
to
public
;
grant
select
on
v_$mystat
to
public
;
grant
plustrace
to
dba
with
admin
option
;
grant
plustrace
to
public
;
grant
alter
session
to
public
;
使用命令
set
autotrace
on
set
autotrace
off
set
autotrace
on
explain
set
autotrace
on
statistics
set
autotrace traceonly
autotrace输出内容解释
recursive calls：执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句（如触发器）。
测试举例：
举例1
conn scott/tiger@gx
set
autotrace
on
alter
system flush shared_pool
set
autotrace
on
select
*
from
emp
select
*
from
emp
举例2
create
table
exchage_table
(
bill_code number(10),
exchage_rate number(16,3)
)
BILL_CODE EXCHAGE_RATE
100 4.678
200 5.235
300 5.430
400 2.654
create
or
replace
function
today_exchage(p_code
in
number)
return
number
is
v_exange number(16,3);
begin
select
exchage_rate
into
v_exange
from
exchage_table
where
bill_code=p_code;
return
v_exange;
end
;
create
table
affair
(
trans_id number(10),
bill_code number(10),
balance number(16,2)
)
TRANS_ID BILL_CODE BALANCE
1000 100 1234.00
2000 200 4324.32
3000 300 65464.23
较好的写法为
select
trans_id,
(
select
exchage_rate
from
exchage_table
where
bill_code=affair.bill_code )*balance
from
affair
举例3
drop
table
tppp purge
create
table
tppp(p
integer
)
create
or
replace
trigger
t_trigger
before
insert
on
tppp
for
each row
declare
begin
if :new.p>5
then
raise_application_error(-20001,
'bbbbbbbb'
);
end
if;
end
t_trigger;
统计信息
29 recursive calls
19 db block gets
54 consistent gets
0 physical reads
1172 redo
size
676 bytes sent via SQL*Net
to
client
627 bytes received via SQL*Net
from
client
3 SQL*Net roundtrips
to
/
from
client
1 sorts (memory)
0 sorts (disk)
9
rows
processed
在一次运行
统计信息
29 recursive calls
0 db block gets
117 consistent gets
1 physical reads
0 redo
size
483 bytes sent via SQL*Net
to
client
416 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
9
rows
processed
drop
trigger
t_trigger;
统计信息
0 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo
size
483 bytes sent via SQL*Net
to
client
416 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
9
rows
processed
解决方法为
1、 编写高效的
trigger
2、 用过程代替
trigger
举例4:
自我管理表空间与数据字典表空间
本地管理的表空间能够减少递归sql
输出内容: 逻辑I/O （DB BLOCKS| CONSISTENT GETS）
解释
对于一个SQL 逻辑I/O越小越好，通常通过SQL调整实现的
TKPROF
使用 TKPROF 工具简介
TKPROF 工具简介
TKPROF 工具的使用步骤
TKPROF 工具如何分析 trace 文件
启用TKPROF
如何设置自动跟踪
1、设定执行表，autotrace。方法如前所述，这里再重复一边。
用system登录
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用，则
SQL>
create
public
synonym plan_table
for
plan_table;
SQL>
grant
all
on
plan_table
to
public
;
2、设定tkprof
ALTER
SESSION
SET
SQL_TRACE =
TRUE
ALTER
SESSION
SET
TIMED_STATISTICS =
TRUE
;
alter
session
set
events ‘10046 trace
name
context forever,
level
12’;
alter
session
set
max_dump_file_size=unlimited；
alter
session
set
events
'10046 trace name context off'
获取跟踪文件名称
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
conn system/pwd
SELECT
p1.value||
'\'||p2.value||'
_ora_
'||p.spid||'
.trc' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE
p1.
name
=
'user_dump_dest'
AND
p2.
name
=
'db_name'
AND
p.addr = s.paddr
AND
s.audsid = USERENV (
'SESSIONID'
)
在unix的目录下
http://www.eygle.com/faq/script/gettrcnameunix.sql
有了正确而详细的诊断数据之后，你需要以摘要的形式对其进行查看，这有助于你以最快的速度做出响应。
Cmd tkprof path\xxx.prc xxx.txt
报告解读：
parse(分析)：在共享池中找到该查询（软分析）或者创建该查询的新计划（硬分析）
execute
(执行)：执行查询的所有工作
fetch
(提取)：显示
select
的提取工作，对于
update
，则没有内容
count
(计数):执行的次数
cpu:此阶段cpu的耗时，以毫秒为单位
elapsed(占用时间):挂钟时间，如果大于cpu时间，则有等待时间
disk(磁盘)：执行物理I/O的次数
QUERY(查询):检索一致性执行的I/O次数
CURRENT
(当前)：到当前多执行的逻辑I/O次数
ROW:此阶段被处理或者受到影响的行
如果一个
UPDATE
语句
EXECUTE
的QUERY,
CURRENT
，
ROWS
分别为2000 1000 500,表示这个语句访问了2000个块找到需要
UPDATE
的行记录，在
UPDATE
的时候只访问了1000个块，一共更新了500行。如果只获取很少的数据，而要访问了大量的块，表明SQL与需要优化了。
MISSES 缓存命中率：0 表示已经通过软分析
OPTIMIZER GOAL（优化程序目标）
执行计划：与前面的执行计划相比，增加了各个阶段涉及的行数
关闭
alter
system
set
events
'10046 trace name context off'
;
更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪：
dbms_support.start_trace(waits=>;
true
, binds=>;
true
)
/* code
to
be traced goes here */
dbms_support.stop_trace()
请注意DBMS_SUPPORT 没有文档说明，可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息，请参考MetaLink ( metalink.oracle.com)。
跟踪别人的代码。如果你想跟踪没有读/写权限的代码，则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用，可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数：
dbms_system.set_bool_param_in_session(
sid =>; 42,
serial# =>; 1215,
parnam =>;
'timed_statistics'
,
bval =>;
true
)
dbms_system.set_int_param_in_session(
sid =>; 42,
serial# =>; 1215,
parnam =>;
'max_dump_file_size'
,
intval =>; 2147483647)
（对于Oracle8 8.1.6以前的版本，你可以用
ALTER
SYSTEM命令处理这些参数。）
接下来要激活跟踪。有几种方法可以采用，包括下面两个：
方法一是使用DBMS_SUPPORT：
dbms_support.start_trace_in_session(
sid =>; 42,
serial# =>; 1215,
waits =>;
true
,
binds =>;
true
)
/* code
to
be traced executes during this
time
window */
dbms_support.stop_trace_in_session(
sid =>; 42,
serial =>; 1215)
若想激活扩展SQL跟踪，请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。
第二种方法更为精致，但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题，这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动，而不指定要跟踪的Oracle数据库会话：
dbms_monitor.serv_mod_act_trace_enable(
service_name =>;
'APPS1'
,
module_name =>;
'PAYROLL'
,
action_name =>;
'PYUGEN'
,
waits =>;
true
,
binds =>;
true
,
instance_name =>;
null
)
/* code
to
be traced executes during this
time
window */
dbms_monitor.serv_mod_act_trace_disable(
service_name =>;
'APPS1'
,
module_name =>;
'PAYROLL'
,
action_name =>;
'PYUGEN'
)
利用DBMS_MONITOR包，Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。
在PL/SQL中,由于不能执行
alter
session,可以使用
dbms_session.set_sql_trace(
TRUE
);
必须安装DBMS_SESSION包，并
"直接"
赋给用户
alter
session的权限。
当我们使用sql
For
Unix:
$ sqlplus
"/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production
on
Fri Oct 8 12:08:09 2004
Copyright (c) 1982, 2002, Oracle Corporation.
All
rights reserved.
Connected
to
:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP
and
Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL>
set
echo
on
SQL> @gettrcnameunix
SELECT
d.VALUE
||
'/'
||
LOWER
(RTRIM (i.INSTANCE, CHR (0)))
||
'_ora_'
|| p.spid
||
'.trc'
trace_file_name
FROM
(
SELECT
p.spid
FROM
v$mystat m, v$session s, v$process p
WHERE
m.statistic# = 1
AND
s.SID = m.SID
AND
p.addr = s.paddr) p,
(
SELECT
t.INSTANCE
FROM
v$thread t, v$parameter v
WHERE
v.
NAME
=
'thread'
AND
(v.VALUE = 0
OR
t.thread# = TO_NUMBER (v.VALUE))) i,
(
SELECT
VALUE
FROM
v$parameter
WHERE
NAME
=
'user_dump_dest'
) d
TRACE_FILE_NAME
/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc
For
Nt:
SELECT
d.VALUE
||
'\'
||
LOWER
(RTRIM (i.INSTANCE, CHR (0)))
||
'_ora_'
|| p.spid
||
'.trc'
trace_file_name
FROM
(
SELECT
p.spid
FROM
v$mystat m, v$session s, v$process p
WHERE
m.statistic# = 1
AND
s.SID = m.SID
AND
p.addr = s.paddr) p,
(
SELECT
t.INSTANCE
FROM
v$thread t, v$parameter v
WHERE
v.
NAME
=
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/297293/viewspace-555659/，如需转载，请注明出处，否则将追究法律责任。
127
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
