Redo und Undo für INSERT APPEND

Mein Gedächtnis ist zwar manchmal etwas unzuverlässig, aber dass ich schon mehrfach über INSERT /*+ APPEND */ geschrieben habe, ist mir nicht entfallen. Trotzdem an dieser Stelle noch mal ein Versuch, die Effekte von Redo und Undo klar auseinander zu halten. Dazu folgender Test (in 10.2.0.4 auf zwei Datenbanken):

-- grundsätzlich ist das die gleiche Datengrundlage
-- wie in den oben verlinkten Tests vom Januar 2011
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;

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

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

-- im zweiten Testfall relevant:
-- alter table test_insert nologging;

-- Statistikerfassung

insert into test_insert
select *
  from test_insert_source;

commit;  

-- Statistikerfassung

truncate table test_insert;

-- Statistikerfassung

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

-- Statistikerfassung

Zur Statistikerfassung dient jeweils folgende harmlose Query:

select name.name
     , ses.value
  from v$sesstat ses
     , v$statname name
 where ses.statistic# = name.statistic#
   and ses.sid in (select sid from v$mystat)
   and name in ('redo size', 'undo change vector size');

Getestet werden folgende Varianten:

• Insert / Insert Append
• Logging / Nologging
• Archivelog / Noarchivelog

DB-Logging     Table-Logging  Append       Redo      Undo 
------------   -------------  ------   --------   -------
NoArchiveLog             yes      no   75,89 MB   2,53 MB
NoArchiveLog             yes     yes    0,25 MB   0,06 MB
NoArchiveLog              mo      no   76,04 MB   2,55 MB
NoArchiveLog              no     yes    0,25 MB   0,06 MB
ArchiveLog               yes      no   79,18 MB   3,14 MB
ArchiveLog               yes     yes   78,78 MB   0,06 MB
ArchiveLog                no      no   79,18 MB   3,14 MB
ArchiveLog                no     yes    0,28 MB   0,06 MB

Demnach gilt für Inserts in Tabellen ohne Indizes:

• mit dem APPEND-Hint wird in allen Fällen nur ein minimales Undo erzeugt (was einleuchtet, da die Operation ja am Buffer Cache vorbei abläuft)
• in einer NoArchiveLog-Datenbank wird bei Verwendung des APPEND-Hints nur minimales Redo erzeugt
• in einer ArchiveLog-Datenbank wird bei Verwendung des APPEND-Hints nur dann minimales Redo erzeugt, wenn die Tabelle als nologging definiert ist
• in allen anderen Fällen wird redo in normalem Umfang erzeugt

