Historische Tabellenstatistiken

Zur Analyse eines Problems mit einem Frequency Histogramm, in dem zwischenzeitlich ein paar relevante Werte fehlten, habe ich dieser Tage nach einer Möglichkeit gesucht, die aktuellen Tabellen-Statistiken mit früheren Werten zu vergleichen, und bin in Uwe Hesses Blog fündig geworden. Das erforderliche Hilfsmittel ist die table function dbms_stats.diff_table_stats_in_history. Dazu ein Beispiel:

-- Anlage einer Testtabelle
create table t_stats
as
select rownum id
     , mod(rownum, 10) col1
  from dual
connect by level <= 10000;

-- erste Statistikerhebung
exec dbms_stats.gather_table_stats(user, 't_stats')

-- Hinzufügen neuer Sätze
insert into t_stats(id, col1)
select rownum id
     , mod(rownum, 10) col1
  from dual
connect by level <= 100000;

commit;

-- zweite Statistikerhebung
exec dbms_stats.gather_table_stats(user, 't_stats')

Mit Hilfe der View user_tab_stats_history kann man die historischen Zeitpunkte der Statistikerfassung bestimmen:

select table_name
     , stats_update_time
  from user_tab_stats_history
 where table_name = 'T_STATS';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------
T_STATS                        30.08.12 20:26:04,607000 +02:00
T_STATS                        30.08.12 20:26:42,486000 +02:00

Und dann kann man die table function mit einer geeigneten Zeitangabe parametrisieren und erhält einen Report:

select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => user,
                    tabname => upper('T_STATS'),
                    time1 => systimestamp,
                    time2 => to_timestamp('30.08.2012 20:26:10','dd.mm.yyyy hh24:mi:ss'),
                    pctthreshold => 0));

REPORT
-------------------------------------------------------------------------------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T_STATS
OWNER         : DBADMIN
SOURCE A      : Statistics as of 30.08.12 20:29:28,935000 +02:00
SOURCE B      : Statistics as of 30.08.12 20:26:10,000000 +02:00
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T_STATS                     T   A   110000     248        8          110000
                                B   10000      21         7          10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   100824  ,000009918 NO   0       5    C102  C30B  110000
                B   10000   ,0001      NO   0       4    C102  C302  10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

Mit diesem Hilfsmittel kann man nachträglich noch die Entscheidungsgrundlagen des CBO bestimmen.

Recursive Sessions

Im Rahmen seiner jüngsten Oracle Hacking Session zum Thema Oracle Parameter Infrastructure (deren Kernpunkte ich hier vielleicht gelegentlich auch noch ausführen werde) weist Tanel Poder auf einen Artikel Recursive Sessions and ORA-00018: maximum number of sessions exceeded hin, dessen Veröffentlichung ich übersehen (oder schon wieder vergessen) hatte.

Darin erklärt er, dass Oracle im Rahmen von DDL-Operationen rekursive Sessions startet, die dazu führen können, dass ein DDL-Kommando in einer bereits angemeldeten Session den (ohne diese Information verblüffenden) Fehler "ORA-00018: maximum number of sessions exceeded" hervorruft. Die rekursiven Sessions erscheinen nicht in V$SESSION, werden aber in V$RESOURCE_LIMIT berücksichtigt.

Block Size und Performance

Rick van Dyke untersucht im Hotsos-Newsletter (von dem ich hoffe, dass seine Web-Adresse dauerhaft erreichbar bleibt) die Rolle der Block Size für die Performance von DML-Operationen und Queries. Die traditionelle Position in dieser Frage lautet dabei:

• kleine Blocks für OLTP: weil hier viel DML anfällt und diese Operationen weniger Probleme bereiten, wenn jeder Block relativ wenige Sätze enthält und das Verhältnis von row-Anzahl zu itl-Einträgen (= interested transaction list) günstig ist.
• große Blocks für DWHs: weil kaum DML erfolgt. Außerdem gibt es die Annahme, dass größere Index-Blocks das BLEVEL des Index verringern und dass größere Table-Blocks die Anzahl der Leseoperationen beim FTS reduzieren.

