使用dbms_xplan包查看执行计划

SQL> set timing on
SQL>  select count(*) from scc.scc_wip_issue_pr_line;

  COUNT(*)
----------
  13157661

Elapsed: 00:00:13.84
SQL> select sql_id from v$sql where sql_text='select count(*) from scc.scc_wip_issue_pr_line';

SQL_ID
-------------
7ma0duc32q8yn

Elapsed: 00:00:03.22
SQL>  select * from table(dbms_xplan.display_cursor('7ma0duc32q8yn'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7ma0duc32q8yn, child number 0
-------------------------------------
select count(*) from scc.scc_wip_issue_pr_line

Plan hash value: 1465348541

--------------------------------------------------------------------------------
----

| Id  | Operation          | Name                  | Rows  | Cost (%CPU)| Time
   |

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

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT   |                       |       |   101K(100)|
   |

|   1 |  SORT AGGREGATE    |                       |     1 |            |
   |

|   2 |   TABLE ACCESS FULL| SCC_WIP_ISSUE_PR_LINE |    12M|   101K  (2)| 00:20:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
15 |

--------------------------------------------------------------------------------
----

 

14 rows selected.

Elapsed: 00:00:00.63
SQL> select * from table(dbms_xplan.display_cursor('7ma0duc32q8yn',null,'all iostats last'));     

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7ma0duc32q8yn, child number 0
-------------------------------------
select count(*) from scc.scc_wip_issue_pr_line

Plan hash value: 1465348541

--------------------------------------------------------------------------------
-----

| Id  | Operation          | Name                  | E-Rows | Cost (%CPU)| E-Tim
e   |

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

--------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT   |                       |        |   101K(100)|
    |

|   1 |  SORT AGGREGATE    |                       |      1 |            |
    |

|   2 |   TABLE ACCESS FULL| SCC_WIP_ISSUE_PR_LINE |     12M|   101K  (2)| 00:20

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
:15 |

--------------------------------------------------------------------------------
-----

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / SCC_WIP_ISSUE_PR_LINE@SEL$1

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

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - Warning: basic plan statistics not available. These are only collected when
:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l

 

31 rows selected.

Elapsed: 00:00:00.10

 

--未格式化输出，可读性比较差