Die Ergebnisse entsprechen (was wenig überrascht) auch der Matrix, die Tom Kyte vor fast zehn Jahren zur redo Erzeugung erstellt hat. Mit Indizes wäre der Fall noch mal etwas unübersichtlicher. Ein paar weitere Anmerkungen zum Thema liefert auch noch ein - ebenfalls uralter - Thread aus Oracle-L (darin vor allem Jonathan Lewis' Erläuterung). Ich vermute, dass das Verhalten in späteren Oracle-Versionen nicht verändert ist.

Zum Bezug von cost und time

Wieder ein Link, wieder auf Randolf Geists Blog ...
Diesmal erläutert der Herr Geist ein neues "cost is time" Modell, das mit 11.2.0.2 eingeführt wurde und stellt in diesem Zusammenhang die Evolution der cost/time-Schätzungen des CBO seit Version 7 dar. Enthalten sind auch Links auf die Aussagen, die sich zum Thema in Jonathan Lewis' Blog finden.

Besonders glücklich ist Randolf Geist offenbar nicht mit dem neuen 11.2.0.2er Verfahren (das vorhandene Ergebnisse der I/O resource calibration verwendet, um eine Zeitangabe für das Lesen eines aus der ursprünglichen cost-Angabe abgeleiteten Datenvolumen zu ermitteln - was so seltsam zu sein scheint, wie es klingt):

A sophisticated time estimate (remember that it even includes a CPU time component that has nothing to do with an I/O volume) is turned into data volume to arrive at a new time estimate using a rather simplistic approach

Problematisch daran ist wohl vor allem, dass der solide Zusammenhang von cost- und time-Angaben durch dieses (nicht besonders solide wirkende) Verfahren zerstört wird. Die neuen Zeitangaben spielen dann eine wichtige Rolle für das neue Auto-DOP-Feature, das ich mir allerdings noch nicht angeschaut habe.

Nested Loops Prefetching

Randolf Geist erläutert in seinem Artikel Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching einige technische Details des Prefetchings bei NL Operationen. Offenbar spielt die Sortierung der Daten in den beiden via NL verknüpften Tabellen eine entscheidende Rolle:

Table Prefetching has been introduced in Oracle 9i in order to optimize the random physical access in Nested Loop Joins, however it also seems to have a positive effect on logical I/O. The effectiveness of this optimization depends on the data order - if the data from the driving row source is in the same order as the inner row source table buffers can be kept pinned. Note that the same doesn't apply to the index lookup - even if the data is ordered by ID and consequently the same index branch and leaf blocks will be accessed repeatedly with each iteration, a buffer pinning optimization could not be observed.

Im Beispiel wird auch deutlich, wie unterschiedlich sich als unique und als non-unique definierte Indizes in diesem Zusammenhang verhalten - was möglicherweise ein Argument gegen den Einsatz von non uninque indexes für primary keys sein mag.

Performance von Delete und Truncate

Der Titel des Eintrags ist ein wenig irreführend: eigentlich ging es mir in diesem Fall nur darum, endlich einmal Adrian Billigtons (auf Tom Kytes gleichnamiger Grundlage basierende) runstat-Packages auszuprobieren, die Randolf Geist in seinen Tests gerne einsetzt. Mit Hilfe dieser Packages kann man die Ressourcennutzung von zwei unterschiedlichen Testläufen miteinander vergleichen - wobei die erforderlichen Statistikdaten durch Zugriff auf die dynamischen Performance-Views v$mystat, v$statname und v$latch ermittelt werden. Bisher habe ich zu diesem Zweck immer eine selbstgebastelte Lösung verwendet, die allerdings deutlich simpler aufgebaut war (und z.B. keine Pausen-Funktion enthielt).

Die Fragestellung, die ich mit Hilfe von runstat untersuchen will, lautet: ab welchem Punkt wird es im Rahmen einer ETL-Operation - also in einer single user Umgebung - günstiger, eine Tabelle über Truncate zu leeren und neu zu befüllen, statt einen Teil der Daten über Delete zu löschen und nur die erforderlichen Daten neu einzuspielen. Dazu lege ich zwei Tabellen an, von denen die eine per truncate geleert und dann über insert neu gefüllt wird und die andere per delete teilweise geleert wird und die gelöschten Sätze dann wieder über insert neu eingefügt werden. In mehreren Testläufen wird dann der Anteil der in Fall 2 gelöschten Sätze erhöht. Meine Vermutung ist, dass es für diesen Test einen Punkt gibt - wenn mehr als n % gelöscht werden -, ab dem die brutale truncate/insert-Variante effizienter wird als die delete/insert-Variante.

Zunächst zu den Vorraussetzungen:

• 11.1.0.7
• noarchivelog (was bekanntlich im Zusammenspiel mit dem Append-Hint Einfluß auf die redo-Erzeugung hat)
• mssm tablespace
• 8 K blocksize

Dann das Test-Script:

-- test_delete_truncate_performance.sql
-- 27.07.2011

/*
-- nur einmal auszuführen:
drop table t0;

create table t0
as
select level id
     , mod(level, 100) + 1 col1
     , lpad('*', 100, '*') filler
  from dual
connect by level <= 1000000
 order by dbms_random.value;
*/

undefine pct_del

drop table t1;
drop table t2;

create table t1
as
select *
  from t0;

create table t2
as
select *
  from t0;

exec runstats_pkg.rs_start;

-- Fall 1: truncate + insert

truncate table t1;

insert into t1
select *
  from t0;
      
exec runstats_pkg.rs_middle;

-- Fall 2: delete % der Sätze + erneutes insert der gelöschten Sätze

delete from t2
 where col1 <= &&pct_del;

insert into t2
select *
  from t0
 where col1 <= &pct_del;

exec runstats_pkg.rs_stop(100000);

Es folgen das runstat-Ergebnis für die Löschung unterschiedlicher Teile der Tabelle:
Fall 1 - gelöschte Sätze: 1%

================================================================================
Runstats report : 27-JUL-2011 10:54:12
================================================================================

--------------------------------------------------------------------------------
1. Summary timings
--------------------------------------------------------------------------------
Run1 ran in 247 hsecs
Run2 ran in 174 hsecs
Run2 was 29,6% quicker than Run1

--------------------------------------------------------------------------------
2. Statistics report
--------------------------------------------------------------------------------

Type  Name                                        Run1         Run2         Diff
----- ----------------------------------- ------------ ------------ ------------
LATCH checkpoint queue latch                       955       12,037       11,082
STAT  free buffer requested                     10,331       21,653       11,322
STAT  physical reads cache prefetch              1,582       15,296       13,714
STAT  physical reads                             1,834       15,712       13,878
STAT  physical reads cache                       1,834       15,712       13,878
STAT  Heap Segment Array Inserts                14,555          145      -14,410
STAT  calls to get snapshot scn: kcmgss         16,446          233      -16,213
LATCH cache buffers lru chain                    2,382       24,520       22,138
LATCH object queue header operation             26,907       56,462       29,555
STAT  redo entries                              42,224       10,406      -31,818
STAT  db block gets                             46,358       10,708      -35,650
STAT  db block gets from cache                  46,358       10,708      -35,650
STAT  session logical reads                     64,449       26,630      -37,819
STAT  db block changes                          67,919       20,784      -47,135
LATCH cache buffers chains                     252,917      120,542     -132,375
STAT  undo change vector size                3,131,416    2,144,744     -986,672
STAT  table scan rows gotten                 1,008,449    2,015,616    1,007,167
STAT  session pga memory                    -1,507,328      589,824    2,097,152
STAT  redo size                            124,435,844    4,753,664 -119,682,180
STAT  cell physical IO interconnect bytes   30,048,256  257,425,408  227,377,152
STAT  physical IO disk bytes                30,048,256  257,425,408  227,377,152
STAT  physical read bytes                   30,048,256  257,425,408  227,377,152
STAT  physical read total bytes             30,048,256  257,425,408  227,377,152

--------------------------------------------------------------------------------
3. Latching report
--------------------------------------------------------------------------------
Run1 used 317,391 latches
Run2 used 221,404 latches
Run2 used 30,2% fewer latches than Run1


================================================================================
End of report
================================================================================

Für die Löschung eines Prozents der Daten ergibt sich demnach Folgendes:

• die delete Variante benötigt weniger Zeit als die truncate Variante
• Das hatte ich für 1% erwartet.
• Run2 was 29,6% quicker than Run1: 2,47 sec vs. 1,74 sec
• die delete Variante verwendet deutlich weniger redo als die truncate Variante
• leuchtet mir ein, da die redo-Einträge für das insert von 1.000.000 Sätzen natürlich umfangreicher sind als die für delete und insert von 10.000 Sätzen
• die delete Variante verwendet weniger logical reads als die truncate Variante
• die delete Variante verwendet mehr physical reads als die truncate Variante
• die delete Variante verwendet weniger latches als die truncate Variante

Fall 2 - gelöschte Sätze: 5%

================================================================================
Runstats report : 27-JUL-2011 11:00:02
================================================================================

--------------------------------------------------------------------------------
1. Summary timings
--------------------------------------------------------------------------------
Run1 ran in 244 hsecs
Run2 ran in 264 hsecs
Run1 was 7,6% quicker than Run2

--------------------------------------------------------------------------------
2. Statistics report
--------------------------------------------------------------------------------

Type  Name                                        Run1         Run2         Diff
----- ----------------------------------- ------------ ------------ ------------
STAT  physical reads cache prefetch              2,637       15,307       12,670
STAT  physical reads                             2,717       15,708       12,991
STAT  physical reads cache                       2,717       15,708       12,991
STAT  free buffer requested                     11,178       24,659       13,481
STAT  Heap Segment Array Inserts                14,555          728      -13,827
LATCH checkpoint queue latch                       916       15,331       14,415
STAT  calls to get snapshot scn: kcmgss         16,447          895      -15,552
LATCH simulator hash latch                      25,530        8,406      -17,124
LATCH cache buffers lru chain                    2,433       30,777       28,344
STAT  HSC Heap Segment Block Changes            15,047       50,740       35,693
STAT  db block changes                          67,922      104,012       36,090
LATCH object queue header operation             28,593       66,692       38,099
LATCH DML lock allocation                       39,058           26      -39,032
STAT  buffer is pinned count                         2       49,593       49,591
STAT  session uga memory                       457,856      523,264       65,408
LATCH cache buffers chains                     250,979      335,975       84,996
STAT  table scan rows gotten                 1,008,449    2,014,080    1,005,631
STAT  session pga memory                    -1,572,864      458,752    2,031,616
STAT  undo change vector size                3,131,668   10,893,104    7,761,436
STAT  redo size                            124,324,584   23,817,964 -100,506,620
STAT  cell physical IO interconnect bytes   44,515,328  257,359,872  212,844,544
STAT  physical IO disk bytes                44,515,328  257,359,872  212,844,544
STAT  physical read bytes                   44,515,328  257,359,872  212,844,544
STAT  physical read total bytes             44,515,328  257,359,872  212,844,544

--------------------------------------------------------------------------------
3. Latching report
--------------------------------------------------------------------------------
Run1 used 383,102 latches
Run2 used 460,789 latches
Run1 used 16,9% fewer latches than Run2


================================================================================
End of report
================================================================================

Erwartungsgemäß bleiben die Werte für das truncate relativ stabil. Darüber hinaus kann mal feststellen, dass:

• truncate hier schon schneller ist als delete
• Run1 was 7,6% quicker than Run2: 2,44 sec zu 2,46 sec
• die Differenz bei den logical reads bereits unter dem Schwellwert von 10.000 liegt (möglicherweise wäre eine andere Filterung des runstats-Ergebnisses hier sinnvoller gewesen)
• die physical reads auch für das delete stabil bleiben (gegenüber dem 1% Fall)
• der truncate Fall jetzt schon weniger latches verwendet

Fall 3 - gelöschte Sätze: 25%

================================================================================
Runstats report : 27-JUL-2011 11:08:27
================================================================================

--------------------------------------------------------------------------------
1. Summary timings
--------------------------------------------------------------------------------
Run1 ran in 243 hsecs
Run2 ran in 577 hsecs
Run1 was 57,9% quicker than Run2

--------------------------------------------------------------------------------
2. Statistics report
--------------------------------------------------------------------------------

Type  Name                                        Run1         Run2         Diff
----- ----------------------------------- ------------ ------------ ------------
STAT  Heap Segment Array Inserts                14,555        3,666      -10,889
STAT  dirty buffers inspected                    1,671       13,169       11,498
STAT  calls to get snapshot scn: kcmgss         16,445        4,204      -12,241
STAT  physical reads cache prefetch              2,189       15,308       13,119
STAT  physical reads                             2,419       15,738       13,319
STAT  physical reads cache                       2,419       15,738       13,319
LATCH simulator hash latch                       5,681       22,251       16,570
STAT  free buffer requested                     10,915       29,069       18,154
LATCH checkpoint queue latch                     1,258       22,243       20,985
LATCH cache buffers lru chain                    3,208       43,567       40,359
LATCH object queue header operation             29,596       90,771       61,175
STAT  session uga memory                       457,856      523,264       65,408
STAT  redo entries                              42,222      260,447      218,225
STAT  db block gets                             46,353      268,940      222,587
STAT  db block gets from cache                  46,353      268,940      222,587
STAT  session logical reads                     64,431      287,375      222,944
STAT  HSC Heap Segment Block Changes            15,047      253,726      238,679
STAT  buffer is pinned count                         2      247,918      247,916
STAT  db block changes                          67,918      520,097      452,179
STAT  table scan rows gotten                 1,008,449    2,015,488    1,007,039
LATCH cache buffers chains                     253,277    1,416,450    1,163,173
STAT  session pga memory                    -1,572,864      458,752    2,031,616
STAT  redo size                            124,419,928  119,169,836   -5,250,092
STAT  undo change vector size                3,131,416   54,710,068   51,578,652
STAT  cell physical IO interconnect bytes   39,632,896  257,851,392  218,218,496
STAT  physical IO disk bytes                39,632,896  257,851,392  218,218,496
STAT  physical read bytes                   39,632,896  257,851,392  218,218,496
STAT  physical read total bytes             39,632,896  257,851,392  218,218,496

--------------------------------------------------------------------------------
3. Latching report
--------------------------------------------------------------------------------
Run1 used 320,956 latches
Run2 used 1,606,384 latches
Run1 used 80% fewer latches than Run2

================================================================================
End of report
================================================================================

Hier ist das truncate bereits deutlich effizienter:

• Run1 was 57,9% quicker than Run2: 2,43 sec zu 5,77 sec
• delete benötigt die sechsfache Menge an logical reads
• die physical reads bleiben weiterhin stabil (wobei physical reads unter Umständen vom OS gecachet sein können)
• Run1 used 80% fewer latches than Run2

Der Vollständigkeit halber noch die extremeren Fälle, in denen delete natürlich noch schlechter abschneidet.

Fall 4 - gelöschte Sätze: 50%

================================================================================
Runstats report : 27-JUL-2011 11:15:09
================================================================================

--------------------------------------------------------------------------------
1. Summary timings
--------------------------------------------------------------------------------
Run1 ran in 218 hsecs
Run2 ran in 901 hsecs
Run1 was 75,8% quicker than Run2

--------------------------------------------------------------------------------
2. Statistics report
--------------------------------------------------------------------------------

Type  Name                                        Run1         Run2         Diff
----- ----------------------------------- ------------ ------------ ------------
STAT  physical reads cache prefetch              2,831       15,243       12,412
STAT  calls to kcmgas                            2,521       15,603       13,082
STAT  physical reads                             3,017       16,108       13,091
STAT  physical reads cache                       3,017       16,108       13,091
STAT  free buffer inspected                     13,011       28,743       15,732
STAT  dirty buffers inspected                    1,665       18,386       16,721
STAT  free buffer requested                     11,517       34,303       22,786
LATCH checkpoint queue latch                       940       26,535       25,595
LATCH simulator hash latch                       5,733       39,483       33,750
LATCH DML lock allocation                       39,058          202      -38,856
LATCH cache buffers lru chain                    2,638       52,859       50,221
STAT  session uga memory                       457,856      523,264       65,408
LATCH object queue header operation             31,948      115,674       83,726
STAT  redo entries                              42,201      521,548      479,347
STAT  db block gets                             46,355      539,041      492,686
STAT  db block gets from cache                  46,355      539,041      492,686
STAT  HSC Heap Segment Block Changes            15,047      507,970      492,923
STAT  buffer is pinned count                         2      495,976      495,974
STAT  session logical reads                     64,485      561,606      497,121
STAT  db block changes                          67,919    1,044,330      976,411
STAT  table scan rows gotten                 1,008,449    2,015,104    1,006,655
STAT  session pga memory                    -1,572,864      458,752    2,031,616
LATCH cache buffers chains                     253,454    2,770,061    2,516,607
STAT  undo change vector size                3,131,416  109,653,688  106,522,272
STAT  redo size                            124,157,056  238,877,476  114,720,420
STAT  cell physical IO interconnect bytes   49,430,528  263,913,472  214,482,944
STAT  physical IO disk bytes                49,430,528  263,913,472  214,482,944
STAT  physical read bytes                   49,430,528  263,913,472  214,482,944
STAT  physical read total bytes             49,430,528  263,913,472  214,482,944

--------------------------------------------------------------------------------
3. Latching report
--------------------------------------------------------------------------------
Run1 used 370,161 latches
Run2 used 3,025,717 latches
Run1 used 87,8% fewer latches than Run2

================================================================================
End of report
================================================================================

Fall 5 - gelöschte Sätze: 100%

================================================================================
Runstats report : 27-JUL-2011 11:16:21
================================================================================

--------------------------------------------------------------------------------
1. Summary timings
--------------------------------------------------------------------------------
Run1 ran in 216 hsecs
Run2 ran in 1711 hsecs
Run1 was 87,4% quicker than Run2

--------------------------------------------------------------------------------
2. Statistics report
--------------------------------------------------------------------------------

Type  Name                                        Run1         Run2         Diff
----- ----------------------------------- ------------ ------------ ------------
STAT  redo ordering marks                        1,781       13,681       11,900
LATCH MinActiveScn Latch                         3,238       15,815       12,577
STAT  physical reads cache prefetch              2,651       15,257       12,606
STAT  consistent gets                           18,093       32,080       13,987
STAT  consistent gets from cache                18,093       32,080       13,987
STAT  physical reads                             2,746       20,628       17,882
STAT  physical reads cache                       2,746       20,628       17,882
STAT  calls to kcmgas                            2,521       22,032       19,511
STAT  dirty buffers inspected                    2,698       26,639       23,941
STAT  free buffer inspected                     12,227       39,414       27,187
STAT  free buffer requested                     11,210       42,908       31,698
LATCH checkpoint queue latch                     1,637       37,689       36,052
STAT  session uga memory                       457,856      523,264       65,408
LATCH simulator hash latch                       5,735       73,497       67,762
LATCH cache buffers lru chain                    4,186       73,309       69,123
LATCH object queue header operation             32,129      162,218      130,089
STAT  redo entries                              42,226    1,031,879      989,653
STAT  buffer is pinned count                         2      992,187      992,185
STAT  HSC Heap Segment Block Changes            15,047    1,014,922      999,875
STAT  table scan rows gotten                 1,008,449    2,014,592    1,006,143
STAT  db block gets                             46,375    1,067,775    1,021,400
STAT  db block gets from cache                  46,375    1,067,775    1,021,400
STAT  session logical reads                     64,468    1,099,855    1,035,387
STAT  db block changes                          67,927    2,084,254    2,016,327
STAT  session pga memory                    -1,572,864      458,752    2,031,616
LATCH cache buffers chains                     253,597    5,453,856    5,200,259
STAT  undo change vector size                3,131,728  219,793,172  216,661,444
STAT  cell physical IO interconnect bytes   44,990,464  337,969,152  292,978,688
STAT  physical IO disk bytes                44,990,464  337,969,152  292,978,688
STAT  physical read bytes                   44,990,464  337,969,152  292,978,688
STAT  physical read total bytes             44,990,464  337,969,152  292,978,688
STAT  redo size                            124,379,784  477,840,336  353,460,552

--------------------------------------------------------------------------------
3. Latching report
--------------------------------------------------------------------------------
Run1 used 325,330 latches
Run2 used 5,838,489 latches
Run1 used 94,4% fewer latches than Run2

================================================================================
End of report
================================================================================

Zusammenfassung:

• Adrian Billingtons runstats-Packages werden meine Eigenbaulösung wahrscheinlich verdrängen
• truncate ist bereits ab einer Löschung von 5% der Daten genauso schnell wie delete und bei größeren Prozentwerten deutlich effektiver
• truncate verwendet bereits ab 5% Datenlöschungen deutlich weniger latches
• bis 25% Löschungen ist die redo size für den truncate-Fall höher, bei höheren Werten dann für den delete-Fall
• die physical reads bleiben für alle delete-Varianten relativ stabil
• die logical reads steigen im delete-Fall ab 5% Löschungen über die Werte des truncate-Falls

Man könnte die Zahlen natürlich noch sehr viel detaillierter analysieren, aber für diesmal sei's genug.

SELECT_CATALOG_ROLE

Arup Nanda ist auch einer dieser Leute, bei denen man in Gefahr gerät, jeden Blog-Artikel zu verlinken. Im aktuellen Eintrag zeigt er die Unterschiede zwischen der SELECT_CATALOG_ROLE (die ich mir gerne geben lasse) und dem Systemprivileg Select Any Dictionary. Und ehe ich versuche, den Inhalt zusammenzufassen, kopiere ich lieber die griffige Zusammenfassung:

[...] what is the difference between two seemingly similar privileges - SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. The former is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant. The latter is not a system grant; it's a role which does not allow the grantee to build stored objects on the granted objects. The role can also be non-default which means the grantee must execute a set role or equivalent command to enable it. The role can also be password protected, if desired.

The core message you should get from this is that roles are different from privileges. Privileges allow you to build stored objects such as procedures on the objects on which the privilege is based. Roles do not.

Die Link-Sammlung des Herrn Lewis

Wenn Jonathan Lewis eine Link-Sammlung auf seiner Webseite unterbringt, dann kann man sie sicher noch mal verlinken.

Langsames Estimate bei expdp

Seit vielen Jahren benutze ich Data Pump zum Transferieren und - ich geb es zu - auch zum Sichern größerer Datenmengen. Aber erst in den letzten Wochen ist mir aufgefallen, die groß der Overhead des Estimate-Schrittes zu Beginn des Exports ist, bei dem ermittelt wird, wie groß das Datenvolumen ist, das exportiert werden soll. Besonders auffällig wird das Verhalten, wenn der Export einzelne kleine Tabellen betrifft. Für eine Test-Tabelle mit 1.000 rows und 12 KB (!!) Datenvolumen ergaben sich in meinem Test für expdp folgende Gesamtlaufzeiten:

• DB 11.1.0.7: 12 min
• DB 10.2.0.4: 4 min

Im Fall 11.1.0.7 habe ich mir die zugehörigen Prozesse während des Exports etwas genauer angeschaut und dabei folgende Beobachtungen gemacht:

• expdp.exe: geringe DBTime (v$sess_time_model); Waits fast ausschließlich für event 60 "wait for unread message on broadcast channel" (v$session_event)
• Data Pump Master: geringe DBTime (v$sess_time_model); Waits fast ausschließlich für event 60 "wait for unread message on broadcast channel" (v$session_event)
• Data Pump Worker: hohe DBTime (v$sess_time_model); sehr viele Waits für event 137 "db file sequential read" (in der Regel Index-Zugriff) (v$session_event)

Ein 10046er Trace für die worker Session (die offenbar tatsächlich die ganze Arbeit leistet) enthält vor allem zwei Queries mit höherer Laufzeit. Erwähnenswert ist dabei vielleicht auch noch, dass das worker-Trace-File in 11.1.0.7 einen Namen _dw01_.trc trägt und sich im trace-Verzeichnis des üblichen diag-Pfades findet.

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), 
  XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,
  KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,
  to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.NONSCOPED_REF ,KU$.XMLSCHEMACOLS ,
  KU$.NAME ,KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.PARTTYPE ,KU$.PROPERTY 
  ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,
  KU$.SCHEMA_OBJ.NAME ,KU$.TRIGFLAG ,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2, 
  decode(bitand(KU$.PROPERTY,8224),8224,'NESTED PARTITION',8192,'NESTED 
  TABLE','TABLE'),19, decode(bitand(KU$.PROPERTY,8224),8224,'NESTED 
  PARTITION','PARTITION'),20,'PARTITION','SUBPARTITION') ,
  to_char(KU$.UNLOAD_METHOD) ,KU$.XMLTYPE_FMTS 
FROM
 SYS.KU$_TABLE_DATA_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND  
  NOT (BITAND (KU$.BASE_OBJ.FLAGS,16)=16) AND  NOT XML_OUTOFLINE='Y' AND   
  KU$.BASE_OBJ.OBJ_NUM IN (SELECT * FROM 
  TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001)))
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    751.00     752.83      39060   10309856        347           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    751.00     752.83      39060   10309856        347           1 