Relevant ist dabei sicher das contention Argument. Die Verbesserung der Performance durch größere Index- und Table-Blocks lässt sich in van Dykes Test hingegen nicht beobachten: zwar reduzieren sich die consistent gets für den FTS bei größeren Blocks, aber die Laufzeit der Operationen reduziert sich nicht entsprechend (tatsächlich ist in seinem Test der FTS bei 4K Blocks trotz hoher LIO-Anzahl am schnellsten). Für die Indizes zeigt sein Test nahezu keine Wirkung der Blockgrößen. Dabei spielt wahrscheinlich auch eine Rolle, dass das BLEVEL von Indizes in der Regel ohnehin nicht stark variiert (für größere Indizes - in den Systemen, die ich kenne -  normalerweise 2 oder selten 3, sehr selten 4).

DDL_LOCK_TIMEOUT

Bei Tim Hall findet man ein interessantes Feature, das in Version 11.1 eingeführt wurde: das DDL_LOCK_TIMEOUT, mit dessen Hilfe man dem System eine Wartezeit angeben kann, ehe eine DDL-Operation den Fehler "ORA-00054: Ressource belegt und Anforderung mit NOWAIT angegeben oder Timeout abgelaufen" liefert:

-- Session 1:
SQL> create table t1 (id number);

Tabelle wurde erstellt.

SQL> insert into t1 (id) values (1);

1 Zeile wurde erstellt.


-- Session 2:
SQL> alter table t1 add constraint t1_pk primary key (id);
alter table t1 add constraint t1_pk primary key (id)
            *
FEHLER in Zeile 1:
ORA-00054: Ressource belegt und Anforderung mit NOWAIT angegeben oder Timeout abgelaufen

-- die PK-Anlage scheitert, weil Session 1 ein DML-Lock (TM)
-- im lmode 3 (Shared-Row Exclusive) für die Tabelle hält
-- wie man in v$lock sehen kann

SQL> ALTER SESSION SET ddl_lock_timeout=30;

Session wurde geändert.

SQL> alter table t1 add constraint t1_pk primary key (id);

-- Session wartet:
-- wenn Session 1 innerhalb von 30 sec ein commit oder ein rollback durchführt,
-- wird der Constraint in Session 2 erzeugt. Wenn nicht, dann tritt nach 30 sec 
-- der ORA-00054 auf

Tabelle wurde geändert.

Abgelaufen: 00:00:07.87

Nützlich ist diese Option z.B. dann, wenn man versucht DDL auf Objekten durchzuführen, die häufige Änderungen erleben, da sie die endlose Wiederholung erfolgloser Erzeugungsversuche verhindert. Der Parameter kann übrigens auch auf SYSTEM-Ebene (und maximal auf 1.000.000 sec) gesetzt werden.

CLOBs und dbms_stats

In seinem Blog weist Jonathan Lewis darauf hin, dass die Bestimmung der AVG_ROW_LEN für Datensätze mit CLOB-Spalten mit Hilfe von dbms_stats.gather_table_stats nicht so ganz funktioniert, da sie mit 8.1.7.4 unterschätzt und mit 11.2.0.3 überschätzt wird. Das alte ANALYZE-Kommando (das bekanntlich schon lange deprecated ist) liefert für solche Fälle eine korrekte Satzlänge.

Tabibitosan

Vor längerer Zeit hatte ich hier schon mal einen Link auf die Technik Tabibitosan untergebracht, die Aketi Jyuuzou gelegentlich im OTN-Forum vorstellte. Da ich aber gestern auf die Frage nach dem Vorgehen beim Zusammenfassen wiederholter Werte in Intervallangaben auf Anhieb nur antworten konnte: es geht, aber wie, habe ich vergessen - hier noch mal eine kurze Darstellung. Zunächst erstellte ich ein paar Testdaten mit einer Spalte VKP (was Verkaufspreise sein könnten) und einer Datumsangabe:

create table test_vkp
(vkp number
, startdate date);

insert into test_vkp values (2, '01.01.2012');
insert into test_vkp values (2, '02.01.2012');
insert into test_vkp values (2, '03.01.2012');
insert into test_vkp values (3, '04.01.2012');
insert into test_vkp values (3, '05.01.2012');
insert into test_vkp values (3, '06.01.2012');
insert into test_vkp values (3, '07.01.2012');
insert into test_vkp values (3, '08.01.2012');
insert into test_vkp values (2, '09.01.2012');
insert into test_vkp values (2, '10.01.2012');
insert into test_vkp values (3, '11.01.2012');
insert into test_vkp values (3, '12.01.2012');
insert into test_vkp values (4, '13.01.2012');
insert into test_vkp values (5, '14.01.2012');
insert into test_vkp values (2, '15.01.2012');
insert into test_vkp values (2, '16.01.2012');

Ziel ist es nun, die wiederholten Einzelangaben zusammenzufassen und Intervalle zu bilden. Eine einfache Gruppierung mit MIN und MAX wäre dazu nicht in der Lage, da sie das erneute Auftreten eines Wertes nicht berücksichtigen kann:

select vkp
     , min(startdate) min_date
     , max(startdate) max_date
  from test_vkp
 group by vkp
 order by vkp;

VKP MIN_DATE   MAX_DATE
--- ---------- ----------
  2 01.01.2012 16.01.2012
  3 04.01.2012 12.01.2012
  4 13.01.2012 13.01.2012
  5 14.01.2012 14.01.2012

Schon auf den ersten Blick wird deutlich, dass diese Query die maximalen Intervalle liefert, in denen ein VKP-Wert vorkommt. Die Intervalle sind dabei nicht überschneidungsfrei - und absolut nicht das, was ich haben möchte.

Sinnvolle Intervalle kann man in einem solchen Fall mit Analytics bilden. Dabei erzeugt man zunächst eine laufende Nummer über alle Sätze und eine laufende Nummer pro Sub-Gruppe. Wenn man den zweiten Wert vom ersten subtrahiert, erhält man ein Ergebnis, das für die gesamte Gruppe einheitlich ist, und über das man dann gruppieren kann:

select t.*
     , row_number() over(order by startdate) a1
     , row_number() over(partition by vkp order by startdate) a2
     , row_number() over(order by startdate) - row_number() over(partition by vkp  order by startdate) a3
  from test_vkp t;

VKP STARTDATE          A1         A2         A3
--- ---------- ---------- ---------- ----------
  2 01.01.2012          1          1          0
  2 02.01.2012          2          2          0
  2 03.01.2012          3          3          0
  3 04.01.2012          4          1          3
  3 05.01.2012          5          2          3
  3 06.01.2012          6          3          3
  3 07.01.2012          7          4          3
  3 08.01.2012          8          5          3
  2 09.01.2012          9          4          5
  2 10.01.2012         10          5          5
  3 11.01.2012         11          6          5
  3 12.01.2012         12          7          5
  4 13.01.2012         13          1         12
  5 14.01.2012         14          1         13
  2 15.01.2012         15          6          9
  2 16.01.2012         16          7          9

with
basedata as (
select t.*
     , row_number() over(order by startdate) a1
     , row_number() over(partition by vkp order by startdate) a2
     , row_number() over(order by startdate) - row_number() over(partition by vkp order by startdate) a3
  from test_vkp t
)
select vkp
     , min(startdate) min_date
     , max(startdate) max_date
  from basedata
 group by vkp
        , a3
 order by min_date

VKP MIN_DATE   MAX_DATE
--- ---------- ----------
  2 01.01.2012 03.01.2012
  3 04.01.2012 08.01.2012
  2 09.01.2012 10.01.2012
  3 11.01.2012 12.01.2012
  4 13.01.2012 13.01.2012
  5 14.01.2012 14.01.2012
  2 15.01.2012 16.01.2012

