dbms_xplan and Statspack 12c oops
Date: Wed, 30 Dec 2015 17:32:03 +0100
Message-ID: <f75b6edb593883e184c4ca7c53718b85.webmail_at_mx1bln1.prossl.de>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 30 2015 - 17:32:03 CET