--> der Zugriffsplan spottet jeder Beschreibung
 
SELECT LVL 
FROM
 SYS.KU$_REF_PAR_LEVEL_VIEW WHERE OBJ# = :B1 
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   8452      1.24       1.38          0          0          0           0
Fetch     8452    706.21     707.06          1    8866148          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    16905    707.46     708.44          1    8866148          0           0 

--> auch hier spare ich mit den Execution Plan 

In MOS findet man zum Verhalten und zur ersten Query mehrere Hinweise, die die Schuld an der hohen Laufzeit dem RULE-Hint geben:

• Expdp Is Slow During Estimate Step [ID 1253955.1]: caused by internal Bug 7362589; die dort vorgeschlagenen Workarounds  (expdp with parameter VERSION=10.2.0.2; Use parameter INCLUDE=TABLE:"IN('EMP')" instead of parameter TABLES) funktionieren leider nicht
• Expdp Slow for a Small Table [ID 950995.1]: empfiehlt Patch 7362589 für 11.1.0.7
• Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp) [ID 453895.1]: mit einem Überblick über diverse Performance-Probleme und ihre Ursachen.

Vermutlich wäre hier tatsächlich Patch 7362589 das Mittel der Wahl. Gelegentlich sollte ich vielleicht auch noch das Verhalten in 10.2.0.4 genauer betrachten, denn auch dort scheint der Schätzungs-Overhead beträchtlich zu sein.

