- Fall 1: eine Lauf-Partition mit 10M rows und eine Default-Partition mit 0 rows aufteile
- Fall 2: eine Lauf-Partition mit 5M rows und eine Default-Partition mit 5M rows aufteile
Die Analyse erfolgt dabei mit Adrian Billingtons Version des runstats Utilities von Tom Kyte.
-- test_split_partition.sql
-- Test zur Ressourcennutzung von Split Partition
-- 20.07.2012
-- Aufbau einer Testtabelle mit 10M Sätzen in einer Default-Partition
-- (5M rows für Lauf_Id 1; 5M rows für Lauf_Id 2; 5M rows für Lauf_Id 3)
drop table test_partition_split1;
create table test_partition_split1 (
lauf_id number
, padding varchar2(100)
)
partition by list( lauf_id )
(
partition p_max values (default)
);
insert into test_partition_split1
select 1 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split1
select 2 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats(user, 'test_partition_split1')
-- Aufbau einer identischen zweiten Testtabelle
drop table test_partition_split2;
create table test_partition_split2 (
lauf_id number
, padding varchar2(100)
)
partition by list( lauf_id )
(
partition p_max values (default)
);
insert into test_partition_split2
select 1 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split2
select 2 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats(user, 'test_partition_split2')
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql start
-- Test 1
-- Split in p_lauf mit 10M rows und p_max ohne Daten
alter table test_partition_split1
split partition p_max values (1, 2)
into (partition p_lauf, partition p_max);
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql middle
-- Test 2
-- Split in p_lauf mit 5M rows und p_max mit 5M rows
alter table test_partition_split2
split partition p_max values (1)
into (partition p_lauf, partition p_max);
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql stop l=write
Hier die (um Statistikwerte, die für beide Operationen = 0 sind, gekürzte) Runstat-Ausgabe:
=============================================================================================== RunStats report : 20-JUL-2012 09:54:36 =============================================================================================== ----------------------------------------------------------------------------------------------- 1. Summary timings ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ TIMER elapsed time (hsecs) 285 951 666 TIMER cpu time (hsecs) 53 181 128 Comments: 1) Run1 was 70% quicker than Run2 2) Run1 used 70% less CPU time than Run2 ----------------------------------------------------------------------------------------------- 2. Statistics report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ ... STAT change write time 0 1 1 STAT physical write total multi block requests 0 950 950 STAT physical write IO requests 0 968 968 STAT physical write total IO requests 0 968 968 STAT physical writes 0 30,304 30,304 STAT physical writes direct 0 30,304 30,304 STAT physical writes non checkpoint 0 30,304 30,304 STAT redo size for direct writes 0 50,380 50,380 STAT physical write bytes 0 248,250,368 248,250,368 STAT physical write total bytes 0 248,250,368 248,250,368 ----------------------------------------------------------------------------------------------- 3. Latching report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ LATCH total latches used 198,222 397,045 198,823 Comments: 1) Run1 used 50,1% fewer latches than Run2 ----------------------------------------------------------------------------------------------- 4. Time model report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ TIME hard parse (sharing criteria) elapsed time 0 502 502 TIME hard parse elapsed time 2,538 3,128 590 TIME PL/SQL execution elapsed time 13,356 14,077 721 TIME parse time elapsed 3,514 9,784 6,270 TIME DB CPU 531,250 1,859,375 1,328,125 TIME sql execute elapsed time 2,614,057 9,367,270 6,753,213 TIME DB time 2,688,528 9,481,900 6,793,372 ----------------------------------------------------------------------------------------------- 5. About ----------------------------------------------------------------------------------------------- - RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net) - Based on the original RUNSTATS utility by Tom Kyte =============================================================================================== End of report ===============================================================================================
Demnach fallen in Fall 1 keine Write-Operationen an, während Fall 2 recht massive physikalische Schreiboperationen hervorruft. Also scheint Fall 1 tatsächlich eine reine Metadaten-Operation zu sein, während Fall 2 eine echte Reorganisation darstellt.
Im Anschluss noch eine zweite Frage: wie clever ist Oracle bei der Durchführung eines Splits einer Partition in zwei mit unterschiedlichen Datenmengen gefüllte Teile? Anders ausgedrückt: kann sich der Transfer darauf beschränken, die kleinere Datenmenge in eine neue Partition zu verschieben, oder spielt in diesem Fall die Definition des Splittings eine größere Rolle? Dazu ergänze ich in den Tabellen des ersten Tests eine dritte Lauf_id, zu der ebenfalls 5M rows gehören. Anschließend splitte ich die Tabelle so, dass in:
- Fall 1: Lauf-Partition mit 10M rows, Default-Partition mit 5M rows
- Fall 2: Lauf-Partition mit 5M rows, Default-Partition mit 10M rows
-- test_split_partition2.sql
-- Zweiter Test zur Ressourcennutzung von Split Partition
-- 20.07.2012
-- Aufbau einer Testtabelle mit 15M Sätzen in einer Default-Partition
-- (5M rows für Lauf_Id 1; 5M rows für Lauf_Id 2; 5M rows für Lauf_Id 3)
drop table test_partition_split1;
create table test_partition_split1 (
lauf_id number
, padding varchar2(100)
)
partition by list( lauf_id )
(
partition p_max values (default)
);
insert into test_partition_split1
select 1 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split1
select 2 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split1
select 3 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats(user, 'test_partition_split1')
-- Aufbau einer identischen zweiten Testtabelle
drop table test_partition_split2;
create table test_partition_split2 (
lauf_id number
, padding varchar2(100)
)
partition by list( lauf_id )
(
partition p_max values (default)
);
insert into test_partition_split2
select 1 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split2
select 2 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
insert into test_partition_split2
select 3 lauf_id
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats(user, 'test_partition_split2')
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql start
-- Test 1
-- Split in p_lauf mit 10M rows und p_max ohne Daten
alter table test_partition_split1
split partition p_max values (1, 2)
into (partition p_lauf, partition p_max);
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql middle
-- Test 2
-- Split in p_lauf mit 5M rows und p_max mit 5M rows
alter table test_partition_split2
split partition p_max values (1)
into (partition p_lauf, partition p_max);
@ D:\db_scripts\reporting\sqlpath\billington\runstats.sql stop l=write
Für diesen Fall ergibt sich folgendes Resultat:
=============================================================================================== RunStats report : 20-JUL-2012 10:23:33 =============================================================================================== ----------------------------------------------------------------------------------------------- 1. Summary timings ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ TIMER elapsed time (hsecs) 1,405 1,340 -65 TIMER cpu time (hsecs) 252 217 -35 Comments: 1) Run2 was 4,6% quicker than Run1 2) Run2 used 4,6% less CPU time than Run1 ----------------------------------------------------------------------------------------------- 2. Statistics report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ STAT physical write bytes 372,375,552 372,375,552 0 STAT physical write total bytes 372,375,552 372,375,552 0 STAT physical write total multi block requests 1,425 1,425 0 STAT physical writes 45,456 45,456 0 STAT physical writes direct 45,456 45,456 0 STAT physical writes non checkpoint 45,456 45,456 0 STAT physical write IO requests 1,446 1,448 2 STAT physical write total IO requests 1,446 1,448 2 STAT redo size for direct writes 75,324 75,384 60 ----------------------------------------------------------------------------------------------- 3. Latching report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ LATCH total latches used 569,083 487,430 -81,653 Comments: 1) Run2 used 14,3% fewer latches than Run1 ----------------------------------------------------------------------------------------------- 4. Time model report ----------------------------------------------------------------------------------------------- Type Name Run1 Run2 Diff ----- -------------------------------------------------- ------------ ------------ ------------ TIME repeated bind elapsed time 5 15 10 ----------------------------------------------------------------------------------------------- 5. About ----------------------------------------------------------------------------------------------- - RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net) - Based on the original RUNSTATS utility by Tom Kyte =============================================================================================== End of report ===============================================================================================
Beide Operationen rufen demnach die gleiche Ressourcennutzung hervor: offenbar kann Oracle in diesem Fall also sehr genau bestimmen, wie das Splitting am effizientesten durchgeführt werden kann.
Keine Kommentare:
Kommentar veröffentlichen