FORWISS
Oracle Measurement Results
Prof. Bayer, PhD
Dipl.-Inform. Volker Markl Roland Pieringer
FORWISS
Contents
Environment of the benchmark suite
Results of the measurements
– Example measurement suite
– Comparison of the operating systems SUN Solaris and Windows NT – Variation of the sizes of the tuples
– Variation of the number of tuples
– Variation of the number of restricted dimensions
Future Work
FORWISS
Environment of the benchmark suite
Measures on two machines:
– Compaq PC (4 Intel Processors, 200 MHz, 512 MB RAM) with Oracle 8.0.4.0.0
– SUN Ultra 2 (2 Ultra Sparc Processors, 200 MHz, 1 GB RAM) with Oracle 8.0.4.0.0
Data uniformly distributed and created by a program
Elimination of cache side effects by NOCACHE
FORWISS
Used indexes
UB:
UB Tree (index that causes this meeting)
COMPOUND:
concatenation of several indexes to get a multi dimensional index
in Oracle as index only table implemented
MULT:
secondary indexes on all index attributes
SCAN:
no index, relation scan, in Oracle as full table scan
FORWISS
Kinds of benchmarks
c% measurements:
Q = (I1,...,In) Ik = 1 % ..100 % I1, ..., Ik-1, Ik+1, In = c % random starting point
Cube measurements:
Q=(I1,...,In) Ii = 0% .. 100%, for i = 1,...,n fix starting point
20%
40%
60%
80%
Q20%
Q20%
Q40%
Q40%
Q60%
Q60%
Q80%
Q80%
FORWISS
Results of the measurements
Complete benchmarks suite
Comparison of Windows NT and SUN Solaris
Variation of the size of tuples
Variation of the number of tuples
Variation of the number of restricted dimensions
FORWISS
0 50000 100000 150000 200000 250000 300000 350000 400000 450000
0 20 40 60 80 100
restriction in %
time in ms
UB, x1 var. Compound, x1 var. Relationenscan Sekundärindexe UB, x2 var. Compound, x2 var.
20% measure with 5 restricted dimensions
(250K tuples, tuple size 428 Byte)
FORWISS
0 50000 100000 150000 200000 250000 300000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB, x1 var. Compound, x1 var. Relationenscan Sekundärindex UB, x2 var. Compound, x2 var.
35% measure with 5 restricted dimensions
(250K tuples, tuple size 428 Byte)
FORWISS
Cube measure
(250K tuples, tuple size 428 Byte)
0 50000 100000 150000 200000 250000 300000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB Compound Scan Sekundärindex
FORWISS
Solaris - NT:
35% measure (125K tuples, 428 Byte tuple size) with SUN
0,000 20000,000 40000,000 60000,000 80000,000 100000,000 120000,000 140000,000 160000,000 180000,000 200000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB, x1 var. Compound, x1 var. Relationenscan Sekundärindex UB, x2 var. Compound, x2 var.
FORWISS
Solaris - NT:
35% measure (125K tuples, 428 Byte tuple size) with NT
0,000 50000,000 100000,000 150000,000 200000,000 250000,000 300000,000 350000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB, x1 var. Compound, x1 var. Relationenscan Sekundärindex UB, x2 var. Compound, x2 var.
FORWISS
Comparison of tuple size
Intersection of compound and relation scan:
– small tuples: about 10%
– large tuples: about 8%
Intersection UB Tree and relation scan:
– small tuples: about 65%
– large tuples: about 47%
Decrease of performance of the relation scan:
– small tuples: factor 4 – large tuples: factor 1.4
FORWISS
Comparison of the number of tuples
Cube measure (1M tuples, 228 Byte tuple size)
0,000 100000,000 200000,000 300000,000 400000,000 500000,000 600000,000 700000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
Compound Scan UB
FORWISS
Comparison of the number of tuples
Cube measure (2M tuples, 228 Byte tuple size)
0 200000 400000 600000 800000 1000000 1200000 1400000 1600000 1800000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB Compound Scan
FORWISS
Comparison of the number of tuples
Cube measure (4M tuples, 228 Byte tuple size)
0,000 100000,000 200000,000 300000,000 400000,000 500000,000 600000,000 700000,000 800000,000 900000,000 1000000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB Compound Scan
FORWISS
Variation of the number of restricted dimensions
Cube measure (250K tuples, 428 Byte tuple size)
0,000 50000,000 100000,000 150000,000 200000,000 250000,000 300000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB, 2 Dimensionen Compound Scan UB, 3 Dimensionen UB, 4 Dimensionen
UB, 1 Dimension UB, 5 Dimensionen UB, 6 Dimensionen Compound x2 var.
FORWISS
Variation of the number of restricted dimensions
35% measure (250K tuples, 428 Byte tuple size)
0,000 50000,000 100000,000 150000,000 200000,000 250000,000 300000,000
0 10 20 30 40 50 60 70 80 90 100
restriction in %
time in ms
UB, 0 at 35% UB, 1 at 35% UB, 2 at 35% UB, 3 at 35% UB, 4 at 35%
UB, 5 at 35% Comp, 0 at 35% Comp, 1 at 35% Comp, 2 at 35% Comp, 3 at 35%
Comp, 4 at 35% Comp, 5 at 35% Compound, x2 var.