Zeitangaben in V$-Views

Kyle Hailey liefert eine Liste mit den unterschiedlichen Einheiten, die in den V$-Views für diverse Zeitangaben verwendet werden.

Nachtrag 09.05.2014: leider funktioniert der Link inzwischen offenbar nicht mehr. Wahrscheinlich sollte ich solche Informationen lieber gleich exzerpieren.

Hints für den SQL Server

Auch der Optimizer des SQL Servers kann über Hints beeinflusst werden, obwohl diese Möglichkeit - so weit ich sehe - seltener genutzt wird als im Fall von Oracle. In Simon Sabins Blog findet man Erläuterungen zu den in 2008 R2 SP 1 eingeführten Hints FORCESEEK und FORCESCAN. Offenbar wird das Thema für den SQL Server allmählich interessanter.

Wissensbasierte Query-Optimierung

Ich nenn's mal so - wohlwissend, dass das ein scheußlicher Titel ist ...

Worum es geht, ist Folgendes: Tanel Poder hat vor ein paar Wochen ein sehr schönes Beispiel dafür gebracht, wie man eine SQL-Query ohne wildes Rumprobieren optimieren kann, wenn man eine klare Vorstellung davon hat, wie die zugrunde liegenden Objekte beschaffen sind, und wie ein sinnvollerer Zugriff aussehen könnte (sinnvoller als der, den der CBO tatsächlich wählte).

