32
Oracle Datenbank / Ubuntu Sebastian Gath & Hannes Schwarz Seminar Database Tuning & Administration“ Universit¨ at Konstanz - SS 2007

Varian, Inc. Vacuum Technologies -

  • Upload
    others

  • View
    2

  • Download
    0

Embed Size (px)

Citation preview

Oracle Datenbank / Ubuntu

Sebastian Gath & Hannes SchwarzSeminar

”Database Tuning & Administration“

Universitat Konstanz - SS 2007

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

AdministrationVorbereitungZeitmessungErste Zeitmessung

AnfragenoptimierungenAnfrage 13Anfrage 22Anfrage 2Anfrage 3

DatenbankoptimierungenAusgangssituation & MoglichkeitenErgebnisse

Fazit

2 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

VorbereitungZeitmessungErste Zeitmessung

Vorbereitung

1. Installation Ubuntu 6.10

2. Installation Oracle (dkpg oracle...)I Konfiguration (/etc/init.d/oracle-xe configure)I User anlegen (Administrationsoberflache)

3. Installation TPC (Makefile)I Einstellungen:

Database DB2Machine Linux

4. Erstellen der Daten (dbgen scale 1.0)

5. Erstellen/Fullen der Tabellen (sqlplus/sqlldr)

3 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

VorbereitungZeitmessungErste Zeitmessung

Anmerkungen

I Treiberprobleme bei Ubuntu (Grafikkarte)

I Libraries fur gcc fehlten

I Datumsformat anpassen→ alter system set nls date format=‘YYYY-MM-DDHH24:MI:SS’ scope=spfile;

4 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

VorbereitungZeitmessungErste Zeitmessung

ShellSkript - Zeitmessung

#! /bin/shecho Jede Anfrage 1000 mal >> zeit.txtfor i in *.sqldo

echo XXXXXXX $i XXXXXXXXX >> zeit.txtfor x in ‘seq 1 1000’do

sqlplus oracle/schnitzel @$i | grep Elapsed >> zeit.txtdone

done

5 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

VorbereitungZeitmessungErste Zeitmessung

Laufzeiten ohne Optimierungen

6 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Anfrage 13

select c count, count(*) as custdistfrom (

select c custkey, count(o orderkey) c countfrom customer left outer join orders on

c custkey = o custkeyand o comment not like ’%special%requests%’

group by c custkey) c orders

group c countorder by custdist desc, c count desc;

7 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Ausfuhrungsplan

8 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee I

I SubstringsucheI Vermeiden von Substringsuche auf Attribut o comment

I Einfugen und Fullen von neuem Attribut o nonspecialI Special request → o nonspecial=1

I Resultierende Anfrage (gekurzt):select ... from (

select c custkey, count(o orderkey) c countfrom customer left outer join orders on

c custkey = o custkeyand o nonspecial=1

group by c custkey) c orders

...

9 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Tuning I die Effekte

I Einfugen und Fullen von neuem Attribut o nonspecialI Speicherverbrauch steigtI ABER: Anpassung in Anwendungsprogramm notwendigI Laufzeit sinkt von 3.05 sek auf 2.48 sek (ca. um 20% schneller)

I Weitere Optimierungsschritte:I Index auf o nonspecial (Verschlechterung ?)I Partitionierung der Tabelle (Effekt auf andere Anfragen ?)

10 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee II

I Umgehen des left outer join orders on c custkey = o custkey

I Tabellen orders und customer geclustered gespeichert(Primarschlussel)

I Vereinigungsmenge von Tabelle orders und neu erstelltemCluster berechnen

I Effekte

I Speicher durch Cluster effizienter genutztI Schlechtere Performance bei Anfragen auf nicht

ClusterparameterI Einfugen und Veranderungen langsamer (Hashcluster fixe

Große)I Im konkreten Fall drastische Performanceeinbußen

