direct path read temp mit cnt=1

Beim Untersuchen der Performance von Analytics bin ich dieser Tage einmal mehr auf das Phänomen gestossen, dass für größere Sortieroperationen zahlreiche direct path read temp-Events mit einem cnt=1 Wert angezeigt werden, obwohl man hier eher Multiblock-I/O erwarten würde. Jonathan Lewis hat dieses Problem gelegentlich genauer beleuchtet und in einem Kommentar zu diesem Artikel, nennt Greg Rahn den Bug 9041800 - Window function operation using IO inefficiently, in dessen Beischreibung die Verwendung des Events 10032 vorgeschlagen wird:

The information provided by event 10032 can be used to check what the average size of the reads is. The trace file will contain a section such as the following:

Size of read slots for merge phase        57344
Number of read slots for merge phase      26
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               1557
Number of blocks read synchronously       10063

The number of blocks read can be divided by the number of reads to get the number of blocks per read operation. If it is low (close to 1 block per read), then you may be encountering this problem.

Dazu wieder ein sehr übersichtlicher Test in 11.1.0.7, in dem ich nur das Event aktiviere und eine harmlose CTAS-Operation mit Analytics durchführe

alter session set events '10032 trace name context forever, level 1';

drop table test_analytics_1;
create table test_analytics_1
as
select col1
     , col2
     , col3
     , col4
     , col_day_of_month
     , median (val1) over ( partition by col1 ) median_val1
     , val1
  from test_analytics;

Im Trace-File finden sich dazu folgende Angaben:

---- Sort Parameters ------------------------------
sort_area_size                    2949120
sort_area_retained_size           2949120
sort_multiblock_read_count        15
max intermediate merge width      4

    ---- Direct Read Statistics ------
Size of read slots for merge phase        245760
Number of read slots for merge phase      8
Size of read slots for output             49152
Number of read slots for output           24
Number of direct sync reads               12141
Number of blocks read synchronously       12185

Ich verstehe den MOS-Hinweis so, dass die Number of blocks read synchronously/Number of direct sync reads knapp über 1 liegt, wenn man diesen Bug trifft - und das ist dann hier offenbar der Fall.

Memory-Verwendung bei Hash-Aggregations

Randolf Geist zeigt in seinem Blog (wie üblich sehr ausführlich und mit umfassenden Test-Scripts versehen), dass ältere Oracle Versionen (vor 10.2.0.5 bzw. 11.2.0.1) Probleme mit der korrekten Bestimmung des für HASH-Sortierungen erforderlichen Arbeitsspeichers haben, wenn automatisches PGA-Management im Spiel ist:

If your database uses automatic PGA management then for the hash aggregation HASH GROUP BY / HASH UNIQUE operations every initial execution that is based on bad cardinality estimates potentially has a problem because it might not make efficient use of the available memory.

The same applies to applications that share cursors, however in that case only the initial execution after re-optimization / invalidation is affected, subsequent executions benefit from the workarea statistics feedback mechanism.

Furthermore in 10g versions below 10.2.0.5 and 11g versions below 11.2.0.1 the initial execution of a HASH UNIQUE operation ignores the cardinality estimates and will always be based on minimum assumptions.

So you might want to carefully check the runtime execution statistics of your critical hash aggregations.

Für DWH-Operationen ist das auf jeden Fall ein Effekt, den man im Auge behalten muss.

Performance von Analytics

Tom Kyte pflegt zu sagen: "Analytics rock Analytics roll" - und damit hat er völlig recht, da man mit Hilfe analytischer Funktionen ganz erstaunliche Dinge machen kann. Dass Analytics bei großen Sortierungen auch Ärger machen können, hat Jonathan Lewis vor einiger Zeit gezeigt (einen Link dazu findet man auch schon an anderer Stelle hier im Blog). Heute habe ich ein Beispiel gesehen, in dem eine CTAS-Operation auf Basis einer einzelnen Tabelle, die ohne Analytics < 10 sec. lief, nach dem Einbau von vier Kennzahlen mit Analytics ca. 3 min benötigte. Dass das nicht nur an der Menge der zu sortierenden Daten liegen kann, wird deutlich, wenn man die Operation als Join der Basistabelle an gruppierte Zwischenergebnisse umformuliert - dadurch sinkt die Laufzeit im gegebenen Fall unter 2 min.

Den Fall habe ich zum Anlaß genommen, einen kleinen Test zu basteln - bei dem ich aber nicht allzu tief in die Details absteigen wollte:

• Version: 11.1.0.7
• Blockgröße: 16 K
• Noarchivelog
• ASSM

-- Anlage einer Testtabelle, die etwa der Struktur des beobachteten Falls entspricht
create table test_analytics
as
select t1.*
     , t2.col_day_of_month
     , round(dbms_random.value(10, 1000)) val1
     , round(dbms_random.value(10, 1000)) val2
  from (select level col1
             , round(dbms_random.value(1, 40)) col2
             , round(dbms_random.value(1, 40)) col3
             , round(dbms_random.value(1, 10000)) col4
          from dual
       connect by level <= 100000) t1
     , (select level col_day_of_month
          from dual
       connect by level <= 31) t2;

Im Rahmen des Tests wurde dann folgendes Script ausgeführt, in dem jeweils ein paar Parametrisierungen angepasst wurden:

-- Parameter ... 
exec DBMS_MONITOR.SESSION_TRACE_ENABLE()

drop table test_analytics_0;
create table test_analytics_0
as
select col1
     , col2
     , col3
     , col4
     , col_day_of_month
  -- , median (val1) over ( partition by col1 ) median_val1
     , val1
  from test_analytics;

drop table test_analytics_1;
create table test_analytics_1
as
select col1
     , col2
     , col3
     , col4
     , col_day_of_month
     , median (val1) over ( partition by col1 ) median_val1
     , val1
  from test_analytics;