MINIMIZE RECORDS_PER_BLOCK

Noch ein paar Ergänzungen zur "MINIMIZE RECORDS_PER_BLOCK" Option, über die ich hier schon geschrieben hatte. Durch Randolf Geists Kommentar und die Erläuterungen in Julian Dykes Bitmap Index Internals Präsentation wurde deutlich, dass der Hakan Faktor einer Tabelle - der die Anzahl möglicher Sätze pro Block angibt - in der SPARE1 Spalte von SYS.TAB$ gespeichert wird, und dass die "MINIMIZE RECORDS_PER_BLOCK" Option dafür sorgt, dass das 0x8000 Bit in dieser Spalte gesetzt wird, so dass sich der dezimale Wert um 32768 erhöht. Das folgende Script prüft, wie sich der Hakan Faktor abhängig von der initialen Füllung einer Testtabelle verändert:

-- test_minimize_records_per_block.sql
undefine rowcount

set verify off
set timin off
set feedback off

-- creation of a table with  rows in a mssm tablespace
drop table t&&rowcount;

create table t&rowcount 
tablespace test_ts
as
select rownum id
  from dual
connect by level <= &rowcount;

-- gather statistics
exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

-- without minimize records_per_block
insert into T&rowcount
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

prompt
prompt without minimize records_per_block

