2.基于成本的优化器
1.集的势
定义：是指指定集合所包含的记录数，即是结果集的行数，cardinality表示对目标SQL的某个具体执行步骤的执行结果所包含的记录数的估算。如果是针对整个SQL，此时cardinality是指SQL最终结果所包含记录行数
2.可选择率
定义：是指施加指定谓词条件后返回结果集的记录数占未施加任何条件的原始结果集的记录数的比率。
总结：可选择率的值越大，就意味着返回结果集的cardinality的值就越大，所以估算出来的成本值也就会越大
2.1.测试CBO计算MGR的可选择率和该SQL返回结果集的cardinality
scott@ORCLPDB01 2023-04-01 14:56:12> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-01 14:56:13> set autot trace;
scott@ORCLPDB01 2023-04-01 14:56:40> select * from emp where empno = 7902;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMP_MGR | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGR"=7902)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
962 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCLPDB01 2023-04-01 14:57:57> update emp set mgr = 7902;
14 rows updated.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-01 14:58:10> commit;
Commit complete.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-01 14:58:14> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-01 14:59:19> select * from emp where mgr = 7902;
14 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 351129165
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 14 | 532 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_MGR | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGR"=7902)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1713 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
scott@ORCLPDB01 2023-04-01 15:12:49> exec dbms_stats.set_table_stats(ownname=>'SCOTT', tabname=>'EMP', numrows => 10000000, no_invalidate => false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-01 15:12:56> exec dbms_stats.set_index_stats(ownname=>'SCOTT', indname => 'IDX_EMP_MGR', numlblks => 100000, no_invalidate => false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-01 15:12:58> select * from emp where mgr = 7902;
14 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 362M| 118 (98)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 10M| 362M| 118 (98)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR"=7902)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1620 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed总结：
1.CBO选择的执行计划可能会随着目标SQL中涉及的对象的统计信息的变化而变化。
2.cardinality和selectivity的值会直接影响CBO对于执行步骤成本值得估算，进而影响CBO对于目标SQL执行计划得选择。
3.可传递性
3.1.简单谓词传递
t1.c1 = t2.c1 and t1.c1 = 10;
--谓词传递
t1.c1 = t2.c1 and t1.c1 = 10 and t2.c1 = 103.2.连接谓词传递
t1.c1 = t2.c1 and t2.c1 = t3.c1
--谓词传递
t1.c1 = t2.c1 and t2.c1 = t3.c1 and t1.c1 = t3.c13.3.外连接谓词传递
t1.c1 = t2.c1(+) and t1.c1 = 10
--谓词传递
t1.c1 = t2.c1(+) and t1.c1 = 10 and t2.c1(+) = 104.CBO的局限性
4.1.CBO会默认目标SQL语句where条件中出现得各个列之间是独立的，没有关系
4.2.CBO会假设所有的目标SQL都是单独执行的，并且互补干扰
4.3.CBO对直方图统计信息有诸多限制
4.4.CBO在解析夺标关联目标SQL时，可能会漏选正确的执行计划
