看执行计划有两种最常用的方式.
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/