跟踪用户在执行什么SQL语句，执行的过程
SQL> alter session set sql_trace=true;
会话已更改。
SQL> select * from emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
SQL> alter session set sql_trace=false;
会话已更改。
使用tkprof工具翻译跟踪文件转换trace文件为文本文件
SQL> host tkprof orcl_ora_2316.trc a.txt explain=scott/tiger sys=no;
TKPROF: Release 10.2.0.1.0 - Production on 星期六 4月 9 22:29:45 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
使用文本编辑工具打开查看转换后的文件
TKPROF: Release 10.2.0.1.0 - Production on 星期六 4月 9 22:29:45 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: orcl_ora_2316.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
emp
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=68 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
14 TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)
********************************************************************************
alter session set sql_trace=false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.00 0 8 0 14
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: orcl_ora_2316.trc
Trace file compatibility: 10.01.00
Sort options: default
0 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
17 lines in trace file.
10 elapsed seconds in trace file.
打开*autotrace自动跟踪
SQL> @E:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL> @E:\oracle\product\10.2.0\db_1\rdbms\admin\utlxplan.sql
SQL> grant plustrace to scott;
授权成功。
SQL>
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set autot on
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
使用dbms_monitor工具来跟踪
1)trace session
select sid,serial#,username from v$session
SYS>dbms_monitor.SESSION_TRACE_ENABLE(session_id,serial_num,wait,binds);
SYS>dbms_monitor.SESSION_TRACE_ENABLE(144，38，true,true);
用SCOTT用户作些操作
SYS>dbms_monitor.SESSION_TRACE_disABLE(session_id,serial_num);
2)trace service
$sqlplus scott/tiger@orcl
$sqlplus scott/tiger@orcl
select NAME from dba_aervices;
SYS>exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'orcl');
SCOTT_1>select * from emp where empno=7788;
SCOTT_2>select * from emp where empno=7369;
SYS>exec dbms_monitor.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'orcl');
$trcsess utput=trace.txt service=orcl *
trcsess 把所有关于这个服务的跟踪文件合并在一起
$tkprof trace.txt tk.txt explain=scott/tiger sys=no