Das ist natürlich erst mal ein recht simpler Fall, aber das Verfahren lässt sich ohne Weiteres auf komplexere Fragestellungen erweitern, was im oben erwähnten OTN-Beispiel auch getan wird.

NOT IN und NULLs

Heute Morgen hat mich ein Kollege nach dem berüchtigten Problem von NULL-Werten in NOT IN Vergleichen gefragt und eigentlich wollte ich ihn auf meinen Blog verweisen, musste dann aber feststellen, dass dort kein entsprechendes Beispiel zu finden ist. Dem soll hiermit abgeholfen werden:

Angelegt werden zwei Tabellen mit jeweils 10 Sätzen. Die erste enthält die Werte 1 bis 10, die zweite die Werte 2 bis 10 und an Stelle der 1 einen NULL-Wert. Demnach enthält die Schnittmenge beider Mengen also die Werte 2 bis 10. Nur ein Wert weicht ab.

-- Anlage der Test-Tabellen
drop table t1;
drop table t2;

create table t1
as
select rownum id
  from dual
connect by level <= 10;

create table t2
as
select rownum id
  from dual
connect by level <= 10;

update t2 
   set id = NULL
 where id = 1;   

-- Queries zur Bestimmung der Sätze, aus t1, die in t2 nicht erscheinen
-- NOT IN
select * 
  from t1 
 where t1.id not in (select t2.id 
                       from t2);

Es wurden keine Zeilen ausgewählt

-- NOT EXISTS
select * 
  from t1
 where not exists (select NULL
                     from t2
                    where t2.id = t1.id);

        ID
----------
         1

-- MINUS
select id from t1
minus
select id from t2;

        ID
----------
         1

Demnach liefern MINUS und NOT EXISTS das erwartete Ergebnis - den Wert 1 -, aber NOT IN liefert kein Ergebnis. Verantwortlich dafür ist die logische Behandlung von NULL-Werten in Oracle: NOT IN (NULL) ergibt weder TRUE noch FALSE, sondern unbekannt. Bei AskTom gibt's eine kurze Erklärung zum Thema (dunkel erinnere ich mich, vom Herrn Kyte auch noch ausführlichere Aussagen dazu gelesen zu haben, aber möglicherweise war das in einem seiner Bücher).

Nachtrag 06.02.2013: eine konzise Erklärung des Verhaltens hat gelegentlich Jonathan Lewis gegeben: colx NOT IN (value1, value2, value3) ist logisch äquivalent zu colx != value1 AND colx != value2 AND colx != value3. Wenn einer der Vergleiche NULL liefert ist auch das Gesamtergebnis NULL. Im Fall eines IN ergibt sich eine Verknüpfung von OR-Prädikaten: wenn eines davon NULL wird, bleibt das Gesamtergebnis davon unbeeinflusst.

Rely Constraints

Uwe Küchler liefert in seinem Blog eine schöne Darstellung der Vorteile von Rely Constraints, die man dort verwenden kann, wo - z.B. durch die Logik eines ETL-Prozesses - sichergestellt ist, dass die Daten der Tabelle dem Constraint notwendigerweise entsprechen, so dass sich der Oracle Server die Validierung des Constraints schenken kann. Die Constraints helfen dann dem Optimizer dabei, vereinfachende Transformationen durchzuführen (table elimination etc.). Der Artikel nennt die Voraussetzungen und Beschränkungen des Features und enthält außerdem noch Links auf die Dokumentation und die Aussagen der Herren Kyte, Lewis und Scott zum Thema.

Ein recht wichtiger Punkt bei der Einrichtung von RELY-Constraints ist, dass ein RELY FK immer auf einen RELY PK bezogen sein muss: die Implementierung kann folglich zu relativ umfangreichen Anpassungen führen.

Optimierung durch Histogramm-Löschung