column object_id new_value obj_id 
select object_id 
  from user_objects 
 where object_name = 'T&rowcount';
 
select &rowcount initial_rows
     , rowcnt
     , blkcnt
     , spare1
  from sys.tab$
 where obj# = &obj_id; 

-- with minimize records_per_block

drop table t&rowcount;

create table t&rowcount 
tablespace test_ts
as
select rownum id
  from dual
connect by level <= &rowcount;

alter table T&rowcount minimize records_per_block;

insert into T&rowcount
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

prompt
prompt with minimize records_per_block

column object_id new_value obj_id 
select object_id 
  from user_objects 
 where object_name = 'T&rowcount';
 
select &rowcount initial_rows
     , rowcnt
     , blkcnt
     , spare1
  from sys.tab$
 where obj# = &obj_id; 
 
set verify on
set timin on
set feedback on

Es folgen die (zusammengefassten) Ergebnisse für unterschiedliche rowcount-Angaben bei MINIMIZE RECORDS_PER_BLOCK:

INITIAL_ROWS     ROWCNT     BLKCNT     SPARE1
------------ ---------- ---------- ----------
           1      10000       5001      32769
           2      10000       5001      32769
           3      10000       3337      32770
           4      10000       2500      32771
           5      10000       2003      32772
          10      10000       1004      32777
         100      10000        103      32867

