加了driving_site的HINT之后为何用xplan看不到执行计划了？

深圳－刀

SQL> SELECT /*+ driving_site(b) gather_plan_statistics test41 */ COUNT(distincta.v_dept_code)
  2    FROM test_del_me a, tb_mailbaseinfo@sddc b
  3   WHERE a.v_mail_code = b.v_mail_code;

COUNT(DISTINCTA.V_DEPT_CODE)
----------------------------
                           1

已用时间:  00: 00: 00.03
SQL> select sql_id from v$sql where sql_text like '%test41%' and sql_text not like '%v$sql%';
SQL_ID
-------------
7gx9ywu7hy1b5

已用时间:  00: 00: 00.35
SQL> SELECT * FROM table(dbms_xplan.display_cursor('7gx9ywu7hy1b5'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7gx9ywu7hy1b5, child number 0
SELECT /*+ driving_site(b) gather_plan_statistics test41 */ COUNT(distinct a.v_dept_code)   FROM test_del_me a, tb_mailbaseinfo@sddc b WHERE a.v_mail_code = b.v_mail_code
NOTE: cannot fetch plan for SQL_ID: 7gx9ywu7hy1b5, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

SQL> SELECT * FROM v$sql_plan where sql_id = '7gx9ywu7hy1b5';

未选定行

Maclean Liu(刘相兵

一般是这个SQL 被totally remote execute了， 执行计划不在本地

深圳－刀

问题是，我在远程数据库上也查不到执行计划。这种情况下如何查到执行计划？

深圳－刀

记录一下答案：只能用AUTOTRACE，10046和10053查执行计划