drop table test_analytics_2;
create table test_analytics_2
as
select col1 
     , col2
     , col3
     , col4
     , col_day_of_month
     , median (val1) over ( partition by col1 ) median_val1
     , median (val2) over ( partition by col1 ) median_val2
     , val1
  from test_analytics;
  
exec DBMS_MONITOR.SESSION_TRACE_DISABLE()

exit

Geprüft wurden folgende Varianten:

• Standardverhalten (ohne weitere Parametrisierung)
• manuelle Setzung von SORT_AREA%_SIZE-Parameter +_sort_multiblock_read_count = 16
• Setzung von _newsort_enabled=false

Heraus kam dabei Folgendes:

1. Standard:

• Laufzeit/Gesamt: 56.96
• Laufzeit/test_analytics_0: 3.10 sec
• Laufzeit/test_analytics_1: 18.82 sec
• Laufzeit/test_analytics_2: 34.90 sec

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.01       0.02          0          3          0           0
Execute     15     52.43      56.94      62757      20289      27016     9300009
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29     52.44      56.96      62757      20292      27016     9300009

Misses in library cache during parse: 7

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      14        0.00          0.00
  SQL*Net message from client                    14        0.00          0.00
  reliable message                                3        0.00          0.00
  log file sync                                   4        0.00          0.00
  db file sequential read                       272        0.00          0.00
  db file scattered read                        393        0.00          0.40
  control file sequential read                   15        0.00          0.00
  direct path write                            1243        0.03          1.81
  direct path sync                                5        0.29          0.77
  enq: RO - fast object reuse                     2        0.03          0.06
  direct path write temp                       3259        0.02          3.79
  direct path read temp                       86146        0.00          1.33

Das unformatierte Trace-File enthält sehr viele WAIT-Angaben direct path read temp mit cnt=1, was mich an die hier beobachteten Effekte erinnert.

2. manuelles PGA-Management

alter session set WORKAREA_SIZE_POLICY=Manual;
alter session set sort_area_size = 500000000;
alter session set sort_area_retained_size = 500000000;
-- zur Sicherheit doppelt 
alter session set sort_area_size = 500000000;
alter session set sort_area_retained_size = 500000000;
alter session set "_sort_multiblock_read_count" = 16;

• Laufzeit/Gesamt: 34.77 sec
• Laufzeit/test_analytics_0: 3.06 sec
  
• Laufzeit/test_analytics_1: 12.14 sec
  
• Laufzeit/test_analytics_2: 19.51 sec

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.00       0.00          0          3          0           0
Execute     15     32.93      34.77      19132      20289      26962     9300009
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29     32.94      34.77      19132      20292      26962     9300009

Misses in library cache during parse: 7
Misses in library cache during execute: 1 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      14        0.00          0.00
  SQL*Net message from client                    14        0.00          0.00
  db file sequential read                       216        0.00          0.00
  db file scattered read                        388        0.00          0.40
  control file sequential read                   15        0.00          0.00
  direct path write                            1252        0.01          1.72
  direct path sync                                3        0.29          0.76
  log file sync                                   4        0.03          0.07
  enq: RO - fast object reuse                     1        0.00          0.00

Auffällig ist zunächst, dass das raw trace file < 1 MB ist, im Fall 1 aber ca. 11 MB umfasste. Darüber hinaus spart sich diese Variante die ganzen direct path read temp/direct path write temp Waits.

3. _newsort_enabled auf false

alter session set "_newsort_enabled" =false;

• Laufzeit/Gesamt: 56.69 sec
  
• Laufzeit/test_analytics_0: 3.10 sec
  
• Laufzeit/test_analytics_1: 18.65 sec
  
• Laufzeit/test_analytics_2: 34.78 sec

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.00       0.00          0          3          0           0
Execute     15     52.32      56.69      62211      20291      27036     9300009
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29     52.32      56.69      62211      20294      27036     9300009

Misses in library cache during parse: 6

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      14        0.00          0.00
  SQL*Net message from client                    14        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: RO - fast object reuse                     3        0.03          0.07
  log file sync                                   5        0.00          0.00
  db file sequential read                       336        0.00          0.01
  db file scattered read                        354        0.02          0.40
  control file sequential read                   15        0.00          0.00
  direct path write                            1254        0.02          1.69
  direct path sync                                5        0.29          0.77
  direct path write temp                       3219        0.02          3.76
  direct path read temp                       86146        0.00          1.31

Erinnert sehr stark an Fall 1.

Aus Zeitgründen nur ein ganz kurzes Fazit:

• manuelles PGA-Management beschleunigt die Operation deutlich (ist aber für Parallelisierung wohl eher ungeeignet, da ja jede workarea-Operation auf die mit sort_area_size gesetzten Speichermenge zugreifen darf)
• die Beschleunigung ergibt sich offenbar daraus, dass die Sortierung im Speicher erfolgen kann, wie das Fehlen der write/read temp Waits andeutet (und der deutliche Unterschied bei den disk reads)
• _newsort_enabled scheint gegenüber dem Standard keinen Unterschied zu bringen. Zu prüfen wäre noch, wie der Parameter in der Instanz gesetzt ist
• ich kann mich nicht mehr erinnern, ob die WAIT-Angaben direct path read temp mit cnt=1 ein Problem sind (d.h., ob hier größere Blöcke erscheinen sollten)
• beim manuellen PGA-Management muss man darauf achten, die ALTER SESSION-Kommandos doppelt abzusetzen, da das System den Hinweis sonst nicht ernst nimmt... (ich falle darauf regelmäßig rein)

Gelegentlich vielleicht mehr dazu...

Nachtrag 30.01.2011: die direct path read temp mit cnt=1 gehören vermutlich in den Kontext von Bug 9041800 - Window function operation using IO inefficiently [ID 9041800.8]: "If an operation involving a sort shows poor IO throughput or spends a lot of time doing reads from the temp tablespace, you may be encountering this problem." Dort wird empfohlen ein 10032er Trace zu erstellen, was ich vielleicht auch noch machen werde.