11 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Anfrage 22select cntrycode, count(*) numcust, sum(c acctbal) totacctbalfrom (select substr(c phone, 1, 2) cntrycode, c acctbal

from customerwhere substr(c phone, 1, 2)

in (’13’, ’31’, ’23’, ’29’, ’30’, ’18’, ’17’)and c acctbal > (select avg(c acctbal)

from customerwhere c acctbal > 0.00and substr(c phone, 1, 2) in

(’13’, ’31’, ’23’, ’29’, ’30’, ’18’, ’17’))and not exists (select *

from orderswhere o custkey = c custkey)) custsale

group by cntrycodeorder by cntrycode;

12 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Ausfuhrungsplan Anfrage 22

13 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee II Substringsuche

I Definition eines Textindex auf das Attribut c phoneI Vermeiden von Substringsuche auf Attribut c phone

I Einfugen und Fullen von neuem Attribut c areacodeI Kurzen von c phone um die Vorwahl

I Resultierende Anfrage (gekurzt):select...where (c areacode = 13 or c areacode = 31 ...)and c acctbal > (select ...

where c acctbal > 0.00and (c areacode = 13 or c areacode = 31 ...))

and not exists (select *...)

group by cntrycode...

14 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee II

I And not exsists mit anschließendem Subselect durchMengenoperator minus ausdrucken

I Resultierende Anfrage (gekurzt):select...from customerAC,

(select c custkey minkey from customerACminus select o custkey from orders ) ominu

where (c areacode = 13 or c areacode = 31 ...)and c acctbal > (select ...

where c acctbal > 0.00and (c areacode = 13 or c areacode = 31 ...))

group by cntrycode...

15 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Effekte

I Definition eines Textindex auf das Attribut c phoneI Speicherverbrauch steigt (150.000 Datensatze, Varchar(30))I Anfrage- und Updatepeformance nimmt ab

I Einfugen und Fullen von neuem Attribut c areacodeI Speicherverbrauch unverandertI ABER: Anpassung in Anwendungsprogramm notwendigI Laufzeit sinkt von 1.98 sek auf 1.51 sek (ca. um 25% schneller)

I And not exsists mit anschließendem Subselect durchMengenoperator minus ausdrucken

I Laufzeit steigt von 1.98 sek auf 3.61 sek (ca. Verdoppelung)I Customer 150.000, Orders 1.500.000 Eintrage

16 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Anfrage 2

select ...from part, supplier, partsupp, nation, regionwhere p partkey = ps partkey and s suppkey = ps suppkey

and p size = 15 and p type like ’%BRASS’and s nationkey = n nationkey and n regionkey = r regionkeyand r name = ’EUROPE’and ps supplycost = (

select min(ps supplycost)from partsupp, supplier, nation, regionwhere p partkey = ps partkey

and s suppkey = ps suppkeyand s nationkey = n nationkeyand n regionkey = r regionkeyand r name = ’EUROPE’ )

order by s acctbal desc, n name, s name, p partkey;

17 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Ausfuhrungsplan

18 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Tuningideen I

I Idee 1: Selektion p size = 15and p type like ’%BRASS’beschleunigen

I Bitmapindex auf p size definieren → geht nicht, wahrscheinlichgeringer Effekt

I Textindex auf p type definieren → speicherintensiv,Verschlechterung der Performance da fuhrende Wildcard

I Idee 2: Unteranfrage durch Materialized View undPartitionierung beschleunigen

I Materialized View mit r name, p partkey undmin(ps supplycost) as min supplycost anlegen

I Partitionierung nach r nameI Index auf p partkey legen und min supplycost aus Statistik

ermitteln

19 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Tuningideen II

I Idee 3: Tabellen nation/region geclustered speichern

I Idee 4: Unteranfrage mit Index auf p partkey; Partionierungmaterialisieren

select p partkey as vtun partkey,min(ps supplycost) as min supplycost

from partsupp, supplier, nation, regionwhere p partkey = ps partkey

and s suppkey = ps suppkeyand s nationkey = n nationkeyand n regionkey = r regionkeyand r name = ’EUROPE’

group by p partkey

20 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Tuningidee 4

