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