Darstellung von HIGH_VALUE/LOW_VALUE Werten

Nicht neu, aber sehr nützlich ist Greg Rahns Hinweis, wie man die HIGH_VALUE- und LOW_VALUE-Werte aus den %_TAB_COL_STATISTICS-Views in lesbare Inhalte verwandeln kann.

Trace Events

Neben PSOUG liefert auch Julian Dyke eine Liste verfügbarer Trace Events. Charles Hooper hat gerade eine ausführliche Erläuterung zu Event 10200 geliefert, mit dem man Consistent Reads dumpen kann.

Bei PSOUG heißt es übrigens: "TKPROF stands for Transient Kernel Profiler".

INSERT-Performance (Indexes)

Dies ist eine Fortsetzung. Was bisher geschah und auch die Rahmenbedingungen der Tests findet man hier.

Dass AUTOTRACE wahrscheinlich nicht das ideale Tool ist, um die Details von Undo- und Redo-Nutzung zu bestimmen, sollte mir eigentlich klar sein - deshalb hier noch die zweite Meinung von v$sesstat (abgefragt über ein Snapshot-Script wie jenes, das Tom Kyte in Expert Oracle Database Architecture an verschiedenen Stellen verwendet). Zunächst noch mal der Vergleich eines normalen INSERTs mit einem INSERT /*+ APPEND */ - hier eine Auswahl der Statistiken, für die die größten Abweichungen für die beiden Ausführungen vorliegen:

NAME                                   Noappend       Append         Diff
-------------------------------------------------------------------------
cell physical IO interconnect bytes   290111488    650493952   -360382464
physical IO disk bytes                290111488    650493952   -360382464
physical write bytes                          0    324386816   -324386816
physical write total bytes                    0    324386816   -324386816
physical read total bytes             290111488    326107136    -35995648
physical read bytes                   290111488    326025216    -35913728
session pga memory                            0       524288      -524288
redo size for direct writes                   0        64880       -64880
db block gets direct                          0        19799       -19799
physical writes                               0        19799       -19799
physical writes direct                        0        19799       -19799
physical writes non checkpoint                0        19799       -19799
physical reads cache prefetch             17275        19567        -2292
physical reads                            17707        19899        -2192
physical reads cache                      17707        19899        -2192
table scan blocks gotten                  19969        19966            3
table scan rows gotten                  4000012      4000003            9
consistent gets                           59162        21165        37997
consistent gets from cache                59162        21165        37997
db block gets from cache (fastpath)       45095         1286        43809
redo entries                             108925         3088       105837
db block changes                         147646         2699       144947
db block gets                            175714        23063       152651
db block gets from cache                 175714         3264       172450
session logical reads                    234876        44228       190648
undo change vector size                10538312        81740     10456572
redo size                             318279636       372380    317907256

Laufzeit (sec)                            12.21         7.37

Jetzt lege ich einen Index an

create index TEST_INSERT_idx1 on TEST_INSERT(RN);

Und führe anschließend wieder die beiden INSERT-Varianten durch:

NAME                                   Noappend       Append         Diff
-------------------------------------------------------------------------
cell physical IO interconnect bytes   318701568   1165656064   -846954496
physical IO disk bytes                318701568   1165656064   -846954496
physical write bytes                          0    581124096   -581124096
physical write total bytes                    0    581124096   -581124096
physical read total bytes             318701568    584531968   -265830400
physical read bytes                   318701568    584368128   -265666560
sorts (rows)                                 23      4000020     -3999997
redo size for direct writes                   0        65352       -65352
physical writes                               0        35469       -35469
physical writes direct                        0        35469       -35469
physical writes non checkpoint                0        35469       -35469
db block gets direct                          0        19799       -19799
physical reads                            19452        35667       -16215
physical reads direct                         0        15774       -15774
physical reads direct temp tablespace         0        15774       -15774
table scan blocks gotten                  19969        19966            3
table scan rows gotten                  4000012      4000003            9
consistent gets                           81651        30552        51099
consistent gets from cache                81651        30552        51099
db block gets from cache (fastpath)      117353        30129        87224
redo entries                             235015        60649       174366
session pga memory                       131072      -131072       262144
db block changes                         388343       116627       271716
db block gets                            444170       149641       294529
db block gets from cache                 444170       129842       314328
session logical reads                    525821       180193       345628
undo change vector size               171275800    124524096     46751704
redo size                             656132736    262187604    393945132

Laufzeit (sec.)                           28.86        28.07

Die Auswahl der Statistiken ist eher willkürlich (unter anderem deshalb, weil es hier nicht besonders komfortabel ist, echte Tabellen einzubauen), aber ein paar Ergebnisse sind recht deutlich:

• ohne Indizes ist die Variante mit APPEND sehr viel schneller (hier 7,37 sec zu 12,21 sec)
• mit Index benötigen beide Varianten nahezu die gleiche Zeit
• die APPEND-Variante ohne Index benötigt sehr viel weniger Redo und Undo als die Variante ohne APPEND (jeweils um ~ 1% des Vergleichswerte)
• mit Index steigt der undo-Wert der APPEND-Version auf über 70%, der redo-Wert auch ca. 40% der Variante ohne APPEND
• offenbar lassen sich undo und redo für die Tabellenoperation reduzieren, nicht aber für die zugehörigen Änderungen am Index. Das entspricht den Beobachtungen zum Thema, die ich vor ein paar Jahren hier vermerkt hatte. Anders als dort behauptet, sehe ich allerdings auch keine signifikanten Unterschiede zwischen dem rebuild eines Index mit logging oder nologging.
• Erwartungsgemäß fordert die APPEND-Operation mehr physikalische Lese- und Schreiboperationen, da sie den Buffer Cache umgehen kann.
• der Neuaufbau des Index auf der gefüllten Tabelle benötigt übrigens ca. 10 sec, so dass sich wieder mal erweist, dass man Indizes vor Masseninserts auf unusable setzen und nach der Operation neu aufbauen lassen sollte (sofern das möglich ist). Ein Beispiel dafür, wie das mit partitionierten Tabellen funktioniert, liefert Tom Kyte.