Jonathan Lewis hat in seinem Blog einen interessanten Fall beschrieben, in dem die Löschung von Histogrammen einen positiven Effekt auf die Performance von Zugriffen brachte. Allerdings sind in seinem Beispiel die Histogramme nicht das grundlegende Problem (oder höchstens ein Teil davon), denn sie liefern eigentlich eine korrekte Information, die den CBO dann allerdings auf einen unglücklichen Weg bringt, bei dem massive Loops (in diesem Fall hervorgerufen durch FILTER-Operationen) die Laufzeit erhöhen. Die Ursache für dieses Verhalten ist anscheinend ein Bug beim Costing von (zumindest bestimmten Typen von) Subqueries. Interessant ist der Artikel auch als Beispiel für ein strukturiertes Vorgehen bei der Analyse von SQL-Zugriffsproblemen.

Oracle Sessions beenden

Heute habe ich (erfolglos) versucht zu ermitteln, warum ich für einen User mit DBA-Rolle (in der das Privileg ALTER SYSTEM bekanntlich enthalten ist) beim Versuch eine Session in sqlplus zu killen den Fehler "ORA-01031: Nicht ausreichende Berechtigungen" bekomme:

GRANTED_ROLE                   ADM DEF OS_
------------------------------ --- --- ---
DBA                            YES YES NO

SQL> alter system kill session '231, 24114';
alter system kill session '231, 24114'
*
FEHLER in Zeile 1:
ORA-01031: Nicht ausreichende Berechtigungen

Über den SQL Developer funktioniert der Abbruch von Sessions dabei problemlos und mit SQL_TRACE kann ich (in einer anderen Datenbank) erkennen, dass auch in diesem Fall ein ALTER SYSTEM KILL SESSION 'sid, serial#' immediate; abgesetzt wird. Das Verhalten bleibt mir erst einmal unklar.

Nachtrag 30.08.2012: das Problem war eines der Rollen-Aktivierung. Im login-Script für sqlplus wurde die SELECT_CATALOG_ROLE explizit aktiviert, was die DBA-Rolle dann implizit deaktivierte.

Immerhin habe ich bei Tim Hall noch ein paar interessante Erläuterungen zum Verhalten von ALTER SYSTEM KILL SESSION und ALTER SYSTEM DISCONNECT SESSION gefunden:

• KILL SESSION: "The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible."
  
• DISCONNECT SESSION: "The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause."

Außerdem liefert der Herr Hall auch noch weitere Hinweise zum Abbruch von Prozessen auf OS-Ebene. Aber eine Erklärung für meinen ORA-01031 liefert das natürlich auch nicht.

Mutex Internals

Wenn ich versuchen wollte, das Verhalten von Mutexes genauer zu verstehen, würde ich mich wahrscheinlich bei Andrey Nikolaev informieren, der mal wieder eine Präsentation zum Thema veröffentlicht hat, die eine Menge technischer Details und Vorschläge zur Analyse und zum Tuning dieser Spinlock-Mechanismen enthält.

In seinem Blog hat der Herr Nikolaev zuletzt den Artikel Mutex waits. Part III. Contemporary Oracle wait schemes diversity veröffentlicht, in dem er das Verhalten der Mutexes in 11.2.0.2.2 und 11.2.0.3 erläutert und ein paar hidden parameters erklärt, mit denen sich Mutex Waits beeinflussen lassen. Sein Fazit lautet: "We may choose between four basic wait schemes ranging from superaggressive to having negligible CPU consumption and a variety of parameters to tune them. The next post will discuss when and how use them."

Eine schöne einführende Erklärung der Mutex Waits "cursor: pin s" und "cursor: pin S wait on X" findet man bei Gorjan Todorovski von Pythian, der die Rolle der Mutexes beim Parsen (soft oder hard) recht detailliert erklärt:

