Oracle CBO und RBO

[sayso]

Hallo Kollegen,

ich habe mal eine Frage bezüglich des Cost Based bzw. Rule Based Optimizers.

Der RBO wird ab 10G ja offiziell nicht mehr supported, d.h. es "gibt nur noch" den CBO.

Wenn optimizer_mode bzw. optimizer_goal (bei kleiner gleich 9i) auf choose gesetzt ist, wird ja automatisch entschieden welcher Optimizer genutzt wird. Ich habe im Metalink auch schon ein Dokument gefunden, das beschreibt wann welcher Optimizer genommen wird.

Da bei 10G der RBO nicht mehr supported wird bzw. in spaeteren Releases rausfaellt kommt ja nur noch der CBO zum Zug, richtig?

Angenommen ich habe jetzt ein Schema das viele Tabellen enthält - fuer die Tabellen wurden keine Statistiken erzeugt, zieht dann trotzdem der CBO oder wie optimiert Oracle dann die Abfrage? Falls der CBO zum Einsatz kommt, nach welchem Kriterien optimiert er ohne Statistiken?

Ist für den CBO ein Index auf Tabellen unbedingt erforderlich?

Wäre super wenn mir jemand die Fragen beantworten koennte, da ich leider keinen Performance Kurs besuchen kann

Danke schoen

[Jasper]

Da bei 10G der RBO nicht mehr supported wird bzw. in spaeteren Releases rausfaellt kommt ja nur noch der CBO zum Zug, richtig?

richtig.

Angenommen ich habe jetzt ein Schema das viele Tabellen enthält - fuer die Tabellen wurden keine Statistiken erzeugt, zieht dann trotzdem der CBO oder wie optimiert Oracle dann die Abfrage? Falls der CBO zum Einsatz kommt, nach welchem Kriterien optimiert er ohne Statistiken?

der CBO erstellt stats on-the-fly mittels dynamic sampling. falls das nicht möglich ist (feature abgeschalten oder bei bestimmten tabletypen) werden defaults verwendet.

Ist für den CBO ein Index auf Tabellen unbedingt erforderlich?

die frage verstehe ich nicht.

-j

[sayso]

Erstmal danke für deine Antwort. :e@sy

der CBO erstellt stats on-the-fly mittels dynamic sampling. falls das nicht möglich ist (feature abgeschalten oder bei bestimmten tabletypen) werden defaults verwendet.

Was meinst du mit defaults?

Die stats die er "on-the-fly" erstellt, wo legt er diese ab? Existieren diese dann nur in der SGA? (wenn ja in welchem Bereich)

Habe mal im Metalink nachgelesen und habe das Dynamic Sampling so verstanden. Der CBO liest X Blocks und erstellt über diese dann eine Statistik und arbeitet mit dieser Statistik.

Ich kann mir zwar nicht vorstellen, wie ein paar Blöcke representativ für die ganze Tabelle mit sehr vielen Einträgen stehen können , aber Oracle wird schon wissen was sie tun

die frage verstehe ich nicht.

Sorry, vllt. etwas falsch formuliert.

Wenn ich auf verschiedene Tabellenfelder einen Index gelegt habe, und nun einen Select über diese Felder mache, dann existieren für den Index ja keine Statistiken. Wie arbeitet der CBO dann?

Klar ist das er über den Index geht, aber wendet er dann auch irgendwelche Costs an? wenn ja wie ... denn er hat ja keine Statistiken für die Indezes..

[Jasper]

Was meinst du mit defaults?

es werden einfach standardwerte für die verschiedenen CBO-parameter angenommen, z.b btree-level (1), selectivity für bound (0.0025), unbound (0.05) ranges, etc.pp.

Die stats die er "on-the-fly" erstellt, wo legt er diese ab? Existieren diese dann nur in der SGA? (wenn ja in welchem Bereich)

diese werte werden nicht gespeichert und sind nicht einsehbar (ausser mit einem 10053 trace).