Nachtrag vom 27.01.2011: Hier noch ein paar Zahlen zu den Effekten von INSERTs mit APPEND und vor oder nach der Operation angelegten Indizes:

-- das verwendete Testscript enthielt jeweils dbms_monitor-Aufrufe
-- und die Ergebnisse stammen aus dem Trace-File

drop table test_insert;
create table test_insert
as
select *
  from test_insert_source
 where 1 = 0;

-- create index TEST_INSERT_idx1 on TEST_INSERT(col1);

-- create index TEST_INSERT_idx2 on TEST_INSERT(col2);

-- create index TEST_INSERT_idx3 on TEST_INSERT(col3);

-- create index TEST_INSERT_idx4 on TEST_INSERT(col4);

exec DBMS_MONITOR.SESSION_TRACE_ENABLE()

create index TEST_INSERT_idx1 on TEST_INSERT(col1);
create index TEST_INSERT_idx2 on TEST_INSERT(col2);
create index TEST_INSERT_idx3 on TEST_INSERT(col3);

insert into test_insert
select *
  from test_insert_source;
  
exec DBMS_MONITOR.SESSION_TRACE_DISABLE()

exit  

Operation   Indizes   Indexaufbau  Laufzeit       disk      query    current
----------------------------------------------------------------------------
NOAPPEND          0                   11.47      20026      59841     177384
APPEND            0                    9.57      19910      21691      24170
NOAPPEND          1    vor Insert     66.62      16127      81226   11341931
NOAPPEND          1   nach Insert     21.23      39815      98914     181720
APPEND            1    vor Insert     31.11      33715      30191     139920
APPEND            1   nach Insert     16.56      44668      41178      29090
NOAPPEND          2    vor Insert    120.93      18454     100438   22599704
NOAPPEND          2   nach Insert     31.88      55866     118926     188505
APPEND            2    vor Insert     55.58      47587      37120     255288
APPEND            2   nach Insert     27.57      69387      61473      35848
NOAPPEND          3    vor Insert    201.21      20689     119727   34016033
NOAPPEND          3   nach Insert     45.20      72652     139550     194531
APPEND            3    vor Insert     85.94      59640      45677     374341
APPEND            3   nach Insert     37.79      94093      82002      43662

INSERT-Performance (APPEND-Hint)

Mal wieder ein kleiner Konzepttest, allerdings einer, von dessen Plausibilität ich selbst nicht zu 100% überzeugt bin. Grundsätzlich geht es darum, zu bestimmen, wie groß der Effekt von Indizes auf die INSERT-Performance ist. Ich erinnere mich, dass Tom Kyte in (mindestens) einem seiner Bücher solche Tests durchgeführt hat, aber man wird das Rad ja wohl noch mal neu erfinden dürfen.

Hier noch ein paar Rahmenbedingungen (die ich sonst gerne verschweige, was eigentlich ziemlich unklug ist...)

• Version 11.1.0.7
• die Datenbank befindet sich im Noarchivelog-Modus
• Blockgröße 16K
• ASSM-Tablespace

Zunächst lege ich mir eine Datenquelle (Tabelle) an, aus der ich meine eigentliche Testtabelle über INSERT ... AS SELECT fülle. Durch die Verwendung der Tabelle will ich vermeiden, in den Ergebnissen die Performance der Satzgenerierung über die CONNECT BY LEVEL-Operation zu messen. Allerdings bin ich relativ sicher, dass es noch eine ganze Reihe weiterer Seiteneffekte gibt, die ich hier komplett vernachlässige, aber vielleicht fallen sie mir im Rahmen der Test ja noch ein.

-- Quelltabelle mit Testdaten
drop table test_insert_source;
create table test_insert_source
as
select rownum rn
     , mod(rownum , 2) col1
     , mod(rownum , 4) col2
     , mod(rownum , 8) col3
     , mod(rownum , 16) col4
     , lpad('*', 50, '*') col_pad
  from dual
connect by level <= 1000000;

-- mehrfache Verdoppelung der Satzanzahl
insert into test_insert_source
select *
  from test_insert_source;
 insert into test_insert_source
 

select *
  from test_insert_source;
 insert into test_insert_source

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST_INSERT_SOURCE')
 

select count(*) 
  from test_insert_source;
  
  COUNT(*)
----------
   4000000  

 
Also eine Quelle mit 4.000.000 Sätzen - laut USER_SEGMENTS sind das ca. 320 MB. Jetzt lege ich noch eine leere Testtabelle an, in die diese Sätze übertragen werden:

drop table test_insert;
create table test_insert
as
select *
  from test_insert_source
 where 1 = 0;

Nun zu den Tests:

Fall 1: Logging 

insert into test_insert
select *
  from test_insert_source;

4000000 Zeilen wurden erstellt.

Abgelaufen: 00:00:08.50

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1139762557

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |  4000K|   255M|  6148   (1)| 00:04:13 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT        |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | TEST_INSERT_SOURCE |  4000K|   255M|  6148   (1)| 00:04:13 |
-----------------------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
       6004  recursive calls
     176803  db block gets
      59553  consistent gets
      11716  physical reads
  317893156  redo size
        760  bytes sent via SQL*Net to client
        833  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
    4000000  rows processed

