dbms_xplan and Statspack 12c oops

From: Uwe Küchler <uwe_at_kuechler.org>
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-l
Received on Wed Dec 30 2015 - 17:32:03 CET

Original text of this message