• "during the scanning of the cursor children linked-list we need to pin those children with a shared cursor mutex, so they don’t get purged from the shared pool during scanning. It obviously will be longer if the linked list we need to go through is longer. If we have a large number of a particular SQL execution which leads to JUST soft parsing, meaning we successfully reuse cursors, we can experience many brief waits on the cursor: pin S wait event."
• "When we can’t find a suitable match between the cursor children, we need to create a new one. As we said creating a new one, involves hard parsing (syntax and semantic check of the SQL, checking user privileges on objects in the SQL , creating a new exaction plan….). While we do this, we must prevent another session from creating the same type of a cursor child. We also need to prevent other sessions to get this mutex in a shared mode, since it is still not ready for being reused. So we must hold an exclusive lock on the cursor child mutex.  While holding the mutex in X mode, if other sessions are trying to execute the same SQL with the same context (cursor metadata), they will find that a child like that is there but still being held in X mode, so they will wait to get it in a shared (S) mode to reuse it while we see the session waiting on a cursor: pin S wait on X."

NULL is NOT NULL Filter

Martin Widlake zeigt in seinem Blog ein schönes Beispiel dafür, wie der CBO überflüssige Plan-Abschnitte beim Zugriff auf UNION ALL-verknüpfte Views durch die Verwendung von Filter-Prädikaten der Form NULL IS NOT NULL (also: FALSE) ausschalten kann (was an Partition Elimination erinnert).

Besonders interessant finde ich, dass diese Optimierung nicht nur bei einer UNION ALL-Verknüpfung, sondern auch bei Verwendung eines UNION eingesetzt werden kann. In diesem Fall wird die SORT UNIQUE-Operation erst durchgeführt, wenn die Eliminierung überflüssiger Zugriffe durchgeführt wurde.

Constraint-Aktivierungs-Bug mit Interval Partitioning in 11.2.0.3

Noch einen Bug mit Interval Partitioning (diesmal speziell für 11.2.0.3) hat Doug Burns in seinem Blog beschrieben und dafür auch einen Workaround geliefert. Das Problem (Bug 14230768) betrifft die Definition eines PK-Constraints mit der USING INDEX Option, die (in 11.2.0.3) einen ORA-600 hervorruft. Als Workaround dient die Aufspaltung der Operation in zwei Schritte (Index-Anlage, Constraint-Aktivierung). Mein Vermutung ist, dass auch hier wieder ein Problem mit der defered segment creation im Spiel ist (und damit ein Problem der Reihenfolge der Operationen).

ANSI Outer Join

Jonathan Lewis hat vor einigen Wochen einen jener Fälle beschrieben, in denen die ANSI SQL-Syntax ein anderes (und ungünstigeres) Verhalten zeigt als Standard Oracle SQL. Außerdem handelt es sich auch noch um einen jener traurigen Fälle, in denen die Reihenfolge von Bedingungen in der WHERE clause eine Auswirkung auf die interne Optimierung hat.

Kostenlose e-books zum SQL Server

Bernhard Lauber weist in seinem Blog darauf hin, dass man auf der Web-Site von Simple Talk Publishing eine ganze Reihe kostenloser PDF-Versionen von Büchern zum SQL Server finden kann, die einen sehr soliden Eindruck machen.

Auch von Microsoft gibt's allerlei kostenlose e-books, die hier aufgelistet sind.

Zweites CBO Webinar von Randolf Geist

Vor einigen Tagen hat Randolf Geist wieder ein Webinar bei AllThingsOracle gehalten, diesmal zum Thema Oracle Cost-Based Optimizer Advanced. Hier eine stichpunktartige Zusammenfassung diverser interessanter Punkte (ohne Anspruch auf Vollständigkeit und mit unterschiedlich intensiver Beschreibung und Kommentierung).

