Oracle执行计划 讲解(一)
Oracle执行计划 讲解(一)
看懂Oracle执行计划是优化的第一步,让我们从下面的例子开始吧。
下面为补充内容
1、创建测试表
- SQL> create table t as select 1 id,object_name from dba_objects;
- Table created
- SQL> update t set id=99 where rownum=1;
- 1 row updated
- SQL> commit;
- Commit complete
- SQL> create index t_ind on t(id);
- Index created
oracle优化器:RBO和CBO两种, 从oracle10g开始优化器已经抛弃了RBO,下面的列子说明CBO大概是怎样的
- SQL> select /*+dynamic_sampling(t 0) */* from t where id=1;
- 50819 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1376202287
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 195 | 15405 | 51 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 195 | 15405 | 51 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | T_IND | 78 | | 50 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("ID"=1)
而实际情况如下所示:
- SQL> select * from t where id=1
- 2 ;
- 50819 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1601196873
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 49454 | 3815K| 67 (2)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| T | 49454 | 3815K| 67 (2)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("ID"=1)
我们来收集一下统计信息
- SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
- SQL> select * from t where id=1;
- 50819 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1601196873
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("ID"=1)
如果我们更新了所有的id为99看看。
- SQL> update t set id=99;
- 50820 rows updated
- SQL> select * from t where id=99;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1376202287
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 27 | 2 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("ID"=99)
我们收集一把统计信息。
- SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
- PL/SQL procedure successfully completed
- SQL> select * from t where id=99;
- 50820 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1601196873
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("ID"=99)
上面为补充内容,下面正式开始
|
评论暂时关闭