Auffällig ist zunächst mal, dass die Rows-Angabe akkurat ist, aber die Bytes zu niedrig geschätzt werden, was wahrscheinlich mit der AVG_ROW_LEN=67 zusammenhängt, die DBMS_STATS ermittelt hat ((67 * 4000000) / 1024 / 1024 = 255,584717) - aber das nur am Rande. Die größte Zahl hier ist die redo size - und da die Daten ja komplett nutzlos sind, schaue ich mir mal an, was passiert, wenn ich das Logging für die Tabelle abschalte:

Fall 2: Nologging 

truncate table TEST_INSERT;
alter table TEST_INSERT nologging;

insert into test_insert
select *
  from test_insert_source; 

4000000 Zeilen wurden erstellt.

Abgelaufen: 00:00:09.70

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1139762557

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |  4000K|   255M|  6148   (1)| 00:04:13 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT        |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | TEST_INSERT_SOURCE |  4000K|   255M|  6148   (1)| 00:04:13 |
-----------------------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
       5009  recursive calls
     175689  db block gets
      59063  consistent gets
      19961  physical reads
  317771112  redo size
        760  bytes sent via SQL*Net to client
        833  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
    4000000  rows processed 

Scheint nicht viel geholfen zu haben. Aber da hilft mal wieder Tom Kyte mit seiner Matrix der Effekte von Nologging (die ich mir schon ziemlich oft angesehen habe, aber offenbar nicht merken kann):

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

Offenbar ist Nologging für eine Noarchivelog-DB irrelevant. Da ich gerade keine Archivelog-DB zur Hand habe, muss dieser Test demnach verschoben werden.

Fall 3: APPEND

truncate table TEST_INSERT;
alter table TEST_INSERT logging;

insert /*+ append */ into test_insert
select *
  from test_insert_source;

Abgelaufen: 00:00:04.37

Ausführungsplan
----------------------------------------------------------
ERROR:
ORA-12838: Objekt kann nach paralleler Änderung nicht gelesen/geändert werden


SP2-0612: Fehler beim Generieren von AUTOTRACE EXPLAIN Bericht

Statistiken
----------------------------------------------------------
       4861  recursive calls
      23060  db block gets
      21167  consistent gets
      11701  physical reads
     372228  redo size
       1552  bytes sent via SQL*Net to client
        999  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    4000000  rows processed

Auffällig ist:

• der ORA-12838-Fehler bei der Erzeugung des Zugriffsplans (wobei tatsächlich keine Parallelisierung im Spiel ist, wie man über DBMS_XPLAN.DISPLAY_CURSOR oder SQL_TRACE sehen kann)
• die Laufzeit sinkt (gegenüber Fall 1) von 8,50 sec. auf 4,37 sec. Also etwa auf die Hälfte.
• die redo size sinkt dramatisch (< 400K gegenüber > 300M)
• auch die Anzahl von db block gets, consistent gets, physical reads reduziert sich stark. 

Autotrace ist hier nicht das geeignete Tool, aber ein SQL_TRACE zeigt für den APPEND-Fall Wait-Events wie direct path write und direct path sync, während der Fall ohne APPEND vor allem auf db file scattered read und log events (log file switch completion, log buffer space) wartet. Das zeigt einerseits, dass die Leseoperationen im APPEND-Fall erwartungsgemäß am Buffer Cache vorbei und über direct path Operationen laufen (was auch die Ursache für den ORA-12838-Fehler ist) - und andererseits, dass der Performance-Unterschied zwischen den beiden Fällen zumindest zum Teil von der redo-Generierung her kommt. Ich messe also offenbar mehrere Dinge, die nicht unmittelbar miteinander zu tun haben.

Damit habe ich also klar das Thema verfehlt... - und angesichts dieses Ergebnisses - und der Länge dieses Eintrags - vertage ich die Untersuchung des Effekts von Indizes auf einen späteren Zeitpunkt ...

Kosten für FTS mit IN-List

Im Kapitel 3 seines CBO-Buchs (S. 45 ff.) untersucht Jonathan Lewis die Cardinality-Schätzungen des CBO bei IN-Listen und entdeckt dabei allerlei kleinere Unterschiede zwischen den betrachteten Releases - vor allem hinsichtlich des Umgangs mit Werten, die außerhalb der bekannten Werte-Ranges einer Spalte liegen. Deshalb prüfe ich hier mal das Verhalten von 11.2.0.1 mit ähnlichen Beispielen:

-- Tabelle mit 120.000 Sätzen, jeweils 10.000 Sätze pro mon_col-Wert
create table test_inlist
as
select rownum pk_col
     , mod(rownum, 12) mon_col
     , lpad('*', 100, '*') pad_col
  from dual
connect by level <= 120000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST_INLIST', estimate_percent=>dbms_stats.auto_sample_size)

-- Statistiken für mon_col
select column_name
     , num_distinct
     , num_nulls
     , density
     , num_buckets
  from user_tab_columns
 where table_name = 'TEST_INLIST'
   and column_name = 'MON_COL';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
MON_COL                                  12          0 ,083333333           1

Also die erwartete Density von 1/12 und offenbar keine Histogramme im Spiel (die in diesem Fall aber auch irrelevant wären, da die Daten ja völlig gleichverteilt sind). Jetzt die unterschiedlichen Testfälle mit folgender Testquery:

select count(*)
  from test_inlist
 where mon_col in (...)

Überprüft werden die Fälle, die schon in Jonathan Lewis betrachtet wurden:

• mon_col in (25): Wert oberhalb des high_value
• mon_col in (4, 4): wiederholte Werte
• mon_col in (3, 25): Werte innerhalb und außerhalb des bekannten Bereichs
• mon_col in (3, 25, 26): dito
• mon_col in (3, 25, 26, 27, 28): dito
• mon_col in (3, 25, 25, 25, 26, 26, 26): dito, mit Wiederholungen
• mon_col in (3, 25, null): werden NULL-Werte erkannt
• den Test mit Bindewerten lasse ich erst mal aus

