Oracle Optimierung & Tuning Tipps und Tricks. Andreas Widmann Oracle Optimierung & Tuning Themen Einführung Einführung Tools sqlplus & tkprofTools sqlplus

Oracle Optimierung & TuningOracle Optimierung & Tuning

Tipps und TricksTipps und Tricks

Oracle Optimierung & Tuning Oracle Optimierung & Tuning

Andreas Widmann ThemenThemen

• EinführungEinführung

•Tools sqlplus & tkprofTools sqlplus & tkprof•Datenbank ParameterDatenbank Parameter•Beispiel Session SQLPLUSBeispiel Session SQLPLUS•Beispiel TKPROFBeispiel TKPROF•Explain planExplain plan

•Identifikation von LangläufernIdentifikation von Langläufern•Buffer_getsBuffer_gets•Disk_readsDisk_reads•Aktive sessionsAktive sessions•V$longopsV$longops

• die SQL-Optimizerdie SQL-Optimizer•Rule basedRule based•CostbasedCostbased•Funtionsweise des optimizersFuntionsweise des optimizers

• Optimierungs & TuningsbeispieleOptimierungs & Tuningsbeispiele

• Verwendung von HintsVerwendung von Hints•appendappend

•Betriebsystem Tools (vmstat,sar)Betriebsystem Tools (vmstat,sar)

•PerfstatPerfstat

