A "good to know" regarding explain plans and DBMS_XPLAN (2303)

The 11 May quiz tested your knowledge of the DBMS_XPLAN package and how it can be used to display explain plan information. As part of the explanation of the scoring, we wrote:

This choice does not specify a statement ID, but that's OK,  because the call to the DBMS_XPLAN.display function does not pass a value for  the statement ID parameter. Oracle automatically passes back explain plan  information for the most recently explained statement.

Anna Onishchuk wrote to suggest that this statement is not entirely true:

Oracle does not pass back explain plan information for the  most recently explained statement, it passes back information for the highest  PLAN_ID which is quite different in databases with DB links. So in most cases  your explanation is correct, but not in all cases. So technically the answer should be “As we have a totally  clean database (no db links), you’ll see the plan for the last query you  run”.

She also offered this example:

If you run a query for a remote table via DB Link, the Oracle will take sequence from the remote database and use it in the local database. Here is an example:

You current sequence plan_id = 20 in your local database.
In remote database plan_id= 1000.

Run the query:

Explain plan for 'select * from dual@my_link where 1=1'
/

The query will place new records in PLAN_TABLE with PLAN_ID = 1001. If you try to do the quiz, the greatest PLAN_ID will be selected so the remote plan will be returned. (Unless you try the quiz for 800 times)

SELECT *
  FROM TABLE (DBMS_XPLAN.display ('PLAN_TABLE', NULL))
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  1 |  1 | 3 (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL     | DUAL |  1 |  1 | 3 (0)| 00:00:01 |

===== End of example

I will update the explanation for the question with this information, but I thought it would also be worth highlighting it on the blog.

Thanks, Anne!