Up to Oracle 11.2 it was possible to display archived SQL plans from Statspack using DBMS_XPLAN. I make use of this in some of my scripts and SQL Developer Reports since I first saw this in Christian Antognini’s Book “Troubleshooting Oracle Performance“.
But in 12c (here: 12.1.0.1 on Linux), there’s a piece missing now:
select * from table(dbms_xplan.display( table_name => 'perfstat.stats$sql_plan', statement_id => null, format => 'ALL -predicate -note', filter_preds => 'plan_hash_value = '|| &&phv ); ERROR: an uncaught error in function display has happened; please contact Oracle support Please provide also a DMP file of the used plan table perfstat.stats$sql_plan ORA-00904: "TIMESTAMP": invalid identifier
So it looks like STATS$SQL_PLAN wasn’t synchronized to the changes in 12c’s PLAN_TABLE. Maybe because the timestamp wouldn’t make much sense there, anyway, maybe simply because Oracle forgot.
==> Quick and most certainly unsupported workaround:
ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
Another workaround could be to create a separate view with an additional dummy timestamp column an reference the view. I chose to stick with the invisible column solution so I won’t have to create new objects in the DB and change scripts to use these objects.
Hopefully, this will be solved in 12.2 – at least, I had filed an SR / Enhancement Request with Oracle Support.
UPDATE 2020-01:
My 4 year old (!) Service Request concerning this issue led to an Enhancement Request. This means that a fix will only be implemented in future versions (if at all).
Adding the timestamp column manually as a workaround was approved by Support in this SR.
Oracle Support Bug 23013887 (NEED OPTION TO DISPLAY EXECUTION PLANS FROM STATSPACK TABLES) can be found at: https://support.oracle.com/epmos/faces/BugDisplay?id=23013887
LikeLike