如何获取远程dblink 的执行计划

How to Get the Remote DB's Exectuion Plan Through DBlink. (Doc ID 2757731.1)

GOAL

Get the remote DB's exectuion plan though DB Link.

SOLUTION

-- Set the GATHER_PLAN_STATISTICS hint to the target SQL.
select /*+ GATHER_PLAN_STATISTICS */ ...;

SET SERVEROUT ON
DEFINE DBLink='<DBLINK_NAME>';
DECLARE
   c INTEGER;
   r INTEGER;
   n INTEGER;
   output VARCHAR2(4000);
BEGIN
   c:=DBMS_SQL.OPEN_CURSOR@&DBLink;
   DBMS_SQL.PARSE@&DBLink(c, q'[SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'))]', DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN@&DBLink(c, 1, output, 4000);
   r:=DBMS_SQL.EXECUTE@&DBLink(c);

   LOOP
      IF DBMS_SQL.FETCH_ROWS@&DBLink(c) > 0
      THEN
         DBMS_SQL.COLUMN_VALUE@&DBLink(c, 1, output);
         DBMS_OUTPUT.PUT_LINE(output);
      ELSE
         EXIT;
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR@&DBLink(c);
END;
/