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
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.
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 ...
Keine Kommentare:
Kommentar veröffentlichen