获取执行计划的方式

最新推荐文章于 2024-08-19 03:44:14 发布

转载最新推荐文章于 2024-08-19 03:44:14 发布 · 146 阅读

本文介绍了五种在Oracle数据库中获取SQL执行计划的方法：autotrace方式、explain plan命令、AWR方式、cursor方式及通过跟踪客户端产生的.trc文件并格式化分析。每种方法各有优劣，适用于不同场景。

前些日子，对执行计划的获取方式大概的总结了下，常用的有以下几种方式。各有所长、投其所好。记录下来，以便查阅。

1、autotrace方式。

SQL> set autotrace on
SQL> select user_name,user_code from sm_user;
USER_NAME            USER_CODE
-------------------- -------------------------
???                  11109
???                  11111
????????             23003
.                     .
.                     .
.                     .
203 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 897523934

-----------------------------------------------------------------------------
| Id | Operation                        | Name        | Rows | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |   203 | 2639 |     8   (0)          | 00:00:01|
|   1 | TABLE ACCESS FULL| SM_USER |   203   | 2639 |     8   (0)          | 00:00:01|
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        251 recursive calls
          0 db block gets
        105 consistent gets
          0 physical reads
          0 redo size
       5938 bytes sent via SQL*Net to client
        612 bytes received via SQL*Net from client
         15 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
        203 rows processed

这种获取执行计划方式的好处是能对结果一目了然。然后这也是其弊端，使得执行速度较慢，尤其在对大表产生时。

倘若你不想显示语句执行结果，可以用如下语句：

SQL> set autotrace traceonly。

产生之后，需对autotrace进行关闭。

SQL> set autotrace off。

2、explain plan命令

SQL> explain plan set statement_id='test' for
2 select user_name,user_code
3 from SM_USER;
Explained.
SQL> select * from table(dbms_xplan.display);

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id | Operation                         | Name    | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         | 8168 | 16336 |    24   (0)         | 00:00:01 |
|   1 | COLLECTION ITERATOR

           PICKLER FETCH| DISPLAY |        |             |            |                   |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         13 recursive calls
         12 db block gets
         24 consistent gets
          0 physical reads
          0 redo size
       1100 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

使用这种方法，只会产生执行计划，不会显示语句执行结果。

3、AWR方式

使用AWR方式需的先对数据库进行awr报告搜集，然后相关的“statement_id”进行分析。

select plan_table_output from table(dbms_xplan.display_awr('statement_id'));

这种分析方式比较理想，可以对过去某一时刻的SQL执行计划进行分析，而autotrace和explain都需对SQL语句清楚。这里的“statement_id”需在AWR报告中取。跟explain方式中

的不一样。

4、cursor方式

查询活动会话ID：

SQL> select username,sid,sql_id,sql_child_number
2 from v$session
3 where sql_id is not null；

输出：

SQL> select plan_table_output
2 from table(dbms_xplan.display_cursor('sql_id',0,'ALL'));

使用此种方式的优点是能对抓取SQL语句以及逐条解析执行计划。缺点是需及时抓取“sql_id”。一旦会话结束，很可能会出现产生不了执行计划的现象。

5、跟踪客户端发出的sql语句产生.trc文件，进而格式化该文件，分析执行计划。

此种方式比较麻烦。而且实时性要求也比较高，优点是信息较全，是监测一段时间的该session所产生的sql。缺点是没有对cost给个明确的答案。

1)、识别要跟踪的客户端程序到数据库的数据库连接

SQL> select se.sid,se.SERIAL#,se.machine,se.program,p.spid,s.sql_text,se.username
2 from v$session se,v$process p,v$sqlarea s
3 where se.paddr=p.addr and se.sql_address=s.address;

2)、根据产生的sid与SERIRAL#设置相应的参数；

SQL> exec sys.dbms_system.set_bool_param_in_session( -
> sid =>A,-
> serial# =>B,-
> parnam => 'timed_statistics', -
> bval =>true);

PL/SQL procedure successfully completed.

SQL>

SQL> exec sys.dbms_system.set_int_param_in_session( -
> sid =>A,-
> serial# =>B,-
> parnam => 'max_dump_file_size', -
> intval => 2147483647);

PL/SQL procedure successfully completed.

SQL>

3)、启动跟踪功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(A,B,true);

PL/SQL procedure successfully completed.

SQL>

4)、系统运行一段时间。以便可以搜集数据。

5)、关闭跟踪功能。

SQL> exec sys.dbms_system.set_sql_trace_in_session(A,B,false);

PL/SQL procedure successfully completed.

SQL>

6)、格式化跟踪数据。注意，格式化前先查看下跟踪文件的目录。

SQL> show parameter user_dump_file

[oracle@NCTEST udump]$ tkprof TEST_ora_20156.trc test20090518.txt sys=no explain=username/password;

TKPROF: Release 10.2.0.1.0 - Production on Mon May 18 17:59:42 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

[oracle@NCTEST udump]$

7)、查看跟踪文件

此种方法比较繁琐，但是信息较全，dba可以值得参考。

6、toad

其实上，toad是一个对数据库管理相当理想的工具，很多都转为可视化操作，非常人性化。有条件的DBA还是建议使用这个第三方工具。

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

转载于:http://blog.itpub.net/17253074/viewspace-600384/