Wenn ich auf verschiedene Tabellenfelder einen Index gelegt habe, und nun einen Select über diese Felder mache, dann existieren für den Index ja keine Statistiken. Wie arbeitet der CBO dann?

genauso wie mit statistiken, sind keine da -> sampling, sampling nicht möglich -> defaults

Klar ist das er über den Index geht, aber wendet er dann auch irgendwelche Costs an? wenn ja wie ... denn er hat ja keine Statistiken für die Indezes..

wieso soll das klar sein? bei RBO ja, bei CBO nicht. sampling wird auch bei indizes angewendet.

-j

[sayso]

wieso soll das klar sein? bei RBO ja, bei CBO nicht. sampling wird auch bei indizes angewendet.

Hallo,

nochmal vielen Dank für deine Antwort.

Eine Frage noch zum Index und dem CBO.

Existiert ein Index auf einem Tabellenfeld, auf was wird dann das Dynamic Sampling angewendet?

Auf den Index des Feldes oder auf das Feld ansich? Ist dann ein Index für den CBO überhaupt noch relevant?

Danke

[Jasper]

Existiert ein Index auf einem Tabellenfeld, auf was wird dann das Dynamic Sampling angewendet?

Auf den Index des Feldes oder auf das Feld ansich? Ist dann ein Index für den CBO überhaupt noch relevant?

dynamic sampling wird auf das feld unter verwendung des index angewendet. ich sehe schon, um einen 10053 komme ich nicht herum (ich habe alles unwesentliche weggelassen):

tabelle dyn wurde erstellt mit 'CTAS * from dba_objects', der index mit 'craete index ix1 on dyn(owner)', alle statistiken für tabelle und index wurden gelöscht. das sql-statement zum testen ist "select * from dyn where owner='DUMMY'", welches ca. 40 zeilen zurückgeben sollte. kommentare von mir beginnen mit >>:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: DYN Alias: DYN (NOT ANALYZED)

#Rows: 12990 #Blks: 319 AvgRowLen: 100.00

>> für AvgRowLen wird der default 100 verwendet, #Blks (anzahl blöcke unter HWM) ist >> aus dem segmentheader bekannt, #Rows errechnet sich dann wie folgt:

>> #Rows = #Blks * (blocksize-blockheadersize) / AvgRowLen

>> blocksize ist 4096 bytes, blockheadersize=24 bytes -> 12990 rows

Index Stats::

Index: IX1 Col#: 1 (NOT ANALYZED)

LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00

>> auch hier defaults: jeder index ohne stats wird als level1 mit 25 LB angesehen

***************************************

SINGLE TABLE ACCESS PATH

*** 2006-06-05 12:48:24.394

** Performing dynamic sampling initial checks. **

Column (#1): OWNER(VARCHAR2) NO STATISTICS (using defaults)

AvgLen: 30.00 NDV: 406 Nulls: 0 Density: 0.0024634

** Dynamic sampling initial checks returning TRUE (level = 2).

** Dynamic sampling updated index stats.: IX1, blocks=61

** Dynamic sampling index access candidate : IX1

** Dynamic sampling updated table stats.: blocks=319

*** 2006-06-05 12:48:24.418

** Generated dynamic sampling query:

query text :

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("DYN") FULL("DYN") NO_PARALLEL_INDEX("DYN") */ 1 AS C1, CASE WHEN "DYN"."OWNER"='DUMMY' THEN 1 ELSE 0 END AS C2 FROM "DYN" SAMPLE BLOCK (19.749216 , 1) SEED (1) "DYN") SAMPLESUB

*** 2006-06-05 12:48:24.422

** Executed dynamic sampling query:

level : 2

sample pct. : 19.749216

actual sample size : 2527

filtered sample card. : 8

orig. card. : 12990

block cnt. table stat. : 319

block cnt. for sampling: 319

max. sample block cnt. : 64

