执行计划

1. 预执行 explain plan for

实际并没有执行SQL语句。

SQL> explain plan for select ca_cstid from c_acc;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3827578295

-----------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |      7 |      1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | DEPT_ACC |      1 |      7 |      1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

8 rows selected.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3827578295

-----------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |      7 |      1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | DEPT_ACC |      1 |      7 |      1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

8 rows selected.

 

SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3827578295

-----------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |      7 |      1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | DEPT_ACC |      1 |      7 |      1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

8 rows selected.

 

 

2. 即时执行计划

在执行SQL的同时查看执行计划。

SQL> set autotrace traceonly;    # 运行查询但不显示查询结果;显示执行计划与统计信息
SQL> select ca_cstid from c_acc;


Execution Plan
----------------------------------------------------------
Plan hash value: 3827578295

-----------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |      7 |      1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | DEPT_ACC |      1 |      7 |      1   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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

SQL>

SQL> set autotrace traceonly explain    #不运行查询(DML操作会运行);显示执行计划,不显示统计信息。
SQL> select ca_cstid from c_acc;

Execution Plan
----------------------------------------------------------
Plan hash value: 3827578295

-----------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |      7 |      1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | DEPT_ACC |      1 |      7 |      1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL>

 

3. 查看指定SQL执行计划

查看已经执行过的SQL的执行计划

查看v$sql_plan中最后一个会话SQL的执行计划

SQL> set linesize 160
SQL> select plan_table_output from table(dbms_xplan.display_cursor());

Execution Plan
----------------------------------------------------------
Plan hash value: 3713220770

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

通过SQL_ID查询其执行计划

SQL> set autotrace traceonly

SQL> select mb_id,mb_tname from mng_bak;

2294 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1749878440

-----------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  2294 | 59644 |     12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MNG_BAK |  2294 | 59644 |     12   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    188  consistent gets
      0  physical reads
      0  redo size
      90151  bytes sent via SQL*Net to client
       2191  bytes received via SQL*Net from client
    154  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
       2294  rows processed

SQL>

SQL> set autotrace off
SQL> select sql_id,child_number from v$sql where sql_text like 'select mb_id,mb_tname from mng_bak%';

SQL_ID          CHILD_NUMBER
------------- ------------
bk8rtgsb6jm37         0

SQL>

SQL> set linesize 80
SQL> select plan_table_output from table(dbms_xplan.display_cursor('bk8rtgsb6jm37',0,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    bk8rtgsb6jm37, child number 0
-------------------------------------
select mb_id,mb_tname from mng_bak

Plan hash value: 1749878440

-----------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |        |        |     12 (100)|        |
|   1 |  TABLE ACCESS FULL| MNG_BAK |  2294 | 59644 |     12   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------


13 rows selected.

 

4.  历史执行计划 dba_hist_sql_plan

dba_hist_sql_plan 与 dba_hist_sqltext 通过SQL_ID关联。

SQL> desc dba_hist_sqltext
Name                       Null?    Type
----------------------------------------- -------- ----------------------------
DBID                       NOT NULL NUMBER
SQL_ID                    NOT NULL VARCHAR2(13)
SQL_TEXT                        CLOB
COMMAND_TYPE                        NUMBER

 

SQL> select p.* from dba_hist_sqltext t,table(DBMS_XPLAN.DISPLAY_AWR(t.sql_id,null,null,'TYPICAL')) p where t.sql_text like 'select mb_id,mb_tname from mng_bak%';

no rows selected

posted @ 2016-02-04 22:50  方诚  阅读(224)  评论(0编辑  收藏  举报