Rowsource-Statistiken nach Query-Abbruch

Ein nützlicher Hinweis von Randolf Geist im OTN-Forum:

one thing that is not so commonly known about the Rowsource Statistics is that *no matter how* the statement execution completed, the data will be available. So a long running query can be cancelled and the Rowsource Statistics will still be populated (but only after the statement execution stopped in some way). Usually the information then available is already good enough to tell why the execution takes so long.

Und damit ich mir das Verhalten besser merken kann, ein kleines Beispiel dazu:

drop table test;

create table test
as
select rownum id
  from dual
connect by level <= 100000;

-- ein cross join, der auf meinem Rechner einige Zeit unterwegs ist
select /*+ gather_plan_statistics */ count(*)
  from t t1, t t2;

Wenn man nach dem Start der Abfrage in einer zweiten Session die rowsource-Statistiken abrufen möchte, erhält man folgendes Resultat:

select *
  from table(dbms_xplan.display_cursor('6jn221223mda9', 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  6jn221223mda9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from t t1, t t2

Plan hash value: 840385502

--------------------------------------------------------------------------
| Id  | Operation             | Name | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        |       |       |          |
|   1 |  SORT AGGREGATE       |      |      1 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|      |     10G|       |       |          |
|   3 |    TABLE ACCESS FULL  | T    |    100K|       |       |          |
|   4 |    BUFFER SORT        |      |    100K| 73728 | 73728 |          |
|   5 |     TABLE ACCESS FULL | T    |    100K|       |       |          |
--------------------------------------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Die Statistiken sind also während der Ausführung nicht verfügbar. Aber wenn ich die Query in sqlplus nach einiger Zeit mit ctl+c terminiere, bekomme ich die bis zum Abbruch gesammelten Statistiken:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
SQL_ID  6jn221223mda9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from t t1, t t2

Plan hash value: 840385502

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|      |      1 |     10G|    542M|00:01:12.22 |     901 |       |       |          |
|   3 |    TABLE ACCESS FULL  | T    |      1 |    100K|   5423 |00:00:00.03 |      49 |       |       |          |
|   4 |    BUFFER SORT        |      |   5423 |    100K|    542M|00:00:37.74 |     852 |  2675K|   740K| 2377K (0)|
|   5 |     TABLE ACCESS FULL | T    |      1 |    100K|    100K|00:00:00.01 |     852 |       |       |          |
-------------------------------------------------------------------------------------------------------------------


17 Zeilen ausgewählt.

Nach 5423 Schleifen ist die Datenbank also von ihrem nutzlosen Unterfangen erlöst worden - und die Analyse kann beginnen.