estimated plan stability

Sometimes I am searching for any method to solve a problem. And after some investigations, mailing lists, direct contact of much smarter people, I come to the conclusion:

It's just not possible!

(Or at least not within reasonable effort).

One of these problems, or more precise questions is:

How likely is the current explain plan for a given SQL statement to change?

I call this

estimated plan stability

Unfortunately there is currently no such feature but at least I can give some examples, what I would like to have:

• E-rows vs. A-rows
  If they differ a lot (in any line of the execution plan) it might be a hint the plan is far away from reality, or in risk to change?
  Of course for A-rows gather_plan_statistics or similar is needed.


• Best so far in 10053 trace
  
  If you ever have analysed a 10053 trace, you might know the line starting with Best so far ....
  If the 2nd best is not far from the 1st, I assume small changes in the data might lead to a different execution plan.


• Binds outside histogram boundaries
  
  If a bind variable is outside of the min/max values of a histogram, the optimiser tries to guess how many rows it will get from this predicate/filter. Of course this can be horrible wrong, and should be also shown by my 1st suggestion.
  

These are only 3 possibilities. They should show some areas of information where I'd like Oracle to collect and provide more data than they do at the moment. Probably they would also be valuable for others? Any other suggestions out there?

avoid implicit COMMIT at DDL

Some times, I'd like to have a transaction open, even there is a DDL somewhere in between.
Oracle might have some reasons for the implicit COMMIT, but I have mine to avoid them in my transactions.

Here a small example, how I do it (don't just copy it, it's really small, just an example)!

Let's prepare a package and two tables

CREATE OR REPLACE PACKAGE my_ddl_package AS
FUNCTION do_ddl(p_ddl varchar2)  return varchar2;
END my_ddl_package;
/

CREATE OR REPLACE PACKAGE BODY my_ddl_package AS
function do_ddl(p_ddl varchar2) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin

execute immediate p_ddl;
return NULL;
end;
END my_ddl_package;
/

create table t1 as select * from dual;

create table t2 as select * from dual;

And now the testcase

I try to update t1, drop t2 and afterwards rollback the update on t1.
Let's give it a try.

SQL> update t1 set dummy ='U';

1 row updated.

SQL> select my_ddl_package.do_ddl('drop table t2') from dual;

MY_DDL_PACKAGE.DO_DDL('DROPTABLET2')
--------------------------------------------------------------------------------


SQL> select * from t1;

D
-
U

SQL> rollback;

Rollback complete.

SQL> select * from t1;

D
-
X

SQL> select * from t2;
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Everything works as expected: update - DDL - rollback

Did I miss something?

If you did not want to read (or think) too much, just the shortcut:
What saved my day? It's PRAGMA AUTONOMOUS_TRANSACTION!