参考案例一:
SQL> set autotrace traceonly
SQL> create table t(col varchar2(20),col2 number);
表已创建。
SQL>
SQL> insert into t select rownum,rownum+1 from dual connect by level<=1000000;
已创建1000000行。
执行计划
----------------------------------------------------------
Plan hash value: 1731520519
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | COUNT | | | | |
| 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
892 recursive calls
24493 db block gets
4477 consistent gets
0 physical reads
22946168 redo size
667 bytes sent via SQL*Net to client
610 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL> create index idx_t on t(col);
索引已创建。
SQL> select * from t where col=2 and col2=3;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 103 | 2575 | 553 (8)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 103 | 2575 | 553 (8)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
2406 consistent gets
0 physical reads
0 redo size
459 bytes sent via SQL*Net to client
384 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>
SQL> select * from t where col='2' and col2=3;
执行计划
----------------------------------------------------------
Plan hash value: 1594971208
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 103 | 2575 | 8 (0)| 00:00
:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 103 | 2575 | 8 (0)| 00:00
:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 26 | | 3 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=3)
2 - access("COL"='2')
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
112 recursive calls
0 db block gets
92 consistent gets
2 physical reads
0 redo size
459 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
分析:
执行sql :select * from t where col=2 and col2=3; 未走索引,逻辑读2406,获取一行,2406/1=2406 ,不符合获取单行记录小于5个逻辑读的标准。由此可见,该sql 还存在很大的调优空间。从谓词部分的执行计划可以看出使用了to_number类型转换。
predicate information 信息中有两种取值,分别是filter和access,一般而言索引读和hash连接的时候,体现为access。此时我们看到的filter。
通过修改查询sql ,重新查看执行计划发现predicate information部分发生了变化,逻辑读和获取行数的比率也发生了变化。
参考案例二:
SQL> set autotrace traceonly;
SQL> select * from t where col=2 and col2=3;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 103 | 2575 | 553 (8)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 103 | 2575 | 553 (8)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
139 recursive calls
0 db block gets
2425 consistent gets
3 physical reads
0 redo size
459 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> analyze table t compute statistics for table for all indexes for all indexed colu
表已分析。
SQL> select * from t where col=2 and col2=3;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 552 (8)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 1 | 15 | 552 (8)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
32 recursive calls
0 db block gets
2337 consistent gets
0 physical reads
0 redo size
459 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析:
在执行计划中看到“ - dynamic sampling used for this statement” 说明进行了动态采样。但我们提前收集表的信息是,可以看到能够减少一些额外的开销。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24349972/viewspace-759076/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24349972/viewspace-759076/