• Zusammenfassung AusblickZusammenfassung Ausblick•Analyse (sqlplus,tkprof,Langläufer)Analyse (sqlplus,tkprof,Langläufer)•Optimierung zugriffspfadeOptimierung zugriffspfade•Bind variablenBind variablen•Cursor sharing similarCursor sharing similar•ParallelisierungParallelisierung•PartitionierungPartitionierung•Rebuild index – foa all indexed columnsRebuild index – foa all indexed columns•Dbms_stats (monitoring..Dbms_stats (monitoring..


Andreas Widmann EinführungEinführung

Erfahrungen haben gezeigt, dass bei Optimierung und Tuning Maßnahmen im Oracle Umfeld die Potentiale zu 80% auf derErfahrungen haben gezeigt, dass bei Optimierung und Tuning Maßnahmen im Oracle Umfeld die Potentiale zu 80% auf derApplikationsseite und 20% auf der Systemseite liegen.Applikationsseite und 20% auf der Systemseite liegen.

Das zentrale Problem liegt in der Identifikation der relevanten SQL-Statements. Üblicherweise belasten wenige Prozent Das zentrale Problem liegt in der Identifikation der relevanten SQL-Statements. Üblicherweise belasten wenige Prozent der Anfragen die Datenbank bis zu 90%.der Anfragen die Datenbank bis zu 90%.


Andreas Widmann Analyse in der Praxis Datenbank ParameterAnalyse in der Praxis Datenbank Parameter

Notwendige VoraussetzungenNotwendige Voraussetzungendie Konfigurationsparamter im init/spfile.ora können folgende Werte annehmen:die Konfigurationsparamter im init/spfile.ora können folgende Werte annehmen:

sql_trace=truesql_trace=trueDeaktiviert oder aktiviert die SQL-Trace-Funktion. Bei TRUE werden Optimierungsinformationen erfasst, mit denen die Leistung verbessrtDeaktiviert oder aktiviert die SQL-Trace-Funktion. Bei TRUE werden Optimierungsinformationen erfasst, mit denen die Leistung verbessrtwerden kann. Weil die SQL-Trace-Funktion einen System-Overhead verursacht, sollte TRUE nur benutzt werden, wennwerden kann. Weil die SQL-Trace-Funktion einen System-Overhead verursacht, sollte TRUE nur benutzt werden, wennOptimierungsinformationen erforderlich sind. Optimierungsinformationen erforderlich sind. Wertebereich: TRUE | FALSE Wertebereich: TRUE | FALSE Standardwert : FALSEStandardwert : FALSE

time_statistics=truetime_statistics=trueErfasst Betriebssystem-Timing-Informationen, mit denen die Datenbank und SQL-Anweisungen optimiert werden Erfasst Betriebssystem-Timing-Informationen, mit denen die Datenbank und SQL-Anweisungen optimiert werden können. Um zu verhindern, dass die Zeit vom Betriebssystem angefordert werden muss, legen Sie diesen Wert auf Null fest.können. Um zu verhindern, dass die Zeit vom Betriebssystem angefordert werden muss, legen Sie diesen Wert auf Null fest.Ein Wert von TRUE kann nützlich sein, um den Fortschritt von lange andauernden Vorgängen anzuzeigen. Ein Wert von TRUE kann nützlich sein, um den Fortschritt von lange andauernden Vorgängen anzuzeigen. Wertebereich: TRUE | FALSE Wertebereich: TRUE | FALSE Standardwert : FALSEStandardwert : FALSE

trace_enabled=truetrace_enabled=trueDer Oracle Server schreibt Fehlermeldungen der Oracle Prozesse in diverse Diagnosefiles. Dieses Flag sollte immer eingeschaltet sein.Der Oracle Server schreibt Fehlermeldungen der Oracle Prozesse in diverse Diagnosefiles. Dieses Flag sollte immer eingeschaltet sein.Wertebereich: TRUE | FALSE Wertebereich: TRUE | FALSE Standardwert : TRUEStandardwert : TRUE

tracefile_identfier=tracefile_identfier=Festlegen eines Kürzels für die TraceAusgabe auf dem Server.Festlegen eines Kürzels für die TraceAusgabe auf dem Server.Kein Standardwert :Kein Standardwert :

background_dump_dest,user_dump_dest,max_dump_file_sizebackground_dump_dest,user_dump_dest,max_dump_file_sizeFestlegen der Verzeichnisse auf dem Server, maximale Größe der traceFiles Festlegen der Verzeichnisse auf dem Server, maximale Größe der traceFiles


Andreas Widmann

Abgelaufen: Abgelaufen: 00:00:05.08 Ausführungsplan00:00:05.08 Ausführungsplan---------------------------------------------------------- ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DUAL' 2 1 TABLE ACCESS (FULL) OF 'DUAL'

StatistikenStatistiken-------------- -------------- 0 recursive calls 0 recursive calls 0 db block gets 0 db block gets 3 consistent gets 3 consistent gets 0 physical reads 0 physical reads 0 redo size 0 redo size 212 bytes sent via SQL*Net to client 212 bytes sent via SQL*Net to client 271 bytes received via SQL*Net from client 271 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (memory) 0 sorts (disk) 0 sorts (disk) 1 rows processed 1 rows processed

Analyse Tools in der Praxis Ausführungsplan und ZeitermittlungAnalyse Tools in der Praxis Ausführungsplan und Zeitermittlung

Beispiel Session mit SQL-PLUSBeispiel Session mit SQL-PLUS

SQL> set autotrace onSQL> set autotrace onSQL> set timing onSQL> set timing onSQL> select count(*) from dual;SQL> select count(*) from dual;COUNT(*) COUNT(*) ---------- ---------- 1 1


Andreas Widmann

------ start der applikation Package,oderSQL-- start der applikation Package,oderSQL-- -- select select

count(*) into icountcount(*) into icount from from

user_tables a,user_tab_columns buser_tables a,user_tab_columns b wherewhere

a.table_name=b.table_name anda.table_name=b.table_name and a.table_name like 'B%'a.table_name like 'B%' ; ;

---- aufräumen-- aufräumen-- -- dbms_session.set_sql_trace (sql_trace => false);dbms_session.set_sql_trace (sql_trace => false); dbms_application_info.set_action(action_name => '' );dbms_application_info.set_action(action_name => '' ); end;end;//

Analyse Tools in der Praxis – tkprofAnalyse Tools in der Praxis – tkprof

Beispiel SessionBeispiel Session

declaredeclare icount number;icount number;beginbegin------ vorbereitungen-- vorbereitungen-- -- execute immediate 'alter session set tracefile_identifier = mytrc'; execute immediate 'alter session set tracefile_identifier = mytrc'; dbms_application_info.set_action(action_name => 'start mytrace' );dbms_application_info.set_action(action_name => 'start mytrace' ); dbms_session.set_sql_trace (sql_trace => true);dbms_session.set_sql_trace (sql_trace => true);


Andreas Widmann

Beispiel SessionBeispiel Session

Auf dem Datenbank Server wurde ein Tracefile für diese Session im udump-Verzeichnis angelegtAuf dem Datenbank Server wurde ein Tracefile für diese Session im udump-Verzeichnis angelegt

inst3_ora_16684_MYTRC.trcinst3_ora_16684_MYTRC.trc

tkprof inst3_ora_16684_MYTRC.trc inst3_ora_16684_MYTRC.txt explain=usr/pwd@inst3 sort=fchela

Die von der Datenbank geschriebenen Trace Dateien sind nur schwer lesbar, deshalb müssen diese mit dem Programmtkprof auf eine lesbare Form gebracht werden. Übliche Sortierungen haben sich bewährt:

Sortierung nach AusführungszeitSort=prsela,exeela,fchela

Sortierung nach Anzahl AufrufeSort=prscnt,execnt,fchcnt

Analyse Tools in der Praxis – tkprof - auswertenAnalyse Tools in der Praxis – tkprof - auswerten


Andreas Widmann

select select executions,executions, to_number(to_char((disk_reads /executions) /300,'9999')) Antwortzeit_in_s,to_number(to_char((disk_reads /executions) /300,'9999')) Antwortzeit_in_s, sql_textsql_text

from from v$sqlv$sql

wherewhere (disk_reads /executions) /300 >10(disk_reads /executions) /300 >10

and executions > 0and executions > 0order by 2 descorder by 2 desc

Identifikation von Langläufern über disk_readsIdentifikation von Langläufern über disk_reads

AnnahmenAnnahmenDer Datenbankserver kann 300 Disk/IO‘s pro Sekunde ausführenDer Datenbankserver kann 300 Disk/IO‘s pro Sekunde ausführen


Andreas Widmann

selectselect executions, executions, to_number(to_char((buffer_gets /executions) /4000,'9999')) Antwortzeit_in_s, to_number(to_char((buffer_gets /executions) /4000,'9999')) Antwortzeit_in_s, sql_text sql_text

fromfrom v$sql v$sql

wherewhere (buffer_gets /executions) /4000 >10 (buffer_gets /executions) /4000 >10

andand executions > 0 executions > 0orderorder byby 2 2 descdesc

Identifikation von Langläufern über buffer_getsIdentifikation von Langläufern über buffer_gets

AnnahmenAnnahmenDer Datenbankserver kann 4000 buffer gets pro Sekunde ausführenDer Datenbankserver kann 4000 buffer gets pro Sekunde ausführen


Andreas Widmann

SELECT SELECT

start_time,start_time,

sofar Anzahl_Einheiten_getan,sofar Anzahl_Einheiten_getan,

totalwork Einheiten_zu_tun,totalwork Einheiten_zu_tun,

units Einheit,units Einheit,

elapsed_seconds,elapsed_seconds,

sql_textsql_text

FROM FROM

v$sql,v$session_longopsv$sql,v$session_longops

WHERE WHERE

sql_address = addresssql_address = address

AND sql_hash_value = hash_valueAND sql_hash_value = hash_value

AND start_time >= SYSDATE -10AND start_time >= SYSDATE -10

-- AND sofar <> totalwork-- AND sofar <> totalwork

ORDER BY ORDER BY

start_time DESC;start_time DESC;

Identifikation von Langläufern über v$session_longopsIdentifikation von Langläufern über v$session_longops

AnmerkungenAnmerkungen

Das Verhältnis von sofar/totalwork ergibt den %Staz der Verarbeitung des sql StatementsDas Verhältnis von sofar/totalwork ergibt den %Staz der Verarbeitung des sql Statements


Andreas Widmann

SELECT SELECT

s.status "Status", s.serial# "Serial#", s.TYPE "Type",s.status "Status", s.serial# "Serial#", s.TYPE "Type",

s.username "DB User", s.osuser "Client User", s.server "Server",s.username "DB User", s.osuser "Client User", s.server "Server",

s.machine "Machine", s.module "Module", s.client_info "Client Info",s.machine "Machine", s.module "Module", s.client_info "Client Info",

s.terminal "Terminal", s.program "Program", p.program "O.S. Program",s.terminal "Terminal", s.program "Program", p.program "O.S. Program",

s.logon_time "Connect Time", lockwait "Lock Wait",s.logon_time "Connect Time", lockwait "Lock Wait",

si.physical_reads "Physical Reads", si.block_gets "Block Gets",si.physical_reads "Physical Reads", si.block_gets "Block Gets",

si.consistent_gets "Consistent Gets",si.consistent_gets "Consistent Gets",

si.block_changes "Block Changes",si.block_changes "Block Changes",

si.consistent_changes "Consistent Changes", s.process "Process",si.consistent_changes "Consistent Changes", s.process "Process",

p.spid, p.pid, si.SID, s.audsid, s.sql_address "Address",p.spid, p.pid, si.SID, s.audsid, s.sql_address "Address",

s.sql_hash_value "Sql Hash", s.action,s.sql_hash_value "Sql Hash", s.action,

SYSDATE - (s.last_call_et / 86400) "Last Call"SYSDATE - (s.last_call_et / 86400) "Last Call"

FROM v$session s, v$process p, SYS.v_$sess_io siFROM v$session s, v$process p, SYS.v_$sess_io si

WHERE s.paddr = p.addr(+)WHERE s.paddr = p.addr(+)

AND si.SID(+) = s.SIDAND si.SID(+) = s.SID

AND (s.username IS NOT NULL)AND (s.username IS NOT NULL)

AND (NVL (s.osuser, 'x') <> 'SYSTEM')AND (NVL (s.osuser, 'x') <> 'SYSTEM')

AND (s.TYPE <> 'BACKGROUND')AND (s.TYPE <> 'BACKGROUND')

ORDER BY 1ORDER BY 1

Ermittlung aktiver sessionsErmittlung aktiver sessions


Andreas Widmann Die SQL Die SQL OptimizerOptimizer

Jedesmal wenn eine SQL-Anweisung durchgeführt wird, muß die Datenbank entscheiden , wie die jeweilige Anweisung optimal durchgeführtJedesmal wenn eine SQL-Anweisung durchgeführt wird, muß die Datenbank entscheiden , wie die jeweilige Anweisung optimal durchgeführt

wird. Es gibt in Oracle verschiedene Möglichkeiten den Optimizer Modus einzustellen. Dazu gibt es in init.ora folgenden Parameterwird. Es gibt in Oracle verschiedene Möglichkeiten den Optimizer Modus einzustellen. Dazu gibt es in init.ora folgenden Parameter

optimizer_modeoptimizer_mode

Gibt das Verhalten des Optimizers an. Wenn dieser Parameter auf Gibt das Verhalten des Optimizers an. Wenn dieser Parameter auf RULE RULE eingestellt ist, wird der regelbasierte Optimizer benutzt, es seieingestellt ist, wird der regelbasierte Optimizer benutzt, es sei

denn, die Abfrage enthält Hinweise.(Hints) Wenn dieser Parameter auf denn, die Abfrage enthält Hinweise.(Hints) Wenn dieser Parameter auf CHOOSECHOOSE eingestellt ist, wird der statistikorientierte Optimizer eingestellt ist, wird der statistikorientierte Optimizer

benutzt, es sei denn, Tabellen in der Anweisung enthalten keine Statistiken. Bei ALL_ROWS oder FIRST_ROWS wird immer derbenutzt, es sei denn, Tabellen in der Anweisung enthalten keine Statistiken. Bei ALL_ROWS oder FIRST_ROWS wird immer der

statistikorientierte Optimizer verwendet. statistikorientierte Optimizer verwendet.

Wertebereich: RULE | CHOOSE | FIRST_ROWS | ALL_ROWS Wertebereich: RULE | CHOOSE | FIRST_ROWS | ALL_ROWS

Standardwert : CHOOSEStandardwert : CHOOSE

Vorgehensweise des kostenbasierten Optimizers

Parsen der SQL-Anweisung Erzeugen einer Liste aller potentiellen Ausführungspläne Berechnen, schätzen der Kosten jedes Ausführungsplanes unter Verwendung aller Objekt-Statistiken Auswählen des Ausführungsplanes mit den niedrigsten Kosten

Rule oder Choose ?

Eindeutige Präferenz für „Choose“, weil dieser Optimizer Mode effizienter ist. Der regelbasierte arbeitet nach Schema F und berücksichtigt keine DatenverteilungenDie Zukunft gehört dem kosten basierenden optimizer. Weiterentwicklung des RBO ist eingestelltEs gibt viele Features für den CBO. Z.B. funktionsbasierter IndexDas Risiko beim Hinzufügen neuer Indizes ist deutlich geringer


Andreas Widmann Die SQL OptimizerDie SQL Optimizer

Hinweise

Standardmäßig wird der kostenbasierte Optimizer immer dann verwendet, wenn Statistiken für die entsprechenden Objekte existieren.

Falls nicht für alle verwendeten Objekte eine Statistik existiert , so werden diese Objekte im RDBMS geschätzt.

Falls überhaupt keine Statistik existiert wird der regelbasierte Optimizer verwendet. es sei denn der Modus wird über Hints definiert.

Der CBO kann die Ausführungspläne nicht optimal bestimmen , wenn eine Tabelle analysiert wurde, aber nicht deren Indizes.

Der Einsatz von veralten Statistiken kann schädlich für die Performance sein. Im Feld last_analyzed steht das letzte Analyse Datum

Das Analysieren großer Tabellen, kann bei der Option compute sehr lange dauern. Abhilfe über estimate 3%

Der Befehl truncate löscht keine Statistiken.


Andreas Widmann

select ac_name,ac_date,ac_wertfrom actionwhere substr(ac_name,1,7) = 'TRANS';

Optimierung & Tuningbeispiele – Warum werden vorhandene Indizes nicht verwendet ??

select ac_name,ac_date,ac_wertfrom actionwhere ac_name like 'TRANS%';

Die Funktion substr deaktiviert den Index da sie auf eine indexierte Spalte angewendet wird

select ac_name,ac_date,ac_wertfrom actionwhere ac_wert != 0

Alle Referenzen von not,!=,<> deaktivieren Indizes

select ac_name,ac_date,ac_wertfrom actionwhere ac_wert >0;


Andreas Widmann

select ac_name,ac_date,ac_wertfrom actionwhere ac_name || ac_typ = ‘TRANSX‘


select ac_name,ac_date,ac_wertfrom actionwhere ac_name = ‘TRANS‘ and ac_typ = ‘X‘

Die Funktion || concatenate deaktiviert den Index da sie auf eine indexierte Spalte angewendet wird

select ac_name,ac_date,ac_wertfrom actionwhere ac_wert +200 <500

Alle aritmetischen Operationen +,-,*/ deaktivieren Indizes

select ac_name,ac_date,ac_wertfrom actionwhere ac_wert < 300;


Andreas Widmann

select count(*) from actionwhere upper(ac_name) ='HUGO'


Die Funktion upper deaktiviert normalerweise den Index da sie auf eine indexierte Spalte angewendet wird

Abhilfe:Erstelle einen funktionsbasierten Index :

create index action_idx on action (upper(ac_name))

Setze Parameter:Query_rewrite_enabled in init.ora auf TRUE setzen

Analysiere Tabelle action mit dbms_stats


Andreas Widmann

ac_typ char(1)

select ac_name,ac_date,ac_wertfrom actionwhere ac_typ = 1


select ac_name,ac_date,ac_wertfrom actionwhere

ac_typ = ‘1‘

Da ac_typ ein char Wert und die Konstante ein numerischer Wert , wandelt Oracle den char in einen numerischenWert um. Folge davon ist ein full table scan auf die Tabelle


Andreas Widmann

Optimierung & Tuningbeispiele – Fehlende order ungeeignete Indizes

Mit Hilfe eines optimalen Index , kann die Performance erheblich verbessert werden.

Ein Problem mit dem Index kann es geben , wenn der Index z.B. mehr als 15% einer Tabelle als Ergebnis zurückliefert.Dann ist oft ein full table scan schneller.

101,Müller201,Mayer301,Huber

101,Müller201,Mayer301,Huber

index

301,Huber…..101,Müller,….401,Gruber….201,Mayer……301,Huber…..

301,Huber…..101,Müller,….401,Gruber….201,Mayer……301,Huber…..

daten

Ein ungeeigneter Index ist, wenn zu jedem Index Zugriff noch ein physikalischer Datenzugriff erfolgt.siehe Beispiel oben

Die Index Suche wird extrem wirksam, wenn alle Abfragespalten im index repräsentiert sind.

Kleinere Tabellen sollten indexiert werden, weil die Eindeutigkeit über den Primär oder Unique keygewährleistet ist und der Optimizer einen besseren Ausführungsplan erstellen kann.


Andreas Widmann

select count(*)from actionwhere ac_typ = 1 and ac_wert > 100

Optimierung & Tuningbeispiele – Indexierung einer Spalte – Mehrspalten Index

Index1 (ac_typ)Index2 (ac_wert) Jeder Index Eintrag wird für jeden Wert gelesen

IndexGemeinsam (ac_typ,ac_wert)Nur eimaliges Lesen des Indexes, wesentlich performanter


Andreas Widmann

Optimierung & Tuningbeispiele – Nested Loop sort-merge hash-join im Execution Plan

Nested loop/*+ use_nl +*/

Sort merge/*+ use_merge +*/

Hash join/*+ use_hash +*/

Sind gut für Online-Transaktionssysteme, Daten werden schnellst möglichst zurückgeliefertSollten benutzt werden wenn weniger als 10% der Zeilen zurückkommen

Das Gesamtergebnis wird bestimmt , bevor eine einzige Zeile an den Benutzer zurückgeliefert wirdEs wird eine hash Tabelle im Speicher aufgebaut die auf der kleinsten Tabelle basiert, danachwird die andere Tabelle verarbeitet

Ein merge Join ist dann schneller , wenn alle Spalten in der where Klausel durch einen Indexvorsortiert werden.


Andreas Widmann

Optimierung & Tuningbeispiele – Was ist schneller „in“ „oder exists“ ?

select ….. from emp ewhere exists(select ‘x‘from dept dwhere e.deptno=d.deptno and d.cat =‘AUS‘);

Wann sollte man die Konstrukte Not in, not exists , in , exists verwenden ? Unter Umständen ist ein ein „not/in“ schneller als ein „not/exists“ Es kommt auf die Bedingungen an. In den meisten Fällen ist es besser „Exists“ statt „in“ zu verwenden. Folgende Beispiele sind Ausnahmen wo exists eine schlechtere Performance hat.

select …from dept d,emp ewhere e.deptno=d.deptno and d.cat =‘AUS‘

Ein Join ist schneller als eine Unterabfrage

delete from emp ewhere exists(select ‘x‘from dept dwhere e.deptno=d.deptno and d.cat =‘AUS‘);

delete from empwhere (deptno,id)in(select deptno,id from temp );

Das „in“ Konstrukt is hier nur schneller wenn die emp sehr groß und die temp Tabelle sehr klein ist !z.B. emp hat mehere Millionen und temp hat wenige tausend Zeilen.

delete from emp ewhere exists(select /*+ HASH_SJ +*/ ‘x‘from dept dwhere e.deptno=d.deptno and d.cat =‘AUS‘);

Um die Ausführung von exists zu beschleunigen kann man die Hints „hash_sj“ und „merge_sj“ verwenden.


Andreas Widmann

Optimierung & Tuningbeispiele – Unnötige Sortiervorgänge

Die folgenden Operationen benötigen eine Sortierung:

Create indexDistinctGroup byOrder byIntersectMinusUnionsUnindexed table joins

Sortiervorgang kann verbessert werden:Sort_area_size erhöhen , initial und next für tablespace temp um ein vielfaches von sort_area_size zuordnen.


ac_typ = ‘1‘unionselect ac_name,ac_date,ac_wertfrom action2where

ac_typ = ‘X‘


ac_typ = ‘1‘union allselect ac_name,ac_date,ac_wertfrom action2where

ac_typ = ‘X‘

Verwenden von union all statt unionDuplikate werden nicht entfernt !!!


Andreas Widmann

Optimierung & Tuningbeispiele – Unnötige Sortiervorgänge

select ac_name,ac_date,ac_wertfrom actionorder by

ac_name


ac_name > chr(1)

Die Dummy where Klausel erzwingt den Index Zugriff, so daß die Zeilen sortiert zurückgeliefert werden

Verwenden eines Indexes, um eine Sortierung zu vermeiden.

Index auf ac_name und ac_date


Andreas Widmann

Optimierung & Tuningbeispiele – Zuviele Indices

Bei Aktualisierungen und Batch-Prozessen können sich viele Indices auf die Performancenegativ auswirken.

Anahl der InsertsAnahl der Inserts Anzahl IndicesAnzahl Indices Laufzeit in SekundenLaufzeit in Sekunden

256256 00 1,1011,101

512512 00 1,1611,161

256256 55 3,9363,936

256256 1010 12,55812,558

512512 1010 22,13222,132


Andreas Widmann

insert into nehmerkredite ( periode, sv_id, paragraph, kredite_id, gebernr, gebernr_pz, schuldnernr, schuldnernr_pz, POS_110_gk) select periode, sv_id, '14', id, gebernr, gebernr_pz, schuldnernr_tk, schuldnernr_tk_pz, POS_110_GK from kredite k1 where k1.periode = '200403' and k1.sv_id = 1 and (k1.schuldnernr_tk > '0999999' or k1.schuldnernr_tk < '0100000') and satzart = '1' and paragraph = '14' and rowid = (select min(rowid) from kredite k2 where k2.periode = '200403' and k2.sv_id = 1 and k2.gebernr = k1.gebernr and k2.schuldnernr_tk = k1.schuldnernr_tk and satzart = '1' and paragraph = '14')

Optimierung & Tuningbeispiele – aktuelles Projekt Beispiel

insert /*+ append +*/ into nehmerkredite ( periode, sv_id, paragraph, kredite_id, gebernr, gebernr_pz, schuldnernr, schuldnernr_pz, POS_110_gk) select periode, sv_id, paragraph, min(id) , gebernr, gebernr_pz, schuldnernr_tk, schuldnernr_tk_pz, POS_110_GK from kredite k1 where k1.periode = '200403' and k1.sv_id = 1 and (k1.schuldnernr_tk > '0999999' or k1.schuldnernr_tk < '0100000') and satzart = '1' and paragraph = '14' group by periode, sv_id, paragraph, gebernr, gebernr_pz, schuldnernr_tk, schuldnernr_tk_pz, POS_110_GK

421->48 Sekunden, faktor 10 schneller


Andreas Widmann

Zusammenfassung - AusblickZusammenfassung - Ausblick

Analyse der Datenbank Zugriffe & OptimierungTracen ausgewählter Sitzungen und kritischer PfadeAnalysen des Execution PläneBeachtung der Optimierungsregelngeeignete Indizes verwendenevtl umschreiben des SQL-CodesZeitmessungen über sqlplus,tkprof,explain plan

Verwendung von BIND Variablen – Vermeidung von wiederholtem parsenevtl Cursor Sharing in den initOra Parameter auf similar anpassenGroße Arrays wenn möglich über nocopy referenzierenString Verarbeitung , zu große Arrays vermeiden evtl compilieren des sqlCodesCachen von häufigen Zugriffen innerhalb von pl-sql

Parallelisierung

Falls möglich Anfragen und Jobs parallel verarbeiten über dbms_jobs oder eigenes Frameworkasynchron/synchronSchon in der Architektur und Design Phase der Applikation/Datenmodell beachtenSkalierbarkeit wird verbessert.Sehr gute Erfahrungen im Projekt Bankenaufsicht


Andreas Widmann

Zusammenfassung - AusblickZusammenfassung - Ausblick

Rebuild IndexBei häufigen deletes,inserts,updates ist es notwendig die Indexe zu reorganisieren

Statistik

Absolut wichtig sind Tabellen & Index Statistiken beim kostenbasierten OptimizerMit der Option „for all indexed columns“ wir die IndexStatistik automatisch mitgemachtAb Oracle 9 Package dbms_stats verwenden – nicht analyze verwenden

Partitionierungbei großen Datenmengen wird dringend empfohlen Oracle‘s Partitioning Option zu verwendenRange Partition – List Partition oder kombiniertGrößter Vorteil sindlocal partition IndexeDas Antwort Zeitverhalten bleibt konstant und steigt nicht mit der DatenmengeRebuild und Statistik über subPartitions möglich


Andreas Widmann

Beispiel indexRebuild und Statistik bei einer einfachen Beispiel indexRebuild und Statistik bei einer einfachen TabelleTabelle

Hat die Tabelle und der Index Statistik Daten ?

select owner,table_name,last_analyzed from dba_tables where table_name='C_BRANCHEN' and owner=userselect owner,table_name,index_name,last_analyzed from dba_indexes where table_name='C_BRANCHEN' and owner=user

Index rebuild & Generierung der Statistik

begin execute immediate 'alter index HABL.C_BRANCHEN_PK rebuild parallel nologging compute statistics tablespace HABL_INDEX_S_01';

dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', '''.,'''); -- nur notwendig weil BUG in dbms_stats Package dbms_session.set_nls('NLS_TERRITORY', '''america'''); -- nur notwendig weil BUG in dbms_stats Package

dbms_stats.gather_table_stats(ownname => 'HABL', tabname => 'C_BRANCHEN', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE, method_opt => 'FOR ALL INDEXED COLUMNS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);end;

Löschen der Statistik Daten ?

begin dbms_stats.delete_table_stats(ownname => 'HABL', tabname => 'C_BRANCHEN');end;

Analysiere die Indizes

Grad der Parallelisierung


Andreas Widmann

Table/Index MonitoringTable/Index Monitoring

Einschalten des MonitoringEinschalten des Monitoring

Alter table c_branchen monitoring;Alter table c_branchen monitoring;Alter index idx_br monitoring usage;Alter index idx_br monitoring usage;Alter index idx_br nomonitoring usage;Alter index idx_br nomonitoring usage;

Überprüfen was hat sich in den Tabellen/Indexen verändert ?Überprüfen was hat sich in den Tabellen/Indexen verändert ?

Select * from user_tab_modifications;Select * from user_tab_modifications;Select * from v$object_usage;Select * from v$object_usage;

Schema Statistik ausführen Schema Statistik ausführen

beginbegin dbms_stats.gather_schema_stats(ownname => 'HABL',

cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE, options => 'GATHER STALE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);-- estimate_percent => 10), es werden nur die Tabellen analysiert , bei denen mehr als 10% der

Zeilen-- geändert wurden.end;


Andreas Widmann

Kopieren und Sichern von StatistikenKopieren und Sichern von Statistiken

Generiere Tabelle zum Speichern der StatistikenGeneriere Tabelle zum Speichern der Statistiken

beginbeginsys.dbms_stats.create_stat_table(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT');sys.dbms_stats.create_stat_table(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT');

end;end;

Erzeugte stat Table füllenErzeugte stat Table füllen

beginbeginsys.dbms_stats.export_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');sys.dbms_stats.export_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');

end;end;

Tabelle exportierenTabelle exportieren

exp user/pwd@inst file=stats tables=habl_statexp user/pwd@inst file=stats tables=habl_stat

Tabelle importierenTabelle importieren

imp user/pwd@inst file=stats tables=habl_statimp user/pwd@inst file=stats tables=habl_stat

Satistik mit Dictionary füllenSatistik mit Dictionary füllen

beginbeginsys.dbms_stats.import_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');sys.dbms_stats.import_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');

end;end;


Andreas Widmann

Rückkehr zu gesicherten StatistikenRückkehr zu gesicherten Statistiken

Sicherung erzeugenSicherung erzeugen

beginbegin

sys.dbms_stats.export_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');sys.dbms_stats.export_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02.2005');

end;end;

Man sammelt neue StatistikenMan sammelt neue Statistiken

beginbegin

dbms_stats.gather_schema_stats(ownname => 'HABL',

cascade => TRUE,

degree => DBMS_STATS.DEFAULT_DEGREE,

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

end;

Rückkehr zur alten StatistikRückkehr zur alten Statistik

beginbegin

sys.dbms_stats.import_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02‘');sys.dbms_stats.import_schema_stats(OWNNAME =>'HABL' ,STATTAB => 'HABL_STAT' , STATID => 'Statistik vom 14.02‘');

end;end;

Documents

Oracle Optimierung & Tuning Tipps und Tricks. Andreas Widmann Oracle Optimierung & Tuning Themen Einführung Einführung Tools sqlplus & tkprofTools sqlplus