sample block cnt. : 63

min. sel. est. : 0.01000000

** Using recursive dynamic sampling card. est. : 12795.444444

>> level 2 sampling, d.h. 64 blocks ergibt eine estimated cardinality von 12795, die tabelle hat genau 11438, also ca. 10% daneben

*** 2006-06-05 12:48:24.422

** Generated dynamic sampling query:

query text :

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("DYN") INDEX("DYN" IX1) NO_PARALLEL_INDEX("DYN") */ 1 AS C1, 1 AS C2, 1 AS C3 FROM "DYN" "DYN" WHERE "DYN"."OWNER"='DUMMY' AND ROWNUM <= 2500) SAMPLESUB

>> wie man hier sieht, erfolgt das sampling auf der tabelle unter benutzung von IX1 (siehe hint)

*** 2006-06-05 12:48:24.424

** Executed dynamic sampling query:

level : 2

sample pct. : 100.000000

actual sample size : 12795

filtered sample card. : 41

filtered sample card. (index IX1): 41

orig. card. : 12795

block cnt. table stat. : 319

block cnt. for sampling: 319

max. sample block cnt. : 4294967295

sample block cnt. : 319

min. sel. est. : 0.01000000

index IX1 selectivity est.: 0.00320427

** Using dynamic sampling card. : 12795

** Dynamic sampling updated table card.

** Using single table dynamic sel. est. : 0.00320427

>> diese selectivity von 0.00320427 errechnet sich aus sampled cardinality der tabelle

>> von 12795 und des index von 41, d.h. der optimizer nimmt aufgrund des dynamic

>> sampling an, dass die tabelle 12795 rows hat und das jeder disctinct value des index

>> auf 41 rows der tabelle verweist

Table: DYN Alias: DYN

Card: Original: 12795 Rounded: 41 Computed: 41.00 Non Adjusted: 41.00

Access Path: TableScan

Cost: 40.31 Resp: 40.31 Degree: 0

Cost_io: 40.00 Cost_cpu: 4422632

Resp_io: 40.00 Resp_cpu: 4422632

Access Path: index (AllEqRange)

Index: IX1

resc_io: 4.00 resc_cpu: 29403

ix_sel: 0.0032043 ix_sel_with_filters: 0.0032043

Cost: 4.00 Resp: 4.00 Degree: 1

Best:: AccessPath: IndexRange Index: IX1

Cost: 4.00 Degree: 1 Resp: 4.00 Card: 41.00 Bytes: 0

der rest ist normale optimizer-rechnerei. auf das predicate owner=DUMMY treffen 41 aus 11438 zeilen zu, daher gewinnt hier der indexzugriff mit seiner besseren selectivity.

das sollte deine fragen beantworten.

-j

[mme]

Mir stellt sich die Frage warum du keine Statistiken erstelltst, bzw. die Datenbank erstellt diese doch defaultmaßig, durch voreingestellte Jobs von selbst

Hast du diese Jobs deaktiviert und warum?

Soweit ich weiß kannst du den Rulebased optimizer noch verwenden. Er wird halt nicht mehr SUPPORTET, (und bringt in der Regel schlechtere Ergebnisse) aber wenn du ihn durch einen Hint beispielsweise anweist Rule zu nehmen, wird er das wohl noch tuen.

Wie du schon sagtest sind beim sampling die ersten paar Blöcke nicht representativ. (wird von Oracle definitiv nicht empfohlen).

Aber auch "einfache" Statistiken sind da nicht umbedingt viel besser. Deshalb solltest du auch sowas wie Histogramme haben. Diese erstellt der voreingestellte Job aber gleich mit für alle indizierten Spalten...

[Jasper]

Mir stellt sich die Frage warum du keine Statistiken erstelltst, bzw. die Datenbank erstellt diese doch defaultmaßig, durch voreingestellte Jobs von selbst

Hast du diese Jobs deaktiviert und warum?

