oracle tkprof

weixin_33827590

于 2018-07-16 17:52:00 发布

阅读量105

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1862478/blog/1857466

版权

2019独角兽企业重金招聘Python工程师标准>>>

1、设置TIMED_STATISTICS为True，可以在会话级别，也可以在实例级别

system@ORCL>alter system set timed_statistics=true scope=both;

系统已更改。

2、启用SQL_TRACE

system@ORCL>alter session set sql_trace = true;

会话已更改。

3、执行SQL：

system@ORCL>select count(Y) from
  2  (select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized) ;

  COUNT(Y)
----------
    100000

4、查询此会话产生的TRACE文件

system@ORCL>show parameter user_dump_dest;

NAME             TYPE        VALUE
---------------  ------      ------------------------------
user_dump_dest   string      d:\app\administrator\diag\rdbms\orcl\orcl\trace


system@ORCL> select username,sid,serial# from v$session where username='SYSTEM';

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYSTEM                                 73       6371


system@ORCL>select 'orcl_ora_'||spid||'.trc' from v$process where addr = (select
 paddr from v$session where sid=73);

'ORCL_ORA_'||SPID||'.TRC'
-------------------------------------
orcl_ora_9108.trc

5、退出sqlplus

system@ORCL>alter session set sql_trace = false;

会话已更改。

system@ORCL>exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

d:\app\Administrator\diag\rdbms\orcl\orcl\trace>

6、在trace生成目录下，执行TKPROF格式化语句：

d:\app\Administrator\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_9108.trc d:\test_0715.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期一 7月 16 17:43:11 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


d:\app\Administrator\diag\rdbms\orcl\orcl\trace>

转载于:https://my.oschina.net/u/1862478/blog/1857466