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: 22.214.171.124 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.