GATHER_STATS_PROG verwendet auto-sample, was vor allem bei grossen datenbanken (>250G) ziemlicher unsinn ist. da ist estimate mit festem prozentwert (2-5) um längen besser.

Soweit ich weiß kannst du den Rulebased optimizer noch verwenden. Er wird halt nicht mehr SUPPORTET, (und bringt in der Regel schlechtere Ergebnisse) aber wenn du ihn durch einen Hint beispielsweise anweist Rule zu nehmen, wird er das wohl noch tuen.

RBO kennt keine der neuen features ab oracle 7. bitmap-index, hash joins, etc. kann man damit vergessen.

Wie du schon sagtest sind beim sampling die ersten paar Blöcke nicht representativ. (wird von Oracle definitiv nicht empfohlen).

kommt drauf an. gerade bei temporary tables oder im DWH (bei tabellen mit gleichverteilten daten) ist dynamic sampling von vorteil.

Aber auch "einfache" Statistiken sind da nicht umbedingt viel besser. Deshalb solltest du auch sowas wie Histogramme haben. Diese erstellt der voreingestellte Job aber gleich mit für alle indizierten Spalten...

histogramme zu erstellen ist resourcenintensiv und lohnt sich nur für spalten mit ungleichverteilten daten oder bei abhängigkeiten zwischen spalten. ich würde keine histogramme per default erstellen.

-j

[mme]

RBO kennt keine der neuen features ab oracle 7. bitmap-index, hash joins, etc. kann man damit vergessen.

deshalb ja auch unsupportet, nutzen kann er es trotzdem...

kommt drauf an. gerade bei temporary tables oder im DWH (bei tabellen mit gleichverteilten daten) ist dynamic sampling von vorteil.

bei temporary tables ist das klar, aber gerade im DWH-Bereich lohnt es sich ein sinnvolles "Statistikmanagement" zu betreiben. Die Daten sind von ihrer Verteilung ziemlich fix und somit halten sich einmal erstellte Statistiken ziemlich lange und gerade bei den großen Datenmengen kann man dadurch viel Zeit bei den Abfragen raushohlen. Hier auf sampling zu setzen ist geradezu blödsinnig.

Und die Tabellen/Spalten mit gleichverteilten Daten gibt es nach meinen Erfahrungen nur sehr selten.

histogramme zu erstellen ist resourcenintensiv und lohnt sich nur für spalten mit ungleichverteilten daten oder bei abhängigkeiten zwischen spalten. ich würde keine histogramme per default erstellen.

Meiner Erfahrung nach sind die Daten meistens ungleichverteilt. Und wenn ich erst hingehe und von mir jede Spalte händisch anschaue ob die Daten ungleichverteilt sind (was die Histogramme ja gerade tun sollen), so kostet das mehr als wenn ich das einfach ausführe.

Deine gleichverteilten Daten höhren sich für mich sehr nach Bilderbuchdatenbank an...

Natürlich hängt das auch alles vom Einzelfall ab.

Bei kleinen Datenbanken ist das Erstellen schnell durch, aber die Statistiken schnell wieder veraltet, da sich prozentual auf die Gesamtmenge viele Daten ändern.

Bei großen Systemen braucht der Aufbau sehr lange /Kostenintensiv, dafür halten die Statistiken viel länger. Und gerade bei den großen Systemen ist der Performance gewinn am größten.

Somit würde ich das sampling erst recht nicht bei großen Systemen nutzen und es gibt relativ wenige Systeme, die 7x24 so ausgelastet sind, das da nie ein Fenster ist, wo nebenbei Statistiken erstellt werden können.

Oder löscht ihr euer DWH jede Woche und baut es komplett neu auf??? Dann hat man natürlich ein Problem, aber das liegt dann an einer schlechten "Architektur"...

[Jasper]