select ...from part, supplier, partsupp, nation, region, Vtuning2where p partkey = ps partkey

and p partkey = vtun partkeyand s suppkey = ps suppkeyand p size = 15and p type like ’%BRASS’and s nationkey = n nationkeyand n regionkey = r regionkeyand r name = ’EUROPE’and ps supplycost = min supplycost

order by s acctbal desc, n name, s name, p partkey;

21 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Effekte

I Idee 3 - ClusterI Die Auswirkungen sind minimal, da Tabellen sehr klein

I Idee 4 - Materialized ViewI Speicherverbrauch steigtI Refresh-Clause muss angegeben werden (ON COMMIT)I Laufzeit sinkt von 0.39 sek auf 0.31 sek (ca. 20% schneller)

22 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Anfrage 3

select l orderkey, sum(l extendedprice * (1 - l discount)) revenue,o orderdate, o shippriority

from customer, orders, lineitemwhere c mktsegment = ’BUILDING’ and c custkey = o custkeyand l orderkey = o orderkeyand o orderdate < date ’1995-03-15’and l shipdate > date ’1995-03-15’group by l orderkey, o orderdate, o shippriorityorder by revenue desc, o orderdate;

23 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Ausfuhrungsplan Anfrage 3

24 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee I

I Laufzeit ohne Tuning 0.95 sek

I o orderdate < date ’1995-03-15’ durch Materialized Viewund Index beschleunigen

I 623 orderkeys je o orderdate vorhanden → Materialized Viewmit o orderdate, o orderkey, o custkey, o shippriority

I Partitionierung nach o orderdate je Jahr; Bitmapindex aufo orderdate je Jahr/Monat → nicht erlaubt ...

I Laufzeit mit Tuning I 0.26 sek (ca. um 73% schneller)

25 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Die Tuningidee II

I Selektion where c mktsegment = ’BUILDING’ beschleunigenI table access full auf 150.000 Eintragen in Tabelle customerI davon erfullen 30.142 das SelektionskriteriumI Bitmapindex auf c mktsegment definierenI geht nicht ...

I Laufzeit mit Tuning I 0.26 sek (ca. um 73% schneller)

26 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Anfrage 13Anfrage 22Anfrage 2Anfrage 3

Ergebnisse im Uberblick

2 3 13 22

Nicht optimiertOptimiert

Queryname

Lauf

zeit

(sek

)

0.0

0.5

1.0

1.5

2.0

2.5

3.0

27 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Ausgangssituation & MoglichkeitenErgebnisse

Ausgangssituation & Moglichkeiten

I Hauptspeicher den Oracle zur Verfugung hat: 236 MB SGAI Alter system set sga max size = 236MI Buffer Cache

Cache fur Datenbanksegmente, z.B. Tabellen, Indexedb 8k cache size = 64 * 1024 * 1024 Byteselect * from v$shared pool advice;

I Shared PoolAusfuhrungsplane, Data Dictionary Cache

I Tabellen mit Primary Key versehen

I Keine Tabelle dauerhaft im Cachealter table tab storage (buffer pool keep);

28 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Ausgangssituation & MoglichkeitenErgebnisse

Laufzeiten unter verschiedenen Einstellungen

29 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Fazit

I Viele Maßnahmen nicht moglich (Express)I PartitionierungI BitmapindexI Index auf Materialized ViewI Maximal Datenbankgroße ca. 5 GBI SQL Tuning Advisor, Access AdvisorI ...

I Oracles Anfragenoptimierer optimiert sehr gut

I Indexe verlangsamen oft

I Dennoch Performancesteigerungen mit akzeptablen Kostenmoglich

30 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Literatur

I Loney, Kevin, Oracle Database 10g. Die umfassende Referenz;Hanser 2005

I Haas, Frank; Oracle-Tuning in der Praxis Munchen; Hanser2005

I http://www.oracle.com/pls/db102/homepage

31 / 32

AdministrationAnfragenoptimierungen

DatenbankoptimierungenFazit

Vielen Dank fur die Aufmerksamkeit.

32 / 32