看执行计划的两种方式

看执行计划有两种最常用的方式.

1.explain plan

SQL>create table qgw_test (x char(1) constraint test_pk primary key,y date);

SQL>insert into qgw_test values('1',sysdate);

SQL>delete from plan_table;

SQL>explain plan for select * from qgw_test where x=1;

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 246687246

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

Predicate Information (identified by operation id):
---------------------------------------------------

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

   1 - filter(TO_NUMBER("X")=1)

Note
-----
   - dynamic sampling used for this statement

注:这里不走索引的原因是在索引x上使用了函数.

2.set autotrace on(显示执行计划和查询结果)

set autotrace traceonly(显示执行计划和统计信息)

set autotrace traceonly explain(只显示执行计划)

set autotrace traceonly statistics(只显示统计信息)

SQL> set autotrace traceonly explain;
SQL> select * from qgw_test1 where x=1;

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'QGW_TEST1' (TABLE) (Cost=2 Card=1
          Bytes=12)

 

SQL> set autotrace traceonly statistics;
SQL> select * from qgw_test1 where x=1;

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace on;
SQL> select * from qgw_test1 where x=1;

X  Y
-- --------------
1  13-9月 -08

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'QGW_TEST1' (TABLE) (Cost=2 Card=1
          Bytes=12)

 

 

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly;
SQL> select * from qgw_test1 where x=1;

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'QGW_TEST1' (TABLE) (Cost=2 Card=1
          Bytes=12)

 

 

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

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

转载于:http://blog.itpub.net/13387766/viewspace-446963/