bei temporary tables ist das klar, aber gerade im DWH-Bereich lohnt es sich ein sinnvolles "Statistikmanagement" zu betreiben. Die Daten sind von ihrer Verteilung ziemlich fix und somit halten sich einmal erstellte Statistiken ziemlich lange und gerade bei den großen Datenmengen kann man dadurch viel Zeit bei den Abfragen raushohlen. Hier auf sampling zu setzen ist geradezu blödsinnig.

wenn sampling zu den gleichen ausführungsplänen führt, werde ich nicht stunden- bis tagelang irgendwelche statistiken generieren nachdem ende des monats ein neuer datenblock geladen wurde. das wäre 'blödsinnig'.

Deine gleichverteilten Daten höhren sich für mich sehr nach Bilderbuchdatenbank an...

dann hab ich halt 800G bilderbuchdaten. vielleicht liest du mein posting nochmal, ich schrieb "keine histogramme _per default_". histogramme machen probleme mit bindvariablen, mit VARCHAR/CHAR, bei denen die ersten 32 bytes sich nicht signifikant unterscheiden und haben oftmals keinen einfluss auf den ausführungsplan. wolfgang breitling und dave ensor haben dazu recht gute papers verfasst, ebenso wie jonathan lewis.

Somit würde ich das sampling erst recht nicht bei großen Systemen nutzen und es gibt relativ wenige Systeme, die 7x24 so ausgelastet sind, das da nie ein Fenster ist, wo nebenbei Statistiken erstellt werden können.

jede datenbank, die von leuten rund um den globus genutzt wird, hat probleme mit solchen fenstern, wobei man ab einer gewissen grösse die statistiken nicht mehr eben nebenbei erstellen kann. fluglinien und telcos kennen das problem nur zu gut.

Oder löscht ihr euer DWH jede Woche und baut es komplett neu auf??? Dann hat man natürlich ein Problem, aber das liegt dann an einer schlechten "Architektur"...

spekulationen kommentiere ich nicht.

-j

[sayso]

@Jasper:

Was soll ich sagen...

Das sich jemand soviel Mühe für mich macht, ist echt der Wahnsinn... :uli :uli

Vielen Dank für deinen ausführlichen Trace (inklusive Kommentierung) :byby:

Jetzt wird mir auch klar wie so ein dynamic sampling funktioniert, und die anschliessende Entscheidung fällt.

Warum ich diese Frage stellt habe?

Wir haben eine Datenbank mit Oracle Text, in der verschiedene Descriptionfelder durchsucht werden können.

Jeden Tag wird eine Statistik über die Felder, ergo den Intermedia-Index gefahren.

Die Frage die ich mir gestellt habe, war nun ob es überhaupt sinnvoll ist jede Nacht zu analysieren, und ob evtl. ein dynamic sampling schneller wäre.

Dies kann ich ja jetzt mal mit dem oben genannten geposteten Trace selbst analysieren. Im Metalink gibt es ja bestimmt auch etwas zu den Schlagwörtern.

Desweiteren haben wir eine Datenbank in der sehr viel Bewegungsdaten sind, es existieren aber Statistiken für Tabellen , die mehrmals am Tag aufgebaut werden. Hier ist nun auch fraglich, ob dies sinnvoll ist wenn der CBO mit dynamic sampling doch relativ genau arbeitet....

Vllt. hat jemand ja schon im Zusammenhang mit Oracle Text analysen gemacht und kann Erfahrungen posten

Vielen Dank an Euch.. ihr seid super!!!

[mme]

wenn sampling zu den gleichen Ausführungsplänen führt, werde ich nicht stunden- bis tagelang irgendwelche statistiken generieren nachdem ende des monats ein neuer datenblock geladen wurde. das wäre 'blödsinnig'.

Brauchst du auch in der Regel nicht. Wenn du eine Tabelle von 30 GB hast und da kommen 500MB am ende des Monats dazu, brauchst du keine neuen Statistiken. Wo ist das Problem... Und meine Erfahrung bisher ist, das dass Sampling zumindest bei unseren Daten nicht gerade optimal ist...