Hier nur die Ergebnisse:

Prädikat                               |        rows      |   Kommentar
---------------------------------------|------------------|-------------------------------------
mon_col in (25)                        |           1      |   ok
mon_col in (13)                        |        8182      |   jenseits des bekannten Bereichs
                                       |                  |   sinkt die Schätzung nicht sofort
                                       |                  |   auf 1, sondern langsam ab, wobei
                                       |                  |   ein Zusammenhang mit der Anzahl
                                       |                  |   distinkter Spalten-Werte besteht;
                                       |                  |   dieses Verhalten beschreibt Lewis
                                       |                  |   auf S. 49 bereits für 10.1.0.2
mon_col in (4, 4)                      |       10000      |   ok
mon_col in (3, 25)                     |       10000      |   ok
mon_col in (3, 25, 26)                 |       10001      |   wahrscheinlich liegt schon das
                                       |                  |   Ergebnis für 25 zwischen 0 und 1;
                                       |                  |   in der Summe kommt man für 25 und
                                       |                  |   26 dann über 1
mon_col in (3, 25, 26, 27, 28)         |       10002      |   bestätigt die Vermutung
mon_col in (3, 25, 25, 25, 26, 26, 26) |       10001      |   Wiederholungen scheinen immer
                                       |                  |   erkannt zu werden
mon_col in (3, 25, null)               |       20000      |   NULL-Werte werden offenbar immer
                                       |                  |   noch nicht korrekt bewertet

Offenbar hat der cbo set 10.1 allerlei gelernt, aber mit NULL-Werten hat er anscheinend immer noch Probleme.

Erlaubte Werte für Initialisierungsparameter

Tanel Poder hat schon vor längerer Zeit ein Script bereitgestellt, mit dessen Hilfe man die möglichen Werte für multioption parameter aus fixed tables auslesen kann. Da es auf X$KSPVLD_VALUES zugreift, funktioniert es auch für hidden parameters (mit führendem Underscore), aber deshalb kann es nur von SYS (oder sonst jemandem, der die X$-Tabellen sehen kann) ausgeführt werden.

Invalidierung von Hints

In Charles Hoopers Blog findet sich ein Beispiel dafür, dass Kommentare in einem Hint dazu führen können, dass ein Hint nicht berücksichtigt wird - wirksam wird dieser Effekt, wenn ein solcher Kommentar vor dem Hint erscheint. Der Effekt wird durch reserved words hervorgerufen - was halbwegs einleuchtet - aber auch durch einzelne Buchstaben (A, D, aber nicht B, C) und durch diverse Zeichen (wie: ;:.,-). Vermutlich ist es tatsächlich sinnvoll, in Hint-Kommentaren keine erläuternden Kommentare unterzubringen.

Jonathan Lewis hat auch noch einen kurzen Beitrag zum Thema geschrieben.

FTS cost

Dieser Tage lese ich noch mal Cost-Based Oracle von Jonathan Lewis - und diesmal glaube ich sogar, einigermaßen genau zu verstehen, wovon der Mann da redet...

Um mir die Details besser merken zu können, spiele ich ein paar der Beispiele hier nach:

-- Setzen der system stats
begin
   dbms_stats.set_system_stats('CPUSPEED', 500);
   dbms_stats.set_system_stats('SREADTIM', 5.0);
   dbms_stats.set_system_stats('MREADTIM', 30.0);
   dbms_stats.set_system_stats('MBRC', 12);
end;
/

-- Anlage eines TS ohne ASSM
CREATE TABLESPACE test_ts
datafile 'C:\ORADATA\TESTDB\TEST_TS01.DBF' size 100M autoextend on maxsize unlimited
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;

-- Anlage einer Testtabelle + Statistikerhebung
create table test tablespace test_ts
as
select case when rownum <= 10000 then mod(rownum, 10) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test')

-- Infos zur Tabelle
SQL> exec show_space('TEST')

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.19
SQL> set serveroutput on
SQL> exec show_space('TEST')
Free Blocks.............................               0
Total Blocks............................          15,360
Total Bytes.............................     125,829,120
Total MBytes............................             120
Unused Blocks...........................             207
Unused Bytes............................       1,695,744
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          14,464
Last Used Block.........................             817

PL/SQL-Prozedur erfolgreich abgeschlossen.

Nach Jonathan Lewis errechnen sich die Kosten in diesem Fall als:

(Anzahl_Blocks / MBRC) * (MREADTIM/SREADTIM)

also:

SQL> select (15360/12) * (30/5) from dual;

(15360/12)*(30/5)
-----------------
             7680

-- Testquery
SQL> select count(*) from test;

Abgelaufen: 00:00:00.06

Ausf³hrungsplan
--------------------------------------------------------
Plan hash value: 1950795681

--------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  7680   (2)|
|   1 |  SORT AGGREGATE    |      |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  7680   (2)|
--------------------------------------------------------

Das Ergebnis entspricht also genau den Erwartungen. Jetzt noch eine kleine Variante: ich setze MBRC auf 16:

exec dbms_stats.set_system_stats('MBRC', 16)

SQL> select (15360/16) * (30/5) from dual;

(15360/16)*(30/5)
-----------------
             5760


SQL> select count(*) from test;

Abgelaufen: 00:00:00.07

Ausf³hrungsplan
---------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5786   (2)|
|   1 |  SORT AGGREGATE    |      |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  5786   (2)|
---------------------------------------------------------

Keine große Abweichung, aber doch eine, über die ich noch nachdenken muss.

Nachtrag 29.04.2011: Bei erneuter Untersuchung des Themas ist mir der Rechenfehler aufgefallen: ich hatte die Blocks oberhalb der HWM eingerechnet. Mit der richtigen Rechnung erhält man dann auch das passende Ergebnis: (15360-207)/16 * 30/5 = 5682,375. Das entspricht dem autotrace-Ergebnis bei Auswahl eines extremen CPU-Speeds (ein Trick, den man in Randolf Geists Artikeln zum Thema findet):

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5682   (0)| 00:00:29 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  5682   (0)| 00:00:29 |
-------------------------------------------------------------------

