Mittwoch, Oktober 03, 2012

Index-Aufbau und INSERT APPEND

Dass die Laufzeit eines INSERT APPEND in eine Tabelle mit aktiven Indizes höher ist als die eines entsprechenden INSERT APPEND in eine Tabelle ohne Indizes (oder mit Indizes im Zustand UNUSABLE), bei dem die Indizes nach der Ladeoperation neu aufgebaut werden, habe ich gelegentlich überprüft - und entsprechende Hinweise findet man auch ziemlich regelmäßig als Optimierungsvorschlag für ETL-Ladeprozessen. Den Grund für das unterschiedliche Verhalten konnte ich aber auf Anhieb nicht nennen: meiner Erinnerung nach sorgt der APPEND Hint hinsichtlich der Indizes dafür, dass die Maintainance verschoben wird bis die Daten in die Tabellenblocks eingefügt worden sind. Wo also liegt der Unterschied zum kompletten Neuaufbau? Ich habe dazu - natürlich - einen kleinen Test gemacht; aber wahrscheinlich hätte man sich die Antwort auch an zwei Fingern abzählen können ... Der Test erfolgte auf meinem PC mit 11.2.0.1, Windows 7, einer Noarchivelog-DB und sehr langsamen Platten, so dass ich keine allzu großen Datenmengen brauchte, um signifikante Laufzeiten zu erreichen:

drop table test_insert_source;
drop table test_insert_ind_recreate;
drop table test_insert_ind_maintain;

-- Anlage einer Quelltabelle mit 1M rows
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;

-- Zieltabelle für Index-Anlage nach INSERT APPEND
create table test_insert_ind_recreate
as
select *
  from test_insert_source
 where 1 = 0;

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table recreate_start as
-- select *
--   from v$sesstat
--  where sid = 72;

-- Insert und folgender Aufbau der Indizes
insert /*+ append */ into test_insert_ind_recreate
select * from test_insert_source;

create index test_insert_ind_recreate_ix1 on test_insert_ind_recreate(col1);
create index test_insert_ind_recreate_ix2 on test_insert_ind_recreate(col2);
create index test_insert_ind_recreate_ix3 on test_insert_ind_recreate(col3);
create index test_insert_ind_recreate_ix4 on test_insert_ind_recreate(col4);

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table recreate_end as
-- select *
--   from v$sesstat
--  where sid = 72;


-- Zieltabelle für Index-Anlage vor INSERT APPEND
create table test_insert_ind_maintain 
as
select *
  from test_insert_source
 where 1 = 0;

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table maintain_start as
-- select *
--   from v$sesstat
--  where sid = 72;

-- Index-Anlage vor dem Insert
create index test_insert_ind_maintain_ix1 on test_insert_ind_maintain(col1);
create index test_insert_ind_maintain_ix2 on test_insert_ind_maintain(col2);
create index test_insert_ind_maintain_ix3 on test_insert_ind_maintain(col3);
create index test_insert_ind_maintain_ix4 on test_insert_ind_maintain(col4);

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

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table maintain_end as
-- select *
--   from v$sesstat
--  where sid = 72;

Man hätte natürlich auch Adrian Billigtons runstat-Script zur Erfassung der Deltas verwenden können, aber die vier Snapshot erfüllen den gleichen Zweck:

with
recreate as (
select rs.statistic#
     , re.value - rs.value value_recreate
  from recreate_start rs
     , recreate_end re
 where rs.statistic# = re.statistic#
)
,
maintain as (
select ms.statistic#
     , me.value - ms.value value_maintain
  from maintain_start ms
     , maintain_end me
 where ms.statistic# = me.statistic#
)
, 
basedata as (
select recreate.statistic# statistic#
     , recreate.value_recreate
     , maintain.value_maintain
  from recreate
     , maintain
 where recreate.statistic# = maintain.statistic#
)
select sn.name
     , basedata.value_recreate
     , basedata.value_maintain
     , basedata.value_recreate - basedata.value_maintain diff
  from v$statname sn
     , basedata
 where sn.statistic# = basedata.statistic#
   and basedata.value_recreate <> basedata.value_maintain
 order by abs(basedata.value_recreate - basedata.value_maintain) desc

Diese Query liefert mir 121 Zeilen, von denen ich hier aber nur eine kleine Auswahl aufführe, zu der ich Erklärungen habe:

NAME                                         VALUE_RECREATE VALUE_MAINTAIN       DIFF
-------------------------------------------- -------------- -------------- ----------
-- recreate ist schneller
DB time                                                1937           3565      -1628
non-idle wait time                                     1418           2263       -845
CPU used by this session                                498           1266       -768

-- recreate muss mehr Leseoperationen 
-- durchführen
table scan rows gotten                              5014711        1014711    4000000
table scans (direct read)                                 4              0          4

-- maintain erzeugt mehr undo + redo 
-- und Schreiboperationen
physical read bytes                               407265280      169582592  237682688
physical write bytes                              144875520      251494400 -106618880
redo size                                         146141776      314122640 -167980864
undo change vector size                              140052      108339092 -108199040
db block gets                                         25017         212748    -187731

-- maintain kann nicht alle Sortierungen 
-- im Speicher ausführen
physical reads direct temporary tablespace                0          20701     -20701
physical writes direct temporary tablespace               0          20701     -20701
workarea executions - optimal                            10              4          6
workarea executions - onepass                             0              6         -6
sorts (disk)                                              0              3         -3

-- Index-Maintainance 
-- ist kein Index-Aufbau!
leaf node 90-10 splits                                    0           6885      -6885
leaf node splits                                          0           6885      -6885
branch node splits                                        0             12        -12
root node splits                                          0              4         -4

Dazu noch ein paar Erläuterungen:
  • die nachträgliche Erzeugung der Indizes (recreate) benötigt für die komplette Operation ca. 19 sec gegenüber ca. 35 sec, die bei im Rahmen des INSERTs aktiven Indizes anfallen (maintain). Dabei verteilten sich diese 16 sec. Unterschied zu gleichen Teilen auf CPU und Waits
  • im recreate-Fall werden deutlich mehr Sätze gelesen: nämlich ca. 5M gegenüber 1M für den maintain-Fall. Das ist unmittelbar einleuchtend, denn im recreate-Fall muss die Tabelle natürlich für jeden Index noch einmal komplett gelesen werden.
  • der maintain-Fall ruft deutlich mehr redo und undo hervor, wobei sich die Unterschiede beim redo vermutlich aus der Verwendung einer Noarchivelog-DB ergeben, bei der APPEND-Operationen kein (oder wenig) redo erzeugen (jedenfalls für das Tabellen-Insert, die Indizes sind ein anderer Fall). Die Matrix von Tom Kyte zum Thema hatte ich schon mindestens einmal in diesem Blog verlinkt.
  • der maintain-Fall kann die erforderlichen Sortierungen nicht komplett im Speicher durchführen und muss in einigen Fällen auf "workarea executions - onepass" ausweichen.
  • der wichtigste Aspekt ist aber wohl, dass der maintain-Fall genau das tun muss, was sein Name schon andeutet: nämlich den Index sukzessive erweitern, statt ihn komplett neu aufbauen zu können. Zwar verschiebt die APPEND-Operation diesen Schritt ans Ende der Verarbeitung, aber er erfordert dann doch das allmähliche Erweitern der Index-Struktur um neue Knoten, was Splits auf Root-, Branch- und Leaf-Ebene hervorruft.
Der praktische Fall, für den sich die Frage nach der Performance der beiden Verfahren stellte, betraf die initiale Befüllung vorher leerer Tabellen - aber Oracle kann das nicht wissen und deshalb kann die Maintainance nicht durch einen kompletten Neuaufbau ersetzt werden. Kürzlich hat Alberto Dell'Era darüber geschrieben, dass Oracle in 11.2.0.3 den fast refresh einer MV, deren Parent MV über complete refresh neu gefüllt wurde, stillschweigend in einen complete refresh umwandelt (weil der complete refresh in solchen Fällen deutlich flotter ist als der fast refresh, der wirklich besser incremental refresh genannt worden wäre ...). Eine solche Operationsumwandlung wäre hier ebenfalls nett, aber vielleicht ein wenig viel verlangt. Sie landet in der gleichen Kategorie wie die (nicht existierende) Force-Option für's DROP TABLE, mit der ich manchmal gerne monumentale Rollback-Operationen für Tabellen abkürzen würde, die keinerlei relevanten Inhalt enthalten.

Keine Kommentare:

Kommentar veröffentlichen