Dear fellows of the Oracle,
on the brink of new year's eve here's my last subject for 2015:
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.
A quick Google and MOS search didn't return anything related to this
specific error; should I really be the first to experience this or did any
of you folks stumble into this error as well?
==> Quick, dirty and most certainly unsupported workaround:
alter table stats$sql_plan add (timestamp date);
A less dirty workaround could be to create a separate view with an
additional dummy timestamp column an reference the view. Any better
suggestions (aside from opening an SR)?
Have a safe journey into 2016!
Cheers,
Uwe
---
http://oraculix.com
--
//www.freelists.org/webpage/oracle-l