Zum Vergleich die Angaben für den Fall ohne MINIMIZE RECORDS_PER_BLOCK

    ROWCNT     BLKCNT     SPARE1
---------- ---------- ----------
     10000         10       1481

SPARE1 wird also offenbar tatsächlich um 32768 erhöht, wenn das MINIMIZE RECORDS_PER_BLOCK Kommando ausgeführt wird. Die Anzahl der Sätze pro Block ist dann offenbar SPARE1 - 32768 + 1. Interessant ist, dass auch für den Fall mit INITIAL_ROWS = 1 ein SPARE1-Wert von 32769 und 2 Rows pro Block untergebracht werden. Anscheinend ist es nicht möglich über MINIMIZE RECORDS_PER_BLOCK auf einen Satz pro Block zu kommen - sofern mehr als ein Satz in den Block hinein passt.

LIO Optimierung

Randolf Geist hat eine Artikelserie begonnen, in der er verspricht diverse Optimierungen für die Durchführung von logical I/O zu erläutern, die in den letzten Versionen des Oracle Servers eingeführt wurden. Der erste Artikel Logical I/O - Evolution: Part 1 - Baseline stellt zunächst die Voraussetzungen dar - und das in sehr strukturierter Form mit einem griffigen Beispiel und ausführlichen Erläuterungen zu den Ergebnissen. Zum Artikel noch zwei Anmerkungen:

MINIMIZE RECORDS_PER_BLOCK

Interessant ist die verwendete Option "MINIMIZE RECORDS_PER_BLOCK", die die maximale Anzahl von Datensätzen pro Block begrenzt - offenbar ein Feature, das eigentlich zur Optimierung von Bitmap Indizes eingeführt wurde. Laut Doku gilt: "The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible. [...] Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records."

Das Verhalten dieser Option entspricht übrigens nicht ganz meinen Vorstellungen, wie folgender Test (mit 11.2.0.1) zeigt:

-- Anlage einer Tabelle mit einem Datensatz in einem non-assm-Tablespace
create table t1 tablespace test_ts
as
select rownum id
  from dual;

-- Erzeugung von Statistiken
exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

-- Angaben aus USER_TABLES
select num_rows
     , blocks
  from user_tables
 where table_name = 'T1';

NUM_ROWS     BLOCKS
-------- ----------
       1          1

-- ohne minimize records_per_block
insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000         20

-- mit minimize records_per_block
-- Neuanlage der Tabelle mit einem Satz
alter table t1 minimize records_per_block;


insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000       5001 

Demnach komme ich auch zwei Sätze pro Block, obwohl vor dem ALTER TABLE nur ein Satz im Block vorliegen kann; allerdings sagt die Doku auch: "Oracle recommends that a representative set of data already exist in the table before you specify MINIMIZE" - möglicherweise ergibt sich der beobachtete Effekt also daraus, dass die Abschätzung der rows per block nicht 100% akkurat ist. Für das Beispiel des Herrn Geist ist das aber irrelevant, da er zum gewünschten rows per block-Verhältnis kommt.

Nachtrag 17.07.2011: in den Kommentaren zu Randolf Geists Blog-Eintrag findet man noch ein paar Ergänzungen zum Thema (etwa, dass der Hakan factor, der die Anzahl möglicher Sätze in einem Block angibt, in der SPARE1 Spalte von sys.tab$ zu finden ist); möglicherweise kann man über "minimize records_per_block" keine Block-Füllung mit einem einzelnen Satz hervorrufen.

Nachtrag 19.07.2011: mal wieder eine interessante Koinzindenz: Richard Foote hat gerade auch über die "minimize records_per_block" Option geschrieben.

Pinned Buffers

Im verwendeten Nested Loops Join liegt die Gesamtzahl der Blockzugriffe deutlich niedriger als erwartet, da die per FTS ermittelten Blocks der inneren Tabelle und der Root Block des Index gepinnt werden können, so dass kein Get für das cache buffers chains latch erforderlich ist - und damit kein logical I/O - vgl. dazu Jonathan Lewis Ausführungen, die ich gelegentlich hier verlinkt hatte. Die Anzahl gepinnter Blocks liefert die Statistik "buffer is pinned count". Das Pinnen der Buffer ist auch die Ursache für unterschiedliche LIO-Werte bei veränderter Arraysize:

Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the "fetchsize" or "arraysize" for bulk fetches can influence the number of logical I/Os required to process a result set.

ASH Grundlagen

In einem Kommentar in Charles Hoopers Blog hat Marcin Przepiorowski dieser Tage auf eine interessante Präsentation hingewiesen, in der Graham Wood erläutert, was ASH (Active Session History) ist und wozu man es einsetzen kann. Hier ein paar ungeordnete exzerpierte Stichpunkte:

• V$ACTIVE_SESSION_HISTORY
• mit 10g eingeführt
• automatisch aktiv
• lizensiert mit dem Diagnostic pack
• samples active sessions every second (als ob man immer wieder Snapshots der Inhalte von v$session_wait erzeugen würde)
• registriert non-idle events oder on cpu
• Ergebnisse werden im ASH Buffer gehalten (also in memory)
• DBA_HIST_ACTIVE_SESS_HISTORY (on disk) takes samples from in-memory ASH (wird einmal pro Stunde persitiert (durch das AWR Framework) - oder wenn der ASH Buffer gefüllt ist)
• 7 Tage Historie (per default)
• Zugriff über Views; Dump via ORADEBUG
• Präsentation enthält ein paar einfache Auswertungsqueries

Bitmap Indexes and NOT EQUAL

Richard Foote führt in zwei Blogeinträgen vor, dass (B*Tree und Bitmap) Indizes in der Regel für Ungleichheitsprädikate nicht verwendet werden können (weil der cbo sie auch dann nicht nutzt, wenn es laut Statistik sinnvoll wäre), dass es aber für kombinierte Bitmap Indizes eine Ausnahme gibt:

• Bitmap Indexes and Not Equal (Holy Holy)
• Bitmap Indexes and Not Equal Part II (Sheep) 

Row Migration nach ALTER TABLE

Heute wurde ich von einem Kollegen nach meiner Einschätzung gefragt, welchen Effekt das Anhängen einer neuen Spalte an eine bereits existierende Tabelle mit Massendaten hat. Ich hatte dazu zwar eine Meinung und eine relativ starke Vermutung (nämlich dass die Effekte weitgehend denen eines entsprechenden Updates entsprechen sollten), die ich aber trotzdem durch einen Test absichern wollte:

-- Anlage einer Tabelle ohne vorgesehenen Platz für Satzerweiterungen
create table t1 pctfree 0
as
select rownum id
     , dbms_random.string('a', 1000) col1
  from dual
connect by level <= 10000;

-- Ergänzung einer neuen Spalte 

alter table t1 add col2 varchar2(100) default 
('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

-- Analyze zur Bestimmung der Row Migration
analyze table t1 compute statistics;

-- Prüfung in user_tables
TABLE_NAME                       PCT_FREE   NUM_ROWS     BLOCKS  CHAIN_CNT
------------------------------ ---------- ---------- ---------- ----------
T1                                      0      10000        766       1250

Ich habe dann auch noch einen Block-Dump erstellt, der ebenfalls zeigt, dass die neue Spalte in die bestehenden Sätze integriert wird, was dann abhängig von den gegebenen Umständen (PCTFREE, COMPRESS etc.) zu Row Migration oder Row Chaning führen kann (wobei Chaining im gegebenen Fall nicht vorkommen dürfte, da die Einzelsätze sicher nicht zu groß für einen Block werden).

Das Ergänzen einer neuen Spalte ohne Daten ist übrigens offenbar eine reine Metadaten-Operation, die nahezu keine Arbeit verursacht, wie ein 10046er Trace zeigt:

alter table t1 add col3 varchar2(100)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

-- auch die zugehörigen rekursiven Queries sind billig

In diesem Fall würde ein folgendes Update der neuen Spalte dann die gleichen Migrations-Effekte hervorrufen.

Wenn es wahrscheinlich ist, dass die ergänzte Spalte Row Migration hervorruft (also die Sätze um mehr als Prozent vergrößert), würde ich in einem solchen Fall eher einen kompletten Neuaufbau der Tabelle über CTAS vorschlagen.

Satzoperatoren und Duplikate

Ein Klassiker beim Schreiben schlechter SQL-Queries ist bekanntlich die Bildung von Joins mit unzureichenden Join-Bedingungen, die im Ergebnis Duplikate liefern, die dann wiederum durch DISTINCT unterdrückt werden. Dieser Tage habe ich gesehen, dass der Oracle Warehouse Builder (OWB) dazu noch eine andere Variante beisteuert (sofern es der OWB war und keine nachträgliche Anpassung durch einen Entwickler): nämlich die Bildung einer Menge mit Duplikaten, die dann über ein MINUS vereindeutigt wird. Zurückgeführt auf ein simples Beispiel geschah dabei Folgendes:

select mod(rownum, 3)
  from dual
connect by level < 10;

MOD(ROWNUM,3)
-------------
            1
            2
            0
            1
            2
            0
            1
            2
            0

9 Zeilen ausgewählt.

select mod(rownum, 3)
  from dual
connect by level < 10
minus
select 0 from dual;

MOD(ROWNUM,3)
-------------
            1
            2

Die Hauptquery lieferte aufgrund eines (nicht sinnvollen) 1:n-Joins Duplikate, die das MINUS als Satzoperation unterdrückte - bekanntlich liefert von den Satzoperationen nur UNION ALL doppelte Elemente in der Ergebnismenge (was möglicherweise daher kommt, dass die Mengenlehre keine Duplikate in Mengen vorsieht). Da ich nur den (ziemlich unübersichtlichen) Code des OWB gesehen habe und nicht die Darstellung in der GUI, kann ich auf Anhieb allerdings nicht sagen, ob diese Deduplizierung beabsichtigt war oder einfach geschehen ist.

sqlplus Ergebnisse als csv oder html rendern

Uwe Hesse liefert in seinem Blog ein Script mit dessen Hilfe man das Ergebnis der letzten ausgeführten SQL-Query als HTML im Browser anzeigen kann. Die Idee hat er von Tanel Poder, der auch ein ähnliches Script für den csv-Export nach Excel anbietet.