... histogramme machen probleme mit bindvariablen, ...

Das würde mich interessieren, was für Probleme mit Bindvariablen?

...es existieren aber Statistiken für Tabellen , die mehrmals am Tag aufgebaut werden.

Die statistiken werden mehrmals täglich neu aufgebaut oder die Tabellen?

Wenn die Statistiken mehrmals am Tag neu aufgebaut werden, so lohnt sich das doch nur, wenn da sich richtig viel ändert...

Wird auf die Tabellen durch eine Applikation abgefragt, die immer die gleichen Statements absetzt? Wenn ja, so wird beim sampling nur beim ersten absetzen des Statements das sampling durchgeführt und alle nachfolgendenen Ausführungen nutzen den gleichen Ausführungsplan (gesetz den Fall deine Pools sind groß genug). D.h. auch wenn sich die Inhalte gravierend ändern, sodas ein anderer ExecutionPlan wesentlich günstiger ist, bleibt der alte Plan ( es sei den der Plan fliegt aus dem Pool).

Wenn du wie jetzt die Statistiken neu aufbaust (wenn es diese sind die du meinst) so werden die Execution Pläne ungültig nach jedem neuaufbau und es wird ein neuer Plan erstellt.

Sollten die Tabellen neu aufgebaut werden, stehen da vermutlich zu großen Teilen die gleichen Daten drin wie vorher. Dann kam man ggf. die alten Statistiken wieder verwenden und muss sie nicht neu aufbauen...

Wir erstellen unsere Statistiken (ausser im DWH-Bereich) z.B. auf dem (identischen) Backupsystem. Dort exportieren wird die Statistiken dann und importieren sie im Produktivsystem. So haben wir regelmäßig neue Statistiken ohne das Produktivsystem überhaupt damit zu belasten...

[Jasper]

Das würde mich interessieren, was für Probleme mit Bindvariablen?

der wert der bindvariablen legt beim hard parse den execution plan fest. selbst wenn aufgrund des histograms danach ein anderer exeution plan vorteilhafter wäre, wird kein neuer plan erstellt solange der im shared pool gültig ist.

-j

[mme]

Das habe ich durchaus von Oracle anders verstanden... ?

Beim Parsen des Statements wird meiner Meinung nach der Inhalt einer Bindvariable nicht beachtet. (unabhängig davon ob Histogramme vorhanden sind oder nicht).

Die "normalen" Statistiken erfassen unteranderem wieviele Rows eine Tabelle hat und wieviele unterschiedliche Werte in einer indizierten Spalte vorhanden sind. Habe ich nun 1 Mio Datensätze und 100 Unterschiedlichen Werte in der Spalte so wissen die normalen Statistiken nicht ob ein Wert 999.901 mal vorhanden ist un alle anderen Werte nur einmal oder ob die Daten gleichmäßig verteilt sind.

Dies errechnen Histogramme. Der Inhalt der Bindvariable wird hier nicht ausgewertet, genauso wenig wie der Inhalt eines "hard codierten" Wertes im Statement. Somit ist meiner Meinung nach das nutzen der Histogramme mit Bindvariablen kein Problem, oder habe ich dich falsch verstanden, bzw. ist meine Information an dieser Stelle falsch?

(Habe ich unter anderem von der Schulung Oracle Performance-Tuning von Oracle University, und auch meine Papers/Bücher stellen mir das so dar. Aber wenn man einmal etwas falsch verstanden hat, dreht man sich ja seine Welt ggf....?)

[Jasper]

bei einem hard parse, wenn bind variablen auf histogram-spalten verwendet werden, wird bind peeking verwendet, d.h. der wert der bind variablen wird wie ein literal angesehen. bei nachfolgenden soft-parses werden dagegen sowohl die histogramme als auch die bind variablen ignoriert und der gecachte plan verwendet.

-j