dbms_xplan.display显示执行计划错误（plan_table.plan_id）
以下问题我不能确定算不算oracle的bug，但是确实会影响观察结果。
说明：
1.plan_table表的plan_id字段的值取自序列ORA_PLAN_ID_SEQ$；
2.提示driving_site的作用当涉及到使用dblink连接的查询语句时，提示优化器在哪一个site上执行；
3.oracle版本：10.2.0.4
1.设置格式
set line 150
col statement_id for a12
col plan_id for 999
col timestamp for a20
col operation for a20
col options for a20
col object_node for a10
col object_owner for a15
col object_name for a15
col id for 99
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
2.查询plan_table表（表中没有数据）
SQL> select statement_id, plan_id, timestamp, operation, options, object_node, object_owner, object_name, id from plan_table;
no rows selected
3.生成第一个语句执行计划（此语句在本地执行，即，a所在的机器）
SQL> explain plan for
2 select /*+ driving_site(a) */ * from t_test a, t_test1@ora10g b
3 where a.col1=b.col1;
Explained.
4.查询plan_table表，此时表中有6条数据
SQL> select statement_id, plan_id, timestamp, operation, options, object_node, object_owner, object_name, id from plan_table;
STATEMENT_ID PLAN_ID TIMESTAMP OPERATION OPTIONS OBJECT_NOD OBJECT_OWNER OBJECT_NAME ID
------------ ------- -------------------- -------------------- -------------------- ---------- --------------- --------------- ---
407 2013-06-06 11:13:16 SELECT STATEMENT 0
407 2013-06-06 11:13:16 MERGE JOIN 1
407 2013-06-06 11:13:16 TABLE ACCESS BY INDEX ROWID DASONG T_TEST 2
407 2013-06-06 11:13:16 INDEX FULL SCAN DASONG IDX_TEST_COL1 3
407 2013-06-06 11:13:16 SORT JOIN 4
407 2013-06-06 11:13:16 REMOTE ORA10G T_TEST1 5
6 rows selected.
5.通过dbms_xplan.display显示执行计划，对比plan_table表，可以看出此时显示的为plan_id=407的执行计划，显示是正确的
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1121890636
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 2542 | 5 (20)| 00:00:01 | | |
| 1 | MERGE JOIN | | 82 | 2542 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_TEST | 10 | 50 | 2 (0)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | IDX_TEST_COL1 | 10 | | 1 (0)| 00:00:01 | | |
|* 4 | SORT JOIN | | 82 | 2132 | 3 (34)| 00:00:01 | | |
| 5 | REMOTE | T_TEST1 | 82 | 2132 | 2 (0)| 00:00:01 | ORA10G | R->S |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
filter("A"."COL1"="B"."COL1")
Remote SQL Information (identified by operation id):
----------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
5 - SELECT "COL1","COL2" FROM "T_TEST1" "B" (accessing 'ORA10G' )
24 rows selected.
6.生成第一个语句执行计划（此语句在本地执行，即，b所在的机器）
SQL> explain plan for
2 select /*+ driving_site(b) */ * from t_test a, t_test1@ora10g b
3 where a.col1=b.col1;
Explained.
7.查询plan_table表，表中新增了4条plan_id=71的数据，共10条数据（远程机器时间比本地晚，这个对试验没有影响）
SQL> select statement_id, plan_id, timestamp, operation, options, object_node, object_owner, object_name, id from plan_table;
STATEMENT_ID PLAN_ID TIMESTAMP OPERATION OPTIONS OBJECT_NOD OBJECT_OWNER OBJECT_NAME ID
------------ ------- -------------------- -------------------- -------------------- ---------- --------------- --------------- ---
407 2013-06-06 11:13:16 SELECT STATEMENT 0
407 2013-06-06 11:13:16 MERGE JOIN 1
407 2013-06-06 11:13:16 TABLE ACCESS BY INDEX ROWID DASONG T_TEST 2
407 2013-06-06 11:13:16 INDEX FULL SCAN DASONG IDX_TEST_COL1 3
407 2013-06-06 11:13:16 SORT JOIN 4
407 2013-06-06 11:13:16 REMOTE ORA10G T_TEST1 5
71 2013-06-06 09:44:36 SELECT STATEMENT REMOTE 0
71 2013-06-06 09:44:36 NESTED LOOPS 1
71 2013-06-06 09:44:36 TABLE ACCESS FULL ORA10G DASONG T_TEST1 2
71 2013-06-06 09:44:36 REMOTE ! T_TEST 3
10 rows selected.
8.通过dbms_xplan.display显示执行计划，对比plan_table表，可以看出此时显示的依然是plan_id=407的执行计划（与第5步显示的一样），而实际应该显示新增的数据才对
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1121890636
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 2542 | 5 (20)| 00:00:01 | | |
| 1 | MERGE JOIN | | 82 | 2542 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_TEST | 10 | 50 | 2 (0)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | IDX_TEST_COL1 | 10 | | 1 (0)| 00:00:01 | | |
|* 4 | SORT JOIN | | 82 | 2132 | 3 (34)| 00:00:01 | | |
| 5 | REMOTE | T_TEST1 | 82 | 2132 | 2 (0)| 00:00:01 | ORA10G | R->S |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
filter("A"."COL1"="B"."COL1")
Remote SQL Information (identified by operation id):
----------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
5 - SELECT "COL1","COL2" FROM "T_TEST1" "B" (accessing 'ORA10G' )
24 rows selected.
SQL>
结论：
1.explain生成执行并插入plan_table表时，会取序列ORA_PLAN_ID_SEQ$值作为plan_id字段的值；
2.dbms_xplan.display获取plan_table中plan_id值最大的相关数据（可能通过update修改plan_id，再执行dbms_xplan.display证明）；
3.当sql语句涉及到dblink时可能会取远程数据库中此序列的值（也可能会取本地数据库中序列的值，这个取决于sql在哪一端插）填到本地plan_table的plan_id字段中，而本地与远程数据库中此序列的值相同的可能性基本为0，所以如果最新的执行计划取了较小的序列值填入表中，dbms_xplan.display则会取不到最新的执行计划。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7417681/viewspace-763467/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 区分exp&expdp导出的dmp文件
- ORA-27300、ORA-27301、ORA-27302、ORA-27303
- 字段 与 变量 同名造成的sql问题
- Configuring the Reserved Pool
- char, varchar2, nchar, nvarchar2
- ORA-39714: upgrade script utlmmig.sql failed
- Archive log rejected (thread 1 sequence 2200) at host 'standby'
- 数据文件offline后unusable索引造成的问题
- 物理备库open ORA-10458
- lsnrctl start - Linux Error 29 Illegal seek