SQL Plan Baselines

Jonathan Lewis hat in seinem Blog eine Link-Sammlung zu den SQL Plan Baselines untergebracht, die ich jetzt noch mal verlinke. Zu den Links erklärt er:

Be a little careful as you read through these notes – there are various changes in internal mechanisms, storage, etc. as you go through different versions of Oracle, so check which version the author is writing about.

Das könnte man zu so ziemlich allen Beiträgen zu Oracle sagen.

Histogramme - 4

Bei meinen Histogramm-Tests bin ich noch an einem Detail hängengeblieben, nämlich an den ENDPOINT_NUMBER-Angaben in USER_HISTOGRAMS (oder der anscheinend identischen USER_TAB_HISTOGRAMS). Während man den ENDPOINT_VALUE für frequency histograms relativ leicht als einen distinkten Wert der jeweiligen Spalte wiedererkennen kann, sind die ENDPOINT_NUMBER-Angaben interpretationsbedürftiger:

create table test
as
select case when rownum <= 10000 then mod(rownum, 10) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

select rn
     , count(*)
  from test
 group by rn
 order by rn;

  RN   COUNT(*)
---- ----------
   0       1000
   1       1000
   2       1000
   3       1000
   4       1000
   5       1000
   6       1000
   7       1000
   8       1000
   9       1000
 100     990000

Also 10 Werte mit jeweils 1.000 Vorkommen und einer, der 990.000 mal erscheint. Wenn man dazu USER_HISTOGRAMS befragt, sieht man Werte, die dazu in irgendeiner Relation zu stehen scheinen, aber die Art dieser Relation erschliesst sich nicht unmittelbar - zumindest nicht für mich:

select column_name
     , endpoint_value
     , endpoint_number
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                              ENDPOINT_VALUE ENDPOINT_NUMBER
---------------------------------------- -------------- ---------------
RN                                                    0              65
RN                                                    1             126
RN                                                    2             171
RN                                                    3             235
RN                                                    4             292
RN                                                    5             348
RN                                                    6             400
RN                                                    7             462
RN                                                    8             532
RN                                                    9             586
RN                                                  100            5482 

Die Antwort auf die Frage nach der Bedeutung dieser Werte kennt einmal mehr Jonathan Lewis: die ENDPOINT_NUMBER ist eine kumulierte Anzahl der Sätze pro Wert. Dankenswerterweise liefert er auf gleich eine Query mit deren Hilfe man die kumulierten Zahlen wieder in simple Gruppierungszählungen zurückverwandeln kann (über Analytics):

select
   endpoint_value                          column_value,
   endpoint_number - nvl(prev_endpoint,0)  frequency
from       (
   select
           endpoint_number,
           lag(endpoint_number,1) over(
                   order by endpoint_number
           )                               prev_endpoint,
           endpoint_value
   from
           user_tab_histograms
   where
           table_name  = 'TEST'
   and     column_name = 'RN'
   )
order by
   endpoint_number;

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         65
           1         61
           2         45
           3         64
           4         57
           5         56
           6         52
           7         62
           8         70
           9         54
         100       4896 

Vergleicht man diese Ergebnisse mit den Werten der gruppierenden Query oben, dann ergibt sich, dass die Frequency jeweils ca. 0,5% der absoluten Werte beträgt. Das wiederum lässt vermuten, dass hier ein Sampling im Spiel ist, und um das zu prüfen, habe ich ein kleines Script test.sql geschrieben, das die Statistiken mit unterschiedlicher estimate_percent-Angabe erhebt und anschließend die Query von Jonathan Lewis und eine Abfrage der SAMPLE_SIZE aus USER_TABELS durchführt:

-- test.sql 
exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test', method_opt=>'FOR ALL COLUMNS SIZE 11', estimate_percent=> &sample_pct)

select
   endpoint_value                          column_value,
   endpoint_number - nvl(prev_endpoint,0)  frequency
from       (
   select
           endpoint_number,
           lag(endpoint_number,1) over(
                   order by endpoint_number
           )                               prev_endpoint,
           endpoint_value
   from
           user_tab_histograms
   where
           table_name  = 'TEST'
   and     column_name = 'RN'
   )
order by
   endpoint_number;

select table_name
     , sample_size
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

Hier die Ergebnisse für verschiedene Sampling-Angaben:

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 100

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:03.56

COLUMN_VALUE  FREQUENCY
------------ ----------
           0      10000
           1      10000
           2      10000
           3      10000
           4      10000
           5      10000
           6      10000
           7      10000
           8      10000
           9      10000
         100     900000

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                               1000000

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 10

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.90

COLUMN_VALUE  FREQUENCY
------------ ----------
           0        964
           1        985
           2       1054
           3        984
           4        995
           5        956
           6        978
           7        986
           8       1032
           9       1003
         100      89937

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                 99874

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 1

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.48

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         88
           1        112
           2         98
           3         91
           4         91
           5        104
           6         94
           7        120
           8        102
           9        106
         100       9044

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                 10050

SQL> @ test
Geben Sie einen Wert für sample_pct ein: dbms_stats.auto_sample_size

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.73

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         58
           1         61
           2         43
           3         55
           4         48
           5         55
           6         38
           7         47
           8         66
           9         51
         100       4925

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                  5447

Mit einem 100%-Sample kommt Jonathan Lewis' Analysequery also auf die gleichen Ergebnisse, die auch das GROUP BY Statement für das Auftreten der distinkten Werte liefert. Die anderen %-Samples liefern jeweils die erwarteten Prozentsätze. Mit der Angabe dbms_stats.auto_sample_size erhält man die Werte, die ohne Angabe von estimate_percent erscheinen - und das überrascht nicht, da diese Angabe laut Doku den internen Default darstellt.

