Oracle执行计划讲解51页文档资料.docx
- 文档编号:527414
- 上传时间:2022-10-10
- 格式:DOCX
- 页数:22
- 大小:25.37KB
Oracle执行计划讲解51页文档资料.docx
《Oracle执行计划讲解51页文档资料.docx》由会员分享,可在线阅读,更多相关《Oracle执行计划讲解51页文档资料.docx(22页珍藏版)》请在冰豆网上搜索。
Oracle执行计划讲解51页文档资料
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)
现象t表还没有被分析,提示/*+dynamic_sampling(t0)*/*的目的是让CBO无法通过动态采样获取表中的实际数据情况,此时CBO只能根据T表中非常有限的信息(比如表中的extents数量,数据块的数量)来猜测表中的数据。
从结果中可以看到CBO猜出表中id=1的有195条,这个数值对于表的总数来说,是一个非常小的值,所以CBO选择了索引而不是全表扫描。
而实际情况如下所示:
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)
通过动态取样,CBO估算出行数为49454,非常接近于真实50820数目。
选择了全表扫描。
我们来收集一下统计信息
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)
现在扫描过的行数为50815。
如果我们更新了所有的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)
因为没有对表进行分析,所以表中的分析数据还是之前的信息,CBO并不知道。
我们可以看出Rows值为1,也就是说CBO人为表T中的ID=99的值只有1条,所有选择仍然是索引。
我们收集一把统计信息。
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)
上面为补充内容,下面正式开始
1、sql的执行计划
创建测试表
SQL> create table t1(id int,name varchar2(1000));
Table created
SQL> create table t2(id int,name varchar2(1000));
Table created
SQL> create index ind_t1 on t1(id);
Index created
SQL> create index ind_t2 on t2(id);
Index created
SQL> create index ind_t2_name on t2(name);
Index created
SQL> insert into t1 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a;
50206 rows inserted
SQL> insert into t2 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a where rownum<=20;
20 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade => true);
PL/SQL procedure successfully completed
2、产生执行计划
SQL> select * from t1,t2 where t1.id= t2.id;
20 rows selected.
Execution Plan
Plan hash value:
828990364
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 20 | 780 | 43 (0)| 00:
00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 28 | 2 (0)| 00:
00:
01 |
| 2 | NESTED LOOPS | | 20 | 780 | 43 (0)| 00:
00:
01 |
| 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:
00:
01 |
|* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:
00:
01 |
Predicate Information (identified by operation id)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 执行 计划 讲解 51 文档 资料