Mittwoch, Januar 26, 2011

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

    Keine Kommentare:

    Kommentar veröffentlichen