|
SELECT A.AA CURRENT_PLAN
, B.AA HIST_PLAN
, CASE
WHEN A.AA <> B.AA OR A.AA IS NULL OR B.AA IS NULL
THEN 'X'
END TP
FROM (
SELECT /*+ NO_MERGE */
TO_CHAR(DBID) DBID, SQL_ID, PLAN_HASH_VALUE
, ID
, LPAD(' ', DEPTH*2)
||TRIM(OPERATION||' '||OPTIONS||' '||OBJECT_OWNER
||CASE WHEN OBJECT_NAME IS NOT NULL THEN '.'||OBJECT_NAME END)
||CASE WHEN PARTITION_START IS NOT NULL THEN ': '
||PARTITION_START||' '||PARTITION_STOP END AS AA
FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID = :V_SQL_ID
AND DBID = (
SELECT DBID
FROM V$DATABASE
)
) B
FULL OUTER JOIN
(
SELECT
'CURRENT' DBID, SQL_ID, PLAN_HASH_VALUE
, ID
, LPAD(' ', DEPTH*2)
||TRIM(OPERATION||' '||OPTIONS||' '||OBJECT_OWNER
||CASE WHEN OBJECT_NAME IS NOT NULL THEN '.'||OBJECT_NAME END)
||CASE WHEN PARTITION_START IS NOT NULL THEN ': '
||PARTITION_START||' '||PARTITION_STOP END AS AA
FROM V$SQL_PLAN
WHERE SQL_ID = :V_SQL_ID
AND CHILD_NUMBER = 0
) A
ON (
A.ID = B.ID
)
ORDER BY
A.ID;
|
