Dienstag, Juni 28, 2011

Costing für FIRST_ROWS(n)

In Cost Based Oracle steht meiner Erinnerung nach nichts dazu, aber der Fall ist relativ einfach, so dass man dazu den Herrn Lewis wohl nicht benötigt [dafür aber den Herrn Geist - s. den Nachtrag vom 30.06.]:
(n/number_of_rows_in_table) * initial_cost
Hinweis: wie Randolf Geist in seinem Kommentar erwähnt - und in seiner unten verlinkten Präsentation näher erläutert - ist dieser einfache "proration factor" (= n / total_number_of_rows) nur ein Element innerhalb eines deutlich komplexeren Kalküls.

Dazu noch der Test, auf dem meine Annahmen basieren:


create table test_first_rows_hint
as
select rownum id
  from dual
connect by level <= 1000000;
 
exec dbms_stats.gather_table_stats(user, 'TEST_FIRST_ROWS_HINT', estimate_percent => 100)
 
select * from test_first_rows_hint;
 
1000000 Zeilen ausgewählt.
 
Abgelaufen: 00:00:03.96
 
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3818872010
 
------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |  1000K|  4882K|   780   (0)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TEST_FIRST_ROWS_HINT |  1000K|  4882K|   780   (0)| 00:00:04 |
------------------------------------------------------------------------------------------

In den folgenden Testläufen wurde die Query dann jeweils mit einem Hint versehen:
  • first_rows: 780 (wie im Fall ohne Hint)
  • first_rows(1): 2
  • first_rows(1000): 3
  • first_rows(10000): 9
  • first_rows(100000): 80 (ca. 10% der ursprünglichen Kosten bei Optimierung für 10% der Sätze)
  • first_rows(500000): 391 (ca. 50% der ursprünglichen Kosten bei Optimierung für 50% der Sätze)
Jonathan Lewis und Charles Hooper haben zum Thema übrigens noch ein paar interessante Punkte gesammelt, die über den Rahmen meiner Miniatur-Analyse hinaus gehen:
Nachtrag 30.06.2011: meine Annahme, dass das Costing für den FIRST_ROWS(n) Hint relativ simplen Regeln folgt, hat sich als deutlich zu optimistisch erwiesen, wie Randolf Geists Kommentar zu diesem Blog-Eintrag zeigt. Eine extrem detaillierte Beschreibung der Effekte der FIRST_ROWS%-Hints und ihrer sinnvollen Verwendung findet man in seiner Präsentation im Rahmen der UKOUG 2009: Everything You Wanted To Know About FIRST_ROWS_n But Were Afraid To Ask

Kommentare:

  1. Hallo Martin,

    das mit der "harmlosen" Formel stimmt zwar für diesen trivialen Fall, aber schon für leicht komplexere Abfrage leider nicht mehr, zum Beispiel wenn Indizes mit ins Spiel kommen.

    Ich habe mal eine Präsentation über den FIRST_ROWS(n)-Modus gehalten in der Vergangenheit, und konnte keine endgültige Formel finden, die die unterschiedlichen Ergebnisse beschreiben würde. Man findet Links zu der Präsentation und einer erweiterten Version mit Erklärungen hier:

    http://oracle-randolf.blogspot.com/2009/12/ukoug-2009-slides.html

    Randolf

    AntwortenLöschen
  2. Hallo Randolf,

    Danke für die Hinweise. Die Präsentation ist beeindruckend. Mich erschüttert immer wieder, wie komplex selbst die kleinsten Details werden, wenn man genau genug hinschaut - wobei das vermutlich nicht nur für den CBO gilt...

    AntwortenLöschen