Nachtrag 14.04.2012: die Sample Size von ca. 5.500 Sätzen ist der (nicht ganz unproblematische) Standard-Wert für die Histogrammerstellung mit auto_sample_size, wozu ich zuletzt hier ein paar Details aufgeführt habe.

Redo

Jonathan Lewis erläutert ein paar subtile Details der Redo-Erzeugung. Unter anderem zeigt er, dass die Reihenfolge von Redo-Einträgen in den redo log Dateien seit 10g nicht mehr so ganz den Erwartungen entspricht, was anscheinend dem “private redo/in-memory undo” Mechanismus geschuldet ist:

In the 9.2.0.8 dump we’ll see the “traditional” sequence of redo generation, which follows very closely to the steps listed above and also shows that Oracle pairs a “redo change vector” for a table or index block with the matching “redo change vector” for an undo record that describes how to reverse the table (or index) change – an undo record, of course, is just a piece of information that gets stored in an undo block. In general, each pair of redo change vectors will be gathered into a single “redo record”.

In the 10.2.0.3 dump we’ll see that the same redo change vectors still exist but they’ve all been lumped into a single redo record, and their ordering in that record is completely different from the 9.2.0.8 ordering, demonstrating (or at least supporting) the point that the “private redo/in-memory undo” mechanism uses two separate buffers, one for redo change vectors related to table/index blocks (the private redo bit) and the other for redo change vectors related to undo records (the in-memory undo bit).

In den Kommentaren (vor allem von JL und Mathew Butler) folgt noch eine detailliertere Diskussion der Schritte, die im Zusammenhang des Commits erfolgen.

Locking

Arup Nanda hat in seinem Blog eine sehr schöne Einführung zum Locking in Oracle geschrieben.

Oracle ETL

Da ich mit Oracle-ETL-Tools nur sehr selten arbeite, ist mir Oracles Tool-Strategie in diesem Bereich relativ unklar - möglicherweise wäre das aber auch der Fall, wenn ich damit mehr zu tun hätte... Eine übersichtliche Zusammenfassung zum Thema liefert jedenfalls Vincent Rainardi.

IGNORE_ROW_ON_DUPKEY_INDEX Hint

Richard Foote erläutert in seinem Blog den Hint IGNORE_ROW_ON_DUPKEY_INDEX, der dafür sorgt, dass Duplikatsätze bei einem Insert in eine Tabelle mit einem unique index nicht eingespielt werden. Eingespielt werden also nur "neue" Sätze. Bemerkenswert an diesem Hint ist, dass er - anders als die Optimizer-Hints - zu Fehlern führen kann (z.B.: "IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation") und nicht einfach ignoriert wird. Auf Anhieb würde ich sagen, dass MERGE die bessere Option für das zugrunde liegende Problem ist.

Rule Hint

In Charles Hoopers Blog finden sich ein paar interessante Überlegungen zum Rule-Hint. Unter anderem gibt es dort ein Beispiel, bei dem der rbo einen einspaltigen FBI verwendet, obwohl man bei Jonathan Lewis lesen kann, "that if the first column is a virtual column the rule-based optimizer won’t use the index". Das Testbeispiel führt allerdings nur in 11.1.0.7 zu diesem Ergebnis - in früheren und späteren Versionen aber offenbar nicht. Mal sehen, ob der Fall noch weiter erhellt wird.

Histogramme - 3

Nachdem ich zuletzt ein ganz harmloses Testbeispiel zur Darstellung der Struktur von Frequency Histogrammen definiert hatte, um dann festzustellen, dass das Beispiel ganz so harmlos nicht war - und sich ganz anders verhielt, als ich vorher vermutet hatte - suche ich weiter nach einem Beleg dafür, dass sich Histogramme in weniger extremen Fällen tatsächlich so verhalten, wie ich behautptet hatte:

Zunächst lege ich eine Tabelle mit einem sehr häufigen Wert (100 mit 990.000 Sätzen) und 100 relativ seltenen Werten an, die jeweils 100 mal vorkommen.

 drop table test; 
create table test
as
select case when rownum <= 10000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN'

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ----------
RN                                      39          101 FREQUENCY

Nun ja, näher an den Erwartungen, aber noch nicht ganz dran ... - wieso sind es nur 39 buckets, statt der 101, die ich verlangt hatte.

Vielleicht ein Effekt der Sortierung der Daten? Immerhin dürften die Werte <> 100 ja auf die ersten 10.000 Sätze beschränkt sein. Deshalb werfe ich meine Testmenge mit dbms_random etwas durcheinander:

drop table test;
create table test
as
select case when rownum <= 10000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000
 order by dbms_random.value;
exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ---------
RN                                      42          101 FREQUENCY

Näher dran, aber noch immer nicht das, was ich erwartet hatte. Vielleicht ist der Wert 100 immer noch zu predominant - immerhin betrifft er immer noch 99% der Werte. Gehen wir auf 90% herunter.

create table test
as
select case when rownum <= 100000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ---------
RN                                     100          101 FREQUENCY

select column_name
     , ENDPOINT_VALUE
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN'
 order by column_name;

COLUMN_NAME                    ENDPOINT_VALUE
------------------------------ --------------
RN                                          0
RN                                          1
RN                                          2
RN                                          3
RN                                          4
RN                                          5
RN                                          6
RN                                          7
RN                                          9
RN                                         10
RN                                         11
...
RN                                         98
RN                                         99
RN                                        100

100 Zeilen ausgewählt.

Das wäre dann also - endlich - das erwartete Resultat. Anscheinend ist der von Tom Kyte angesprochene predominant value bei 90% der Gesamtsätze nicht mehr so übermächtig, dass er die bucket-Anzahl reduzieren würde. Für heute reicht mir das.