ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看执行计划

查看执行计划

原创 Linux操作系统 作者:marvinoracle 时间:2011-04-09 23:00:26 0 删除 编辑

跟踪用户在执行什么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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24531354/viewspace-692165/,如需转载,请注明出处,否则将追究法律责任。

下一篇: sqlplus中的set命令
请登录后发表评论 登录
全部评论

注册时间:2010-09-02

  • 博文量
    67
  • 访问量
    181814