1. 创建测试数据
create table lixia.t1 as select * from dba_objects;
delete from lixia.t1 where object_id is null;
alter table lixia.t add constraint pk_t1 primary key(object_id);
exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');
create table lixia.t2 as select * from dba_objects;
delete from lixia.t2 where object_id is null;
alter table lixia.t2 add constraint pk_t2 primary key(object_id);
exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');
create table lixia.t3 as select * from dba_objects;
delete from lixia.t3 where object_id is null;
alter table lixia.t3 add constraint pk_t3 primary key(object_id);
create index lixia.idx_t3_1 on lixia.t3(owner);
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');
2. 数据分布情况
SQL> select owner,count(1) from dba_objects group by owner order by count(1) desc;
OWNER COUNT(1)
------------------------------ ----------
SYS 38008
PUBLIC 34283
SYSMAN 3554
APEX_030200 2561
ORDSYS 2513
MDSYS 2009
XDB 1168
OLAPSYS 721
SYSTEM 637
CTXSYS 389
WMSYS 332
OWNER COUNT(1)
------------------------------ ----------
EXFSYS 312
ORDDATA 257
QUEST 230
PERFSTAT 148
DBSNMP 65
GGS 54
LIXIA 44
FLOWS_FILES 13
OWBSYS_AUDIT 12
OUTLN 10
ORDPLUGINS 10
OWNER COUNT(1)
------------------------------ ----------
ORACLE_OCM 8
SI_INFORMTN_SCHEMA 8
SCOTT 6
APPQOSSYS 5
OWBSYS 2
TEST 1
28 rows selected.
3. 使用提示强制使用错误的表连接顺序
select /*+ leading(t2,t1,t3) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.owner='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 2663886062
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3119 | 179K| | 1058 (1)| 00:00:13 |
|* 1 | HASH JOIN | | 3119 | 179K| | 1058 (1)| 00:00:13 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T3 | 3119 | 56142 | | 92 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_T3_1 | 3120 | | | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 87332 | 3496K| 1968K| 966 (1)| 00:00:12 |
|* 5 | TABLE ACCESS FULL | T2 | 87333 | 938K| | 348 (1)| 00:00:05 |
|* 6 | TABLE ACCESS FULL | T1 | 87335 | 2558K| | 348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 2663886062
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3119 | 179K| | 1058 (1)| 00:00:13 |
|* 1 | HASH JOIN | | 3119 | 179K| | 1058 (1)| 00:00:13 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T3 | 3119 | 56142 | | 92 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_T3_1 | 3120 | | | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 87332 | 3496K| 1968K| 966 (1)| 00:00:12 |
|* 5 | TABLE ACCESS FULL | T2 | 87333 | 938K| | 348 (1)| 00:00:05 |
|* 6 | TABLE ACCESS FULL | T1 | 87335 | 2558K| | 348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - filter("T3"."OBJECT_ID"<=178717)
3 - access("T3"."OWNER"='TEST')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OBJECT_ID"<=178717)
6 - filter("T1"."OBJECT_ID"<=178717)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2505 consistent gets
1247 physical reads
0 redo size
755 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1058 (100)| | 1 |00:00:00.13 | 2505 | 1247 | | | |
|* 1 | HASH JOIN | | 1 | 3119 | 179K| | 1058 (1)| 00:00:13 | 1 |00:00:00.13 | 2505 | 1247 | 1645K| 1645K| 628K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 3119 | 56142 | | 92 (0)| 00:00:02 | 1 |00:00:00.01 | 3 | 0 | | | |
|* 3 | INDEX RANGE SCAN | IDX_T3_1 | 1 | 3120 | | | 8 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 4 | HASH JOIN | | 1 | 87332 | 3496K| 1968K| 966 (1)| 00:00:12 | 87340 |00:00:00.10 | 2502 | 1247 | 5508K| 2261K| 5674K (0)|
|* 5 | TABLE ACCESS FULL | T2 | 1 | 87333 | 938K| | 348 (1)| 00:00:05 | 87340 |00:00:00.01 | 1252 | 0 | | | |
|* 6 | TABLE ACCESS FULL | T1 | 1 | 87335 | 2558K| | 348 (1)| 00:00:05 | 87341 |00:00:00.01 | 1250 | 1247 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - filter("T3"."OBJECT_ID"<=178717)
3 - access("T3"."OWNER"='TEST')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OBJECT_ID"<=178717)
6 - filter("T1"."OBJECT_ID"<=178717)
E-ROWS 列显示SQL最后返回 3119条数据，实际上只返回一条数据，由此我们看到当表的连接顺序错误时会导致优化器
估算出错误的返回记录数。
这里之所以会出现基数估算错误（为3119条记录），原因就是固定了表连接的顺序导致优化器转换出的谓词有问题，
OWNER字段上的索引IDX_T3_1 选择性很差，优化器使用这个索引估算 T3表的基数计算出错误的基数，从而生成错误的谓词
access("T2"."OBJECT_ID"="T3"."OBJECT_ID")、 filter("T3"."OBJECT_ID"<=178717)、access("T3"."OWNER"='TEST')、
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")、filter("T2"."OBJECT_ID"<=178717)、filter("T1"."OBJECT_ID"<=178717)。
正确的谓词请查看 第五部分SQL语句执行计划中的谓词。
Buffers 列显示总共有 2505个逻辑IO。
Reads 列显示总共有 1247个物理IO。
由于使用提示固定了表连接到顺序，导致使用了 HASH 连接产生了大量的逻辑读。
从执行计划中看到 SQL语句只返回一条数据，但是扫描T1表返回了 87335条记录、扫描T2表返回了 87333条记录、T1表和T2表
HASH 连接放回了 87332 条记录。由此判断读取了大量不需要的数据行。
返回一条记录产生了 2505 个逻辑IO，远高于返回记录数与逻辑IO 1/5的比值。
4. 删除提示让ORACLE 优化器生成正确的执行计划
select t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.owner='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 863399664
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3119 | 179K| 788 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 3119 | 179K| 788 (1)| 00:00:10 |
|* 2 | HASH JOIN | | 3119 | 90451 | 440 (1)| 00:00:06 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T3 | 3119 | 56142 | 92 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IDX_T3_1 | 3120 | | 8 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T2 | 87333 | 938K| 348 (1)| 00:00:05 |
|* 6 | TABLE ACCESS FULL | T1 | 87335 | 2558K| 348 (1)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
3 - filter("T3"."OBJECT_ID"<=178717)
4 - access("T3"."OWNER"='TEST')
5 - filter("T2"."OBJECT_ID"<=178717)
6 - filter("T1"."OBJECT_ID"<=178717)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace trace 看到的不是真实的执行计划。
alter session set STATISTICS_LEVEL = ALL;
select t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.owner='TEST';
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));
Plan hash value: 3864569537
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | 1 | 59 | 11 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
| 2 | NESTED LOOPS | | 1 | 1 | 59 | 11 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
| 3 | NESTED LOOPS | | 1 | 1 | 29 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 18 | 9 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_T3_1 | 1 | 1 | | 8 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 7 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T3"."OBJECT_ID"<=178717)
5 - access("T3"."OWNER"='TEST')
7 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
filter("T2"."OBJECT_ID"<=178717)
8 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"<=178717)
BUFFERS 列显示总共有 10 个逻辑IO。
当没有使用提示限制表的连接顺序时，优化器通过统计信息正确的估算出T3表和SQL最后只返回一条记录，并把通过
谓词条件推算出 T1和T2表也只需要返回一条有效的数据。选择使用嵌套循环连接和索引扫描。
5. 使用提示强制使用错误的表连接顺序，但与3不同的是使用主键对T3表的数据进行过滤能生成很优的执行计划
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.object_id=170832;
Execution Plan
----------------------------------------------------------
Plan hash value: 2742238221
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 53 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 12 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T3 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=170832)
6 - access("T3"."OBJECT_ID"=170832)
8 - access("T1"."OBJECT_ID"=170832)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Plan hash value: 2742238221
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 1 | 53 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 1 | 23 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX UNIQUE SCAN | PK_T3 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=170832)
6 - access("T3"."OBJECT_ID"=170832)
8 - access("T1"."OBJECT_ID"=170832)
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id >= 178717
and t3.object_id <= 178717;
Execution Plan
----------------------------------------------------------
Plan hash value: 2742238221
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 53 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 12 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T3 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=178717)
6 - access("T3"."OBJECT_ID"=178717)
filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")
8 - access("T1"."OBJECT_ID"=178717)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
756 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Plan hash value: 2742238221
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 1 | 53 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 1 | 23 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX UNIQUE SCAN | PK_T3 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=178717)
6 - access("T3"."OBJECT_ID"=178717)
filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")
8 - access("T1"."OBJECT_ID"=178717)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
虽然表连接的顺序是错误的，但ORACLE 优化器通过主键正确的估算出估算出 T1和T2表也只需要返回一条有效的数据。
选择使用嵌套循环连接和索引扫描。
6. 使用提示强制使用错误的表连接顺序，但与3不同的是SQL最终不返回数据优化器跳过了T1和T2表的全表扫描
select /*+ leading(t2,t1,t3) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t2 ,lixia.t1,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.owner='test';
Execution Plan
----------------------------------------------------------
Plan hash value: 2663886062
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 | | 968 (1)| 00:00:12 |
|* 1 | HASH JOIN | | 1 | 59 | | 968 (1)| 00:00:12 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 18 | | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T3_1 | 1 | | | 1 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 87332 | 3496K| 1968K| 966 (1)| 00:00:12 |
|* 5 | TABLE ACCESS FULL | T2 | 87333 | 938K| | 348 (1)| 00:00:05 |
|* 6 | TABLE ACCESS FULL | T1 | 87335 | 2558K| | 348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - filter("T3"."OBJECT_ID"<=178717)
3 - access("T3"."OWNER"='test')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OBJECT_ID"<=178717)
6 - filter("T1"."OBJECT_ID"<=178717)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Plan hash value: 2663886062
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 968 (100)| | 0 |00:00:00.01 | 2 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 59 | | 968 (1)| 00:00:12 | 0 |00:00:00.01 | 2 | 1245K| 1245K| 410K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 18 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | IDX_T3_1 | 1 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | HASH JOIN | | 0 | 87332 | 3496K| 1968K| 966 (1)| 00:00:12 | 0 |00:00:00.01 | 0 | 5916K| 1857K| |
|* 5 | TABLE ACCESS FULL | T2 | 0 | 87333 | 938K| | 348 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 87335 | 2558K| | 348 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - filter("T3"."OBJECT_ID"<=178717)
3 - access("T3"."OWNER"='test')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OBJECT_ID"<=178717)
6 - filter("T1"."OBJECT_ID"<=178717)
当返回结果为零行数据时，ORACLE 执行引擎自动取消了T1和T2表的全表扫描和T1、T2的HASH连接，只有2个逻辑IO。
7. T3表的 OBJECT_ID 列数据是唯一的，但建立非唯一索引在表连接顺序错误的情况下 ORACLE优化器仍能正确估算出
T3和T1表只需要返回一条记录，而使用索引扫描和嵌套选好连接
alter table lixia.t3 drop primary key CASCADE;
create index lixia.idx_t3_2 on lixia.t3(object_id);
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');
alter session set STATISTICS_LEVEL = ALL;
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.object_id=170832;
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));
Plan hash value: 3080117625
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 10 | | | |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 53 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 10 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 23 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
|* 6 | INDEX RANGE SCAN | IDX_T3_2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 7 | BUFFER SORT | | 1 | 1 | 30 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=170832)
6 - access("T3"."OBJECT_ID"=170832)
9 - access("T1"."OBJECT_ID"=170832)
8. T3表的 OBJECT_ID 列数据有重复，建立非唯一索引在表连接顺序错误的情况下 ORACLE优化器仍能估算出
T3和T1表只需要返回一条记录(实际返回11条记录)，而使用索引扫描和嵌套选好连接。
由此判断在非唯一索引选择率很低的情况下，即便表连接顺序错误优化器也可以生产优良的执行计划。
索引选择率=1/(索引唯一值)*100
update lixia.t3 set object_id=20 where rownum<1100;
update lixia.t3 set object_id=170832 where rownum<11;
commit;
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.object_id=170832;
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));
Plan hash value: 3080117625
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 11 |00:00:00.01 | 12 | | | |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 53 | 5 (0)| 00:00:01 | 11 |00:00:00.01 | 12 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 23 | 4 (0)| 00:00:01 | 11 |00:00:00.01 | 9 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 2 (0)| 00:00:01 | 11 |00:00:00.01 | 6 | | | |
|* 6 | INDEX RANGE SCAN | IDX_T3_2 | 1 | 1 | | 1 (0)| 00:00:01 | 11 |00:00:00.01 | 3 | | | |
| 7 | BUFFER SORT | | 11 | 1 | 30 | 3 (0)| 00:00:01 | 11 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=170832)
6 - access("T3"."OBJECT_ID"=170832)
9 - access("T1"."OBJECT_ID"=170832)
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id >= 178717
and t3.object_id <= 178717;
Plan hash value: 3984383077
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | 1 | 53 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
| 2 | NESTED LOOPS | | 1 | 1 | 53 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
| 3 | NESTED LOOPS | | 1 | 1 | 23 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | IDX_T3_2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."OBJECT_ID"=178717)
7 - access("T3"."OBJECT_ID"=178717)
filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")
8 - access("T1"."OBJECT_ID"=178717)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS
from lixia.t1 ,lixia.t2,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <= 178717
and t3.object_id=20;
Plan hash value: 3080117625
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1089 |00:00:00.13 | 173 | 10 | | | |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 53 | 5 (0)| 00:00:01 | 1089 |00:00:00.13 | 173 | 10 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 23 | 4 (0)| 00:00:01 | 1089 |00:00:00.12 | 170 | 5 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.11 | 3 | 5 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.09 | 2 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 12 | 2 (0)| 00:00:01 | 1089 |00:00:00.01 | 167 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX_T3_2 | 1 | 1 | | 1 (0)| 00:00:01 | 1089 |00:00:00.01 | 81 | 0 | | | |
| 7 | BUFFER SORT | | 1089 | 1 | 30 | 3 (0)| 00:00:01 | 1089 |00:00:00.01 | 3 | 5 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 5 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=20)
6 - access("T3"."OBJECT_ID"=20)
9 - access("T1"."OBJECT_ID"=20)
上面这条SQL基数估算不准，估算为一条实际有1089条记录，但执行计划是正确的。