• Entscheidende Faktoren für die Effizienz von Zugriffsverfahren (wurden bereits im ersten Webinar erläutert):
• Datenmengen
• Clustering der Daten
• Caching der Daten
• Clustering Factor
• Störfaktoren, die die Aussagekraft des Clustering Factor beeinträchtigen
• bei MSSM: multiple freelists, freelist groups; ASSM (der default in aktuellen Releases): diese Optionen dienen zur Vermeidung von contention (da gleichzeitig mehrere Blocks für INSERTs angesteuert werden), aber ihre Verwendung führt auch dazu, dass der CF die tatsächliche Clusterung der Daten nicht mehr adäquat abbildet: auch für stark geclusterte Daten kann der CF sehr hoch werden, da seine Bestimmung nur den Wechsel von Blockzuordnungen berücksichtigt (eine Funktion, die das Verfahren nachbildet, findet man in Christian Antogninis Troubleshooting Oracle Performance, S. 135)
• Partitioning (sollte allerdings kein Problem sein bei lokalen Indizes)
• Shrink-Operationen
• Scripts zur Beantwortung der Frage, ob der CF die tatsächliche Clusterung der Daten sinnvoll repräsentiert, findet man in Randolf Geists Artikel CLUSTERING_FACTOR What-If Analysis.
• Statistiken, Histogramme
• die method_opt FOR ALL INDEX COLUMNS SIZE ... ist in aller Regel Unfug, da sie bewirkt, dass nur für die indizierten Spalten basic column statistics (und histograms) angelegt werden; für alle übrigen Spalten verwendet der CBO dann default-Werte.
• Die Anlage von Histogrammen (=> size > 1) erfordert ein zusätzliches Lesen der Tabelle für jede betroffene Spalte (weshalb Oracle in diesem Fall ein extremes Sampling verwendet - 5500 rows, was Randolf Geist unter anderem auch hier erläutert hat -; und das hat dann natürlich Auswirkungen auf die Qualität der Histogramme; unter Umständen ergibt sich ein nicht deterministisches Verhalten, wenn das Sample bei wiederholtem Aufruf seltene Extremfälle erfasst oder übersieht)
• height balanced histograms basieren immer auf sampling (und können folglich relevante Informationen verpassen)
• die method_opt size auto sorgt dafür, dass frequency histograms  für alle Spalten mit weniger als 255 distinkten Werten angelegt werden, die in WHERE-Bedingungen erscheinen (was nicht unbedingt gewünscht ist)
• gefährlich ist die (für aktuelle Releases als Standard fungierende) Annahme des cbo, dass ein nicht im frequency histogram vorliegender Wert die halbe Wahrscheinlichkeit des seltensten im histogram vorliegenden Werts besitzt: wenn im histogram ausschließlich extrem populäre Werte vorliegen, kann dieser halbierte Wert sehr hoch sein. Einen Test zu diesem Problem hatte ich gelegentlich hier durchgeführt. Eine mögliche Lösung für solche Fälle stellt dynamic sampling dar. Über fix_control kann man auch das in diesem Fall sinnvollere ältere Verhalten des cbo wieder aktivieren, das für nicht im frequency histogram erscheinende Werte eine sehr niedrige Wahrscheinlichkeit annahm.
• sehr große Attribut-Werte stellen die Histogramme vor Probleme, weil in ihnen nur eine beschränkte Genauigkeit zur Verfügung steht. Weitere Erläuterungen zu diesem Thema findet man (natürlich) in Jonathan Lewis' cbo-Buch und in Randolf Geists Blog.
• height balanced histograms können ebenfalls zu Instabilität führen, da die Bestimmung eines Werts als popular (=> umfasst mehr als einen Bucket) bei wiederholter Statistikerfassung oder geringfügigen Datenänderungen unterschiedliche Ergebnisse bringen kann.
• nützlich sind height balanced histograms zur Erkennung großer Lücken in ranges (und damit auch zur Bestimmung der cardinality von range-Bedingungen).
• Datentypen
• int statt date nimmt dem CBO die Möglichkeit, die spezielle Semantik von Datumswerten zu berücksichtigen
• implizite Typkonvertierungen können Probleme hervorrufen

Level für Event 10046

Christian Antognini liefert eine Liste mit den Level-Angaben, die für Trace Event 10046 (aka SQL Trace) zur Verfügung stehen. Seit Release 11 ist diese Liste etwas länger geworden.