执行计划问题
请问同一条SQL，分别执行两次（相隔一段时间），为什么会出现不同的执行计划。并且第二次的运行时间相对长很多，另外最好能解析该执行计划，谢谢！！SQL 〉explain plan for select * from tssp0_inf_contract_5 a,tssp0_inf_bus b,tssp0_inf_identify_v1 c,tssp0_inf_account d
where a.vc_bus_id = b.vc_bus_id
and b.vc_bus_id = c.vc_bus_id
and a.vc_account_id = d.vc_account_id
and a.vc_node_id = 'JD-006'
and c.vc_identify_id = 'SB-0000000011'
SQL 〉select * from table(dbms_xplan.display);
#######################################################################
PLAN_TABLE_OUTP
--------------------------------------------------------------------------------------------------------
Plan hash value: 2600685752
--------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 181 | 44707 | 80 (2)| 00:00:01 |
|*1 |HASH JOIN | | 181 | 44707 | 80 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 199 | 38009 | 11 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 128 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TSSP0_INF_IDENTIFY_V1 | 1 | 63 | 3 (0)| 00:00:01 |
---------------- 10 --------------
|*5 | INDEX UNIQUE SCAN | SYS_C0011206 | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TSSP0_INF_BUS | 1 | 65 | 2 (0)| 00:00:01 |
|*7 | INDEX UNIQUE SCAN | SYS_C0011189 | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TSSP0_INF_CONTRACT_2| 199 | 12537 | 6 (0)| 00:00:01 |
|*9 | INDEX RANGE SCAN | TSSI0_INF_CONTRACT | 199 | | 3 (0)| 00:00:01 |
|10 | TABLE ACCESS FULL | TSSP0_INF_ACCOUNT | 15648 | 855K| 68 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------- 20 --------------
1 - access("A"."VC_ACCOUNT_ID"="D"."VC_ACCOUNT_ID")
5 - access("C"."VC_IDENTIFY_ID"='SB-0000000011')
7 - access("B"."VC_BUS_ID"="C"."VC_BUS_ID")
9 - access("A"."VC_BUS_ID"="B"."VC_BUS_ID" AND "A"."VC_NODE_ID"='JD-006')
25 rows selected.
#######################################################################
#######################################################################
PLAN_TABLE_OUTP
--------------------------------------------------------------------------------------------------------
Plan hash value: 366286501
--------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 246 | 120K| 821K(1)| 02:44:16 |
|*1 |HASH JOIN | | 246 | 120K| 821K(1)| 02:44:16 |
| 2 | NESTED LOOPS | | 246 | 107K| 821K(1)| 02:44:15 |
| 3 | NESTED LOOPS | | 1 | 128 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TSSP0_INF_IDENTIFY_V1 | 1 | 63 | 3 (0)| 00:00:01 |
---------------- 10 --------------
|*5 | INDEX UNIQUE SCAN | SYS_C0011206 | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TSSP0_INF_BUS | 1 | 65 | 2 (0)| 00:00:01 |
|*7 | INDEX UNIQUE SCAN | SYS_C0011189 | 1 | | 1 (0)| 00:00:01 |
|*8 | TABLE ACCESS FULL | TSSP0_INF_CONTRACT_5| 246 | 78228 | 821K(1)| 02:44:15 |
| 9 | TABLE ACCESS FULL | TSSP0_INF_ACCOUNT | 15648 | 855K| 68 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------- 20 --------------
1 - access("A"."VC_ACCOUNT_ID"="D"."VC_ACCOUNT_ID")
5 - access("C"."VC_IDENTIFY_ID"='SB-0000000011')
7 - access("B"."VC_BUS_ID"="C"."VC_BUS_ID")
8 - filter("A"."VC_NODE_ID"='JD-006' AND "A"."VC_BUS_ID"="B"."VC_BUS_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected.
####################################################################### 你的表中的记录变化了，当表的记录不多，有变化的情况下，oracle基于cbo模式，可能会引起执行计划变化 请问有什么方法可以优化一下吗？
页:
[1]