Maria Rus - Tehnici de diagnisticare şi tuning a performanţelor bazelor de date Oracle

ACADEMIA DE STUDII ECONOMICE DIN BUCUREŞTIFACULTATEA DE CIBERNETICĂ, STATISTICĂ ŞI

INFORMATICA ECONOMICĂ

LUCRARE DE DISERTAŢIE

Coordonator ştiinţificProf.Univ.Dr. Ion LUNGU

AbsolventMaria Rus

1

Februarie, 2010

ACADEMIA DE STUDII ECONOMICE DIN BUCUREŞTIFACULTATEA DE CIBERNETICĂ, STATISTICĂ ŞI

INFORMATICA ECONOMICĂ

TEHNICI DE DIAGNOSTICARE ŞI TUNING A PERFORMANŢELOR BAZELOR DE DATE ORACLE

Coordonator ştiinţificProf.Univ.Dr. Ion LUNGU

AbsolventMaria Rus

2

Februarie, 2010

Cuprins

Introducere.................................................................................................................................................. 4

1. Scopul lucrării...................................................................................................................................... 4

2. Ce presupun problemele de performanţă, definirea unei probleme, de ce apar acestea (cele mai

frecvente cauze)...................................................................................................................................... 4

Capitolul 1 - Metodologia problemelor legate de performaţa bazei de date.................................................7

1. Metodologia de Diagnosticare Oracle (ODM = Oracle Diagnostic Methodology).................................7

2. Paşii diagnosticării unei probleme de performanţă a bazelor de date................................................10

Capitolul 2 - Functionarea lentă a bazei de date.......................................................................................12

Faza 1 - Identificarea problemei.............................................................................................................12

Faza 2 - Determinarea cauzei................................................................................................................30

Faza 3 - Alegerea strategiei de tuning...................................................................................................35

Capitolul 3 - Baza de date este blocata (locking) sau se agaţă (hang).....................................................47



Faza 3 – Alegerea strategiei de tuning..................................................................................................68

Capitolul 4 - Optimizarea interogarilor.......................................................................................................72



Faza 3 - Alegerea strategiei de tuning...................................................................................................99

Studiu de caz........................................................................................................................................... 120

Concluzii.................................................................................................................................................. 135

Anexa 1................................................................................................................................................... 137

Anexa 2................................................................................................................................................... 141

Anexa 3................................................................................................................................................... 145

Anexa 4................................................................................................................................................... 147

Anexa 5................................................................................................................................................... 149

Bibliografie............................................................................................................................................... 151

3

Introducere

1. Scopul lucrării

Problemele de performanţă sunt în general dificil de soluţionat, dar acestea devin şi mai dificile dacă nu

ştim unde să căutăm informaţia necesară.

Colectarea datelor de diagnosticare corecte este cel mai important prim pas în soluţionarea problemelor

de performanţă.

Diferite tipuri de probleme de performanţă necesită colectarea de informaţii diferite. Această lucrare va

acoperi cele mai întâlnite tipuri de probleme de performanţă , şi va sugera ce tipuri de informaţii ar trebui

colectate pentru fiecare categorie de probleme în parte, o analiză a lor, determinarea posibilelor cauze de

apariţie a acestora şi modele de soluţionare/tuning.

2. Ce presupun problemele de performanţă, definirea unei probleme,

de ce apar acestea (cele mai frecvente cauze)

Ce este performanţa?

Performanţa unei baze de date poate să aibă standarde diferite pentru diferiţi utilizatori, de exemplu un

utilizator ce accesează o pagina web va fi interesat să vadă prima pagină de rezultate cât mai curând

posibil (timp de răspuns) iar procesarea altor pagini poate continua în pe fundal. Un utilizator care

lansează un proces, va fi interesat de finalizarea cât mai rapidă a acestuia. El nu este interesat să vadă o

pagină de rezultate, dar baza de date ar trebui să asigure cel mai bun rezultat al procesării (nu timpul de

răspuns). Alte aplicaţii s-ar putea să aibă alte definiţii pentru ceea ce consideră ei performanţe optime.

1. Definirea problemei

Din exemplul prezentat reiese cât de important este să ştim cât expectanţele utilizatorilor înainte de a

începe diagnosticarea propriu-zisă.

Întrebările simple pot fi întotdeauna utile:

Care este exact problema?

Care sunt aşteptarile utilizatorilor?

Rezultatele aşteptate au fost vreodată obţinute?

Lista continuă (detaliată ierarhic pe tipul de problemă descoperit), dar ideea este să obţinem o înţelegere

exactă a problemei. Este foarte important ca problema să poată fii cuantificabilă, pentru a ştii când

rezultatele dorite au fost atinse.

4

2. De ce apar problemele de performanţă?

Această întrebare este cea mai relevantă pentru bazele de date din producţie care au funcţionat bine

pentru o vreme. Aceasta s-ar putea să nu se aplice unei baze de date din dezvoltare sau pre-producţie

unde definim valorile de referinţă.

Abordarea presupune următoarele: dacă un sistem funcţiona performant la un moment dat, ca el să nu

mai aibă aceleaşi performanţe, ceva s-a intâmplat. Uneori acest lucru este evident şi evidenţiat de către

utilizatori, ca de exemplu:

Un upgrade al bazei de date sau a aplicaţiei, instalarea unui pachet reparator sau a unui Service

Pack

Schimbare de hardware

Schimbarea platformei

Recolectarea de statistici

Cresterea numarului de utilizatori sau a volumui de date încarcate

Modificări ale schemei bazei de date

Dar uneori devine un mister de ce au scăzut performanţele. În aceste cazuri utilizatorii trebuiesc întrebaţi

ce acţiuni au întreprins în trecut, de mai multe ori, pentru a evita emiterea de informaţii irelevante pentru

utilizatori, dar deţinatoare a soluţiei.

3. Colectarea de informaţii

Când se începe diagnosticare unei probleme, adesea afirmaţiile utilizatorilor sună de forma: "aplicaţia

merge încet.", "ceva s-a agaţat", "interogarea nu returnează rezultatele dorite". Este foarte important sa

întelegem ce merge “greu” sau s-a “agăţat”. Ar putea fi aplicatia, o sesiune utilizator, câteva interogări

sau insaşi baza de date.

Un set de întrebari preliminare, generale pentru toate tipurile de probleme de performanţă:

Instanţa sau instantele unde poate fi observată problema. Se întamplă în Producţie? Dezvoltare? Test?

Toate? Dacă nu apare în toate sistemele, trebuie documentate diferenţele între acestea. Aceste diferenţe

pot fi izolate? Acest lucru ar putea restrange problema la o singură arie.

Trebuie determinat dacă performanţa slabă a sistemului se observă în mod egal dacă volumul de date

rulat este mare sau mic. Dacă ar opera un singur utilizator activ? Performanţele sunt la fel de slabe?

Documentarea faptului cum performanţa sistemului este influenţată sau nu de volumul de date.

Problema poate fi reprodusă sistematic? Documentarea replicarii. dacă problema nu se poate replica,

trebuisc documentate cauzele.

Exista o soluţie provizorie? Care este aceasta?

5

A fost efectuat un upgrade sau a fost instalat un program nou pe server de când s-a constatat problema?

Au fost aplicate recent pachete noi sau service-pack-uri pentru sistemul de operare, aplicaţie sau baza de

date, care ar putea afecta performanţa sistemului?

Verificarea reţelei, a conectivitaţii, a performanţelor acesteia şi documentarea rezultatelor.

Din răspunsurile la aceste întrebari, putem deja deduce tipul de problemă de performanţă.

Seturile de întrebări specifice per tipul de problemă vor fi detaliate în capitolele dedicate acestora.

6

Capitolul 1 - Metodologia problemelor legate de performaţa

bazei de date

1. Metodologia de Diagnosticare Oracle (ODM = Oracle Diagnostic

Methodology)1

ODM introduce o modalitate definită clar, o abordare standard în rezolvarea problemelor, dar şi o

documentare riguroasă a problemelor şi modalitaţilor de rezolvare a acestora. În acest mod, o nouă

apariţie a aceluiaşi tip de problemă, ar putea să nu mai necesite intervenţia unui specialist.

Metodologia de diagnosticare Oracle se bazează pe Principiul cauzalitaţii al lui Albert Einstein,

generalizat:

“Dacă un eveniment A (“cauza”) cumva influenţează un eveniment B (“efectul”) care apare ulterior în

timp, evenimentul B nu poate influenţa evenimentul A. Evenimentul B trebuie sa apară ulterior

evenimentului A. Cauza trebuie intotdeauna să preceadă efectul. Un efect va avea întotdeauna cel puţin

o cauză.”

Utilizând acest principiu ca fundament, ODM rezolvă problemele utilizând următoarele faze:

Faza 1 - Identificarea problemei

Această etapă se axează pe identificarea şi verificarea problemei corecte pentru a fi soluţionate în cadrul

situaţiei curente. Problema trebuie enunţata în mod obiectiv fără înclinaţii către nici una din posibilele sale

cauze sau soluţii. Trebuie să fie cu scop restrâns, şi ar trebui să nu conţină sub-probleme sau probleme

fară legatură directă.

Care este problema? - Ce problemă vrem să prevenim?

Când s-a întâmplat? - Succesiune de evenimente care au condus la această problemă.

Unde s-a întamplat? - Stabiliţi localizarea aproximativă a problemei, ex. în ce componentă a

produsului s-a observat problema, în ce mediu?

Care este semnificaţia acesteia? - Care este valoarea relativa a prevenirii problemei?

Obiectivele problemei sunt specificate aici, ce ar trebui sa includă starea dorită a sistemului

atunci când problema este rezolvată, constrângeri de timp şi asteptarile.

1What is the Oracle Diagnostic Methodology (ODM)? [ID 553866.1]

https://support.oracle.com/CSP/main/article?cmd=show&id= 553866.1 &type=NOT , accesat la data 23.01.2010

7

https://support.oracle.com/CSP/main/article?cmd=show&id=553866.1&type=NOT

Problema, de asemenea, trebuie să fie verificată pentru a ne asigura că există asa cum a fost descrisă.

Acest lucru se realizează prin colectarea de informaţii şi de date de diagnosticare. Pe parcursul colectarii

datelor, se poate întâmpla ca problema descrisă să nu fie cea ce reiese din informaţiile adunate, în acest

caz este necesară re-definirea problemei presupusă iniţial.

Verificarea problemei evită investigarea unei piste greşite şi pierderea timpului în soluţionarea unei

probleme greşit presupusă. Deasemenea acest pas asigură specialistul că investighează problema

corectă din ceea ce i-a fost raportat.

Faza 2 – Determinarea cauzei

Aceasta faza identifica una sau mai multe cauze posibile pentru problema identificată anterior.

Cei trei paşi în determinarea cauzelor sunt:

Să ne întrebăm “de ce?” - Începând cu datele colectate la verificarea problemei, ne întrebam de ce

vedem aceste rezultate, trebuie sa le căutam cauza. Dacă datele deja adunate nu evidenţiază o posibilă

cauza, mai multe informaţii de diagnosticare ar trebui colectate, alte utilitate şi resurse ar trebui folosite

pentru această identificare.

Căutarea cauzei în termeni de condiţii şi acţiuni – O condiţie este o cauză care există în timp. O acţiune

este o cauză de moment ce cumulează condiţiile, pentru a determină un efect. Încercarea determinării

dacă o anumită cauză este o acţiune sau un efect, de obicei evidenţiază cauze adiţionale.

Justificarea cauzei – Pentru fiecare cauză identificată, ar trebui colectate date pentru a evidenţia că acea

cauză există în mediul examinat. Justificarea trebuie să se bazeze pe date exacte şi documentată clar.

De obicei, când se adună dovezi, o cauză poate fi exclusă sau înlocuită cu altele, datorită dovezilor ce

indică altceva. Dacă datele colectate nu pot proba cauza, aceasta nu ar trebui luată în considerare ca

fiind una validă.

În cazul în care o cauză concretă nu poate fi identificată, există posibilitatea unui bug, şi ar trebui

înregistrat corespunzător.

Faza 3 – Identificarea soluţiilor eficiente

Scopul acestei faze este de a asigura una sau mai multe soluţii eficiente ce vor crea o stare dezirabilă

(problema nu mai există). Soluţiile ar trebui identificate pentru cauzele determinate, dând posibilitatea

posesorului sistemului sa aleagă una dintre ele pe baza criteriilor de mai jos.

8

În timpul acestei faze, se cercetează, încercând să recreem problema şi testând soluţiile posibile. În

timpul identificării soluţiilor posibile, date de diagnostic adiţionale ar putea fi necesare, pentru că soluţiile

corecte să fie identificate.

Fiecare soluţie ar trebui să confirme din următoarele criterii:

Previne apariţia problemei?

Este problema sub control?

Intruneşte obiectivele stabilite în cadrul Fazei 1?

Nu va cauza alte probleme

Va impiedica apariţia ei în alte locuri

Rezolvarea ei se justifică în termeni de costuri

Este alcatuită din acţiuni concrete de urmat

Se poate implementa şi o soluţie care nu întruneşte toate criteriile de mai sus. Trebuie identificate soluţii

pentru TOATE cauzele determinate, deşi unele soluţii se pot adresa unor cauze multiple.

Odata ce soluţiile au fost identificate, ele trebuie verificate să se adreseze cauzelor determinate. Prin

adresarea cauzei şi problemei direct, se poate ca problema să fi fost imparţită în bucăti mai mici, mai uşor

de adresat. În plus, dacă soluţia verifică rezolvarea cauzei, şi totuşi problema persistă, trebuie identificat

un nou set de cauze.

Verificarea soluţiei este o modalitate de a documenta de ce şi cum acea soluţie se adresează unei cauze.

Dacă soluţia nu poate fi verificată, atunci nu poate fi considerată validă. Acest proces reduce posibilitatea

implementării de soluţii care cel mai probabil nu vor rezolva problema iniţială.

Faza 4 – Aplicarea soluţiei optime

Soluţia optimă ar trebui să fie aleasă ţinâd cont de următoarele:

Este cea mai controlabilă?

Are cel mai mare impact?

Presupune cel mai mic risc?

Este cea mai realizabilă?

Un plan de acţiune trebuie definit cuprinzând un numar de acţiuni cu o ordine pre-definită. Pentru fiecare

sarcină de efectuat din implementarea soluţiei, trebuie identificate eventualele probleme şi riscuri, cum se

pot preveni acestea şi cum li se vor face faţă în caz ca vor fi întâlnite.

9

2. Paşii diagnosticării unei probleme de performanţă a bazelor de date

A. Scop - sesiune/bază de date

De obicei o problemă de performanţă ia mai mult timp ca să fie soluţionată decât celelalte.

Încercând să aplicăm ODM pentru o problemă de performanţă, primul lucru pe care ar trebui să ne

întrebăm este „Care este scopul nostru?”

Ar trebui să ştim dacă este o problemă ce afectează toată baza de date sau se referă doar la o sesiune

anume?

Se întamplă în acelaşi moment al zilei, sau aleatoriu?

Apare doar pentru o interogare singulară sau în orice parte a aplicaţiei?

Aceste date trebuie stabilite/ştiute din primele faze ale investigării deoarece vor determina modalitatea de

abordare ulterioară.

Ce încercăm să aflăm în urma diagnosticării este ce fel de problemă de performanţă este. Acest pas este

definitoriu pentru soluţionarea problemei.

B. Diagnosticarea sistemului de operare - resursele SO sunt deficitare?

Pentru facilitarea diagnosticării este important sa tratăm sistemul ca un tot unitar, şi să ştim detalii de

bună funcţionare (sau nu) a tuturor parăţilor sale componente.

Sistemului de operare ar trebui urmărit folosind utilitare precum Oracle OS Watcher, ce colectează

informaţii despre SO ca utilizarea procesorului şi informaţii despre memorie. Dacă instalarea de utilitare

adiniţionale nu este permisă, se poate folosi cel built-in ca vmstat.

În analiza informaţiilor despre sistemul de operare, trebuie să avem în vedere dacă nu cumva resursele

SO sunt deficitare. Se utilizează prea mult din capacitatea disponibilă a procesorului? Este memoria

suprasolicitată?

Dacă în urmă analizei datelor colectate despre SO, se concluzionează o bună funcţionare a acestuia, cel

mai probabil cauza problemei de performanţă cade în vina bazei de date.

În caz contrar, o funcţionare deficitară, înca nu putem fi siguri dacă nu este Oracle cel mai mare

consumator de resurse. Alte produse ar putea rula pe server-ul investigat.

10

a). ->DA -> Oracle consumă cel mai mult? -> DA -> investigarea proceselor

Dacă rezultatele investigării concluzionează ca Oracle database este cel mai mare consumator de

resurse, trebuie văzut care din procesele Oracle vizibile în logurile sistemului consumă resursele

procesorului. Şi de aici este un drum foarte scurt pană la obţinerea unui trace file pentru procesele în

cauză. Rezultatele investigării ar putea fi „în favoarea” Oracle, codul funcţional optim, interogări

performante, iar soluţia să fie adăugarea de memorie fizică.

b). ->NU-> Functionare lentă a bazei de date?->DA-Diagnosticare

În caz contrar când nu resursele deficitare sunt cauza funcţionării anormale a sistemului, în mod sigur

este o problemă a bazei de date.

În acest moment trebuie să ne concentrăm pe clasificarea tipului de problemă de performantă Oracle.

Cel mai simplu ar fi să fie doar vorba de o sesiune singulară lentă, uşor de diagnosticat cu ajutorul unei

trace 10046 şi rezultatele tkprof, ca fiind problema de configurare a bazei de date, un posibil bug, o

problemă de interogări sau de conectivitate/networking.

În unele cazuri, este chiar baza de date în totatlitate care funcţionează lent.

În acest moment avem nevoie de o listă cu sesiunile cheie cu cel mai mare dbtime. Dintre sutele de

sesiuni ce pot fi afişate este imposibil să le analizăm pe toate. Nu le putem urmări pe toate. Trebuie

identificaţi candidaţii optimi de inregistrat şi intocmită o listă cu procesele cele mai afectate de problema

de performanţă. Odată acest lucru realizat le putem analiza corespunzător.

Colectarea de date utilizând rapoartele statspack, AWR, ASH este foarte utilă în identificarea proceselor

problematice.

c). ->DA->Diagnosticarea blocării

Alte daţi, utilizatorii raportează blocări. Se pot referi la blocarea întregii baze de date, sau blocare la

realizarea unor operaţiuni.

„Blocare” are în consensul Oracle un inţeles concret, o resursa ce a fost cerută şi nu raspunde la

comenzi. Această problemă va fi adreasată cu intrumente specifice, de genul HangAnalyze, Systemstate,

Errorstack, Pstack, Transient, LTOM, etc.

d). ->Interogări problematice?->DA-Diagnosticare

Tipul de blocare, cel real, este adeasea confundat de către utilizatori cu interogări care durează foarte

mult până să returneze rezultate. Acesta nu este o blocare în sensul Oracle, şi se va incadra în categoria

interogărilor problematice sau chiar în cea a funcţionarii lente a bazei de date.

11

Capitolul 2 - Functionarea lentă a bazei de date


A. Generalităţi2

Pentru a identifica problema pe care dorim să o rezolvam, trebuie să indeplinim următoarele sarcini:

Să recunoaştem problema.

Să clarificăm detaliile din jurul problemei

Să verificăm dacă problema identificată este acceaşi cu cea raportată.

a. Recunoaşterea unei bd ce funcţionează lent3

Ce este o bază de date lentă?4

O bază de date lentă se manifestă într-unul dintre următoarele feluri:

Un numar mare de sesiuni ce rulează mai lent decât de obicei.

Baza de date permite autentificarea şi pare că funcţionează (nu este blocată) dar dureaza mult

mai mult ca de obicei să returneze rezultate.

Mai multe tipuri de activitaţi îşi incetinesc funcţionarea la aproximativ acelaşi timp.

Acest comportament poate fi identificat din5:

Testare / comparaţie cu valorile de referinţă

Plângerile utilizatorilor

Rapoarte statspack sau AWR ce arată un randament scăzut (ex. tranzacţii/sec)

Rapoarte statspack, AWR sau ASH ce arată timpi de aşteptare mai mari decât cei normali

Date din sistemul de operare ce arăta un consum mai ridicat de procesor sau un volum de I/O

Oracle mai mari decât cele normale.

Acest tip de probleme pot apărea după:

Modificări ale schemei bazei de date

Modificări ale modalitaţii de colectare de statistici

Modificări ale volumuluii de date

Modificări în aplicaţie

Upgrade-uri ale bazei de date.

2 THE COE PERFORMANCE METHOD [ID 190124.1] , Roger Snowden

https://support.oracle.com/CSP/main/article?cmd=show&id=190124.1&type=NOT, accesat la data 23.01.2010

3 Yet Another Performance Profiling Method (Or YAPP-Method) [ID 148518.1],


4 Oracle® Database Performance Tuning Guide 10g Release 2 (10.2),

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/perf_overview.htm#i1006218, accesat la data 23.01.2010

5 Database Performance - FAQ [ID 402983.1],

https://support.oracle.com/CSP/main/article?cmd=show&id= 402983.1 &type=NOT , , accesat la data 23.01.2010

12


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/perf_overview.htm#i1006218



b. Clarificarea problemei

O specificare clară a problemei este foarte importantă. Trebuie declarat exact care este problema. Se

poate întâmpla ca în faze ulterioare, sau în timp ce se lucrează la problema, adevărata problemă să iasă

la iveală, şi să fie necesar o redefinire a acesteia.

Pentru a clarifica problema, este necesar să se ştie cât mai multe dintre următoarele:

Când sistemul a funcţionat lent şi când nu.

Orice schimbari ce coincid cu performanţele slabe ale sistemului.

Secvenţa de evenimente ce conduc la problema raportată

Când/cum a fost observată problema?

Importanţa problemei

Ce funcţionează bine?

Care este rezultatul aşteptat sau acceptat?

Ce s-a încercat deja pentru rezolvarea problemei?

Exemple practice:

Un sistem funcţionează deficitar în fiecare dimineaţă intre 10am şi 12pm, în rest funcţionează

bine.

Problema apare după ce a fost instalată ultima versiune a aplicaţiei.

Aplicaţia funcţionează lent şi împiedică sistemul să înregistreze comenzile.

Sistemul funcţionează bine, cu excepţia intervalului 10am-12pm

Cererile sunt procesate de obicei de către baza de date în 200ms, de când a apărut problema,

procesarea durează 10s.

S-a încercat re-colectarea de statistici, dar situaţia nu s-a îmbunătăţit.

De ce este acest pas important?

Săritul peste acest pas este riscant deoarece ne putem angaja în soluţionarea problemei greşite şi vom

irosi timp şi eforturi semnificative. O definire clară a problemei este critică în determinarea cauzelor şi

soluţiei problemei.

c). verificarea problemei

Obiectivul acestui pas din procesul de diagnosticare este să ne asigurăm că baza de date are

simptomele unei probleme de performanţă. În acest moment trebuie să colectăm date ce verifică

existenţă problemei.

Pentru aceasta verificare trebuie să colectăm:

Dovezi systemwide utilizând rapoarte statspack, AWR, şi/sau ASH când performanţa era bună şi

nesatisfăcătoare.

Dovezi specifice ale performanţelor deficitare pentru o sesiune sau diferite interogări.

13

Trace-uri extinse ale interogărilor pentru una sau mai multe sesiuni în perioadele cu performanţe

bune şi în cele deficitare.6

Exemple şi sugestii despre ce informaţii de diagnosticare sunt necesare pentru rezolvarea problemei se

vor discuta în secţiunea dedicată colectării de date.

Odată ce datele au fost adunate, ele vor fi analizate pentru a verifica dacă intr-adevar este o problemă de

latentă, sau dacă este vorba de alt tip de problemă.


Dacă se sare peste acest pas putem să asumăm ca baza de date este problema, în timp ce aceasta să

fie o problemă de client sau conectivitate.

B. Adunarea de date utile

Intotdeauna trebuie sa colectăm date despre performanţele întregului sistem, şi aditional trace-uri extinse

ale interogărilor dacă anumite sesiuni sunt lente iar multe altele funcţionează bine.

Intreaga bază de date este lentă?

Date adunate trebuie să fie din perioada de funcţionare optimă cât şi din cea deficitară, pentru ca acestea

să poată fii comparate. 7

Începând cu versiunea 10g, rapoartele AWR şi statspack-urile conţin şi date despre sistemul de operare,

nefiind astfel necară utilizarea de utilitare suplimentare.8

Anumite sesiuni prezintă latenţă?

Dacă doar anumite sesiuni sunt lente, trebuie să ne concentrăm asupra lor folosind race-uri extinse SQL

(event 10046, level 12)9.

6Oracle® Database Performance Tuning Guide 10g Release 1 (10.1), Using the SQL Trace Facility and TKPROF,

http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#8723 , accesat la data 24.01.2010

7Oracle® Database Performance Tuning Guide 10g Release 1 (10.1),

http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#8723 , accesat la data 24.01.2010

8FAQ- Statspack Complete Reference [ID 94224.1],

https://support.oracle.com/CSP/main/article?cmd=show&id=94224.1&type=NOT , accesat la data 24.01.2010

9Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1],


14



http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#8723


Exemple:

Event 10046 va produce un fişier de trace în <<Parameter user_dump_dest>> pentru procesele utilizator,

iar pentru procesele background, în <<Parameter background_dump_dest>>.

Urmărirea unei sesiuni:

-- Executaţi interogările sau operaţiile ce vor fi urmărite aici --

Urmarirea unui proces după ce acesta a fost pornit:

Identificarea sesiunii de urmărit prin anumite mijloace

Odată ce identificatorul procesului a fost determinat, urmărirea sa poate fi iniţializată după cum urmează:

Presupunem că acesta are oracle pid 9834.

Dezactivarea urmăririi se face prin:

Urmărirea unei instanţe

Dezactivarea

Iniţializarea setărilor parametrilor

15

Setarea va fi dezactivată prin eliminarea parametrului şi repornirea instanţei

Prin intermediul unui trigger de logon

Există situaţii când trebuie urmărită activitatea unui utilizator, în aceste situaţii utilizarea unui trigger la

logare este foarte utilă.

Trace-urile SQL sunt foarte utile pentru diagnosticarea problemelor de performantă deoarece ele vor

evidenţia exact ce interogări sunt cel mai afectate, şi cum au fost afectate acestea ( procesor, timpi de

aşteptare, idle time). Secretul constă în obţinerea trace-ului pentru cea mai importantă şi mai afectată

sesiune cât mai complet posibil.

Exemplu real:

Următorul proces ne va ajuta sa obţinem datele dintr-un SQL trace în mod corect:

Alegerea sesiunii

În acest exemplu vom identifica care sunt sesiunile active care au acumulat cel mai mult timp din

procesor.

Următoarea interogare filtrează sesiunile pe baza timpului de logare, mai mic de 4 ore, şi ultima accesare

în ultimele 30 de minute. Aceste restricţii sunt pentru a găsi sesiuni curente mai relevante, decât cele

care rulează de mult şi care au acumulat mult CPU time, dar nu au probleme de performanţă. Valorile

prezentate sunt orientative.

Găsirea sesiunilor ce au cel mai mare consum al procesorului

-- sesiunile cu cel mai mare consum al procesorului

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active în ultima jumătate de ora

AND s.logon_time > (SYSDATE - 240/1440) – sesiuni logate în ultimele 4 ore

16

ORDER BY st.value;

SID SERIAL# OS PID USERNAME MODULE CPU sec

---------- ---------- ------------ ------------------------------------------------------------ -------------------------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 8.25

147 575 10577 SCOTT SQL*Plus 258.08

131 696 10578 SCOTT SQL*Plus 263.17

139 218 10576 SCOTT SQL*Plus 264.08

133 354 10583 SCOTT SQL*Plus 265.79

135 277 10586 SCOTT SQL*Plus 268.02

Găsirea sesiunilor cu cei mai mari timpi de aşteptare

-- sesiunile cu cei mai mari timpi de aşteptare

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et < 1800 -- active în ultima jumătate de oră

AND s.logon_time > (SYSDATE - 240/1440) -- sesiuni logate în ultimele 4 ore

AND se.sid = s.sid


ORDER BY se.time_waited;

SQL> /

SID SERIAL# OS PID USERNAME MODULE TIME_WAITED

---------- ---------- ------------ ------------------------------------------------------------ -----------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 4

147 575 10577 SCOTT SQL*Plus 45215

131 696 10578 SCOTT SQL*Plus 45529

135 277 10586 SCOTT SQL*Plus 50288

139 218 10576 SCOTT SQL*Plus 51331

133 354 10583 SCOTT SQL*Plus 51428

Găsirea sesiunilor cu cei mai mari timpi ai bazei de date

-- sesiuni cu cu cei mai mari timpi ai bazei de date

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time

(sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

17

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU


AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid


AND s.last_call_et < 1800 -- active în ultima jumatate de ora


AND st.value > 0;

SID SERIAL# OS PID USERNAME MODULE DB Time(sec) CPU Time (sec) % CPU

---------- ---------- ------------ -------- ----------------------------------------------------- -------------- -----------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 12.92 9.34 72.29

Obţinerea unui trace complet

Ideal ar fi să putem porni un trace imediat ce utilizatorul s-a logat şi începe operaţilunile sale sau

tranzacţiile. Trigger-ul menţionat anterion poate fi folosit în aceste situatii.

Colectarea fişierului trace şi generarea unui raport TkProf

Fişierul de trace va fi obţinut după indicaţiile precedente.

Generarea unui raport TKProf şi sortarea interogarilor cele mai lungi se face utilizand urmatoarea

comanda:

tkprof <trace file name> <output file name> sort=fchela,exeela,prsela

Trebuie să ne asigurăm ca fişierele de trace conţin doar date recente.

Dacă această sesiune a fost urmărită recent, există posibilitatea ca alte trace-uri să fie amestecate în

acelaşi fişier cu date recent colectate.

Trebuie să extragem doar datele care fac parte din testul recent.

Fişier de trace pentru on proces ce rulează mult, care a fost urmarit intermitent în ultimele zile

. . .

*** 2009-07-24 13:35:05.642 <== Timestamp de la o urmarire anterioara

WAIT #8: nam='SQL*Net message from client' ela= 20479935 p1=1650815232 p2=1 p3=0

=====================

PARSING IN CURSOR #9 len=43 dep=0 uid=57 oct=3 lid=57 tim=1007742062095 hv=4018512766

ad='97039a58'

select e.empno, d.deptno<== cursor precedent ce a fost urmarit

from emp e, dept d

18

END OF STMT

PARSE #9:c=630000,e=864645,p=10,cr=174,cu=0,mis=1,r=0,dep=0,og=4,tim=1007742062058

BINDS #9:

EXEC #9:c=0,e=329,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1007742062997

WAIT #9: nam='SQL*Net message to client' ela= 18 p1=1650815232 p2=1 p3=0

. . .

FETCH #9:c=10000,e=513,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1007742148898




. . .

====> ACESTE LINII NU FAC PARTE DIN TEST <====

*** 2006-07-24 18:35:48.850

<== Timestamp oentru trace-urile pe care le urmarim (aprox 5 ore mai tarziu)

=====================


ad='9915de10'

alter session set events '10046 trace name context forever, level 12'

END OF STMT

. . .

=====================


ad='9306bee0'

select e.empno, d.dname<== Cursorul ce a fost urmarit

from emp e, dept d

where e.deptno = d.deptno

END OF STMT


BINDS #3:



WAIT #3: nam='db file sequential read' ela= 7126 p1=4 p2=11 p3=1

. . .






STAT #3 id=1 cnt=14 pid=0 pos=1 obj=0 op='HASH JOIN (cr=15 pr=12 pw=0 time=39402 us)'

=====================


ad='95037918'

alter session set events '10046 trace name context off' <== tracing-ul a fost oprit

END OF STMT

Asigurarea că fişerul de trace este complet

Dacă urmărirea a început sau s-a sfarşit în timpul unei apelări, este bine să regândim procesul ca acest

lucru să nu se întâmple din nou.

19

*** 2006-07-24 15:00:45.538 <== Timpul când s-a inceput urmarirea asteptarea

WAIT #3: nam='db file scattered read' ela= 18598 p1=4 p2=69417 p3=8 <== Wait

*** 2006-07-24 15:01:16.849 <== 10g va scrie mesaje dacă în trace nu s-a scris nimic de

ceva vreme

WAIT #3: nam='db file scattered read' ela= 20793 p1=4 p2=126722 p3=7

. . .

*** 2006-07-24 15:27:46.076

WAIT #3: nam='db file sequential read' ela= 226 p1=4 p2=127625 p3=1 <== Yet more waits




. . .

*** 2006-07-24 15:30:28.536 <== Timestamp final inainte şi după apelarea FETCH

WAIT #3: nam='db file scattered read' ela= 5218 p1=4 p2=127705 p3=16 <== Final wait


=====================


ad='931230c8'

select count(*) from big_tab1, big_tab2 <== Aceasta nu este parsarea unui apel real

END OF STMT

FETCH #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1014506207466 <== Terminarea

apelului de FETCH

FETCH raportează 11 microSec.Acest rezultat este greşit după cum se poate observa din

timestamps. Ar trebui să fie aprox. 30 min.

Date despre performanţele SO

Datele despre sistemul de operare sunt necesare pentru a vedea performanţele maşinii pe care rulează

Oracle.

Începând cu versiunea 10g, rapoartele statspack conţin şi date despre sistemul de operare.

Ca aletrnativă se poate folosi una dintre următoarele:

OS Watcher (de preferat) – este o colecţie de scripturi shell UNIX ce colectează şi arhivează

metrice ale sistemului de operare şi ale reţelei pentru a susţine soluţionarea problemelor de

performanţă. OSW funcţionează ca un set de procese background pe server şi colectează date

despre SO la intervale constante, apelând utilitare UNIX ca vmstat, netstat sau iostat.10

LTOM – Lite Onboard Monitor, este un program java, dezvoltat ca o platformă de diagnosticare

în timp real. Diferenţierea acestui utilitar de altele de acest gen, constă în faptul ca funţionarea sa

este mai mult pro-activă decât reactivă. LTOM asigură detectarea problemelor în timp real şi

colectarea de date, fiind foarte util în cazul problemelor neprevăzute.11

10 OS Watcher User Guide [ID 301137.1],


11LTOM - The On-Board Monitor User Guide [ID 352363.1],

20


Enterprise Manager – pagina de performance management include grafice ce arată date despre

performanţele procesorului şi a memoriei, cât şi informaţii detaliate despre procese. Este tot un

uitlitar în timp real, din aceasta cauza vor fi necesare capturi de ecran pentru analizele

ulterioare.12

Raport RDA

Rapoartele RDA colectează multe tipuri de date diferite despre sistemul investigat, ce vor fi utilizate la un

moment dat în procesul de diagnosticare.13

Loguri ale aplicatiei (optional)

De multe ori aplicaţiile loghează când s-a efectuat o apelare către baza de date şi cât a durat pana la

returnarea rezultatelor. Aceste informaţii pot fi utile pentru determinarea stării performanţei bazei de date.

Totuşi, aceste instrumente trebuies utilizate cu precauţie, deoarece ele ar putea indica o latenţă nereală a

bazei de date, latenţă cauzată de cele mai multe ori de probleme pe client sau de conectivitate.

C. Verificarea problemei - analiza datelor

Verificarea utilizarii resurselor SO

Acest pas va verifica dacă:

Există destul procesor şi memorie disponibile pentru procesele Oracle, în caz contrar dacă macar

Oracle foloseşte aceste resurse şi este necesara o analiza mai detaliata a bazei de date

Sau, dacă procese non-Oracle folosesc majoritatea resurselor procesorului şi ale memorie.

Verificarea consumului procesorului

Utilizarea procesorului se verifică raspunzând la următoarele întrebări:

1. Este capacitatea procesorului deficitara?

Acest lucru se verifică analizând:

Totalul utilizat din capacitatea procesorului (USER+SYS) ar trebui să fie mai putin de 90%

Coada de aşteptare per procesor ar trebui să fie mai mica de 4


12Oracle® Enterprise Manager Concepts 10g Release 5 (10.2.0.5),

http://download-west.oracle.com/docs/cd/B19306_01/em.102/b31949/host_3rdparty_management.htm#sthref1530 , accesat la data

25.01.2010

13Remote Diagnostic Agent (RDA) 4 - Main Man Page [ID 330364.1],


21


http://download-west.oracle.com/docs/cd/B19306_01/em.102/b31949/host_3rdparty_management.htm#sthref1530


Utilizarea procesorului diferă în funcţie de cum colectăm datele despre SO:

10gR2 Statspack Reports

Host CPU (CPUs: 1)

~~~~~~~~ Load Average

Begin End User System Idle WIO WCPU

------- ------- ------- ------- ------- ------- ------------------------------------------------------

2.83 10.16 83.54 16.09 0.37 0.24 #######

Coada de aşteptare este, în acest caz, 10.16 (supraincarcare), iar utilizarea totală a procesorului,

83.54+16.09=99.63 (supraîncărcare).

10gR1: Rapoarte AWR sau Statspack

OS Statistics DB/Inst: DB10GR2/DB10gR2 Snaps: 20-21

-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic Total

------------------------- -------------------------------------------------------

BUSY_TIME 14,257

IDLE_TIME 53

IOWAIT_TIME 34

SYS_TIME 2,302

USER_TIME 11,955

LOAD 10

OS_CPU_WAIT_TIME 156,500

VM_IN_BYTES 0

PHYSICAL_MEMORY_BYTES 2,081,890,304

NUM_CPUS 1

Utilizarea procesorului = 100%*BUSY_TIME / (BUSY_TIME_IDLE_TIME) = 100*14257 / (14257+53) =

99.6%

Coada de aşteptare = OS_CPU_WAIT_TIME / (NUM_CPUS*BUSY_TIME) = 156500 / (1*14257) = 10.98

Dacă procesorul nu este săturat, se va continua cu verificarea memoriei, în caz contrar, se va verifca

dacă procesle Oracle folosesc cel mai mult din capacitatea procesorului.

2. Ce procese consumă cel mai mult din capacitatea procesorului?

Din nou, datele diferă în funcţie de utilitarul ales:

10gR2 Statspack

Instance CPU

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

% of total CPU for Instance: 74.27

% of busy CPU for Instanc: 74.55

22

%DB time waiting for CPU - Resource Mgr:

Procentul ocupat din instanţa ne spune cât din procesorul gazda este utilizat de instanţa investigată, în

acest caz, 74.55%, iar răspunsul este afirmativ, această instanţă consumă cel mai mult din capacitatea

procesorului.

OSWatcher

zzz ***Thu Feb 8 15:03:14 PST 2007

load averages: 3.57, 4.23, 3.32 15:03:15

105 processes: 98 sleeping, 6 running, 1 on cpu

Memory: 2048M real, 29M free, 4316M swap in use, 1847M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND

19003 oracle 1 32 0 0K 0K run 6:37 25.13% oracle

6446 oracle 1 0 0 0K 0K run 0:05 21.31% oracle

1980 oracle 30 29 10 200M 53M sleep 1:23 4.83% java

6408 oracle 1 59 0 26M 12M sleep 0:01 2.68% perl

26471 oracle 1 59 0 0K 0K sleep 0:01 1.48% oracle


697 oracle 14 59 -5 0K 0K sleep 340:59 0.67% ocssd.bin



Primele două procese Oracle folosesc aprox 46% din capacitatea procesorului

Verificarea consumului de memorie

1. Deficit de memorie?

Indiferent de ce instrument s-ar utiliza, trebuie să ţină cont de următoarele:

Utilizarea memoriei (% sau KB liberi) – măsoară cât din memoria fizică a fost alocată acestui proces.

Când aceasta este în jur de 100%, sistemul va utiliza din ce în ce mai mult swap, gravitatea acestui

deficit este dată de următoarele două metrice.

Memory Page Scan Rate (pagini/s) – măsoară cât de mult page-scanner-ul se străduie să revendice

memorie. Când aceasta este de sute/s, cel mai probabil avem un deficit fizic de memorie.

23

Utilizarea Swap (% sau KB liberi) - cât din partiţia de swap este utilizată. Dacă memoria fizică devine

deficitară, acest procent creşte. Dacă acesta se apropie de 100%, nevoia de memorie devine tot mai

mare, iar sistemul devine inutilizabil (şi va pica).

2. Ce foloseşte cea mai multă memorie?

Se încearcă să se determine care procese Oracle sau non-Oracle folosesc cea mai multă memorie.

10gR2 Statspack

Dacă valoarea % Host Mem used for SGA+PGA este ridicată , atunci se verifică utilizarea de către

această instanţă a majoritaţii memoriei.

OSWatcher

3. Procesele Oracle consumă cea mai multă memorie?

Dacă la această întrebare se răspunde afirmativ în urma analizei, atunci s-a verificat existenţa unei

probleme de performanţă Oracle, iar următorul pas este identificarea cauzelor de apariţie.

Verificarea că baza de date funcţionează lent

O problemă de performanţă poate aparea datorită instanţei, dar, de fapt să fie cauzată de latenţa unui

client (de obicei middle-tier) sau a reţelei. În acest pas vom verifica dacă într-adevăr baza de date are

latenţă, sau trebuie să investigăm problema în altă parte.

Idea principală în această comparaţie este să confruntăm DB Time total din rapoartele “bune” şi cele

“rele”. DB Time este timpul total petrecut de baza de date fie procesând (CPU) sau aşteptând după un

eveniment non-idle. Când există o problemă de performanţă, DB Time creşte (de obicei deoarece există

multe sesiuni care aşteaptă după evenimente non-idle).

24

Analiza următoarelor rapoarte ar trebui să fie concludentă.

Analiza rapoartelor AWR, ASH sau a statpack-urilor

1. 10.2.x: Comparaţie între două rapoarte ASH dintr-o perioadă cu funcţionare optimă şi una

deficitară

Vom compara media sesiunilor active în cele două rapoarte. Media sesiunilor active va arăta câte sesiuni

erau fie în procesor sau aşteptând după un eveniment non-idle. Când performanţa este deficitară din

cauza bazei de date, numărul de sesiuni active va fi mai mare decât în cea cu performanţa optimă. Acest

lucru se datorează faptului ca atunci când există un deficit de resurse, mai multe sesiuni vor fi nevoite să

aştepte activ pentru o anumită resursă şi din această cauză numărul de sesiuni active va fi mai mare.

Sumarul unui raport ASH dintr-o perioadă optimă de funcţionare:

şi cel dintr-o perioadă cu performanţe deficitare:

25

Se observă creşterea numărului mediu de sesiuni active când performanţa nu este optimă, este vorba de

o creştere de 500% (de la 2.65 la 10.11). De asemenea, se observă o schimbare la Top User Events,

timpul de aşteptare a trecut de la “db file sequential read” la “read by other session” şi “CPU+Wait for

CPU”, împreună cu “db file scattered read”. Acest fapt se datorează unui index şters într-o tabelă

importantă. O interogare care în mod normal folosea indexul, acum efectuează un scan complet al tabelei

în perioada cu performanţă deficitară.

2. 10.2.x: Comparaţie între doua rapoarte AWR

Pentru realizarea raportului se va utiliza $OH/rdbms/admin/awrrpt.sql şi vom verifica dacă DB Time a

crescut în timpul perioadei deficitare:

Precizare: trebuie comparate instantanee de acceaşi durată.

3. Analiza unui raport statspack

Utilizarea unui singur raport statspack nu este relevantă pentru verificarea problemei. Totuşi, putem să ne

uităm la Top 5 evenimente, şi să vedem dacă acestea sunt bune.

Evenimentele dăunatoare sunt:

Enqueues

Latches

Buffer busy waits

Row cache lock waits

Free buffer waits

RAC waits (having to do with GC)

Library cache lock or pin waits

Shared cursor S to X waits

Aceste evenimente necesită diagnosticare suplimentară pentru a determina cauza lor. De exemplu, o

blocare a library cache-ului nu va fi rezolvată de tuning-ul unei interogări de a efectua mai puţine citiri din

buffer. Soluţiile pentru acest tip de probleme sunt adesea dificil de implementat, de ex. Rescrierea unor

parţi din aplicaţie pentru a evita conflictele de blocare.

Evenimente “sănătoase”:

CPU

db file sequential read

db file scattered read

direct path read

26

direct path write

Prezenţa acestor evenimente înseamnă fie o necesitate de a adăuga capacitate procesorului, sau poate

ceva tuning al interogărilor. În orice caz, soluţiile sunt uşor de obţinut prin rezolvări comune.

4. Analiza unei Extended SQL Trace

Se vor analiza datele din trace-ul interogării pentru a verifica dacă baza de date are o problemă de

performanţă.

Variante:

Comparaţie între doua TKProf (performanţă bună şi deficitară) – de preferat

Putem vedea dacă există o problemă de performanţă prin compararea trace-urilor de execuţie ale

aceleiaşi operaţii în cele doua perioade.

- în varianta optimă, la sfarşitul TKProf-ului se vor observa următoarele valori (atat cele recursive cât şi

cele non-recursive):

total elapsed time

total calls

idle time, i.e., SQL*Net Message from client wait, total waited

total number of rows returned

- se vor calcula următoarele metrice:

elapsed time / call = (total elapsed recursive + total elapsed non-rec) / total calls

rows / call = total rows / total calls

total idle time / call =[ idle time (non-recursive) + idle time (recursive)] / total calls

- se vor repeta operaţiunile anterioare şi pentru performanţa deficitară

- se vor compara metricile calculate între cele doua perioade

Rezultatele ar putea fi următoarele:

Elapsed Time /

Call

Rows / Call Total Idle Time Verificarea rezultatelor

La fel La fel La fel Nici o problemă

Mai mare La fel La fel Problemă a bazei de date

La fel La fel Mai mare Problemă de client (generala)

La fel sau Mai mare Mai mic Mai mare Problemă de client (arraySize)

Concluzii

Nici o problemă – fişierele de trace sunt similare. Acest lucru arată ca nici baza de date, nici

clientul nu au latenţă. Problema ar putea fi inaintea clientului / mid-tier, ex. browser-ul sau

27

reţeaua ar putea avea latenţă. În acest moment investigarea bazei de date va înceta, iar

eforturile se vor concentra pe clienţi.

Problemă a bazei de date – comparaţia între fişierele de trace arată Elapsed Time/call mai mari,

ceea ce înseamnă că bazei de date îi ia mai mult timp să execute aceleaşi procesări. Se

confirmă o problemă de performanţă a bazei de date. Pasul următor ar fi identificarea cauzei.

Problema de client (generală) – comparaţia între fişierele de trace arată că baza de date este mai

mult idle şi nu îi trebuie mulţi timpi de procesare/actiune. De obicei, acest lucru spune despre

clientul bazei de date sau despre reţea că trimit cereri către baza de date, ce pare mai lentă din

punctul de vedere al utilizatorului. În acest moment investigarea bazei de date va înceta, iar

eforturile se vor concentra pe clienţi.

Problema de client (arraySize) – clientul procesează mai puţine cereri/acţiuni de la baza de date.

Acest comportament este ineficient şi stresează baza de date (mai multe citiri logice, block pins,

context switches, etc.). Pentru a rezolva această problemă, clinetul va trebui să ceară mai multe

procesări. În acest moment investigarea bazei de date va înceta, iar eforturile se vor concentra

pe clienţi.

Exemplu de analiza

Urmatorul TKProf a fost colectat într-o perioadă cu performanţă bună.

28

Metricele calculate pentru datele adunate sunt:

Metricele din perioada nefavorabila sunt:

29

Când comparăm rezultatele, obţinem:

Concluzia: este vorba de o problemă a bazei de date.

Dacă în cadrul analizei de mai sus s-a concluzionat că este vorba de o problemă a bazei de date, trebuie

să trecem la pasul următor când vom încerca să identificăm care este cauza apariţiei acestor probleme.

Faza 2 - Determinarea cauzei

În acest moment ar trebui să avem verificată o problemă de latenţă a bazei de date.

Procesul de determinare a cauzei are două etape:

Colectarea de date – de obicei se folosesc datele colectate în etapa de verificare a problemei.

Analiza

Determinarea tipului de problemă de performanţă - bottleneck

Utilizănd datele colectate anterior trebuie să identificăm ariile problematice ale bazei de date. Tuningul

unei baze de date este adesea un proces iterativ, unde sunt identificate blocajele şi rezolvate cu fiecare

iteraţie.

Oracle consumă cel mai mult din capacitatea procesorului

Dacă în urma analizei datelor colectate s-a verificat consumul procesorului, iar capacitatea acestuia a fost

găsită ca fiind deficitară, Oracle consumând cea mai mare parte, trebuie să recurgem la o strategie ce va

reduce consumul acestuia.

Wait bottleneck

TKPROF

Vom compara durata totală cât procesorul a lucrat concret cu durata procesului. Dacă timpii non-idle sunt

mai mari decât cei procesaţi din durata totală, atunci avem o problemă “wait bottleneck”, în caz contrar o

problemă legată de cunsumul procesorului.

În cazul unui wait bottleneck trebuie să:

examinăm timpii de aşteptare şi să îi identificăm pe cei mai mari

să alegem o strategie de tuning adecvată

30

Exemplu

În acest caz vom observa o problemă la timpii de aşteptare “read by other session”. Deasemnea, mai

sunt timpi de aşteptare pentru procesor (ex. o coadă de procese semnificativă datorată supraîncărcării)

ce contează la diferenţa dintre timpul total şi suma timpilor de procesare şi cei de aşteptare.

Total CPU time = 24.58 sec

Total DB Time = 284.16 sec

Wait: "read by other session" = 158.10 / 284.16 = 55.6%

CPU = 24.58 / 284.16 * 100% = 8.7%

Wait: "db file scattered read" = 7.11 / 284.16 = 2.5%

31

Rapoarte AWR / Statspack

Se vor examina timpii de aşteptare din secţiunea Top 5 events

Exemplu

În acest caz se observă un bottleneck mare pentru read by session (79.6% din timpul total al procesării):

Rapoarte ASH

Se va examina secţiunea Top user events, %Activity.

Exemplu

În acest caz, se observă un bottleneck mare pentru read by session (53.4% din sesiunile active).

32

Client bottleneck

Acest tip de blocaj este detectat prin observare faptului că sesiunile petrec majoritatea timpului aşteptând

după un eveniment dinafara Oracle.

Cu ajutorul următorilor paşi, putem identifica acest tip de blocaj:

Timpii de aşteptare ai clientului pot fi văzuti, agregaţi într-un fişier TKProf, uitându-ne la secţiunea

OVERALL TOTALS:

În acest exemplu putem observa că timpii bazei de date reprezintă doar un mic procent din timpul total

(atât timpii recursivi cât şi cei non-recursivi):

Database elapsed time = total elapsed = 2.12 + 0 = 2.12 sec

Client Idle time = SQL*Net message from client = 187.32 + 0 = 187.32 sec

Total Time = Database elapsed time + Client Idle time = 2.12 + 187.32 = 189.44 sec

% Client Idle Time = Client Idle Time / Total Time = 187.32 / 189.44 * 100% = 98.9%

33

Din moment ce aproape 99% din timp este folosit aşteptând clientul, confirmă ipoteza iniţială.

Ca modalitate de rezolvare se poate reduce numărul de apeluri fetch sau tuning-ul clinetului.

Investigarea continuă prin verificarea dacă timpii de aşteptare sunt mari prentru Orice tip de procesare,

prin examinarea unui fişier brut de trace 10046 şi observând timpii de aşteptare pentru mesajele SQL*Net

pentru evenimentele clientului, ce apar în liniile începând ce “WAIT 80”:

În acest exemplu se pot observa două feluri de WAIT: WAIT#0 şi WAIT#1. simbolul # este asociat unui

cursor când este mai mare de 0. Linia "WAIT #0, SQL*Net message from client" este o aşteptare

după ce ultimul fetch s-a terminat şi baza de date aşteaptă un nou apel. Linia "WAIT #1, SQL*Net

message from client" este pentru client, dar pentru un cursor deschis din care tocmai s-a extras. Dacă

ar fi fost executate mai puţine extrageri, numarul aşteptărilor WAIT#1 ar fi putut fi reduse. WAIT#0 pot fi

reduse doar prin folosirea unui client mai rapid.

Acum trebuie să verificăm dacă majoritatea timpului de aşteptare este petrecut aşteptând între două

apeluri de fetch. În raportul TKProf, se poate observa că timpul total al bazei de date este mic în

comparaţie cu cel aşteptat de către client. Deasemenea se poate observa timpul petrecut în mesaje

SQL*Net pentru client:

34

Observatii: 8 apeluri fetch pentru a returna 14 randuri. 78.39 secunde aşteptând SQL*Net message from

client pentru 8 aşteptări. Fiecare aşteptare corespunde fiecărui apel fetch. Timpul total al bazei de date a

fost de 377 microsecunde, dar timpul total pentru returnarea celor 14 rânduri a fost de 78.39 secunde

datorită latenţei clientului. Dacă s-ar reduce numărul de apeluri fetch, se va reduce timpul total.

În orice caz, starea bazei de date este bună, problema fiind una externă.

Oracle consumă cea mai multă memorie

Dacă în urma analizei datelor colectate s-a verificat consumul de memorie, iar capacitatea acesteia a fost

gasită ca fiind deficitară, Oracle consumând cea mai mare parte, trebuie să recurgem la o strategie ce va

reduce consumul memoriei.

Faza 3 - Alegerea strategiei de tuning

Se va alege una dintre strategiile de ma jos, în funcţie de tipul de problemă de performanţă identificat.

Pentru Oracle 10g şi versiunile ulterioare, se recomandă utilizarea ADDM pentru tuning-ul bazei de date.

Oracle 10g poate să îndeplinească analize de tuning utilizând ADDM. Se consideră a fi metoda preferată,

tuning-ul manual fiind ultima variantă. Pentru a putea folosi utilitarul ADDM, este necesară o licenţă

pentru Tuning Pack.

1. Reducerea consumului de procesor

În ceea ce priveşte bazele de date, procesorul poate avea două tipuri de consum diferite:

Parse CPU – capacitatea procesorului utlizată când Oracle procesează (şi optimizează) o

comandă

35

Non-Parse CPU – capacitatea CPU folosită de Oracle când este angajat în procesări, ca de ex.

când citeşte blocuri în buffer cache, când execută o sortare sau un join, citirea unui fişier, etc.

Acest consum mai este referit cu ajutorul sintagmei “Other CPU”.

Procesorul este urmărit sub sigla “Recursive CPU”. Aceasta reprezintă capacitatea procesorului folosită

de comenzile ce sunt apelate de alte comenzi. Deoarece Recursive CPU se referă la ambele tipuri de

comsum precizate anterior, nu prezintă interes pentru tuning.

Tipul de consum al procesorului se poate determina utilizând TKProf:

Parse CPU – în secţiunea Overall Totals, se adună Parse CPU pentru comenzile recursive şi

pentru cele non-recursive.

Total CPU - în secţiunea Overall Totals, se adună Total CPU pentru comenzile recursive şi

pentru cele non-recursive.

Non-Parse CPU – total CPU –parse CPU

Reducerea consumului de Parse CPU

Consumul procesorului pentru parsări

Fapte necesare pentru analiză:

a. Cel mai mult timp de procesare se datorează unei (sau mai multor) interogări SAU agregării lor?

Pentru a raspunde la această întrebare se va genera un nou raport TKProf ordonat după timpul

de parse CPU :

tkpof trace_file_name output_file sort=prscpu

Trebuie să analizăm valorile CPU parse pentru interogările de la începutul fişierului şi să

investigăm cum a fost împărţit consumul procesorului.

b. Interogările cu timpul de procesare cel mai mare sunt Hard-parsed?

Pentru a identifica dacă acesta este cazul, pentru interogările din TKProf cu timpul de procesare

al procesorului ridicat, se verifică dacă “Misses in library cache during parse” este apropiat de

numărul total de procesări pentru acea comandă.

Probleme posibile:

Una sau mai multe interogări ce consumă mult din capacitatea procesorului în timpul HARD parse. Este

adesea observată când comenzi de dimensiuni mari invocă multe obiecte.

Ce să căutăm:

36

Comanda a fost parsată hard?

Sa comparăm parse cpu time cu parse elapsed time şi să vedem dacă parse cpu time>50%

Cauze identificate:

S-a folosit dynamic sampling pentru interogare

Interogarea foloseşte operatori IN / OR

Tabelele partiţionate au multe partiţii

Model de tuning

Problema identificată: Una sau mai multe interogări ce prezintă consum mare de procesor în timpul unei

HARD parse

Acest compertament este adesea întâlnit când comenzi voluminoase invocă multe obiecte sau obiecte

partiţionate.

Ce să căutăm:

1. Trebuie să verificăm dacă instructiunea/comanda a fost parsată HARD

2. Trebuie sa comparăm “parse CUP time” cu “parse elapsed time” şi să vedem dacă “parse cpu time” este mai

mult de 50%

Cauza identificată: Interogarea foloseşte mostre dinamice şi influenţează în mod negativ timpul de procesare

Justificarea cauzei:

- timpul de procesare este responsabil pentru timpul total de execuţie al interogării

- rezultatul planului de execuţie SQLTXPLAIN, scriptul UTLXPLS, trace-ul 10053 vor arăta ca pentru

optimizarea interogării s-au folosit mostre dinamice.

Soluţia identificată: Alternative la utilizarea mostrelor dinamice

Dacă timpul de procesare este mare, sunt necesare soluţii alternative pentru a obţine planul de execuţie dorit

fară utilizarea mostrelor dinamice.

Efort -mediu

Unele alternative sunt mai uşor de implementat (adaugarea unui hint), iar altele sunt mai dificile (determinarea

hint-ului necesar prin comparaţia planurilor de execuţie).

Risc – scăzut

În general, această soluţie va afecta doar interogarea în cauză.

Implementarea soluţiei

Câteva alternative la mostrele dinamice sunt:

37

1. În 10g sau versiuni ulterioare, se va folosi SQL Tuning Advisor (STA) pentru a genera un profil pentru

interogarea vizată.

2. Se vor gasi hint-urile necesare pentru implementarea planului generat normal prin utilizarea mostrelor

dinamice şi se va modifica interogarea cu hint-ul identificat.

3. Se va folosi outline-ul salvat pentru a capta un plan generat cu mostre dinamice. Pentru date foarte volatile

(pentru care mostrele dinamice ajutau la obţinerea unui plan de execuţie bun), se poate folosi

abordarea ca aplicaţia va alege una dintre interogările cu hint-uri în funcţie de starea datelor (ex.

pentru date şterse recent foloseşte interogarea#!, altfel interogarea#2).

Documente utile despre hint-uri

Using Optimizer Hints14

Forcing a Known Plan Using Hints15

How to Specify an Index Hint16

QREF: SQL Statement HINTS17

Documents for stored outlines / plan stability:

Using Plan Stability18

Stored Outline Quick Reference19

How to Tune a Query that Cannot be Modified20

Verificarea implementării

Se va implementa soluţia şi se va verifica dacă performanţele bazei de date se îmbunătăţesc. În caz contrar

trebuie să examinăm următoarele:

- să luăm în considerare alte posibile cauze

- să verificăm dacă datele au fost colectate corect

- să verificăm dacă problema iniţială.

O alternativă ar fi înregistrarea unui Service Request cu Oracle Support pentru asistenţă în rezolvarea

acestei probleme.

14Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 16 Using Optimizer Hints

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602 , accesat la data 25.01.2010

15 Forcing a Known Plan Using Hints [ID 221970.1],


16 How to specify an INDEX Hint [ID 50607.1],


17 QREF: SQL Statement HINTS [ID 29236.1],


18 Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 18 Using Plan Stability,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#sthref1787 , accesat la data 25.01.2010

19 Stored Outline Quick Reference [ID 67536.1],


20 How to Tune a Query that Cannot be Modified [ID 122812.1],


38



http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#sthref1787




http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602

Consumul procesorului pentru activităţi non-parse

Acest tip de consum se datorează, de obicei, interogărilor SQL cu performanţe slabe, sau procedurilor

PL/SQL.

În acest caz, procesorul este folosit în două feluri:

EXECUTE – monitorizează consumul procesorului pentru comenzi PL/SQL, DML şi DDL.

FETCH - monitorizează consumul procesorului pentru comenzi SELECT, când sunt accesate

înregistrările şi sunt pregătite pentru a fi returnate clientului. Aceasta include şi efortul de a traversa

indecşii, de a citi blocurile, de a executa join-urile şi să urmarească planul de execuţie pentru obţinerea

înregistrărilor.

Probleme posibile:

Una sau mai multe interogări folosesc cel mai mult din non-parse CPU

Ce să căutam:

TKProf – doar câteva interogări folosesc cel mai mult din totatul consumul procesorului (top statements

când TKProf este sortat după timpul de fetch şi execute)

Rapoarte AWR sau statspack – doar acteva interogări sunt raportate cu un consum mare de procesor

(secţiunea Top 5 events).

Cauze identificate:

Sunt necesare optimizări ale interogărilor (capitolul V)

2. Reducerea wait bottleneck-urilor

O dată ce a fost identificat un wait bottleneck, se pot examina cauzele apariţiei acestuia. Diversele tipuri

de evenimente de aşteptare sunt prezentate mai jos:

Tipul evenimentului de aşteptare Categorie

buffer busy waits Buffer Busy Waits

db file scattered read Reads / Writes

db file sequential read Reads / Writes

direct path read Reads / Writes

direct path write Reads / Writes

enq: TM - contention Concurrency - Enqueues / Locks / Pins

enq: TX - contention Concurrency - Enqueues / Locks / Pins

enq: TX - row lock Concurrency - Enqueues / Locks / Pins

free buffer waits Reads / Writes

39

global cache cr request Cluster

latch: cache buffers chains Concurrency - Latches and Mutexes

latch: library cache Concurrency - Latches and Mutexes

latch: shared pool Concurrency - Latches and Mutexes

library cache lock Concurrency - Enqueues / Locks / Pins

library cache pin Concurrency - Enqueues / Locks / Pins

log buffer space Configuration

log file sync Commit

read by other session Concurrency - Buffer Busy Waits

Concurrency - Buffer Busy Waits

Buffer busy waits indică existenţa unor buffer-e în buffer cache pe care multiple procese încearcă ori să le

acceseze concurent în timp ce sunt citite de pe disc, ori aşteaptă după altă sesiune să termine

modificarile în blocuri.

Unde să căutăm

TKProf – Overall wait event summary pentru comenzile recursive şi non-recursive arată timpi

semnificativi pentru buffer busy waits

AWR/statspack – buffer busy waits este în topul evenimentelor cele mai mari consumatoare de

timp.

Cauze identificate:

O activitate de inserare masivă ce afectează segmentul de index

Multe interogări concurente ce efectuează citiri fizice

Multe interogări concurente ce efectuează citiri fizice şi performanţa I/O este scăzută

Problema identificată:

Read by other session

Cauze identificate:

Sunt necesare optimizări ale interogărilor, nu sunt prezente şi probleme de I/O

Probleme de performanţă ale I/O

Buffer cache este de dimensiune mică

Problema identificată:

Cozi de aşteptare

40

Cauze identificate:

Coloanele chei străine au lipsă un index21

ANALYZE INDEX blochează comenzile DML

Pe acelaşi obiect se utilizează un DML în timp ce deja rulează un parallel DML

select o.object_name,l.oracle_username,l.os_user_name,l.session_id

,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')

from user_objects o , v$locked_object l

where o.object_id = l.object_id;

Aşteptări ale unei rezervări la nivel de înregistrare datorate unei proiectări defectuase a aplicaţiei

Obiecte ce sunt compilate în timpul sesiunii

Procesul de auditare este pornit

Folosirea excesiva a declanşatoarelor la nivel de înregistrare

Model de tuning

Problema identificată: Buffer busy waits

Verificarea problemei:

TKProf – Overall wait event summary pentru comenzile recursive şi non-recursive arată timpii

semnificativi pentru buffer busy waits

AWR/statspack – buffer busy waits este în topul evenimentelor cele mai mari consumatoare de timp.

Cauza identificată: Multe comenzi SQL concurente execută citiri fizice. Acest fapt conduce la timpi mari

de aşteptare pentru buffer-e, deoarece o sesiune va fi cea care scrie fizic, iar celelate vor fi blocate de

către evenimentul buffer busy wait până când citirea s-a terminat. De obicei, acest fapt este un indiciu că

interogarea ar trebui optimizată.


TKProf

- rapoarte TKProf ordonate după elapsed execute time

- trebuie să ne uităm la topul comenzilor şi să determinăm dacă acestea “vad” buffer busy waits şi dacă

sunt interogări sau comenzi DDL ce conţin sub-interogări.

AWR or statspack reports

- buffer busy waits este în topul evenimentelor

- comenzile SQL cu cei mai mari tipi (elapsed time-cpu time) sunt interogări

Soluţia identificată: Optimizarea interogării folosind SQL Tunning Advisor

21 Anexa 4

41

Oracle SQL Tuning Advisor ne poate ajuta să optimizăm o interogare specifică în mod rapid şi uşor,

pentru aceasta fiind necesară o licenţă pentru Enterprise Manager Tuning Pack.

Efort – scăzut

Risc - scăzut

Acţiunea de optimizare va fi, în general, de creare a unui profil de comandă. Profilul afectează doar o

singura comandă. Orice altă recomandare ar trebui testată cu prudenţă.


Detalii despre SQL Tuning Advisor se pot găsi în următoarele documente:

How-To

How to use the Sql Tuning Advisor22

Documentaţie

Automatic SQL Tuning23

Using Advisors to Optimize Database Performance24

Using SQL Tuning Advisor with Oracle Enterprise Manager25

Referinţe

SQL Tuning Advisor Subprograms26

Using SQL Tuning Advisor APIs27

Automatic SQL Tuning - SQL Profiles28

Anexa 5


22How to use the Sql Tuning Advisor. [ID 262687.1],


23 Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12 Automatic SQL Tuning,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010

24 Oracle® Database 2 Day DBA 10g Release 2 (10.2) - Using the SQL Tuning Advisor,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref784 , accesat la data 25.01.2010

25 Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 12 Automatic SQL Tuning

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010

26 Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) – Chapter 101 DBMS_SQLTUNE,

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#sthref7584 , accesat la data 25.01.2010

27 Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12.2.4 Using SQL Tuning Advisor APIs,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i34827 , accesat la data 25.01.2010

28 Automatic SQL Tuning - SQL Profiles [ID 271196.1],


42


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i34827

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#sthref7584

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref784







- să verificăm dacă problema iniţială.


acestei probleme.

3. Reducerea Client Bottleneck-urilor

Un clinet bottleneck în contextul unei baze de date lente reprezinta faptul ca sesiunile consumă cel mai

mult timp înafara bazei de date, cauzate, în principiu de latenţa clientului sau a reţelei.

Timpii de aşteptare mari datoraţi eventurilor de tip client inaintea oricarui apel de comandă sau

procedură.

Oracle shadow petrece mult timp aşteptând mesajele de la clienţi. Timpii de aşteptare apar între apelurile

de FETCH şi cele de PARSE sau înaintea celor de EXECUTE. Există puţine apeluri FETCH pentru

acelaşi cursor.

După ce să ne uităm:

TKProf:

Overall wait event summary pentru comenzile recursive şi non-recursive arată timpii semnificativi

pentru mesajele SQL*Net de la timpii de aşteptare a clientului comparativ cu „total elapsed time” al

bazei de date.

Fiecare apel FETCH returnează tipic 5 sau mai multe înregistrări (ceea ce indică existenţa array-

fetch-urilor).

43

Model de tuning

Cauza identificată: Latenţa reţelei ce limitează răspunsurile dintre client şi baza de date. Reţeaua este

saturată şi acest fapt limitează abilitatea clientului şi a bazei de date de a comunica între ei.


TKProf:

1. Mesajele SQL*Net pentru timpii de aşteptare client reprezintă o parte mare din timpul total (secţiunea

overall summary)

2. Sunt utilizate operaţii cu array-uri. Acest fapt se observă când există mai mult de 5 înregistrări în medie

per execuţie.

3. Timpul mediu de răspuns la un apel ping este de doua ori cât tipul mediu pentru un mesaj SQL*Net de

la client wait, şi acest timp este mai mare decât câteva milisecunde. Acest lucru indică faptul că cei mai

mulţi timpi sunt petrecuţi înafara reţelei.

De asesemenea, se poate observa o performanţa buna a aceloraşi interogări cerute de un alt client sau

subnet (de obicei,aflate mai aproape de serverul de baze de date).

Soluţia identificată: Investigarea reţelei. Se vor verifica timpii de răspuns ai reţelei la subnet-uri şi

interface card-uri diferite. Netstat ping şi unelte de tracerout pot fi deasemenea utilizate pentru a verifica

performanţele reţelei.

Effor - mediu. Problemele de reţea sunt adesea uşor de verificat, dar dificil de soluţionat.

Risc - scăzut


Consultarea documentaţiei sistemului pentru folosirea utilitarelor gen ping, netstat şi tracerout.






- să verificăm problema iniţială.


acestei probleme.

44

4. Reducerea consumului de memorie de către Oracle

Oracle foloseşte memorie pentru SGA şi PGA. Trebuie examinată dimensiunea SGA şi PGA pentru a

determina ce utilizează memoria sistemului.

Oracle consumă multă memorie datorită unui SGA mare.

Utilizarea uneia sau mai multor SGA pe o maşină lasă puţină memorie pentru a fi utilizată de către PGA

şi alte necesităţi ale maşinii.

Model de tuning

După ce să ne uităm:

RDA:

porţinune mare din memoria maşinii este utilizată de una sau mai multe SGA (se vor verifica mărimea

totală a buffer cache-ului şi a shared-pool):

Overview->System information->Total Physical Memory

RDBMS->SGA Information, se vor aduna toate componentele.

Se va repeta pentru toate instantele de pe maşină

Se va compara mărimea totală a SGA cu memoria fizică.

Cauza identificată: Buffer cache supradimensionat. Buffer cache este foarte mare şi utilizează mai multă

memorie decât este necesar.


Rapoarte AWR sau statspack:

- nu se foloseşte automatic shared memory management (ASMM), ex. SGA_TARGET=0

- buffer cache hit ratio este în jur de 99%

Soluţia identificată: pentru 10g+ se va folosi automatic shared memory management (ASMM)

ASMM va cauta să optimizeze mărimea buffer cache-ului fără intervenţia utilizatorului.

Effort: scăzut. Schimbarea parametrului de iniţializare.

Risc: scăzut. Se va seta pentru SGA_TARGET o valoare rezonabila.

Implementarea soluţiei:

Documentatie

Concepts: Memory Architecture29

Concepts: Automatic Shared Memory Management30

29Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, http://download-

west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm , accesat la data 25.01.2010

45

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm


Admin: Using Automatic Shared Memory Management31

Performance Tuning: Configuring and Using the Shared Pool and Large Pool32

Articole

Understanding and Tuning the Shared Pool33

Oracle Database 10g Automated SGA Memory Tuning34

How To

How To Use Automatic Shared Memory Management (ASMM) În Oracle10g35

Shared pool sizing în 10g36



trebuie sa examinăm următoarele:

- sa luăm în considerare alte posibile cauze

- sa verificăm dacă datele au fost colectate corect



acestei probleme.

30 Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, Automatic Shared

Memory Management,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#CNCPT810 , accesat la data 25.01.2010

31 Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, Using Automatic

Shared Memory Management,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#BABGCBHI , accesat la data 25.01.2010

32 Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 7 – Memory Confiiguration and Use, 7.3 –

Configuring and Using the Shared Pool and Large Pool,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i30970 , accesat la data 25.01.2010

33 Understanding and Tuning the Shared Pool [ID 62143.1],


34 Oracle Database 10g Automated SGA Memory Tuning [ID 257643.1],


35 How To Use Automatic Shared Memory Management (ASMM) În Oracle10g [ID 295626.1],


36 Shared pool sizing in 10g [ID 270935.1],


46





http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i30970

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#BABGCBHI

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#CNCPT810

Capitolul 3 - Baza de date este blocata (locking) sau se agaţă

(hang)


A. Generalităţi

Pentru o identificare corectă a problemei ce se doreşte a fi rezolvată, sunt necesare trei lucuri:

recunoaşterea unei interogări agăţate

clarificarea detaliilor problemei

verificarea ca problema raportată este cea reală

a. Recunoaşterea unei probleme de blocaj sau agăţare

Ce este o problemă de agăţare sau blocaj?

O “adevarată” problemă de agăţare într-o bază de date se poate manifesta astfel:

baza de date nu mai permite utilizatorilor să se conecteze

baza de date nu mai funcţionează

SELECT 1 FROM dual nu returnează nimic

CREATE TABLE nu se finalizează

O problemă de blocaj se poate manifesta astfel:

Una sau mai multe sesiuni au încetat să mai funcţioneze

Este posibilă identificarea agaratii sau a blocajului dintre următoarele:

Benchmarking/testare

Sesizarile utilizatorilor

Systemstate sau handanalyze arata sesiuni blocate (stuck)

O interogare care apare ca agatata

Sesiune ce consumă mult din capacitatea procesorului

Erori ORA-60 care apar în logurile cu alerte

Acest tip de probleme pot să apară după:

Modificări ale schemei

Schimbări ale parametrilor bazei de date

Schimbări în aplicaţie

Upgrade al bazei de date

47



poate întâmpla ca în faze ulterioare, sau în timp ce se lucrează la problemă, adevărata problemă să iasă

la iveală, şi să fie necesară o redefinire a acesteia.


Utilizatorii afectaţi

Secvenţa de evenimente care a condus la problema respectivă

Unde/cum a fost observată


Ce funcţionează

Care este rezultatul acceptat sau aşteptat

Ce s-a făcut pentru a se încerca rezolvarea problemei

Exemple practice:

Multe sesiuni par să nu finalizeze cererile adresate

A fost observată de către utilizatorii finali

Nu se pot face noi conectări la baza de date

Nimic nu pare să mai funcţioneze

În mod normal solicitarile utilizatorilor se finalizează în mai putin de 2 secunde.37

S-a verificat alert log-ul pentru erori ORA-60.

c. Verificarea problemei


Săritul peste acest pas este riscant deoarece ne putem angaja în soluţionarea problemei greşite şi vom

irosi timp şi eforturi semnificative. O definire clară a problemei este critică în determinarea cauzelor şi

soluţiei problemei.

Obiectivul acestui pas din procesul de diagnosticare este să ne asigurăm ca baza de date are

simptomele unei probleme de blocare sau agăţare. În acest moment trebuie sa colectăm date ce verifica

existenta problemei.

Pentru aceasta verificare trebuie sa colectăm dovezi ale agăţării sau blocării.

Exemple:

37 How to Identify Resource Intensive SQL for Tuning [ID 232443.1],


48


SQL> set timing on

SQL> SELECT 1 from dual;

Dacă interogarea nu returnează rezultate, există sansele unei probleme de agăţare reale.


Date despre performanţele SO

Datele despre sistemul de operare sunt necesare pentru a vedea performanţele maşinii pe care rulează

Oracle.

Începând cu versiunea 10g, rapoartele statspack conţin şi date despre sistemul de operare.

Ca aletrnativă se poate folosi una dintre următoarele:

OS Watcher (de preferat) – este o colecţie de scripturi shell UNIX ce colectează şi arhivează

metrice ale sistemului de operare şi ale reţelei pentru a susţine soluţionarea problemelor de

performanţă. OSW funcţionează ca un set de procese background pe server şi colectează date

despre SO la intervale constante, apelând utilitare UNIX ca vmstat, netstat sau iostat.38

LTOM – Lite Onboard Monitor, este un program java, dezvoltat ca o platformă de diagnosticare

în timp real. Diferenţierea acestui utilitar de altele de acest gen, consta în faptul ca funtionarea sa

este mai mult pro-activă decât reactivă. LTOM asigură detectarea problemelor în timp real şi

colectarea de date, fiind foarte util în cazul problemelor neprevăzute.39

Enterprise Manager – pagina de performance management include grafice ce arată date despre

performanţele procesorului şi a memoriei, cât şi informaţii detaliate despre procese. Este tot un

uitlitar în timp real, din această cauză vor fi necesare capturi de ecran pentru analizele

ulterioare.40

Hanganalyze şi SystemState

1. Despre Hanganalyze38 OS Watcher User Guide [ID 301137.1],


39 LTOM - The On-Board Monitor User Guide [ID 352363.1],


40Oracle® Enterprise Manager Concepts 10g Release 5 (10.2.0.5),

http://download-west.oracle.com/docs/cd/B19306_01/em.102/b31949/host_3rdparty_management.htm#sthref1530 , accesat la data

25.01.2010

49




Hanganalyze utilizează apelări interne ale kernelului pentru a determina dacă sesiune aşteaptă o resursă

şi raportează relaţiile dintre procesele ce blochează şi cele care aşteaptă. Adiţional, determină la ce

procese se poate renunţa şi poate realiza transcrieri ale memoriei (dumps) de tip PROCESSSTATE sau

ERRORSTACK pe acele procese, în funcţie de nivelul ales la execuţia HANGANALYZE-ului.41

Comanada HANGANALYZE este disponibilă încă din versiunea ORACLE 8.1.6. Începând cu versiunea 9i

ofera facilităţi pentru medii RAC într-o singură “fotografiere”.

Sintaxa HANGANALYZE:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level <nivel>';

ORADEBUG hanganalyze <nivel>

ORADEBUG -g def hanganalyze <nivel> (pentru clustere)

Nivelul stabileşte cantitatea de informaţie adiţională ce va fi extrasă din procesele identificate de

HANGANALYZE (conţinutul memorie pentru ERRORSTACK) bazata pe starea nodului.

Nivelele sunt:

10 - toate procesele (starea IGN)

5 – Nivelul 4 + conţinutul de memorie pentru toate procesele implicate în lanţuri de aşteptări (starea

NLEAF)

4 – Nivelul 3 + conţinutul de memorie pentru toate nodurile frunză (procesele care blochează) din

lanţurile de aşteptări (starile LEAF, LEAF_NW şi IGN_DMP)

3 – Nivelul 2 + conţinutul de memorie pentru toate procesele ce par a fi agăţate (starea IN_HANG)

1-2 – Doar rezultatul HANGANALYZE, nici un continut de memorie pentru nici un proces.

Se recomandă a nu se utiliza nivel mai mare de 3 datorită numarului potenţial mare de fişiere de trace

care se pot produce (şi ar putea copleşi sistemul de intrări/ieşiri). Deoarece HANGANALYZE se va utiliza

în principal pentru a diagnostica “adevaratele” agaţări, nivelul 3 va scrie conţinutul memoriei doar pentru

procesele care sunt implicate în condiţia de agăţare – de obicei mai putin de 4 procese.

Notă: În unele cazuri, HANGANALYZE poate părea a fi agăţat. HANGANALYZE va afişa

rezultate doar când sunt scrise toate conţinuturile de memorie ale proceselor implicate.

Pentru a verifica dacă HANGANALYZE rulează pe medii ne-clusterizate, se va monitoriza

dimensiunea fişierelor de trace din user_dump_destination. dacă fişierele cresc în

dimensiune, HANGANALYZE nu este agăţat. Mediile RAC nu fac obiectul acestui studiu.

2. Despre SystemState

41 Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1],


50


Starea unui sistem este un set de stări ale proceselor instentei pentru care se scrie conţinutul memoriei

(dump). Un conţinut de memorie al stării unui sistem este util pentru determinarea interacţiunilor dintre

procese. Acest dump va raporta ce resurse sunt reţinute de către fiecare proces în parte.

Sintaxa SYSTEMSTATE

ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level <nivel>';

ORADEBUG dump systemstate <nivel>

ORADEBUG -g all dump systemstate <nivel>

Nivelul stabileşte cantitatea de informaţie adiţională ce va fi extrasă din procesele identificate de

SYSTEMSTATE bazată pe starea nodului.

Nivele sunt:

1 - doar informaţii de bază despre procesele implicate

2 - obiectele de stare ale procesului şi ale sesiunii

10 - cel mai uzual nivel - include structurile arborescente ale obiectelor de stare pentru toate procesele.

256 - adăugând 256 la nivelul ales, se va încerca să se adauge în dump şi stiva de informaţii pentru

fiecare proces.

Obţinerea conţinutului de memorie (fişiere dump) pentru HANGANALYZE şi

SYSTEMSTATE folosind diverse utilitare

1. LTOM – Transient Hang Conditions

LTOM – Lite Onboard Monitor, este un program java, dezvoltat ca o platformă de diagnosticare în timp

real. Diferenţierea acestui utilitar de altele de acest gen, constă în faptul că funtionarea să este mai mult

pro-activă decât reactivă. LTOM asigură detectarea problemelor în timp real şi colectarea de date, fiind

foarte util în cazul problemelor neprevăzute .42

Una dintre trăsăturile utilitarului LTOM este detectarea automată a agaţărilor (Automatic Hang

Detection). Totuşi, în sistemele din producţie, nu este recomandată activarea acestei trăsături ca practică

uzuală. Se va utiliza doar dacă se suspectează situaţii de hang în bază de date. Volumul înregistrărilor

colectate pentru problemele de hang ar putea afecta negativ sistemul şi ar trebui monitorizate.

Colectarea standard cuprinde:

HangAnalyze Level 3

Systemstate Level 266

42 LTOM - The On-Board Monitor User Guide [ID 352363.1],


51


Wait 60 seconds

HangAnalyze Level 3

Systemstate Level 266

Pentru mediile non-clusterizate se va verifica dacă fişierele de trace sunt scrise în

user_dump_destination.

2. HANGFG – Simplified Data Collection43

HANGFG (Hang File Generator) - este o serie de scripturi shell utilizate pentru automatizarea procesului

de generare şi colectare a fişierelor cu înregistrări hanganalyze şi systemstate. HANGFG generează şi

colectează fişiere cu înregistrări ale agăţărilor pe baza impactului obţinerii de înregistrări pentru

diagnosticare într-un sistem cu o stare deja degradată. La ce nivel se va realiza colectarea este lăsat la

alegerea utilizatorului. Nivelul poate fi furnizat utilitarului ca argument. Utilitarul poate şi să ia el această

decizie , dacă utilizatorul alege impact uşor (light impact) - opţiunea 1 ca argument.

Colectarea standard cuprinde:

2 HangAnalyze de Level 3

1 Systemstate de Level 10

Pentru mediile non-clusterizate se va verifica dacă fişierele de trace sunt scrise în user_dump_destination

Paşi pentru colectarea manuală a continutului de memorie pentru HangAnalyze şi

SystemState44

Modul ideal de colectare a vizualizărilor hanganalyze şi systemstate este de a colecta 2 seturi. Totuşi, cel

mai bine ar fi să se deţină 2 vizualizări (dump) hanganalyze într-un singur fişier şi 2 vizualizări (dump)

systemstate în 2 fişiere de înregistrare separate. Pentru aceasta vom avea nevoie de 3 sesiuni sqlplus

separate (SQL1, SQL2 şi SQL3).

A. export ORACLE_SID=PROD ## Se va inlocui PROD cu SID-ul ce se doreşte a fi inregistrat

B. Folosind SQL*Plus ne vom conecta ca SYSDBA folosind comandă:

Se va executa de 3 ori, în 3 ferestre diferite, creâind 3 sesiuni de sqlplus (SQL1, SQL2, şi SQL3)

C. În SQL1 se vor executa următoarele:

43HANGFG User Guide [ID 362094.1],


44 Steps to generate HANGANALYZE trace files [ID 175006.1],


52



D. În SQL2 se vor executa următoarele:

E. Se va aştepta min 2 minute pentru a acorda timp pentru identificarea modificărilor stării procesului

F. În SQL1 se vor execut următoarele:

G. În SQL3 se vor executa următoarele:

Nota: Se va verifica dacă 3 fişiere de trace noi au fost create în user_dump_destination.

Nota: Se va verifica dacă MAX_DUMP_FILE_SIZE are o valoare foarte mare, în caz contrar fişierele de

trace ar putea fi trunchiate, deasemena, dacă DUMP_DEST are referinta către o partitie cu spatiu liber

suficient.

Colectare de date din viziunile (view) V$

Exemple de interogări de rulat când o baza de date este agatata:

SPOOL v_views.log;

set linesize 130

col "Parameter" form a50

col "Session Value" form a30

col "Instance Value" form a30

select a.ksppinm "Parameter",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value"

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

order by 1;

SELECT class, value, name

53

FROM v$sysstat;

SELECT sid, id1, id2, type, lmode, request

FROM v$lock;

SELECT l.latch#, n.name, h.pid, l.gets, l.misses, l.immediate_gets, l.immediate_misses, l.sleeps

FROM v$latchname n, v$latchholder h, v$latch l

WHERE l.latch# = n.latch#

AND l.addr = h.laddr(+);

SELECT *

FROM v$session_wait

ORDER BY sid;

/* se va repeta ultima interogarede 3 ori pentru a vedea ce procese aşteaptă în mod repetat */

SPOOL OFF;

Exemplu de script comple, vezi Anexa 1 .


Verificarea utilizarii resurselor SO

Acest pas va verifica dacă:

Exista destul procesor şi memorie disponibile pentru procesele Oracle, în caz contrar dacă macar Oracle

foloseşte aceste resurse şi este necesara o analiza mai detaliata a bazei de date

Sau, dacă procese non-Oracle folosesc majoritatea resurselro procesorului şi ale memorie.

Verificarea consumului procesorului

Se va vedea Verificarea consumului procesorului.

Verificarea consumului de memorie

Se va vedea Verificarea consumului de memorie .

b. Verificarea ca baza de date

O baza de date poate parea ca este “agatata” (hung), dar în realitate poate doar functiona lent. În acest

caz, se vor aplica modelele de tuning corespunzatoare. În alte cazuri, baza de date pare agatata, când în

realitate doar câteva sesiuni sunt în aceasta situatie.

54

Agatare “reală”

O agăţare “reală” într-o baza de date Oracle poate fi definita ca blocare definitiva, o imbratisare “mortala”

denumita în documentatiile de specialitate “deadlock”. Aceasta este o dependenta ciclica intre doua sau

mai multe procese. Cand avem deaface cu blocari DML (de ex. TM), Oracle este capabil sa detecteze

aceasta dependenta şi sa aduca la starea iniţială (rollback) unul dintre procese, pentru a intrerupe

condiţia ciclica. Pe de alta parte, când aceasta situatie apare la nivelul resurselor kernelului (latche-uri

sau pin-uri), Oracle nu le poate detecta şi rezolva automat.

1. Compararea rezulatelor HangAnalyze

Dacă hangAnalyze a fost colectat asa cum s-a precizat anterior, ar trebui sa existe 2 rezultate intr-un

singur fisier de trace.

În fisierul de trace hanganalyze, secţiunea “CYCLE” raportează dependentele proceselor intre sesiunile

ce sunt într-o conditie de deadlock. Aceste dependente ciclice sunt considerate adevaratele “agaţări” ale

bazei de date.

Exemplu:

Dacă rezultatul colectat prezinta o sectiune “CYCLE”, baza de date este “agatata”.

Se va retine SID-ul şi OSPID-ul procesului ce blochează şi a celui care aşteaptă pentru a putea

determina cauza acestei probleme.

Dacă rezultatul nu contine o sectiune “CYCLE”, se va investiga posibilitatea existentei unor sesiuni

blocate.

2. Date aditionale

Date aditionale sunt colectate în mod automat de comanda hanganalyze dacă o agăţare “reală” a fost

detectata. Vor fi colectate continuturile de memorie pentru systemstate şi processtate pentru sesiunea ce

blochează şi processtate pentru cea care aşteaptă.

Exemplu:

55

Se va retine SID-ul şi OSPID-ul procesului ce blochează şi a celui care aşteaptă pentru a putea

determina cauza acestei probleme.

Sesiuni intepenite sau blocate45

Oracle foloseşte cozile de aşteptare ca mecanism de blocare pentru administrarea accesului la resursele

partajate. O resursa partajata poate fi definitia unei tabele, o tranzactie sau orice tip de structura ce

reprezinta ceva partajat intre sesiuni. Fiecare tip de actiune intreprinsa de către sesiunile Oracle va

necesita un tip diferit de blocare (ex. “select from <tabel>” va necesita ca sesiunea ce o executa sa aiba o

blocare partajata pe definitia tabelei selectate). Cand apar actiuni conflictuale, Oracle va serializa

procesarea prin punerea unor sesiuni în mod aşteptare pana când actiunile ce blochează resursa din

sesiunea curenta ca fi terminata.

1. Compararea rezulatelor HangAnalyze

Dacă hangAnalyze a fost colectat asa cum s-a precizat anterior, ar trebui sa existe 2 rezultate intr-un

singur fisier de trace.

În fisierul de trace hanganalyze, secţiunea “OPEN CHAIN” raportează sesiunile implicate în lantul de

aşteptare. Un lant de aşteptare inseamna ca o sesiune bloceaza una sau mai multe alte sesiuni. “Open

chain” reprezinta siseiunile intepenite sau blocate.

Exemplu:

Hanganalyze #1

Open chains found:

Chain 1 : <sid/sess_srno/proc_ptr/ospid/wait_event> :

<16/44773/0x265f15c/1948/SQL*Net message from client> <--sid 16, ospid 1948:blochează

-- <12/5/0x265fad4/2112/enqueue> <--sid 12, ospid 2112: aşteaptă

-- <13/14/0x265fdfc/2076/enqueue> <--sid 13, ospid 2076:aşteaptă

Chain 2 : <sid/sess_srno/proc_ptr/ospid/wait_event> : <19/3/0x2660124/2392/No Wait>

Hanganalyze #2

Open chains found:


<16/44773/0x265f15c/1948/SQL*Net message from client> <--sid 16, ospid 1948: blochează


45 Anexa 1

56

-- <13/14/0x265fdfc/2076/enqueue> <--sid 13, ospid 2076: aşteaptă

Chain 2 : <sid/sess_srno/proc_ptr/ospid/wait_event> : <19/3/0x2660124/2392/No Wait>

Hanganalyze #3

Open chains found:


<16/44773/0x265f15c/1948/SQL*Net message from client> <--sid 16, ospid 1948: aşteaptă


-- <13/14/0x265fdfc/2076/enqueue> <--sid 13, ospid 2076: aşteaptă

-- <19/3/0x2660124/2392/enqueue> <--sid 19, ospid 2392: aşteaptă

La orice moment, există mari sanse sa existe procese (denumite generic procese) ce blochează sau

aşteaptă, dacă se executa ceva în baza de date. Cheia este sa se determine dacă aceste procese sunut

intepenite pentru o perioada de timp inacceptabila.

Se vor compara lanţurile de aşteptare din sectiunile “OPEN CHAIN” din rapoartele generate anterior.

dacă există un numar mare de lanţuri în secţiunea “OPNE CHAIN”, ar trebui sa ne conectram, pentru

inceput, pe primele 3 sau 4 lanţuri.

O baza de date este într-o stare de blocare care ambele conditii sunt adevarate:

A. Unul sau mai multe lanţuri din secţiunea “OPEN CHAIN” au mai mult decât o sesiune. Sunt

necesari cel putin un agent de blocare şi unul de aşteptare.

Exemplu: “Chain 1” din toate cele 3 exemple de hanganalyze intrunsesc acest criteriu.

B. Sesiunea ce blochează ramane acceasi în toate rapoartele hanganalyze în unul sau mai multe

lanţuri ce indeplinesc condiţia A. Un nou proces de aşteptare poate sa se adauge celor deja

existente, observabil în rapoarte hanganalyze ulterioare, în continuare lantul va fi luat în

considerat ca avand o problema de aşteptare.

Exemplu: “Chain 1” din cele 3 exemple de hanganalyze intrunsesc acest criteriu. Sesiunea ce blochează

din “Chain 1” din Hanganalyze #1 şi Hanganalyze #2 nu se schimba. În “Chain 1”, sid 16 este agentul de

blocare şi sid 12, 13 şi 19 aşteaptă. Lantul este acelaşi în toate rapoartele hanganalyze, cu exceptia lui

sid 19. Sid 19 a fost adaugat în lant în Hanganalyze #3.

În continuare, se va retine SID-ul şi OSPID-ul procesului ce blochează şi a celui care aşteaptă pentru a

putea determina cauza acestei probleme.

2. Date aditionale

57

Se vor colecta continuturile de memorie cu stiva de erori pentru sesiunile din secţiunea “OPEN CHAIN”.

Se va utiliza ospis pentru colectarea continutului de memorie al stivei de erori (ErrorStack) după cum

urmeaza:

Open chains found:


<16/44773/0x265f15c/1948/SQL*Net message from client> <--sid 16, ospid 1948:blochează


-- <13/14/0x265fdfc/2076/enqueue>

-- <19/3/0x2660124/2392/enqueue>

Inaintea colectarii stivei de erori, trebuie sa determina asupa carui lant ne vom concentra. Primele 3-4

cele mai lungi lanţuri din sectiune “OPEN_CHAIN” sunt cei mai buni candidati.

Se va genera stiva de erori pentru sesiunea ce blochează şi prima care aşteaptă pentru fiecare lant. În

exemplul prezentat, ospid 1948 blochează şi ospid 2112 este primul care aşteaptă. Stivele vor fi deci

generate pentru 1948 şi 2112.

Dacă un proces blochează multe alte sesiuni (mai mult de 10), în fisierul de trace hanganalyzer apare o

sectiune “FOUND” inaintea sectiunii “OPEN_CHAIN”. dacă aceasta sectiune apare în raport, se va

compara fiecare sesiune din rapoartele hanganalyze. dacă un proces ce blochează ramane în secţiunea

“FOUND” în toate rapoartele, se va genera stiva de erori corespunzatoare lui folosind ospid.

Found 34 objects waiting for <sid/sess_srno/proc_ptr/ospid/wait_event>

<131/754/0x9fc1e8/576293/No Wait>


<245/2343/0xa19f48/575938/latch free>


<144/2183/0xa0c9b8/575457/latch free>

Pasii pentru colectarea stivei de erori:

Oracle 10.1+

A. Identificarea SID-ului pe care dorim sa il urmarim:

B. Utilizand SQL*Plus ne vom conecta ca “/AS SYSDBA” folosind urmatoarea comanda:

C. Folosind ospid-ul vom executa următoarele comenzi:

O data pentru sesiunea ce blochează pentru fiecare lant din secţiunea “OPEN CHAIN” pe care

am decis sa il urmarim

O data pentru prima sesiune ce aşteaptă pentru fiecare lant din secţiunea “OPEN CHAIN” pe

care am decis sa il urmarim

58

O data pentru fiecare proces ce blockeaza din secţiunea “FOUND” care ramane constant în

toate rapoartele

Mai multe fişiere de trace vor fi generate în user_dump_destination. Va există cate un fisier de trace

pentru fiecare sesiune pentru care s-a generat o stiva de erori.

Baza de date lenta

O problema grava de performanta poate fi interpetata gresit ca fiind o agăţare. Acest fapt se întâmpla, de

obicei, când disputa pentru resurse este atat de “inversunata”, în cât baza de date este complet blocata.

Exemplu:

Hanganalyze #1

Open chains found:


<13/23/0x2660124/1776/No Wait>


<16/8/0x265f7ac/1888/SQL*Net message from client>

-- <15/20/0x265fad4/296/enqueue>

-- <12/5/0x265fdfc/1804/enqueue>

Hanganalyze #2

Open chains found:


<18/3839/0x265ee34/2004/SQL*Net message from client>

-- <19/23/0x265f15c/1876/enqueue>


<12/5/0x265fdfc/1804/No Wait>

O baza de date este lenta dacă nu există lanţuri de blocari în secţiunea “OPEN CHAIN”. Un lant nu este

blocat dacă sesiunea ce blockeaza se schimba de la un raport hanganalyze la altul.

59


În acest moment ar trebui sa avem verificata o problema de blocare sau agăţare a bazei de date.

Procesul de determinare a cauzei are doua etape:

Colectarea de date – de obicei se folosesc datele colectate în etapa de verificare a problemei.

Analiza

În acest moment ar trebui sa avem:

Un nou fisier de trace ce a fost scris în user_dump_destination continand 2 viziuni hanganalyze şi una

pentru processtate.

Unul sau mai multe fişiere de trace ce sa contina stiva de erori

v_views.log

Continuand analiza se va determina:

care sunt evenimentele implicate care aşteaptă

care sunt interogarile SQL active implicate în sesiunile ce blochează şi cele ce aşteaptă.

Care sunt cauzele comune pentru sesiunile intepenite

Care sunt solutiile posibile pentru cauza cea mai probabila


Corectitudinea soluţiei implementate prin verificarea acesteia.

Colectarea de date suplimentare

Agatarea “reală” a bazei de date

Pentru a continua depanarea unei agaţări “reale” a bazei de date, se vor examina mai indeaproape

fişierele de trace rezultate în urma hanganalyze, systemstate şi errorstack..

60

Sesiuni intepenite sau blocate

Utilizarea OEM

Depanarea sesiunilor intepenite se poate face utilizand câteva dintre caracteristicile OEM. Sesiunile

intepenite trebuie sa fie încă în aceasta stare pana când depanarea s’a finalizat sau imagini ale acestora

sa fie salvate pentru referinte ulterioare.

Se va folosi pagina Instance Locks pentru:

Vizionarea tuturor blocarilor

Terminarea sesiunii

Acesarea paginii cu detaliile sesiunii

Acesarea paginii cu detaliile interogarii

Vizionarea detaliilor obictelor

Pagina Blocking Sessions arata lista tuturor sesiunilor ce în mod curent blochează alte sesiuni.

Utilizarea scriptului utllock.sql46 sau utllockt.sql47

Acest script afiseaza, într-o structura arborescenta, sesiunile din sistem care aşteaptă după blocari, şi

după ce blocare anume aşteaptă fiecare. Locatia acestui script difera în funcţie de sistemul de operare

utilizat.

Utllock.sql va afisa rezultatele pe ecran.

Exemplu:

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2

---------------- ---------- -------------- ---------- --------- --------

blochează-> 100 None

aşteaptă ---> 118 DML Exclusive Exclusive 51148 0

aşteaptă ---> 125 DML Row-X (SX) Exclusive 51148 0

O data identificate procesele, se vor rula interogarile asupra viziunilor V$ prezentate anterior.

46 Resolving locking issues using utllockt.sql script [ID 166534.1],


47 Anexa 2

61


Analiza

Datele coectate anterior vor fi analyzate în aceasta etapa pentru determinarea cauzei problemei de

agăţare sau blocare.

Pana în acest punct ar trebui sa se cunoasca dacă se depaneaza o agăţare “reală” a bazei de date sau o

sesiune intepenita sau blocata.

Identificarea datelor despre agentii de blocare şi cei care aşteaptă

Datele ce trebuie identificate pentru a ne ajuta sa analizam aceste tipuri de probleme de performanta

sunt:

OSPID – Identificatorul procesului în cadrul sistemului de operare este necesar pentru a elimina

(kill session) sesiunile de indata ce depanarea este finalizata.

Interogarile active – reprezinta interogarile curente, ce rulează la momentul depanarii. Acestea

vor fi utilizate pentru a identifica ce facea sesiunea care blochează şi cea care aşteaptă la

momentul aparitiei problemei.

Evenimentele de aşteptare – este resursa sau evenimentul pe care il aşteaptă sesiunea. Acesta

va fi utilizat pentru a restrange problema de agăţare sau blocare la un subtip specific.

Nu sunt necesare toate aceste date pentry fiecare tip de problema. Inainte de a utiliza oricare din

metodele prezentate mai jos, trebuie sa identificam sesiunile care blochează şi cele care aşteaptă.

Acestea ar trebui deja stiute din etapa Verificarea problemei.

Identificarea datelor despre agentii de blocare şi cei care aşteaptă utilizand

ErrorStack

Aceste date vor fi utile în determinarea subtipului de problemele de blocaj şi agăţare. Informatiile

suplimentare necesitate vor depinde de tipul evenimentului de aşteptare.

Se pot utiliza pasii urmatori dacă a fost colectat un ErrorStack pentru sesiunea care blochează şi cel putin

unul pentru una care aşteaptă, sesiuni identificate la Date aditionale.

Un dump Errorstack contine o stiva de apeluri şi dump al starii proceselor. În acest pas vom analiza

informatiile despre starea proceselor. Aceste informaţii sunt impartite în sesctiuni numite obiecte de stare

62

(state objects) ce sunt identificate prin “SO”. Tipul obiectului de stare se afla la inceputul celei de’a 2 linii

din starea obiectului, sub “SO”.

Vom examina mai în detaliu câteva dintre aceste obiecte de stare.

1. OSPID

OSPID poate fi gasit în obiectul stare al “procesului”. Acest identificator va fi necesar la terminarea

sesiunilor la finalizarea depanarii.

(process) arata ca obiectul de stare este de tip process

ospid: 25765 arata ca OSPID este 25765

Exemplu:

2. Evenimentul de aşteptare

Evenimentul de aşteptare poate fi gasit în obiectul de stare “sesiunie”. Alte informaţii utile ce se afla în

aceasta sectiune sunt SID-ul şi adresa SQL.

În exemplul de mai jos, cu albastru s-au evidentiat datele de identificat:

(session) arata ca obiectul de stare este de tip sesiune

sid: 118 arata ca Sid-ul sesiunii este 118. Acesta este specificat pentru a

fi asigurati ca urmarim sesiunea care trebuie.

sql: 0x3076f3f8 arata ca adresa pt SQL a acestei sesiuni este 3076f3f8. aceasta

va fi folosita pentru a identifica Interogarile active în pasul urmator.

waiting for 'enq: TM - contention' arata ca evenimentul de aşteptare pentru aceasta sesiune este

enq: TM – contention. Aceasta informaţie se va folosi când vom determina ce tip de blocare este asociata

cu situatia curenta ce este depanata.

63

name|mode=544d0006 arata valoarea pentru P1 dacă evenimentul de aşteptare este o

coada de aşteptare. P1 este o valoare hexa-zecimala ce reprezinta tipul şi modul de blocare (Imode).48

Exemplu:

3. Interogarile active

Interogarile active se gasesc în obiectul de stare “Library Object Lock”. Interogarile active sunt utile

pentru a iidentifica obiectul implicat în agăţare sau blocare.

Cand examinăm ce interogări sunt active trebuie sa descoperim:

Ce obiecte sunt implicate

Ce tip au aceste obiecte

Sunt comenzi DDL sau DML?

Au aceste obiecte dependente şi care sunt acelea

dacă sunt implicate declansatoare (trigger)

dacă, comanda face parte dintr-un pachet sau procedura

Ce parte a aplicatiei este implicata

În exemplul de mai jos, cu albastru s-au evidentiat datele de identificat.

Pentru a identifica obiectul de stare “Library Object Lock” corect ce contine interogarile active, trebuie sa

gasim adresa SQL. Aceasta a fost identificată în exemplul anterior, "3076f3f8".

Odata ce a fost identificat “Library Object Handle” cu aceasta adresa, linia ce incepe cu “name=”

reprezinta interogarea activă.

LIBRARY OBJECT LOCK arata ca obiectul de stare este Library Object Lock

LIBRARY OBJECT HANDLE:

handle=3076f3f8 arata ca adresa Libray Object Handle este 3076f3f8. Aceasta

adresa este la fel cu cea identificată în obiectul de stare “sesiune”. Aceasta se foloseşte pentru a verifica

ca acesta este obiectul de stare ce contine interogarea activă.

48 WAITEVENT: "enqueue" Reference Note (Doc ID 34566.1),

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=34566.1 , accesat la data 25.01.2010

VIEW: "V$LOCK" Reference Note [ID 29787.1],

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=29787.1

64



name=lock table emp in

exclusive mode arata ca interogarea activă este o blocare pe tabela emp in mod

exclusiv.

Exemplu:

Exemplu: Vezi Anexa 3 – script ce arata blocarile DML, inclusiv numele tabelei.

4. Coada de aşteptare (callstack)

Inregistrarile cozii de aşteptare pot fi gasite în rapoartele errorstack sau processstate.

Coada de aşteptare poate fi utila în efectuarea de cautari pentru probleme similare.

Exemplu:

65


interogarea viziunilor V$


Evenimentul de aşteptare poate fi identificat din raportul v$lock.log. Vom cauta datele generate de

v$session_wait_querry.

Exemplu:

2. Interogarile active

Exemplu de date generate de v$lock_querry:

66


OEM (10gR2)

Indiferent de facilităţile utilizate în OEM pentru a viziona sesiunile blocate, informaţiile cele mai valoroase

pentru depanarea sesiunii inţepenite se vor găsi pe pagina “Session Detail” pentru sesiunea factorului de

blocare şi pentru sesiunea factorului de aşteptare.

1. OSPID

OSPID poate fi găsit în secţiunea “Client” de pe pagina “Session Detail”.


Poate fi găsit în secţiunea “Application” din pagina “Session Detail”. Current SQL reprezintă interogarea

activă. Din pagina “Session Detail”, selectarea “SQL Hash Value” ne va direcţiona către pagina “SQL

Details”.

67

Sectiunea “Text” din pagina “SQL Details” contine interogarea activă:

Faza 3 – Alegerea strategiei de tuning

În cele ce urmeaza voi prezenta cele mai des intalnite cauze asociate cu agatarea bazei de date.

Baza de date agatata – evenimente de aşteptare

1. library cache pin

Library cache pin sunt utilizate pentru a administra concurenta library cache-ului.

Fixarea unui obiect cauzează încărcarea heap-urilor în memorie (dacă nu sunt încărcate deja). Fixările

pot fi în mod NULL, SHARE sau EXCLUSIVE şi pot fi considerate o formă specială de blocare/rezervare.

O aşteptare pentru un “library cache pin” implica faptul că alte sesiuni reţin acel PIN într-un mod

incompatibil.

Model de tuning:

Cauza identificată: O sesiune este blocată din cazuza ununi deadlock ce aşteaptă un library cache pin


1. Interogam v$session_wait sa aflam sesiunile ce aşteaptă library cach pin:

select sid,substr(event,1,30),p1,p2 from v$session_wait where wait_time=

0 and event like 'library cache pin%';

SID SUBSTR(EVENT,1,25) P1 P2 P3

---------- ------------------------- ---------- ---------- --------------

9 library cache pin 15417016 10090832 20

68







P1= library chache handle address

P2=library cache pin address

2. Convertim P1(15417016) to HEX --> EB3EB8 şi o cautam în X$KGLPN (Library Cache Object Pin)

select ADDR , INDX , KGLPNADR,KGLPNUSE,KGLPNSES,KGLPNHDL,kGLPNLCK

KGLPNMOD, KGLPNREQ from x$kglpn where KGLPNHDL like '%EB3EB8%';

ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD

KGLPNREQ

-------- ---------- -------- -------- -------- -------- -------- ------

05B0CB30 1 00FB9D40 0011B830 0011B830 00EB3EB8 003ECD10 0 2

05B0CB88 2 00AB4468 000E56E0 000E56E0 00EB3EB8 004473A0 0 2

05B0CBE0 3 005E1848 000E7180 000E7180 00EB3EB8 00A30F60 0 2

05B0CC38 4 00B6FDD8 00126E20 00126E20 00EB3EB8 00DD6738 0 2

05B0CC90 5 0099F950 000BD370 000BD370 00EB3EB8 00E07F78 0 2

05B0CCE8 6 00AEB650 001194B0 001194B0 00EB3EB8 00DB6BC8 0 2

05B0CD40 7 00A5D818 000C0440 000C0440 00EB3EB8 0043A1A8 0 2

05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0

KGLPNHDL: Library Cache Handle Address

KGLPNADR: Library Cache Pin Address.

KGLPNSES: Identifica sesiunea care blochează acest pin the session that locks

this pin

KGLPNREQ: Pin Request

Soluţia identificată Cautam în X$KGLOB (Library Cache Object)

select * from X$KGLOB where KGLHDADR like '%EB3EB8%';

select count(*) from fa_facturas@decmovi where fa_cuenta -> KGLNAOBJ

= :1

ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ

---- ----- -------- -------- -------- -------- -------- ------ --------

69

05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0

Vom cauta în V$SESSION.SADDR după KGLPNUSE pentru a determina SID-ul sesiunii: 263.

În urma verificarii sesiunea corespunde unui mesaj Client.

Efort - mediu..

Risc - scăzut




trebuie sa examinăm următoarele:

- sa luăm în considerare alte posibile cauze

- sa verificăm dacă datele au fost colectate corect



acestei probleme.

70

2. Blocare library cache-ului

Rezervarea library cache-ului controleaza concurenta clientilor library cache-ului prin asigurarea

rezervarii obiectului pentru ca un client sa impiedice pe altii sa acceseze acelaşi obiect, sau clientul poate

mentine o dependenta pentru un timp indelungat (nici un alt client nu poate modifica obiectul).

3. Blocarea row cache-ului

Acest eveniment este utilizat pentru asteptarea unei blocari pe data dictionary cache, specificat prin

“cache id.

Sesiuni blocate - evenimente de aşteptare

1. Cozile de aşteptare

Cozile de aşteptare sunt rezervari locale, ce serializeaza accesul la resurse variate. Acest eveniment de

aşteptare indica asteptarea pentru o rezervare ce este retinuta de alta sesiune (sau sesiuni) intr-un mod

incompatibil fără ce modul cerut.

2. Blocaj al library cache load-ului

Acest eveniment este utilizat când un obiect din baza de date este incarcat. Rezervarea incarcarii este

obtinuta intotdeauna în mod Exclusiv, în asa fel incat nici un alt proces sa nu poata incarca obiectul în

acelaşi timp. dacă rezervarea incarcarii obiectului este ocupata, sesiunea va astepta acest eveniment,

pana când rezervarea devine disponibilă.

71

Capitolul 4 - Optimizarea interogarilor


A. Generalităţi

Pentru o identificare corectă a problemei ce se doreşte a fi rezolvată, sunt necesare trei lucuri:

Să recunoaştem o problemă de optimizare a interogărilor

Să clarificăm detaliile acestei probleme

Să verificăm dacă problema raportată este cea reală.

a. Recunosterea unei probleme de performanta a interogarilor

Ce este o problema de optimizare a unei interogări?

O problemă de optimizare într-un query se poate manifesta astfel:

anumită fraza SQL sau grup de fraze care rulează lent într-un moment în care alte fraze rulează

în mod optim.

Una sau mai multe sesiuni rulează lent şi mare parte din latenta se produce din cauza execuţiei

unei anumite fraze SQL .

Identificării unor astfel de probleme se poate face din:

Benchmark/testare

Plângerile utilizatorilor

statspack sau rapoarte AWR care dezvăluie fraze SQL ce au cost ridicat

interogare ce pare să se fi blocată

sesiuni ce consumă mult din capacitatea procesorului.

Aceste probleme pot apărea după:

Modificări ale schemei

Modificări ale statisticilor

Modificări ale volumului de date

Modoficari ale aplicaţiei

Upgrade-ul bazei de date.


72


poate întâmpla ca în faze ulterioare, sau în timp ce se lucrează la problema, adevărata problemă să iasă

la iveală, şi să fie necesar o redefinire a acesteia.


Interogările afectate

Secvenţa de evenimente ce au condus la apariţia problemei

Unde/cum a fost observată


Ce funcţionează

Care este rezultatil acceptat sau aşteptat

Ce s-a făcut pentru a se încerca rezolvarea problemei

Exemplu:

frază SQL are performante slabe după re-colectarea de statistici

A fost observată de utlizatorii finali.

Face ca aplicaţia să ruleze lent şi împiedica sistemul nostru să preia comenzi

În rest totul este în regulă şi problema nu s-a manifestat în mediul nostru de testare.

În mod normal propoziţia se execută în mai puţin de 2 secunde.

Am încercat să re-colectăm statistici, dar fără niciun efect

c. Verificarea problemei

În acest pas al diagnosticării problemei, obiectivul este de a ne asigura că interogarea despre care se

crede că are nevoie de optimizare este cu adevărat cauza problemei. În acest punct trebuie să

colectatam datele care verifica existenţa problemei.

Pentru a verifica existenţa problemei, trebuie să avem:

Fraza SQL

Dovezi ale performantelor slabe ale interogării

Exemplu:

Următoarea interogare este lentă:

SELECT order_id

FROM po_orders

WHERE branch_id = 6

73

Informaţia despre timpii interogării a fost colectata folosind SQLPlus astfel:

SQL> set timing on

SQL> SELECT order_id

FROM po_orders

WHERE branch_id = 6

ORDER_ID

----------------

232

Elapsed: 00:00:13.26

Consideraţii speciale

Dace se foloseşte Oracle 10g sau versiuni ulterioare, şi se poseda licenţa pentru tuning Pack al

Enterprise Manager-ului, cea mai bună abordare ar fi ca depanarea acestui tip de probleme să fie lăsat în

seama SQL Tuning Advisor-ului.49

Optimizarea interogărilor în bazele de date şi în aplicaţii este un proces interactiv ce necesită o înţelegere

completă a mediului unde rezida baza de date (baza de date, sistemul de operare, aplicaţia, firewall,

etc.).


În aceasta etapa vom aduna date ce ne vor ajuta sa verificăm dacă interogarea suspectata este intr-

adevar cea care va trebui optimizata. Ar trebui sa fim pregatiti sa identificam pasii specifici din aplicaţie ce

au cauzat execuţia lenta a interogarii. Vom urmari sesiunea bazei de date, în timp ce aplicatia executa

interogarea.

Obtinerea unui Extended SQL Trace50

Fisierul de trace Extended SQL (10046 trace la nivelul 12) va surprinde statisticile executiei tuturor

interogarilor sesiunii rulate în timpul inregistrarii. Ne va arata cât timp este alocat fiecarei interogări, cât

timp este datorat prcesorului sau evenimentelor de aşteptare, şi legaturile dintre acestia. Va trebui sa

verificăm dacă interogarea sql “candidata” se afla printre acestea.

49 Oracle® Database Performance Tuning Guide, 10g Release 2 (10.2) – Chapter 12 – Automatic SQL tuning,


50Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1], , accesat la data 25.01.2010


74



Alegerea sesiunii

Vom alege sesiunile cele mai importante sau afectate

Utilizatorii care sunt afectaţi cel mai sever de către problema, ex. în mod normal tranzacţia este finalizata

într-o secundă, dar acum durează 30 sec.

Utilizatorii ce acumulează în mod agresiv timp în baza de date.

Următoarele interogări ne ajuta să descoperim sesiunile curente din baza de date ce au acumulat cel mai

mult timp de procesare pentru anumite evenimente de aşteptare. Aceste sesiuni vor fi folosite pentru

identificarea sesiunilor portentiale de urmărit cu ajutorul 10046.

Următoarea interogare filtrează sesiunile pe baza timpului de logare, mai mic de 4 ore, şi ultima accesare

în ultimele 30 de minute. Aceste restricţii sunt pentru a găsi sesiuni curente mai relevante, decât cele

care rulează de mult şi care au acumulat mult CPU time, dar nu au probleme de performanţă. Valorile

prezentate sunt orientative.

Gasirea sesiunilor ce au cel mai mare consum al procesorului

-- sesiunile cu cel mai mare consum al procesorului

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU


AND st.sid = s.sid



AND s.logon_time > (SYSDATE - 240/1440) – sesiuni logate în ultimele 4 ore

ORDER BY st.value;

SID SERIAL# OS PID USERNAME MODULE CPU sec

---------- ---------- ------------ ------------------------------------------------------------ -------------------------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 8.25

147 575 10577 SCOTT SQL*Plus 258.08

131 696 10578 SCOTT SQL*Plus 263.17

139 218 10576 SCOTT SQL*Plus 264.08

133 354 10583 SCOTT SQL*Plus 265.79

135 277 10586 SCOTT SQL*Plus 268.02

75

Gasirea sesiunilor cu cel mai mari timpi de aşteptare

-- sesiunile cu cei mai mari timpi de aşteptare

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'



AND se.sid = s.sid


ORDER BY se.time_waited;

SQL> /

SID SERIAL# OS PID USERNAME MODULE TIME_WAITED

---------- ---------- ------------ ------------------------------------------------------------ -----------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 4

147 575 10577 SCOTT SQL*Plus 45215

131 696 10578 SCOTT SQL*Plus 45529

135 277 10586 SCOTT SQL*Plus 50288

139 218 10576 SCOTT SQL*Plus 51331

133 354 10583 SCOTT SQL*Plus 51428

Gasirea sesiunilor cu cei mai mari timpi ai bazei de date

-- sesiuni cu cu cei mai mari timpi ai bazei de date

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time

(sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU


AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid




AND st.value > 0;

SID SERIAL# OS PID USERNAME MODULE DB Time(sec) CPU Time (sec) % CPU

---------- ---------- ------------ -------- ----------------------------------------------------- -------------- -----------

141 1125 15315 SYS sqlplus@coehq2 (TNS V1-V3) 12.92 9.34 72.29

76

Obtinerea unui trace complet

Ideal ar fi sa putem porni un trace imediat ce utilizatorul s-a logat şi incepe operatilunile sale sau

tranzactiile. Trigger-ul mentionat anterion poate fi folosit în aceste situatii.

Colectarea fisierului trace şi generarea unui raport TkProf

Paşi de urmat:

O sesiune conectată

Se începe urmărirea unei sesiuni conectate

Ne vom coordona cu utilizatorul să înceapă operaţiunea

Vom măsura timpii de răspuns ai clientului pentru operaţia în cauză. Ideea este de a compara

timpul cât îi ia unei funcţii să ruleze în aplicaţie din perspectiva utilizatorului cu cel cât îi ia

respectivei interogări să ruleze în baza de date. dacă aceşti timpi sunt apropiaţi, atunci problema

de performanţă tine de bază de date, în caz contrar este alt tip de problemă.

Se va opri înregistrarea.

Se va colecta fişierul de trace din locaţia user_dump_dest.

Utilizând un script de test

Se va rula scriptul şi se va colecta fişierul de trace din locaţia user_dump_dest.

Se va generara un raport TKProf şi sortarea interogărilor cele mai lungi se face utilizând următoarea

comanda:


Trebuie sa ne asigurăm ca fişierele de trace conţin doar date recente.

Dacă aceasta sesiune a fost urmărită recent, există posibilitatea ca alte trace-uri să fie amestecate în

acelaşi fişier cu date recent colectate.

Trebuie să extragem doar datele care fac parte din testul recent.

Fişier de trace pentru on proces ce rulează mult care a fost urmarit intermitent în ultimele zile

. . .

*** 2009-07-24 13:35:05.642 <== Timestamp de la o urmarire anterioara


=====================


ad='97039a58'

select e.empno, d.deptno<== cursor precedent ce a fost urmarit

77

from emp e, dept d

END OF STMT


BINDS #9:



. . .





. . .

====> ACESTE LINII NU FAC PARTE DIN TEST <====

*** 2006-07-24 18:35:48.850

<== Timestamp oentru trace-urile pe care le urmarim (aprox 5 ore mai tarziu)

=====================


ad='9915de10'

alter session set events '10046 trace name context forever, level 12'

END OF STMT

. . .

=====================


ad='9306bee0'

select e.empno, d.dname<== Cursorul ce a fost urmarit

from emp e, dept d

where e.deptno = d.deptno

END OF STMT


BINDS #3:




. . .






STAT #3 id=1 cnt=14 pid=0 pos=1 obj=0 op='HASH JOIN (cr=15 pr=12 pw=0 time=39402 us)'

=====================


ad='95037918'

alter session set events '10046 trace name context off' <== tracing-ul a fost oprit

END OF STMT

78

Asigurarea ca fiserul de trace este complet

Dacă urmarirea a inceput sau s-a sfarsit în timpul unei apelări, este bine sa regandim procesul ca ascest

lucru sa nu se intample din nou.

*** 2006-07-24 15:00:45.538 <== Timpul când s-a inceput urmărirea aşteptarii


*** 2006-07-24 15:01:16.849 <== 10g va scrie mesaje dacă în trace nu s-a scris nimic de

ceva vreme


. . .

*** 2006-07-24 15:27:46.076





. . .

*** 2006-07-24 15:30:28.536 <== Timestamp final inainte şi după apelarea FETCH



=====================


ad='931230c8'


END OF STMT

FETCH #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1014506207466 <== Terminarea

apelului de FETCH

FETCH raportează 11 microSec.Acest rezultat este gresit după cum se poate observa din

timestamps. Ar trebui sa fie aprox. 30 min.

Asigurarea că fiserul de trace este complet

Dacă urmărirea a început sau s-a sfârşit în timpul unei apelări, este bine să regândim procesul ca ascest

lucru să nu se întâmple din nou.

*** 2006-07-24 15:00:45.538 <== Timpul când s-a început urmărirea aşteptarii


*** 2006-07-24 15:01:16.849 <== 10g va scrie mesaje dacă în trace nu s-a scris nimic de ceva

vreme


. . .

*** 2006-07-24 15:27:46.076





. . .

*** 2006-07-24 15:30:28.536 <== Timestamp final înainte şi după apelarea FETCH

79



=====================

PARSING ÎN CURSOR #3 len=39 dep=0 uid=57 oct=0 lid=57 tim=1014506207489 hv=1173176699

ad='931230c8'


END OF STMT

FETCH #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1014506207466 <== Terminarea apelului de

FETCH

FETCH raportează 11 microSec.Acest rezultat este greşit după cum se poate observa din timestamps.

Ar trebui să fie aprox. 30 min.

Verificarea dacă cel mai mare timp este cel petrecut aşteptând intre apeluri

Aşteptările pentru mesajele SQL*Net de la client între două apeluri (de obicei apeluri FETCH) indica o

problemă de performanţă a clientului (client lent sau care nu utilizează operaţiile cu array-uri) sau a reţelei

(latenta mare, banda de transfer mică, timeouts). Optimizarea interogărilor nu rezolva acest tip de

probleme.

Dovezi ale timpilor de aşteptare dintre apeluri pot fi identificate uitându-ne după:

În TKProf, vom observa că timpul total petrecut în baza de date este mic în comparaţie cu timpul aşteptat

de către client.

Exemplu:

Sesiune TKProf a unei sesiuni unde clientul utilizează un array de 2 şi cauzează multe apeluri FETCH

select empno, ename from emp

call count cpu elapsed disk query current rows

------- ------ ------ -------- ----- ------- -------- ---------------------------------------------------------------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 8 0.00 0.00 0 14 0 14

------- ------ ------ -------- ----- ------- -------- ---------------------------------------------------------------

total 10 0.00 0.00 0 14 0 14

Rows Row Source Operation

------- -------------------------------------------------------------------------------------------

14 TABLE ACCESS FULL EMP (cr=14 pr=0 pw=0 time=377 us)

80

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

--------------------------- Waited ---------- -------------------------------------------------------

SQL*Net message to client 8 0.00 0.00

SQL*Net message from client 8 29.36 78.39

Se observa 8 apeluri fetch pentru a returna 14 înregistrări.

78.39 s aşteptate pentru mesajele SQL*Net de la cleint pentru 8 aşteptări.

Fiecare aşteptare corespunde unui apel FETCH. Timpul total al bazei de date a fost de 377

microsecunde, dar timpul total de a aduce cele 14 înregistrări a fost de 78.39s, datoraţi timpilor de

aşteptare ai clientului.

Dacă se va reduce numărul de apeluri FETCH, se va reduce şi timpul total. În orice caz, baza de date

funcţionează optim, problema fiind externă.

Pentru a confirma că aşteptările se datporeaza unui client lent, se va examina trace-ul 10046 pentru

interogarea SQL în cauza şi vom analiza timpii de aşteptare între apelurile FETCH:

PARSING ÎN CURSOR #2 len=29 dep=0 uid=57 oct=3 lid=57 tim=1016349402066 hv=3058029015 ad='94239ec0'

select empno, ename from emp

END OF STMT




FETCH #2:c=0,e=423,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=1016349403494 <== Apel terminat

WAIT #2: nam='SQL*Net message from client' ela= 1103179 p1=1650815232 p2=1 p3=0 <== aşteptare a clientului


FETCH #2:c=0,e=330,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=4,tim=1016350507608 <== Apel terminat (2 înregistrări)



FETCH #2:c=0,e=321,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=4,tim=1016379876558 <== Apel terminat (2 înregistrări)



. . .



STAT #2 id=1 cnt=14 pid=0 pos=1 obj=49049 op='TABLE ACCESS FULL EMP (cr=14 pr=0 pw=0 time=377 us)'

Între două apeluri FETCH, există aşteptare pentru client. Acesta este lent şi răspunde la 1-2 sec.

81


În acest pas vorm analiza fişierele de trace şi datele TKProf colectate în etapa anterioară pentru a verifca

dacă interogarea suspectată este cu adevărat cea cu probleme.

a. Verificarea problemei utilizând TKProf

TKProf va sumariza rezultatul fişierului de trace SQL pentru a ne arăta cât timp I-a luat fiecărei interogări

SQL saruleze, planul de execuţie la rulare şi timpii de aşteptare asociaţie fiecărei fraze SQL. În acest

mod, putem vedea uşor interogările ce sunt responsabile pentru cei mai mulţi timpi şi ar trebui luate în

considerare pentru optimizare.

dacă observam că interogările prezentate în TOP SQL statements din TKProf sunt aceleaşi pe care le

sustectam şi noi, atunci problema este verificată.

Date necesare pentru verificare:

Rezultatul TKProf din aplicaţie, ce a fost urmărită în etapa anterioară

Măsurarea timpului de rulare a aplicaţiei din punctul de vedere al utilizatorului.

Paşii verificării

Este timpul total prezentat în TKProf timpul de răspuns al aplicaţiei ce este măsurată când aceasta

rulează?

Dacă da, se trece la următoarea întrebare.

Dacă nu:

A fost urmărită altă sesiune?

Exemplu:

Dacă aplicaţia a rulat în 410s, uitându-ne în secţiunea “Overall Totals” de la sfârşitul raportului TKProf

vom observa că timpul total de urmărire a fost de (presupunând că înregistrarea a început înainte ca

aplicaţia să ruleze şi a fost oprită imediat ce rularea s-a finalizat):

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS


------- ------ -------- -------- ----- -------- -------- -------------------------------------------------------------------------

Parse 1165 0.66 2.15 0 45 0 0

82

Execute 2926 1.23 2.92 0 0 0 0

Fetch 2945 117.03 398.23 5548 1699259 16 39654

------- ------ -------- -------- ----- -------- -------- -------------------------------------------------------------------------

total 7036 118.92 403.31 5548 1699304 16 39654

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS


------- ------ -------- -------- ----- -------- -------- ------

Parse 0 0.00 0.00 0 0 0 0

Execute 0 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- -------- ----- -------- -------- -------------------------------------------------------------------------

total 0 0.00 0.00 0 0 0 0

Timpul total al bazei de date, înregistrat în fişierul de trace a fost:

Total Non Recursive Time + Total Recursive Time = 403.31 sec

În acest caz, 403s din 410s, văzute din punctul de vedere al utilizatorului au fost petrecute în baza de

date. Este necesară o optimizare a interogărilor.

Timpul petrecut parsând, executând şi obţinând înregistrări este cel mai mare din totalul timpului

din fişierul de trace?

Dacă da, se trece la următoarea întrebare.

Dacă nu, se vor verifica timpii de aşteptare dintre apeluri ai clientului (SQL*Net messages from Client).

Timpii de aşteptare ai clientului apar între apeluri pentru acelaşi cursor?

dacă da, acest lucru este o nouă problemă ce o completează pe cea iniţial raportată şi se va trece la

pasul următor.

Dacă cel mai mult timp este petrecut aşteptând intre apeluri de la cursoare diferite, problema este la

nivelul clientului sau a reţelei. Optimizarea interogărilor nu varezolva această problemă.

Explicaţii

Scopul optimizării interogărilor este să reducă timpul petrecut de o interogare parsând, executând şi/sau

obţinând date. dacă fişierul de trace arata ca aceste operaţiuni decurg rapid în comparaţie cu timpul total,

trebuie să optimizăm clientul sau reţeaua.

Când baza de date petrece cel mai mult timp aşteptând intre execuţiile cursoarelor, suspectăm că

reţeaua sau clientul au latentă.

83

Pe de lat parte, când cel mai mult din timpul unei interogări este petrcut aşteptând intre apeluri FETCH

ale aceluiaşi cursor, ar trebui să suspectăm un client ce nu utilizează preluări pe loturi (bulk/array).

Este interogarea suspectată prezenta la începutul raportului TKProf?

Dacă da, se va trece la următoarea întrebare.

Dacă nu:

Este interogarea prezentată în TKProf ca cea mai mare consumatoare de timp o procedură

PL/SQL?

o Se va parcurge fişierul de trace până se găseşte prima interogare non-PL?SQL. dacă

aceasta interogare este cea suspectata se trece la pasul următor.

o În caz contrar, definirea problemei trebuie să fie modificată pt a identifica ori procedura

ori prima interogare non-PL/QSL identificată în trace.

A fost urmărită altă sesiune?

A fost urmărită sesiunea în mod corect? (s-a început urmărirea prea târziu sau a fost încheiată

prea devreme)

Interogarea petrece cel mai mult timp în faza de execuţie/fetch (nu în faza de parsare)?

dacă da, verificarea s-a încheiat şi suntem siguri că aceasta este interogarea ce necesită optimizare.

dacă nu este posibilă existenţa unei probleme de parsare. Tehnicile de optimizare obişnuite ce modifică

planul de execuţie al unei interogări, nu vor ajuta în acest caz. Trebuie să modificăm definirea problemei

şi să adăugăm că avem în vedere o îmbunătăţire a timpului de parsare.

Exemplu:

SELECT * FROM ct_dn dn, ds_attrstore store


------- ------ -------- -------- ------ -------- -------- ------------------------------------------------------------------------

Parse 555 100.09 300.83 0 0 0 0

Execute 555 0.42 0.78 0 0 0 0

Fetch 555 14.04 85.03 513 1448514 0 11724

------- ------ -------- -------- ------ -------- -------- ------------------------------------------------------------------------

total 1665 114.55 386.65 513 1448514 0 11724

84


În acest moment am identificat o interogare individuală ce necesită optimizare şi vom încerca să

identificăm din ce cauză aceasta interogare are un plan de execuţie greşit. Pentru a identifica o cauză

specifică, trebuie să colectăm date despre planul de execuţie, statisticel de rulare şi obiectele referentiate

în interogare, după cum urmează:

Verificări de bază

Ne asigurăm că folosim optimizarea corectă.

Statistici actuale sunt colectate pentru toate obiectele.

Setările parametrilor de bază sunt corecte.

Alegerea unei strategii de optimizare

Oracle 10g+ - se va utiliza SQL Tunning Assistant

High Parse Time – rezolvarea timpului mare de parsare

Exista plan de execuţie “bun” şi “rău” – se vor compara cele două planuri de execuţie pentru a

identifica sursa problemei şi se va rezolva.

Există doar un plan de execuţie “rău” – se va analiza acesta şi se va identifica sursa problemei şi

se va repara.

Există doar un plan de execuţie “rau’, este dorită o rezolvare rapidă – se va folosi metoda triajului

pentru a se găsi rapid un plan de execuţie “bun”.

Urmărirea paşilor strategiei de optimizare alese pentru identificarea cauzei şi a potenţialelor soluţii.

Se va alege o soluţie şi se va implementa.

Verificarea ca soluţia implementată a rezolvat problema sau sunt necesare acţiuni ulterioare.

Colectarea de date suplimentare

Obţinerea planului de execuţie al unei interogări (obligatoriu)

Un plan de execuţie corect şi precis este cheia începerii procesului de optimizare. Procesul de obţinere a

unui plan de execuţie variază în funcţie de versiunea bazei de date.

Premise:

Se va crea o tabelă plan utilizând scriptul utlxplan.sql:

85

SQL> @?/rdbms/admin/utlxplan

Oracle 10g+ : acordarea de privilegii.

Pentru a utiliza funcţionalitatea DBMS_XPLAN.DISPLAY_CURSOR, utilizatorul apelant trebuie să aibă

privilegiul SELECT pe v_$SESSION, v_$SQL_PLAN_STATISTICS_ALL şi V_$SQL_PLAN, în caz

contrar un mesaj de eroare corespunzător va fi afişat.

Oracle 10.2.x51

Valoarea actuală a variabilelor constrânse (bând variables) va influenţa planul de execuţie ce va fi

generat este foarte importantă pentru a obţine planul de execuţie actual pentru interogarea ce întâmpina

probleme de performanţă.

Varianta 1 – de preferat

Această abordare utilizează DBMS_XPLAN pentru a colecta planul de execuţie actual (nu cel

EXPLAINed) şi va asigura informaţii utile despre numărul actual şi estimat de înregistrări returnate.

dacă sunt utilizate variabile constrânse, pentru obţinerea planului de execuţie actual se va utiliza o

metodă dintre următoarele:52

dacă este posibil, se va executa interogarea cu hint-ul “gather_plan_statistics” pentru a reţine

statisticile de rulare. Sau, se poate utiliza STATISTICS_LEVEL=ALL pentru sesiunea de excutie.

Exemplu:

Se execută interogarea şi se colectează statisticile:

SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....

Se afişează planul de execuţie cu statisticile planului (pentru ultimul cursor executat):

SQL> set linesize 150

SQL> set pagesize 2000

SQL> select * from TABLE(dbms_xplan.display_cursor('NULL, NULL , 'ALLSTATS

LAST'))

Pentru obţinerea planului pentru ultima interogare executată:



51 Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) – Chapter 132 – DBMS_XPLAIN – Summary

of DBMS_XPLAIN Subprorams

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i996786 , accesat la data 25.01.2010

52 Methods for Obtaining a Formatted Explain Plan [ID 235530.1],


86


http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i996786

SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

dacă interogarea a fost executată, şi ştim valoarea SQL_ID-ului, putem extrage planul din library

cache:



SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL'));

Dacă a fost executat cursorul când se colectau statisticile, atunci se va utiliza ALLSTATS în loc de ALL.

Varianta 2

Se vă va utiliza doar dacă prima variantă nu se poate folosi. Această abordare poate fi utilizată pentru a

colecta planul de execuţie pentru interogări ce nu au variabile constrânse.

Se va genera planul de execuţie

SQL> EXPLAIN PLAN FOR

< interogarea urmărită >

Se va afişa planul de execuţie

SQL> set lines 130

SQL> set head off

SQL> spool

SQL> alter session set cursor_sharing=EXACT;

SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

SQL> spool off

Important: Se va obţine planul de execuţie al interogării şi când rulează optim şi când întâmpina eficiente

de performanţă.

În cazul în care sistemul cu o performanţă bună nu mai este accesibil, pentru a obţine un plan de

execuţie bun, se poate folosi parametrul optimizer_features_enable, pentru a readuce comportamentul

optimizer-ului la cel vechi. Se vor importa statisticile vechi în sistem. Vom verifica dacă modul optimizer-

ului este setat pe cel vechi.

Colectarea de informaţii cuprinzătoare despre interogare

87

SQLTXPLAIN.SQL colectează date comprehensive despre o anumită interogare. Aceste date pot fi

utilizate pentru a examina obiectele din spatele interogării, pentru a vizualiza planul de execuţie a

interogării şi a intege mai profund cauzele deciziilor optimizatorului privind planul de execuţie.

Instalarea SQLTXPLAIN-ului şi crearea unui fişier cu interogarea ce se

doreşte a fi optimizata53

SQLTXPLAIN are nevoie de o schemă în baza de date, acolo unde se execută interogarea pe care dorim

să o optimizăm. Această schemă va fi folosită pentru tabelele de care se foloseşte SQLTXPLAIN.

Instalarea nu trebuie făcută decât o dată.

Informaţii detaliate de instalare se găsesc în intstructions.txt, distribuit cu arhiva zip SQLTXPLAIN.

În mare, vom dezarhiva sqlt.zip într-un director dedicat pe server, vom rula SQL*Plus idn acel director,

conectaţi cu un utilizator cu privilegii de SYSDBA:

e.g. Start SQL*Plus, then:

SQL> connect / aş sysdba

SQL> @sqcreate.sql

Rularea SQLTXPLAIN.SQL pentru interogarea ce necesită optimizare

Această operaţiune va aduna informaţii despre fiecare tabel sau viziune din interogare, incluzând

şi statistice/histograme, coloane şi dependentele viziunilor.

Planul de execuţie şi informaţia predicat obţinută din comandă EXPLAIN PLAN, vor fi colectate

deasemena.

Va fi colectat un fişier de trace CBO (event 10053)

Un raport final va fi generat în format HTML

Exemplu:

sqlplus <usr>/<pwd>

start sqltxplain.sql <nume fişier text ce conţine interogarea>;

sqlplus apps/apps;

start sqltxplain.sql sql5.txt;

53SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1],


88


Colectarea de informaţii istorice despre interogare

SPREPSQL.SQL şi AWRSQLRPT.SQL colectează costurile istorice, timpii extinse, statistice şi planurile

de execuţie ale unei interogări specifice. Aceste informaţii ne pot ajuta să identificăm când un plan de

execuţie s-a modificat şi cum arată un plan de execuţie mai bun.

Notă: o premisă a utilizării SPREPSQL.SQL este să ne asigurăm că snapshot-urile statspack-ului sunt

colectate la nivelul 6 sau mai mare.54

Indentificarea a “hash value” sau a SQL_ID pentru interigarea în cauză.

cale a de află ambii indicatori este să căutăm interogarea în raportul statspack sau AWR în

secţiunea “Top SQL”.

Altă cale ar fi să ne uităm în fişierul trace brut 10046 colectat dintr-o sesiune din timpul verificării

problemei, identificăm interigarea SQL şi căutam linia asociată cu aceasta interogare ce conţine

“hv=”.

Exemplu:


ad='9d3ad468'

select TO_CHAR(hiredate,:dfmt)

from emp

where sal > :salary

and deptno = :b3

END OF STMT

Hash value este 710622186.

Rularea scriptului sqrepsql.sql sau awrsqlrpt.sql pentru a căuta un reper în timp când interogarea

se execută oprim.

Când suntem anunţaţi să introduce perioada de început şi de sfârşit a unui snapshot, să introducem o

dată când ştim că interogarea se execută deficitar; un raport similar dintr-o perioadă optimă, ar fi

deasemenea util.

Exemplu:

sqlplus perfstat/pwd;

54Using Statspack to Record Explain Plan Details [ID 209197.1],


89


@?/rdbms/admin/sprepsql.sql

Completed Snapshots Snap Snap

Instance DB Name Id Snap Started Level Comment

--------- --------- ----- ----------------- ----- --------------

DB9iR2 DB9IR2 125 18 Aug 2005 21:49 5

. . .

150 03 Apr 2006 16:51 7

151 03 Apr 2006 16:51 7

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 150

Begin Snapshot Id specified: 150

Enter value for end_snap: 151

End Snapshot Id specified: 151

Specify the Hash Value

~~~~~~~~~~~~~~~~~~~~~~

Enter value for hash_value: 710622186

Hash Value specified is: 710622186

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_150_151_710622186. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Colectarea fişierului de trace rezultat

Construirea unui script de test

Se poate utiliza un script de test pentru a rula interogarea cu orice variable de constrângere necesare şi

evenimente de diagnostic pornite sau oprite. Acest script este util ca valoare de referinţă (benchmark) în

timp ce facem modificări asupra interogării.

Acest script va reprezenta aceeaşi performanţă a interogării şi planul de execuţie ca cea originală din

aplicaţie.

Extragerea cazului de test din fişierul de trace extins SQL colectat în etapa verificării problemei

(când interogarea a fost verificată ca fiind un blocaj).

Analizăm interogarea şi remarcam numărul cursorului

Identificăm variabilele constrânse pentru acesta interogare.

90

Exemplu


ad='9f040c28'

select TO_CHAR(hiredate,:dfmt) <-------------- variabilă constrânsă în poziţia 0

from emp

where sal >:salary <-------------------------- variabilă constrânsă în poziţia 1

and deptno = :b3 <---------------------------- variabilă constrânsă în poziţia 2

END OF STMT


BINDS #1: <------------IMPORTANT!, Trebuie să fie acelaşi # ca şi la cursorul de mai sus (#1)

kkscoacd

Bând#0 <-------------------------------------- secţiune pentru poziţia constrângerii 0

oacdty=01 mxl=32(20) mxlc=00 mal=00 şcl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=31 siz=80 off=0

kxsbbbfp=ffffffff7b12bef0 bln=32 avl=10 flg=05

value="mm-dd-yyyy" <-------------------------- valoare constrânsă pentru variabile ":dfmt"




kxsbbbfp=ffffffff7b12bf10 bln=22 avl=02 flg=01

value=10 <------------------------------------ valoare constrâns apentru variabilă ":salary"




kxsbbbfp=ffffffff7b12bf28 bln=22 avl=02 flg=01

value=20 <------------------------------------ valoare constrâns apentru variabilă ":b3"

Determinarea tipului şi valorilor variabilelor de constrângere. Din exemplul anterior, avem:

Crearea unui script de test ce incorporează interogarea cu variabilele de constrângere:

set time on

set timing on

91

spool test_script

-- definirea variabilelor în SQLPlus

variable dfmt varchar2(32)

variable salary number

variable b3 number

-- Setarea variabilele de constrângere

begin :dfmt := 'mm-dd-yyyy'; :salary := 10; :b3 := 20;

end;

/

-- setarea nivelul statisticilor mare

alter session set statistics_level = all;

-- pornirea trace-ul

alter session set events '10046 trace name context forever, level 12';

-- rularea interogării

select TO_CHAR(hiredate,:dfmt)

from emp

where sal > :salary

and deptno = :b3;

select 'end of script' from dual;

-- oprim trace-ul

alter session set events '10046 trace name context off';

select * from table(dbms_xplan.display_cursor(NULL,NULL,'RUNSTATS_LAST'));

-- Reducerea nivelului statisticilor

alter session set statistics_level = typical;

spool off

Se va colecta fişierul rezultat numit “test_script.lst”.

92

Rularea scriptului de test şi colectarea fişierului de trace extins SQL ce a fost produs în directorul

user_dump_deşt.

Exemplu

Dacă scriptul de test a fost denumit test.sql:

sqlplus scott/tiger @test.sql

SQL> show parameter user_dump_deşt

NAME TYPE VALUE

--------------- ------- --------------------------------------------------------------------------------------------------

user_dump_deşt strâng /u01/app/oracle/product/DB10gR2/admin/DB10gR2/udump

Obţinerea unui raport TKProf al fişierului de trace sql extins

Se va genera un raport TKPROf şi se vor ordona interogările SQL după ordinea celui mai mare timp,

folosind comanda:


Se va compara planul de execuţie şi alte statistici de execuţie (physical reads, logical reads, rows

returned per execution) pentru interogarea din scriptul de test cu cele colectate în etapa verificării

problemei. dacă aceste rezultate sunt comparabile, atunci sciptul de test este valid.

Analiza

Datele colectate în paşii anteriori vor fi acum analizate pentru a determina cauza problemei. Este

important să ne asigurăm că datele au fost colectate complet, atât pentru planuri bune, cât şi cele rele.

Acest proces începe întotdeauna prin verificarea validităţii statisticilor, a modului de optimizare, şi a

parametrilor importanţi de iniţializare; apoi, continuă cu alegerea unei strategii de optimizare care să fie

aplicabilă problemei şi obiectivelor tale.

Verificări mandatorii: Modul optimizării, statisticile şi parametrii

93

Trebuie să ne asigurăm că este utilizată optimizarea bazată pe cost (CBO)., statisticile au fost colectate

în mod corect şi că parametri de iniţializare sunt setaţi cum trebuie, înainte de a analiza detaliile planului

de execuţie şi modul în care acesta va fi îmbunătăţit.

Asigurarea că este folosit optimizatorul pe bază de cost

CBO-ul este esenţial pentru a optimiza o interogare, fiindcă RBO-ul nu mai este suportat.

1. Date necesare pentru analiză

Sursa: planul de execuţie (din Colectarea datelor), trebuie să ne uităm după textul „Note: rule

based optimization”, după ce apare planul, pentru a vedea dacă a fost folosit RBO-ul.

2. Observaţii şi Cauze Comune

RBO este depreciat în vafoarea optimizării bazate pe cost.

Model de tuning

Cauza identificată: utilizarea RBO

Justificarea cauzei: Planul de execuţie nu va afişa cardinalitatea estimată sau costul, dacă se utilizează

RBO.

Soluţia identificată: Colectarea corectă a statisticilor.

CBO va genera planuri de execuţie mai bune când are statistici precise pentru tabele şi indecşi.

Trebuie să ne asigurăm că:

- toate obiectele au statistici colectate (parametrul CASCADE)

- orice coloană cu date distorsionate are colectate histograme la o rezoluţie suficientă (parametrul

METHOD_OPT)

- dacă este posibil, statistici globale pe partiţie

Efort – scăzut

Risc – mediu. Adunarea de statistici noi ar putea modifica unele planuri de execuţie în rău, dar este mai

mult probabil că planurile se vor îmbunătăţi. Colectarea statisticilor vor invalida cursoarele

din shared pool – de aceea se recomanda aceasta operatiunie în perioadele cu activitate

redusă a bazei de date.

Implementarea soluţiei: În general, se pot folosi următoarele pentru colecatrea de statistici:

Oracle 10g:

exec DBMS_STATS.GATHER_TABLE_STATS(

94

tabname => ' Table_name '

ownname => NULL,

cascade => 'TRUE',

method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Pentru a şterge şi re-crea statisticile pentru un tabel individual şi indecşii săi:

exec dbms_stats.delete_table_stats(ownname=>'user_name',-

tabname=>'table_name',cascade_indexes=>true);

exec dbms_stats.gather_table_stats(ownname=>'user_name',-

tabname=>'table_name',-

estimate_percent => 100,-

cascade=>true,-

method_opt=>'for all columns size skewonly');

Adunarea de statistici pentru toate obiectele din schema:

exec dbms_stats.gather_schema_stats( -

ownname => ' Schema_name ', -

cascade => TRUE, -

method_opt => 'FOR ALL COLUMNS SIZE 1' );

Adunarea de statistici pentru toate obiectele din baza de date:

exec dbms_stats.gather_database_stats( -

cascade => TRUE, -


Următoarele resurse, pot fi deasemena utile:

:

Gathering Statistics for the Cost Based Optimizer55

Histograms: An Overview56

Best Practices for automatic statistics collection on 10g57

How to check what automatic statistics collection is scheduled on 10g58

Statistics Gathering: Frequency and Strategy Guidelines59

55Recommendations for Gathering Optimizer Statistics on 10g [ID 605439.1],


56 Histograms: An Overview [ID 1031826.6],


57 Best Practices for automatic statistics collection on Oracle 10g [ID 377152.1],


58How to check what automatic statistics collection is scheduled on 10g [ID 377143.1],


59Statistics Gathering: Frequency and Strategy Guidelines [ID 44961.1],


95






Verificarea implementării: Rularea din nou a interogării şi observarea dacă performnta s-a îmbunătăţit. În

caz contrar trebuie să examinăm următoarele:





acestei probleme.

Asigurarea că există statistici exacte ale tabelelor/indexului

Statisticile exacte asupra tuturor tabelelor şi indexurilor din căutare sunt esenţiale pentru că CBO-ul să

creeze planuri de execuţie bune.


- Sursă: Planul de execuţie (obţinut în secţiunea „Colectare de Datelor”)

Numărul exact de rânduri obţinute în urma unei căutări sau un plan de execuţie care arată

numărul exact, şi cel estimat, de rânduri/pas al planului.

Numărul de rânduri estimat obţinut în urma unei căutări („Estim Card” sau similar) din planul

de execuţie.

Determină dacă există o discrepanţă majoră între numărul efectiv şi numărul estimat de

rânduri.

- Sursă: raportul SQLTXPLAIN, statistici ale tabelelor

Examinarea secţiunilor „Tabele” şi „Index”, coloana „Last Analyzed”, pentru a determina dacă

tabelele şi indecşii au fost analizaţi.

Compararea coloanele „Num Rows” şi „Sample Size” în secţiunea „Tabele”, pentru a vedea

cât anume din tabel a fost folosit drept mostră pentru strângerea de date.

Examinarea secţiunilor „Tabele” şi „Index”, coloana „User Status” pentru a determina dacă

statisticile au fost introduse direct sau analizate.

Examinarea „Column Statistics”, „Num Buckets”, iar dacă aici apare 1, înseamnă că nu s-au

colectat histograme.

2. Observaţii şi Cauze Comune

96

Model de tuning

Identificarea problemei: CBO a estimat un număr de înregistrări returnate semnificativ diferit faţă de

numărul real de înregistrări returnate.


- folosind raportul SQLTXPLAIN, vom căuta numărul estimat de înregistrări returnate pe interogare

(“Estim card” pentru) pentru prima linie a planului de execuţie.

- compararea numărului estimat de înregistrări cu cel real. dacă acestea idfera după un ordin de

magnitudine sau mai mult, CBO este afectat de statisticle inadecvate.

Cauza identificată: statistici lipsă sau inadecvate.

Statisctici lipsă:

- statisticile au fost colectate pentru tabelele din interogare

- colectarea statisticilor nu a fost defalcata până la indecşi (cascade)

Mostra de date considerată pentru statistici este inadecvata:

- dimensiunea mostrei este insuficientă pentru a permite CBO să calculeze valorile selective în mod

precis.

- histogramele nu au fost colectate pe coloanele implicate în predicatul interogării.


- statisticile tabelelor lipsesc: DBA_TABLES.LAST_ANALYZED este NULL

- lipsesc statisticile indecsilo: DBA_INDEX.LAST_ANALYZED este NULL

- mostra inadecvată pentru tabele: DBA_TABLES.SAMPLE_SIZE /# de înregistrări din tabela<5%

- mostra inadecvată pentru indecşi: DBA_INDEXE.SAMPLE_SIZE/# de înregistrări din tabela <30%

- histogramele nu au fost colectate: nu sunt înregistrări în DBA_TAB_HISTOGRAMS pentru coloanele ce

au date modificate.

Soluţia identificată: colectarea statisticilor corectă.

Efort – scăzut

Risc – mediu

Implementarea soluţiei: Idem ca la modelul anterior.

Asigurarea că parametrii de iniţializare au fost setaţi corect

97

CBO foloseşte valorile diferiţilor parametri de iniţializare pentru a estima costul variatelor operaţiuni ale

planului de execuţie. Când anumiţi parametri nu au fost setaţi corespunzător, poate cauza estimarea

costului să fie inadecvată, iar planurile să devină sub-optime.60


Sursă: raportul SQLTXPLAIN, secţiunea de trace a optimizer-ului, “Parameters used by the Optimizer”

2. Observaţii şi cauze comune

Definirea problemei: Setările parametrilor afectează calea de acces la tabele şi join-urile.

Unii parametri de iniţializare pot fi setaţi prea agresivi pentru a obţine planuri de execuţie mai bune pentru

anumite interogări. Aceşti parametri pot afecta în mod negativ alte interogări, cauzând acestora o

prefrinta pentru scanări complete a tabelei sau merge-uri sau hash joins în loc accesul cu ajutorl

indecşilor cu nested loop joins.

Verificarea problemei: vom verifica următoarele:

- Setările parametrilor ce afectează optimizerul sunt diferite de cele pre-definite.

- CBO alege o cale de acces, join order sau opratie ce este sub-optimala în comparaţie cu alt plan mai

bun.

Cauza identificată: Parametri de iniţializare ce cauzează scanări complete ale tabelelor şi merge/hash

join-uri

Următorii parametri sunt conoscuti că afectează estimarea costului CBO

- optimizer_index_cost_adj setat mult mai mare de 100

- bd_file_multilock_read_count setat mult prea mare (>1MB/db_block_size)

- optimizer_mode=all_rows

Justificarea cauzei: Scanări complete ale tabelelor, merge/hash join-urile ce apar şi parametrii de mai

sus ce nu sunt setaţi cu valorile iniţiale.

Soluţia identificată: re-setarea parametrilor la valorile iniţiale. Totuşi, schimbarea valorilor iniţiale ale

acestor parametri ar putea îmbunătăţi performanţele unei interogări. Această modificare ar trebui

realizată întâi pentru o sesiune (şi nu la nivelul bazei de date în init.oră sau spfile) şi trebuie luat în

considerare şi impactul asupra altor interogări.

Effort – scăzut. Parametrii se pot modifica uşor.

Risc – mare. Modificarea parametrilor de iniţializare au potenţialul de a afecta şi alte interogări din baza

de date.

Implementarea soluţiei: detalii pe fiecare versiune61

60Oracle® Database Reference 10g Release 2 (10.2), Initialization Parameter Description, pagina 97,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237.pdf

98


Verificarea implementării: Rularea din nou a interogării şi observarea dacă performnta s-a îmbunătăţit.

În caz contrar trebuie să examinăm următoarele:




O alternativă ar fi înregistrarea unui Service Request cu Oracle Support pentru asistenţă în

rezolvarea acestei probleme.

Faza 3 - Alegerea strategiei de tuning62

Răspunsurile la următoarele întrebări ne vor îndruma spre alegerea strategiei de tuning corecte.

1. Exista un plan de execuţie de când interogarea rula optim?

dacă am avea un plan de execuţie bun, adiţional celui curent, deficitar, putem utiliza strategia comparaţiei

intre planurile de execuţie, pentru a identifica prin ce diferă acestea. Odată ce ştim diferenţele, putem

modifica interogarea pentru a produce un plan bun, sau ne putem concentra pe particulatitatile diferite şi

să determinăm cauza acestor diferenţe.

2. Suntem mai degrabă interesaţi să rezolvăm cât mai repede problema, şi nu să identificăm cauza

aparititiei acesteia.

dacă este urgent să optimizăm o interogare şi nu suntem interesaţi să descoprim cauza deficiientelor de

performanţă, putem utiliza strategia soluţiei rapide de livra CBO-ului mai multe informaţii şi este posibil să

obţinem un plan de execuţie mai bun.

3. Interogarea petrece cel mai mult timp în faza de execuţie/fetch (şi nu în faza de parsare)?

dacă interogarea petrece cel mai mult timp parsând, tehnicile obişnuite de optimizare modofica planul de

execuţie pentru reducerea I/O (intrări/ieşiri) în timpul execuţiei sau apelurilor fetch, probabil nu vor fi de

mare ajutor. Atenţia optimizării ar trebui inderptata spre strategia de reducere a parsarilor.

Exemplu:

O bucată dintr-un raport TKProf pentru o interogare:

61Database Initialization Parameters for Oracle Applications Release 12 [ID 396009.1],


62 Oracle® Database Performance Tuning Guide 10g Release 2 (10.2), Chapert 11 – SQL Tuning Overview,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#sthref1060 , accesat la data 25.01.2010

99

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#sthref1060


Timpul petrecut parsând este 300.83s, comparativ cu doar 85.03s pentru fetching. Interogarea are

probleme de parsare.

Oracle 10g SQL Tuning Advisor63

Oracle 10g este capabil să întreprindă analize avansate de optimizare SQL folosind SQL Tuning Advisor

(şi Access Advisor). Este metoda de preferat şi cea mai sigură. Pentru a beneficia de avantajele acestui

utilitar, este necesară o licenţă pentru Tuninig Pack.

Timpi de parsare mari Strategia reducerii timpilor de parsare

Reducerea timpilor de parsare mari necesita o abordare diferită a optimizării, faţă de scopul tipic de a

reduce intrările/ieşirile logice sau un plan de execuţie ineficient.

Se utilizează când s-a identificat ca iterogarea petrece cel mai mult timp în faza de parsare (de obicei

identificată în etapa verificării problemei).

Date necesare pentru analiza:

a. Sursa: TKProf

- parse elapsed time, overall elapsed time

- parse time spent on CPU

Exemplu:

63How to use the Sql Tuning Advisor. [ID 262687.1],

https://support.oracle.com/CSP/main/article?cmd=show&id=262687.1&type=NOT , accesat la data 25.01.2010_ Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12 Automatic SQL Tuning,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010_ Oracle® Database 2 Day DBA 10g Release 2 (10.2) - Using the SQL Tuning Advisor,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref784 , accesat la data 25.01.2010_ Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 12 Automatic SQL Tuning

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028, accesat la data 25.01.2010

100





total elapsed time of 21.45 sec

parse elapsed time of 20.12 sec

total parse CPU time of 20.02 sec

parse wait time = parse elapsed time - parse cpu time = 20.12 - 20.02 = 0.10 sec

- parse time spent waiting (not în CPU);

Exemplu:

Timpul petrecut aşteptând a fost parse elapsed - parse CPU = 20.12 - 0.02 = 20.10 secs.

b. dacă timpul petrecut de procesor parsând este mai mult de 50% din timpul total, timpul de parsare este

dominat de procesor, în caz contrar de evenimentele de aşteptare.

101

Model de tuning

Problema identificată: Una sau mai multe interogări ce prezintă consum mare de procesor în timpul

unei HARD parse

Acest compertament este adesea întâlnit când comenzi voluminoase invoca multe obiecte sau obiecte

partiţionate.

Ce să căutăm:

1. Trebuie să verificăm dacă instrucţiunea/comanda a fost parsata HARD

2. Trebuie să comparăm “parse CUP time” cu “parse elapsed time” şi să vedem dacă “parse cpu time”

este mai mult de 50%

Cauza identificată: Interogarea foloseşte mostre dinamice şi influenţează în mod negativ timpul de

procesare


- timpul de procesare este responsabil pentru timpul total de execuţie al interogării

- rezultatul planului de execuţie SQLTXPLAIN, scriptul UTLXPLS, trace-ul 10053 vor arăta ca pentru

optimizarea interogării s-au folosit mostre dinamice.

Soluţia identificată: Alternative la utilizarea mostrelor dinamice

dacă timpul de procesare este mare, sunt necesare soluţii alternative pentru a obţine planul de execuţie

dorit fără utilizarea mostrelor dinamice.

Efort -mediu

Unele alternative sunt mai uşor de implementat (adăugarea unui hint), iar altele sunt mai dificile

(determinarea hint-ului necesar prin comparaţia planurilor de execuţie).

Risc – scăzut

În general, această soluţie va afecta doar interogarea în cauză.


Câteva alternative la mostrele dinamice sunt:

1. În 10g sau versiuni ulterioage, se va folosi SQL Tuning Advisor (STA) pentru a genera un profil pentru

interogarea vizată.

2. Se vor găsi hint-urile necesare pentru implementarea planului generat normal prin utilizarea mostrelor

dinamice şi se va modifica interogarea cu hint-ul identificat.

3. Se va folosi outline-ul salvat pentru a capta un plan generat cu mostre dinamice. Pentru date foarte

volatile (pentru care mostrele dinamice ajutau la obţinerea unui plan de execuţie bun), se poate folosi

102

abordarea ca aplicaţia va alege una dintre interogările cu hint-uri în funcţie de starea datelor (ex. pentru

date şterse recent foloseşte interogarea#!, altfel interogarea#2).

Documente utile despre hint-uri

Using Optimizer Hints64

Forcing a Known Plan Using Hints65

How to Specify an Index Hint66

QREF: SQL Statement HINTS67

Documents for stored outlines / plan stability:

Using Plan Stability68

Stored Outline Quick Reference69

How to Tune a Query that Cannot be Modified70


Se va implementa soluţia şi se va verifica dacă performanţele bazei de date se îmbunătăţesc. În caz

contrar trebuie să examinăm următoarele:






64Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 16 Using Optimizer Hints

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602 , accesat la data 25.01.2010

65 Forcing a Known Plan Using Hints [ID 221970.1],


66 How to specify an INDEX Hint [ID 50607.1],


67 QREF: SQL Statement HINTS [ID 29236.1],


68 Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 18 Using Plan Stability,


69 Stored Outline Quick Reference [ID 67536.1],


70 How to Tune a Query that Cannot be Modified [ID 122812.1],


103








Doar planul de execuţie deficitar este disponibil şi se doreşte o soluţie

rapidă Strategia soluţiei rapide

Obiectivul acestei strategii este să modifice câteva setări de nivel înalt ale optimizer-ului şi observarea

dacă apar îmbunătăţiri în plnul de execuţie (de ex. se va schimba optimizer_mode sau se va folosi

dynamic sampling). Apoi, se vor folosi hint-uri sau alte mijloace ce vor determina CBO să genereze un

plan de execuţie mai bun. Un plan mai bun poate fi folosit în identificarea ulterioară a cauzei problemei.

Presupuneri

- deja am încercat să folosim SQL Tuning Advisor.

- au fost verificaţi optimizer_mode, statisticile şi parametrii de iniţializare pentru:

o se utilizează CBO

o statisticile au fost colectate pentru toate obiectele, tabelele şi toţi indecşii

o dimensiunea mostrei de analiza este cât mai mare posibilă

o au fost colectate histogramele pentru toate coloanele din predicatul interogării ce au date

modificate

Pregătiri

Vom avea nevoie de textul interogării şi variabilele de constrângere (dacă sunt disponibile) să ruleze pe

sistemul unde interogarea este lentă.

Descoperirea unui plan de execuţie mai bun

Întâi, ar trebui să încercăm următoarele modificări, ce ar putea, cel mai probabil, să aducă rezultate

pozitive în cel mai scurt timp.

1. Schimbarea modului optimizer-ului: dacă optimizer_mode este ALL_ROWS, să încercăm

FIRST_ROWS_N (unde N este numărul de înregistrări pe care dorim să îl vedem imediat), sau

invers. Această modificare se poate realiza utilizând un hint sau parametru:

o Hint:

SELECT /*+ first_rows_1 */ col1, col2, ...FROM table1...WHERE col1 = 1 AND ...

o Parametru:

104

ALTER SESSION SET optimizer_mode = first_rows_1;SELECT col1, col2, ...FROM table1...WHERE col1 = 1 AND ...

2. Dynamic sampling: mostra de înregistrări returnate de interogare va avea o determinare selectivă

precisă, ce, de obicei, conduce la un pland de execuţie mai bun.

o Hint:

SELECT /*+ dynamic_sampling(5) */ col1, col2, ...FROM table1...WHERE col1 = 1 AND ...

o Parametru:

ALTER SESSION SET optimizer_dynamic_sampling = 5;SELECT col1, col2, ...FROM table1...WHERE col1 = 1 AND ...

Nivelul mostrei dinamice controlează dimensiunea acesteia. Un nivel 10 reprezintă faptul

că toate înregistrările vor fi constinute în mostra. Este bine să începem cu nivelul 5 şi să

creştem până când observam îmbunătăţiri de performanţă ale interogării.

Este important să rulăm interogarea de două ori pentru a determina dacă performanta

acesteia s-a îmbunătăţit, deoarece, prima dată, interogarea este parsata, efortul pentru

construirea mostrei dinamice ar putea fi semnificativ, şi nu se va reflecta în performanţa

planului de execuţie. A doua rulare va indica dacă planul de execuţie s-a îmbunătăţit şi

dacă performanta este mai bună.

3. Parametrul OPTIMIZER_FEATURES_ENABLE: dacă acesta interogare rula mai bine într-o

versiune mai veche, acest parametru va fi utilizat pentru a readuce optimizer-ul la vechea

versiune. Este de preferat să se utilizeze la nivel de sesiune şi nu de sistem.

4. Înlocuirea variabilelor de constrângere cu variabile literale: această înlocuire va permite CBO-ului

să aibă valori precise pentru variabile.

5. Descoperirea unui plan de execuţie mai bun – tehnici adiţionale.

dacă modificările prezentate mai sus nu au adus o ombunatatire semnificativă a planului de

execuţie, se mai pot încerca următoarele:

dacă interogarea returnează puţine înregistrări:

o ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10 (sau mai mic)

o ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90 (sau mai mare)

dacă interogarea returnează multe înregistrări:

105

o ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1000 (sau mai mare)

o ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 0

o PARALLEL hint

dacă înregistrarea are viziuni, se pot încerca următoarele hint-uri:

o PUSH_PRED, NO_MERGE

o MERGE

dacă interogarea are sub-interogări, se pot încerca următoarele hint-uri:

o PUSH_SUBQ

o NNEST

6. Implementarea unui nou plan bun.

Dacă avem posibilitatea de a modifica interogarea sau aplicaţia:

- se va examina planul bun pentru a găsi diferenţele.

- se va găsi hint-ul ce va determina CBO-ul să genereze un plan bun

Dacă nu avem posibilitatea să modificăm interogarea

- vom folosi outline-urile pentru a reţine un plan bun

o se vor utiliza parametrii de iniţializare de sesiune pentru a schimba planul de execuţie

o se va capta un stored outline pentru interogare (comanda ALTER SESSION

CREATE_STORED_OUTLINES)

o se va verifica dacă stored outline-ul face ca interogarea să ruleze mai bine

o testarea stored outline-ului într-un sistem de test

o implementarea stored outline-ului în producţie

- utilizarea parametrilor de iniţializare pentru a influenţa CBO-ul

o se va folosi un declanşator la autentificare sau se va modifica aplicaţia să seteze

parametri de sesiune la valorile ce îmbunătăţeşte performanta interogării. Această

abordare nu este recomandată în cele mai multe cazuri, deoarece poate afecta în mod

negativ alte interogări optime.

Existenţa unui plan bun de execuţie şi a unuia deficitar Strategia

comparării planurilor de execuţie

Obiectivul acestei strategii este de a compara cele două planuri de execuţie, bun şi rău, identificarea

diferenţelor şi a modalităţilor de a determina planul deficitar să funcţioneze ca cel optim.

1. Au fost verificaţi optimizer_mode, statisticile şi parametrii de iniţializare pentru:

106

o se utilizează CBO

o statisticile au fost colectate pentru toate obiectele, tabelele şi toţi indecşii

o dimensiunea mostrei de analiza este cât mai mare posibilă

o au fost colectate histogramele pentru toate coloanele din predicatul interogării ce au date

modificate

2. Obţinerea planului de execuţie bun şi a celui deficitar.

o Ar trebui să colectat deja toate datele descrise în etapele anterioare pentru ambele

planuri. Cu această informaţie, se vor compara cele două planuri.

o În Oracle 10gR2, PLAN_TABLE deţine o coloană numită OTHER_XML ce conţine hint-

urile necesare pentru a produce planul. dacă planul bun este din versiunea 10gR2, se va

evalua conţinutul acestei coloane pentru a extrage hint-urile interogării.

3. Parcurgerea paşilor şi compararea valorilor.

o Se vor compara ordinea join-urilor, tipurile de join, metodele de aces şi alte operaţiuni

între cele două planuri. Comparaţia se va face parcurgând planul de execuţie pas cu pas

în ordinea executării lor de către Oracle.

o Raportul SQLTXPLAIN a planului de execuţie are o coloană denumită “Exec Order” ce

ne va ajuta să urmărim secventa exact de paşi (planul începe de la pasul 1).

Exemplu:

În acest caz, execuţia va începe cu pasul ce are “Explain Plan Operatio” “INDEX FAST

FULL SCAN TOWNER.TEAMS_LINKS_IDX_001”, urmat de “INDEX FAST FULL SCAN

TOWNER.UOIS_IDX_003”.

4. Compararea şi schimbarea ordinii în care se efectuează join-ul.

Compararea

Ordinea join-urilor este unul dintre cei mai importanţi factori ce influenţează performanţă

pentru interogările ce implică multe tabele. Puteam obţine ceasta ordine pentru planul bun

astfel:

107

§ Fişierul de trace 10053. În acest fişier vom identifica ultima ordine a join-urilor

aleasă de CBO. Aceasta este citită de sus în jos (de la Table #0 la Table #N).

Exemplu:

Best so far: TABLE#: 0 CST: 8502 CDN: 806162 BYTES: 72554580

Best so far: TABLE#: 1 CST: 40642 CDN: 806162 BYTES: 112056518

Pentru a identifica numele tabelelor, va trebui să ne găsim linia de deasupra

ordinii astfel:

Join order[1]: EMP [ E] DEPT [ D]

Table #0 este tabelul cel mai din stânga, Table #1 este cel din dreapta, şi tot aşa.

În acest exemplu, ordinea este EMP, DEPT.

§ Fişierul SQLTXPLAIN. Se va parcurge planul în ordinea execuţiei, şi se va reţine

ordinea tabelelor.

Exemplu:

În acest caz, sunt scanaţi doi indecşi, şi nu tabelele efective, şi trebuie să

identificăm tabelele corespunzătoare uitându-ne în raportul SQLTXPLAIN:

Acum putem observa că ordinea tabelelor este:

1) TOWNER.TEAM_LINKS, 2) TOWNER.UOIS

108

Comparând ordinea planului bun cu a celui deficitar, dacă acestea diferă, se va

folosi hint-ul ORDERED sau LEADING pentru a direcţiona CBO-ul.

SELECT /*+ ordered */ TL.COL1, TU.COL2 FROM TOWNER.TEAM_LINKS TL,

TOWNER.UOIS TU WHERE ...

5. Se vor compara metodelel de acces la date în ambele planuri

6. Se vor compara tipurile de join

7. Se vor identifica tipurile majore de operaţii ce diferă între cele două planuri

8. Se va influenţa CBO să genereze un plan bun.

Odată ce au fost identificate diferenţele între cele două planuri, este necesară modificarea interogării cu

un hint pentru a asigura generarea unui plan bun.

Dacă nu avem posibilitatea să modificăm interogarea71

- vom folosi outline-urile pentru a reţine un plan bun

o se vor utiliza parametrii de iniţializare de sesiune pentru a schimba planul de execuţie

o se va capta un stored outline pentru interogare (comanda ALTER SESSION

CREATE_STORED_OUTLINES)

o se va verifica dacă stored outline-ul face ca interogarea să ruleze mai bine

o testarea stored outline-ului într-un sistem de test

o implementarea stored outline-ului în producţie

- utilizarea parametrilor de iniţializare pentru a influenţa CBO-ul

o se va folosi un declanşator la autentificare sau se va modifica aplicaţia să seteze

parametri de sesiune la valorile ce îmbunătăţeşte performanta interogării. Această

abordare nu este recomandată în cele mai multe cazuri, deoarece poate afecta în mod

negativ alte interogări optime.

Este disponibil doar planul deficitar şi se doreşte o analiză riguroasă

Strategia analizei planului

Se va revedea textul interogării, căile de acces, ordinea şi metodele join-urilor, se vor implementa soluţii

pentru aceste probleme. Aceasta este abordarea standard a optimizării interogărilor şi este subiectul

principal al acestei strategii.

71How to Tune a Query that Cannot be Modified [ID 122812.1],


109


Examinarea interogării

Vom examina interogarea să identificăm eventualele greşeli şi trebuie să luăm în considerare volumul de

date căruia I se adresează această.

Nu lipsesc predicate din join-uri

Lipsa predicatelor din join-uri cauzează produsuri carteziene ce sunt foarte ineficiente şi ar trebui evitate.

Exemplu:

SELECT e.ename, d.dname FROM scott.emp e, scott.dept d WHERE e.empno < 1000

Ar trebui scrisă:

SELECT e.ename, d.dname FROM scott.emp e, scott.dept d WHERE e.empno < 1000 AND

e.deptno = d.deptno

Predicate neobişnuite

Predicatele neobişnuite apar ca rezultat al utilizării generatoarelor de interogări, ce nu ţin seama de

utilizarea CBO-ului. Acestea pot determina CBO-ul să estimeze eronat.

Exemple:

- predicate identice repetate: WHERE col1 =1 and col1 =1 and col1 =1 and … ce va

determina CBO să subestimeze selectivitatea interogării. Presupunând că selectivitatea

col1=1 este 0.2, această clauză va avea selectivitate=0.2*0.2*0.2=0.008. Aceasta va

determina o cardinalitate estimată mult mai mică decât cea reală şi ar putea bloca calea unui

index, în timp ce o scanare completă a tebelei ar fi fost o variantă mai bună.

- Predicate identice în ambii membri: WHERE d.deptno = d.deptno, ce are ca efecte

eliminarea valorilor NULL din interogare (chiar şi pentru coloanele NULL) şi subestimarea

cardinalitatii.

Predicatele neobişnuite ar trebui eliminaye dacă generează rezultate neaşteptate (ex. eliminarea valorior

nule) sau execuţii deficitare ale planului datorate cardinalitatii estimate greşit.

110

Constructori ce cauzează probleme

Anumiţi consructori pot fi problematici cu CBO. Dintre aceştia amintim:

Liste mari de valori pentru operatorii ÎN / OR

Outer join

Interigarile ierarhice

Vizualizări, sub-interogări

Model de tuning

Definirea problemei: Plan de execuţie deficitar la folosirea operatorilor ÎN / OR

Verificarea problemei: Vom identifica clauzele inetrogarii de formă:

...WHERE col1 ÎN (1, 2, 3, 4, 5, ...)

...WHERE col1 = 1 OR col1 = 2 OR col1 = 3 ...

Cauza identificată: Pentru CBO costul unei scanări complete a tabelei este mai mic decât o serie de

scanări pe serii de indecşi. Când sunt utilizaţi operatorii În şi OR, CBO împarte interogarea în blocuri

separate şi fiecare dintre ele va utiliza un index.

Justificarea cauzei: scanări complete ale tabelei în planul de execuţie în locul scanărilor după intervale

de indecşi utilizând operaţia de CONCATENARE.

Soluţia identificată: implementarea utilizării hint-ului USE_CONCAT pentru a forţa optimizer-ul să

folosească indecşii şi să evite astfel scanările complete. Acest hint va forţa utilizarea unui index (furnizat

de hint) în locul unei scanri complete.

Effort – scăzut. Modificarea interogării cu implementarea hint-ului.

Risc – scăzut. Va fi afectată doar o singură interogare.

Implementarea soluţiei:

SELECT /*+ USE_CONCAT */ * FROM table1 WHERE col1 ÎN (1,2,3,4,…);

Verificarea implementării: Se va implementa soluţia şi se va verifica dacă performanţele bazei de date

se îmbunătăţesc. În caz contrar trebuie să examinăm următoarele:






111

Examinarea căilor de acces la date

Alegerea căilor de acces are un impact deosebit asupra performantei interogărilor. dacă interogarea are

un predicat ce reduce numărul de înregistrări dintr-o tabelă, este de preferat folosirea unui index (în

eventualitatea existenţei unui index pe coloana predicat). Pe de altă parte, dacă nu există un predicat

care să filtreze înregistrările din tabela sau predicatul nu este foarte selectiv şi sunt returnate multe

rânduri, o scanare completă ar putea fi mai eficientă decât o scanare pe bază de index.

Este important să înţelegem care este numărul aşteptat de înregistrări pentru fiecare pas al planului şi să

îl comparăm cu cel estimat de CBO, în acest mod putem determina dacă o scanare completă este

justificată sau nu.

Interogarea ar trebui să returneze puţine înregistrări (specific aplicaţiilor

OLTP)

Dacă ne aşteptam la puţine înregistrări returnate de către interogare, ar trebui ca o metodă de acces pe

baza indexului să afişeze aceste rânduri în mod optim. Trebuie scanat planul de execuţie al interogării,

căutat locul unde cardinalitatea actuală este mică (cea estimată poate să fie ridicată din cauza estimărilor

CBO eronate) şi unde nu s-a utilizat un index.

Probleme posibile:

Interogarea nu foloseşte un index, sau foloseşte indexul greşit. Fie un index nu este disponibil

pentru una sau mai multe coloane din predicatul interogării, sau este disponibil dar nu a fost ales

de către CBO.

Ce să căutăm:

Planul de execuţie ne arată că indexul nu a fost folosit (şi se utilizează scanarea completă a

tabelei) pentru accesarea înregistrărilor tabelei şi coloanele nu au indecşi sau indexul existent pe

coloana nu este folosit.

Cauze identificate:

Nu este definit un index pentru coloane sau predicate

Selectivitatea estimată este incorectă

Indecşii disponibili sunt prea neselectivi

Conversia implicită a tipurilor de date din interogare

Este utilizată o funcţie pe o colană din predicatul interogării ce împiedica utilizarea acelui index

Interogarea are un hint ce împiedica utilizarea index-ului

Utilizarea unui optimizer_mode greşit

Apariţia unui produc cartezian datorat lipsei unui predicat

112

Interogarea ar trebui să returneze multe înregistrări (specific aplicaţiilor de

suport a deciziilor)

Când sunt aşteptate multe înregistrări, scanările complete ale tabelei sau complete indecşilor sunt de

preferat scanărilor parţiale ale indecşilor (unique sau tange). dacă ne aşteptam la un număr mare de

înregistrări, iar performanta interogării lasă de dorit, ar trebui să verificăm dacă indecşii sunt folosiţi

inadecvat.

Probbleme posibile:

Utilizarea full index scan pentru o tabelă de dimensiuni mari. Interogarea este executată utilizând

metoda de acces a acestui index, în loc de INDEX FAST FULL SCAN.

Ce să căutăm:

Planul de execuţie arata INDEX SCAN pe tabela

Tabela are dimensiuni mari (verificarea valorilor NUM_ROWS şi BLOCKS din raportul

SQLTXPLAIN)

Cauze identificate:

Optimizer_mode sau hint este setat pentru FIRST_ROWS sau FIRST_ROWS_K

S-a utilizat INDEX FULL SCAN pentru a evita o sortare

Lipseşte un predicat filtru

Un număr mare de înregistrări trebuiesc procesate de către interogare

Model de tuning

Definirea problemei: Utilizarea full index scan pentru o tabelă de dimensiuni mari. Interogarea este

executată utilizând metoda de acces a acestui index, în loc de INDEX FAST FULL SCAN.

Verificarea problemei: Planul de execuţie arata INDEX SCAN pe tabela

Tabela are dimensiuni mari (verificarea valorilor NUM_ROWS şi BLOCKS din raportul SQLTXPLAIN)

Cauza identificată: Optimizer_mode sau hint este setat pentru FIRST_ROWS sau FIRST_ROWS_K.

Când modul optimizer-ului este setat pe FIRSTT_ROWS sau FIRST_ROWS_K, optimizatorul va favoriza

utilizarea indecşilor pentru a returna înregistrările mai rapid. Acest mod va rezulta într-un plan foarte

infeicient dacă sunt aşteptate multe înregistrări din interogare.


- optimizer_ mode este setat printr-un hint ca “/*+FIRST_ROWS_1*/”

113

- optimizer_ mode este setat printr-un parametru de iniţializare ca OPTIMISER_MODE=FIRST_ROWS_1.

Uneori o sesiune poate avea acesta setare printr-un declanşator la autentificare – trace-ul 10053 poate

arăta dacă acest parametru este setat sau nu.

- TKProf arata modul optimizer-ului

Soluţia identificată: Se va încerca utilizarea hint-ului ALL_ROWS. dacă sunt dorite majoritatea

înregistrărilor din interogare (şi nu doar primele care sunt returnate), atunci ALL_ROWS va permite CBO-

ului să identifice planuri de execuţie mai bune decât modul FIRST_ROWS_N, ce produce planuri ce

returnează înregistrările prompt, dar nu este eficient în returnarea tuturor rândurilor.

Efort – scăzut. Se adauga hint-ul în interogare..

Risc – scăzut. Hint-ul va afecta doar interogarea în care este aplicat.

Implementarea soluţiei: Sintaxa este: /*+ALL_ROWS*/72








Examinarea ordinii join-urilor şi modul acestora

Ordinea join-urilor poate avea un impact foarte mare asupra performantei interogărilor. O ordine optimă

este, de obicei, cea în care sunt returnate cel mai devreme în plan cele mai puţine înregistrări. CBO

încearcă să înceapă ordinea join-urilor cu tabelele pe care le considera că vor returna doar o

înregistrare. dacă acesta estimare este greşită, este ales tabelul greşit şi performanţă interogării este

afectată.

Alegerea tipului de join este deasemenea importantă. Jon-urile imbricate sunt de dorit doar când câteva

înregistrări sunt necesare rapid şi coloanele de join sunt indexate. Hash join-urile sunt de dorit când sunt

implicate tabele de dimensiuni mari, şi se returnează multe înregistrări, sau coloanele de join nu au

indecşi.

Date necesare pentru analiza:

72 Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 16 – Using Optimizer Hintes, 16.1.2.1 Hints for

Optimization Approaches and Gols,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDFIAJD , accesat la data 25.01.2010

114

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDFIAJD

Sursa : planul de execuţie

o Numărul real de înregistrări returnate de către interogare sau un plan de execuţie ce

arata numărul de înregistrări real şi estimat la fiecare pas.

o Numărul estimat de înregistrări returnate (Estim Card) din planul de execuţie

o Determinarea existenţei unei mari discrepante intre numărul de înregistrări real şi cel

estimat

Sursa: raportul SQLTXPLAIN

o Execuţia planului arata ordinea execuţiei în coloana Exec Order

Sursa: raportul SQLTXPLAIN, secţiunea Optimizer trace, Parameters Used by the Optimizer

Ordinea join-urilor

Probbleme posibile:

Ordine incorectă – primele tabele intersectate returnează mai puţine interogări decât ultimele.

Numărul înregistrările actuale returnate din primele tabele intersectate este mult mai mare decât

cel rezultat din ultimele tabele intersectate. Este critic să începem ordinea intersectării cu tabelul

ce va returna cele mai puţine rânduri şi să îl intersectăm cu următorul cu cele mai puţine

intersectări, şi tot aşa. Tabelul ce returnează cele mai puţine înregistrări nu este neapărat cel cu

dimensiunea cea mai mică, deoarece şi un tabel de dimensiune foarte mare poate returna o

singură înregistrare când îi sunt aplicate anumite condiţii. În concordanţă, un tabel mic poate să

nu aibă nici o condiţie.

Ce să căutăm:

Cardinalitatea reală faţă de cea estimată pentru unul sau mai multe tabele din ordinea

intersectării diferă semnificativ.

Cardinalitatea estimată – poate fi găsită în planul de execuţie (în SQLTXPLAIN), în coloana

“Estim Cardinality” corespunzătoare fiecărei tabele din ordinea intersectării.

Cardinalitatea reală – se va verifica rularea planului de execuţie în TKProf pentru interogarea în

cauză.

Cauze identificate:

Selectivitate/cardinalitate estimată greşit pentru primul tabel din join

Parametrul de iniţializare “OPTIMIZER_MAX_PERMUTATIONS”73 are o valoare prea mică

pentru numărul de tabele din join

73 Initialization Parameters, http://www.adp-gmbh.ch/ora/misc/init_params.html , accesat la data 25.01.2010

115

http://www.adp-gmbh.ch/ora/misc/init_params.html

Model de tuning

Definirea problemei: ordinea intersectării este incorectă.


Numărul real de înregistrări returnate de către interogare sau un plan de execuţie ce arata numărul de

înregistrări real şi estimat la fiecare pas.

Numărul estimat de înregistrări returnate (Estim Card) din planul de execuţie

Determinarea existenţei unei mari discrepante intre numărul de înregistrări real şi cel estimat

Cauza identificată: Primul tabel din join are selectivitatea/cardinalitatea estimată greşit.


Cardinalitatea reală faţă de cea estimată pentru unul sau mai multe tabele din ordinea intersectării diferă

semnificativ.

Cardinalitatea estimată – poate fi găsită în planul de execuţie (în SQLTXPLAIN), în coloana “Estim

Cardinality” corespunzătoare fiecărei tabele din ordinea intersectării.

Cardinalitatea reală – se va verifica rularea planului de execuţie în TKProf pentru interogarea în cauză.

Soluţia identificată: colectarea corectă de statistici.

CBO va genera planuri de execuţie mai bune când are statistici precise pentru tabele şi indecşi.

Trebuie să ne asigurăm că:

- toate obiectele au statistici colectate (parametrul CASCADE)

- orice coloană cu date distorsionate are colectate histograme la o rezoluţie suficientă (parametrul

METHOD_OPT)

- dacă este posibil, statistici globale pe partiţie

Efort – scăzut

Risc – mediu. Adunarea de statistici noi ar putea modifica unele planuri de execuţie în rău, dar este mai

mult probabil că planurile se vor îmbunătăţi. Colectarea statisticilor vor invalida cursoarele din shared

pool – de aceea se recomanda aceasta operatiunie în perioadele cu activitate redusă a bazei de date.

Implementarea soluţiei: În general, se pot folosi următoarele pentru colecatrea de statistici:

Oracle 10g:

exec DBMS_STATS.GATHER_TABLE_STATS(

tabname => ' Table_name '

ownname => NULL,

cascade => 'TRUE',

method_opt => 'FOR ALL COLUMNS SIZE AUTO');

116

Pentru a şterge şi re-crea statisticile pentru un tabel individual şi indecşii săi:

exec dbms_stats.delete_table_stats(ownname=>'user_name',-

tabname=>'table_name',cascade_indexes=>true);

exec dbms_stats.gather_table_stats(ownname=>'user_name',-

tabname=>'table_name',-

estimate_percent => 100,-

cascade=>true,-

method_opt=>'for all columns size skewonly');

Adunarea de statistici pentru toate obiectele din schema:

exec dbms_stats.gather_schema_stats( -

ownname => ' Schema_name ', -

cascade => TRUE, -


Adunarea de statistici pentru toate obiectele din baza de date:

exec dbms_stats.gather_database_stats( -

cascade => TRUE, -


Următoarele resurse, pot fi deasemena utile:

Gathering Statistics for the Cost Based Optimizer74

Histograms: An Overview75

Best Practices for automatic statistics collection on 10g76

How to check what automatic statistics collection is scheduled on 10g77

Statistics Gathering: Frequency and Strategy Guidelines78





74Recommendations for Gathering Optimizer Statistics on 10g [ID 605439.1],


75 Histograms: An Overview [ID 1031826.6],


76 Best Practices for automatic statistics collection on Oracle 10g [ID 377152.1],


77 How to check what automatic statistics collection is scheduled on 10g [ID 377143.1],


78 Statistics Gathering: Frequency and Strategy Guidelines [ID 44961.1],


117









Tipul intersectării

Model de tuning

Definirea problemei: Interogarea returnează multe înregistrări, dar a fost aleasă intersectarea imbricată.

Nested loop join permite returnarea radida a unui număr mic de înregistrări. Acestea nu sunt optime când

sunt returnate multe înregistrări.

Verificarea problemei: Execuţia planului arata ordinea execuţiei în coloana Exec Order.

Cauza identificată: interogarea foloseşte hint-ul USE_NL ce nu este recomandat.

Justificarea cauzei: interogarea foloseşte hint-ul USE_NL şi ar rula mai bine fără acesta sau utilizând

hint-ul USE_HASH sau USE_MERGE.

Soluţia identificată: eliminarea hint-ului ce influenţează alegerea indexului. Se va eliminare hint-ul ce

influenţează decizia CBO-ului în alegerea caii de acces: INDEX_**, NO_INDEX, FULL, AND_EQUAL.

Effort – scăzut. Se elimină hint-ul în interogare..

Risc – scăzut. Hint-ul afecta doar interogarea în care este aplicat.

Implementarea soluţiei: Se va adăuga hint-ul corespunzător caii de acces dorite.79








79Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 16 – Using Optimizer Hintes, 16.1.2.2 Hints for

Access Paths,

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDJDIAH , accesat la data 25.01.2010

118

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDJDIAH

Examinarea altor operaţii (paralelism, etc.)

Execuţia paralelă

Date necesare pentru analiza: Sursa: planul de execuţie arata informaţii despre o execuţie paralelă

Probblema posibilă:

- Gradul de paralelism este eronat sau neaşteptat. Această problemă apare când gradul efectiv de

paralelism este diferit de cel pe care Oracle îl utilizează în mod obişnuit.

Ce să căutăm:

- Planul de execuţie arata mai puţine fire de execuţie alocate decât sunt necesare

- _px_trace raportează un grad de paralelism mai mic decât cel necesar

Cauze identificate:

- nu sunt disponibile fire de execuţie, iar interogarea a fost executată serial.80

Problema posibilă:

Este întâlnit paralelismul, dar nu este de dorit

Ce să căutăm:

Planul de execuţie arata operaţii paralele.

Fire de execuţie paralele sunt asociate cu sesiune curentă

Cauze identificate:

Setări de configurare sau hint-uri determina planuri paralele. CBO va încerca să utilizeze operaţii paralele

dacă sunt utilizate:

- Hint-ul parallel(t1, 4)

- ALTER SESSION FORCE PARALLEL

- Setarea gradului de paralelism şi/sau numărul de instante pe o tabelă, index sau interogare

Probbleme posibile:

Interogarea se execută seria, dar ar fi de dorit o execuţie paralelă.

Ce să căutăm:

după ce s-a executat interogarea, vom verifica V$PQ_SESSTAT.LAST_QUERY pentru statistica “queries

parallelized”. dacă acesta este 0, atunci interogarea nu a rulat în mod paralel.

Cauze identificate:

Nu erau disponibile fire de execuţie, iar interogarea a rulat serial.

80Why didn't my parallel query use the expected number of slaves? [ID 199272.1],


119


Studiu de caz

Acest studiu se bazează pe un caz real. Distribuţia datelor, analizei sau concluziei este strict interzisă

fară acordul meu scris.

Descrierea problemei

Un client a raportat o problemă de performanţă cu aplicaţia lor externa în timpul execuţiei unor interogări.

Clientul rulează aplicaţia folosind Oracle 9.2.0.4 pe o maşina AIX.

Problema identificată

Aplicaţia externă a clientului rulează mult mai încet decât ar fi de dorit.

Problema a fost izolată la o interogare specifică.

Interogarea rulează 10 ore în sistemul clientului, faţă de 9 ore în sistemul vânzătorului, având

aceleaşi date.

Nu au fost efectuate modificări în interogare (nu au fost folosite hint-uri) - şi nici nu sunt de

preferat modificări ale interogării.

Clientul doreşte ca interogarea să ruleze aprox 9 min şi în sistemul lui, la fel ca în cel al

vânzătorului.

Clientul a asigurat rapoarte statspack, 10046/TKProf şi 10053 din ambele sisteme, al său şi al

vânzătorului, rapoarte ce au fost colectate în timpul rulării interogării. Din aceste fişiere a fost posibil să

identific că există diferenţe semnificative intre comportamentul interogării în sistemul clientului şi cel al

văzătorului.

Verificarea problemei

Interogarea clientului utiliza un index, cea a vânzătorului utiliză o scanare completă a tabelului.

Când indexul clientului a fost eliminat, performanţa s-a îmbunătăţit din moment ce se utiliza

scanarea completă a tabelului.

Analiza preliminară

Clientul a asigurat rapoarte statspack, 10046/TKProf şi 10053 din ambele sisteme, al său şi al

vânzătorului, rapoarte ce au fost colectate în timpul rulării interogării.

Comenzile pe care le-a utilizat clientul pentru generarea fişierelor de trace au fost următoarele:

Notă: înainte de colectarea fişierelor de trace 10046 şi 10053, am determinat identificatorul procesului din

sistemul de operare, proces ce rulează interogarea.

Statspack:

120

exec statspack.snap;

10046 and 10053:

oradebug setospid <process ID from Session I> oradebug unlimit

oradebug Event 10046 trace name context forever, level 8

La un moment diferit, pentru a nu se amesteca rezultatele 10046 şi 10053:

oradebug setospid <process ID from Session I>

oradebug unlimit

oradebug Event 10053 trace name context forever, level 10

Analiza

Am utilizat fişierele de trace provenite de la client pentru a verifica problema. S-au comparat cele două

seturi vizând următoarele:

Analiza raportului statspack

Versiunea bazei de date

Profilul timpului bazei de date

Timpii de aşteptare pentru intrări/ieşiri (I/O)

Parametrii de iniţializare

Analiza 10046/TKProf

Timpul de rulare pentru a verifica afirmaţia clientului

Evenimentele de aşteptare şi timpul mediu de aşteptare

Numărul final de înregistrări returnate

Şi dacă se potriveşte cu ce afişează statspack

Analiza 19953

Dimensiunea obiectelor

Statisticile colectate

Costurile CBO-ului

Clientul a identificat interogarea problematică şi a fost folosită drept referinţă pentru testare:

Interogarea problematică este un INSERT… SELECT…¦. Partea de SELCET este cea lentă.

121

Această interogare a fost utilizată în cazul de test. S-a identificat că şi alte interogări sunt lente, dar

eforturile au fost concentrate pe această.

Analiza detaliată

A. Analiza raportului statspack

Versiunea bazei de date

Profilul timpului bazei de date

Timpii de aşteptare pentru intrări/ieşiri (I/O)

Parametrii de iniţializare

Raportul statspack al clientului: Overall Info

122

Raportul statspack al clientului: Timed Events

Raportul statspack al vânzătorului: Timed Events

Evenimentul de aşteptare principal al vânzătorului este db file scattered read. Acesta implică utilizarea

scanării complete a tabelei (FTS), ce corespunde observaţiei că performanţa interogării s-a îmbunătăţit

după ce s-a renunţat la index.

Timpul pentru intrări/ieşiri (I/O) este de 5ms, foarte bun dealtfel, şi indică faptul că nu există nici o

problemă în această arie.

123

Evenimentul principal din statspack-ul clientului este db file squential read, acesta indicând utilizarea

indecs-ilor şi nu a FTS.

Raportul statspack al vânzătorului: Top SQL

Interogarea problematică nu este listat în raportul statspack generat în sistemul vânzătorului. Cea mai

apropiată interogare este “insert into E0 select distinct PATT_ID, AGE, NULL, 0, NULL from E

DF_PAŢIENT where AGE>=6574’, probabil nerelevat.

Despre statspack ştim:

Statisticile SQL sunt actualizate când se finalizează fiecare apel.

Dacă interogarea a rulat mai mult decât intervalul temporal al snapshot-ului, statisticile SQL nu

vor fi actualizate.

fotografie a sesiunii pe durata rulării interogării ar fi fost mai utilă.

Concluziile analizei raportului statspack sunt:

B. Rezultatele analizei raportului statspack

Versiunea bazei de date corespunde

I/O sunt rezonabile (~5ms sau mai puţin)

Profilul evenimentului de aşteptare principal este diferit: al vânzătorului favorizează citiri

dispersate, iar al clientului citiri secvenţiale

Timpul de rulare este diferit: al vânzătorului este de 9 min, iar al clientului de 31 de minute.

C. Concluziile analizei

Nu sunt probleme de I/O

Aparent, vânzătorul utilizează FTS şi nu indecşi.

D. Analiza raportului 10046/TKProf ale vânzătorului

La examinarea raportului 10046/TKProf ale vânzătorului, am vizat următoarele:

Timpul de rulare al interogării pentru a verifica afirmaţia clientului că în sistemul vânzătorului,

aceasta rulează în 9 minute, iar în sistemul său mult mai mult.

Evenimentele de aşteptare şi timpii medii de aşteptare.

Planurile de execuţie din testele vânzătorului şi ale clientului

Datele rezultate corepund celor din raportul statspack? Dacă nu, de ce nu?

124

Raportul 10046/TKProf al vânzătorului

Observaţii:

Timpul de rulare al interogării este de 2651.82 secunde = 44,1 minute, şi nu 9 minute cum s-a

susţinut la raportarea problemei.

Au fost returnate 12844 de înregistrări. Acest număr trebuie comparat cu cel din sistemul

clientului.

Planul utilizează o scanare pe baze de indecşi. De ce în statspack văzusem scanare completă a

tabelei?

Raportul 10046/TKProf al vânzătorului

Observaţii:

Timpul mediu de aşteptare pentru intrări/ieşiri = 1710.51sec/20707441 aşteptări = 0.08 ms

Sistemul s-a comportat foarte bine din acest punct de vedere, probabil toate datele erau în

memoria cache.

Problema de performanţă poate fi atribuită în totalitate performanţei superioare a I/O din sistemul

vânzătorului

125

Totuşi aceste date pot proveni dintr-un test neconcludent (ne-reprezentativ) deoarece este

diferită de FTS observat în statspack. Aceste discrepanţe arată că acest test nu este

reprezentativ.

Din moment ce observăm comportamente la care nu ne aşteptăm, trebuie să anunţăm clientul de

suspiciunile apărute.

În urma discuţiei cu clientulul, am aflat că vânzătorul rulase testul folosind din greşeală variabile literale în

loc de cele de legătură (bând).

Observaţii:

Pentru test vânzătorul a utilizat variabile literale şi nu bând

Acest lucru a condus la faptul că CBO a ales scanarea completă

Când CBO avea cea mai bună informaţie a ales FTS, variabilele bând au derutat CBO-ul

Citirile scanării complete/dispersate din statspack erau datorită variabilelor literale?

CBO nu utilizează oricum variabile bind?

N-ar trebui că rezultatele să fie aceleaşi?

Constatăm existenţa unui bug sau aşa ar trebui să funcţioneze?

E. Analiza raportului 10053 a vânzătorului

În acesta analiză urmăream:

Dimensiunea obiectelor

Statisticile colectate

Costurile CBO-ului

Analiza raportului 10053 a vânzătorului

Costul CBO al căilor de acces utilizând variabilele literale

126

În acesta porţiune din 10053 putem efectua calculele costului pentru când sunt implicate variabilele

literale:

FTS: #Blocks / MBDivisor = 548958 / 16.404 = 33,465 (minus 1)

Index: LVL + (Sel * LB) + (Sel * CLUF) = 3 + (.495*283514)+(.495*39975474) = 19,928,202 ~

19,914,925

CBO a ales calea a 2-a, mai convenabilă, ceea ce indică faptul că s-a utilizat FTS.

Analiza raportului 10053 a vânzătorului

Costul CBO al căilor de acces utilizând variabilele literale

Observaţii:

FTS: #Blocks / MBDivisor = 548958 / 16.404 = 33,465 (minus 1 din nou)

Index: LVL + (Sel * LB) + (Sel * CLUF) = 3 + (.0045*283514) + (.0045*39975474) = 181,165 ~

181,169

În mod surprinzător acţiunile CBO au fost:

127

1. A ales “path 4” ce este o scanare pe bază de index deşi FTS ar for fost mai avantajoasă

2. A înlocuit costul pentru “path 4” cu cel al FTS minus 1.

F. Rezultatele analizei raportului 10053 provenind de la vânzător

Când CBO estimează greşit cardinalitatea predicatului, de obicei rezulta un plan de execuţie

inconvenabil. Pentru a determina dacă CBO a estimat greşit cardinalitatea, trebuie să comparăm

cardinalitatea calculată în 10053 cu cea actuală din raportul TKProf de când interogarea a fost executată.

Comparaţia cardinalitatii estimate fata de cea actuală:

o TKProf arată numărul exact de înregistrări vizitate ca fiind:

o Cu variabile literale, 10053 arată cardinalitatea estimată ca fiind:

o Cu variabile legate, 10053 arată cardinalitatea estimată ca fiind:

Utilizarea variabilelor literale, cardinalitatea calculată este mai mică cu 16% decât cea actuală

Folosind variabilele legate, cardinalitatea calculată este mai mică de 235 de ori!

Am concluzionat rapid despre calcului selectivităţii că este inexact.

Mutând atenţia pe sistemul clientului, am comparat datele acestuia cu cele ale vânzătorului.

G. Analiza raportului 10046/TKProf din sistemul clientului

10046 / TKProf din sistemul clientului (cel lent)

128

Această secţiune din raportul TKProf arata:

Timpul scurs a fost de 4793.62 sec = 79.8 min, ce este diferit de valoarea iniţială raportată, de 10

ore. De ce această discrepanţă?

Nu am fost returnată nici o înregistrare, vs. 12844 de înregistrări în sistemul vânzătorului. De ce?

23 mil de înregistrări vizitate fata de 62 mil în testul vânzătorului. De ce?

Aceste discrepante s-ar putea justifica, prin întreruperea rulării interogării de către client înainte

ca acesta să se fi finalizat.

Acelaşi plan de execuţie cu cel raportat în 10046 la vânzător: scanarea tabelului pe bază de

index.

10046 / TKProf din sistemul clientului (cel lent)

La examinarea acestei secţiuni se ridica următoarele întrebări:

Timpul mediu de aşteptare pentru I/O = 4242.57 sec / 5027407 aşteptări = 0.84 mSec

Performanţa I/O foarte bună, deşi este de 10 ori mai înceată decât arată 10046 în sistemul

vânzătorului.

o Se justifica urmărirea acestei probleme?

o Este dificil şi scump să obţinem o performanţa I/O mai bună decât acesta.

o îmbunătăţire a I/O va rezolva problema?

o Dacă I/O eră de 10 ori mai mare, rularea interogarea tot ar fi durat apox. o oră. Acest

timp este în continuare tot prea mare, deoarece ştim că la alegerea FTS, planul se

finalizează în 10 minute.

H. Analiza raportului 10053 provenit de la client

129

Analiza 10053 din sistemul clientului (lent)

Costul CBO al căilor de acces utilizând variabilele legate

Aceasta porţiune din fişierul de trace 10053 arată aceleaşi rezultate observate în 10053 al vânzătorului,

când interogarea nu utiliza FTS:

CBO alega o cale estimată ca fiind mai scumpă decât FTS.

CBO setează costul caii de acces la aceeaşi valoare că utilizând FTS. De ce?

I. Analiza rezultatelor raportului 10053 din sistemul clientului

Comparaţia cardinalitatii estimate vs. cea reală din sistemul clientului:

o Utilizând variabile legate, TKProf arată că numărul real de înregistrări vizitate este:

o Utilizând variabilele legate, 10053 arată cardinalitatea estimată ca fiind:

Cardinalitatea calculată este de 88 de ori mai mare.

o Probabil că diferenţa ar fi fost şi mai mare dacă cele 23 mil de înregistrări ar fi fost

arhivate chiar şi după abandonarea interogării.

130

Raportul 10046 al ambelor părţi ce a utilizat un index, a avut cardinalitati estimate foarte

inadecvate

Ce am observat în urma analizei:

Raportul 10046/Tkprof al clientului a arătat că utilizarea caii de acces bazată pe index nu este

optimă, în timp ce FTS da.

Rapoartele statspack şi 10053 ale vânzătorului arată că utilizau un plan FTS când performanţa

era bună.

Concluzie: uneori, vânzătorul a utilizat un plan bazat pe index, alteori bazat pe FTS

o Probabil FTS când s-au utilizat variabilele literale

o Este necesară o analiză ulterioară.

Ce am verificat până acum?

Accesul FTS asigura o performanţa mai bună decât utilizarea index-ului

Sistemul vânzătorului este mai rapid, dar nu la fel de rapid precum afurmasera iniţial, cel puţin nu

când planul bazat pe index a fost utilizat.

o Trebuiesc colectate date adiţionale de la vânzător.

I/O ale clientului sunt rapide (<1 mSec per I/O)

I/O ale vânzătorului sunt mult mai rapide, de 10 ori mai rapide.

Valorile literale au determinat CBO să aleagă planul mai bun FTS.

Varibilele legate au subestimat în mod constant selectivitatea şi utilizarea indexului corect în

planul de execuţie

Peeking-ul nu funcţionează pentru variabilele legate cu acesta interogăre, în caz contrar execuţia

planului cu variabilele legaţi şi cele literale ar fi fost identică când variabilele legate ar fi avut

valoarea literalelor.

În mod ciudat, CBO alege calea de acces utilizând index-ul, mai ineficienta, decât cea utilizând FTS.

131

Ce trebuie să facem de acum încolo?

Verificarea problemei nu este completă:

o Planurile de execuţie, 10046 şi 10053 ale vânzătorului se contraczic pre mult

o conectare la ambele sisteme ne-ar putea ajuta să lămurim situaţiile.

Totuşi, vom continua investigarea cu informatile deţinute:

o Presupunem că I/O sunt optime

o Planul de execuţie al cleintului nu este optim, cel FTS este.

o Bând peeking pare să nu funcţioneze.. bug?

o Alegerea costului CBO nu este raţional (a fost ales indexul în defavoarea FTS)

Determinarea cauzei latentei interogării utilizând informaţia disponibilă

De ce a ales CBO planul bazat pe index în timp ce FTs este optim?

o Aparent CBO ignora bând peeking. Încă nu ştim de ce. Ar putea fi un bug?

o Cardinalitatea estimată de CBO este eronată. Încă nu ştim ce ar putea cauza acest

comportament. Este un bug sau ar putea fi un caz când dependentă predicatelor

necesita mostre dinamice?

Acum vom încerca să eliminăm posibilitatea existenţei unor bug-uri în această versiune de Oracle prin

căutări în baza de date cu bug-uri. Criteriile căutări ar putea fi:

Cuvinte cheie: cbo bând peek

Produs: Oracle Server Enterprise Edition

Versiunea produsului: 9.2.0.3

Impact: query performance

Fixat în versiunea: 10

Concluzii

În urma căutării, bug-ul 3668224 pare asemănător cu problemele identificate în acest caz. Simptomele

bug-ului se referă tot la o performanţa slabă a interogării.

Ce am aflat din bug:

Simptomele bug-ului se potrivesc cu cele ale clientului:

o Planul este diferit (mai bun) când se utilizează literale, deci ştim că bând peeking nu a

funcţionat

o Bug-ul are referiri la COL <OP> FUNC(:bând)

§ Interogarea clientului avea aceeaşi construcţie:

where DT between to_date(:EDF_DATE…)

Bug-ul determina CBO să intuiască calcularea selectivităţii, din moment ce bând peeking nu

funcţionează

132

Intuirea calculării poate produce estimări eronate ale selectivităţii.

Calcularea selectivităţii este foarte inadecvata pentru aceasta interogare

o Utilizând variabile literale, CBO a estimat 52mil de înregistrări.. valoare apropiată de cea

reală

o Utilizând variabilele legate, CBO a estimat 267000 de înregistrări.. foarte greşit

o Concluzia: CBO intuieşte estimarea din cauza acestui bug.

Soluţii identificate pentru rezolvarea bug-ului 3668224

Aplicarea patch-ului 3668224 şi activarea acestuia prin setarea evenimentelor 38056 şi 38044.

o Aceasta determina ca variabilele bând din funcţii să utilizeze funcţionalitatea de peeking

o Pro: ar putea rezolva şi alte probleme potenţiale cu alte interogări

o Contra: trebuie aplicat un pach (pachet reparator).

Se poate utiliza un stored outline ce impune scanarea completă a tabelei.

o Pro: problema se va rezolva imediat, nu este necesară instalarea pachetului reparator.

o Contra: multe alte interogări ar putea avea nevoie de outline-uri

Modificarea interogării utilizând hint-uri

o Pro: problema se va rezolva imediat, nu este necesară instalarea pachetului reparator

o Contra: ar trebui modificate aplicaţiile auxiliare.

S-a ales optinea instalării pachetului reparator. Cu pachetul instalat şi evenimentele setate, a fost ales

planul de execuţie favorabil (utilizând FTS).

Dar care este explicaţia pentru costul utilizării inex-ului fiind identic cu cel al utilizării FTS pentru situaţia

când variabilele de legătură nu foloseau funcţionalitatea de peeking?

Pentru acesta întrebarea, soluţia este bug-ul 4341508 ce prezentă următoarele simptome:

CBO favorizează accesul pe bază de index atunci când “intuieşte” valorile (variabilele de legătură

ce nu utilizează funcţionalitatea de peeking determina CBO-ul să intuiască valori).

Când predicatul implica o valoare intuită şi CBO nu utilizează primele K înregistrări, atunci:

o CBO calculează costul utilizării metodei FTS

o În cazul în care costul accesului este mai mare decât cel utilizând FTS, valoarea acestuia

va deveni egală cu costul FTS. Acesta este motivul pentru care observasem valorile

similare, minus 1.

Din moment ce _TABLE_SCAN_COST_PLUS_ONE=true, costul FTS era întotdeauna cu doar 1

mai mare decât costul estimat. Aceasta setare, cumulata cu explicaţia anterioară, determina

costul FTS să fie întotdeauna cu 1 mai mare decât valoarea pentru index. Acest fapt a determinat

alegerea caii de acces pe bază de index şi nu a FTS-ului.

133

Trebuie aplicat un pachet reparator pentru acest comportament?

Răspunsul este, cel mai probabil, nu. Acesta este comportamentul intenţionat pentru sistem. Indecşii ar

trebui să fie aleşi în majoritatea cazurilor când sunt implicate variabilele de legătură. Multe aplicaţii OLAP

sunt modelate să utilizeze variabile de legătura în cazul concurentei ridicate cu indecşii coloanelor de

returnează multe înregistrări.

Aplicaţia acestui client, era puţin neobişnuită, deoarece utiliza variabile bând pentru o interogare de

returna multe înregistrări (>1800).

Bând peeking a rezolvat această problemă.

În cazul acestui client, aplicarea pachetului reparator, a determinat activarea funcţionalităţii de peeking a

variabilelor de legătura utilizate în funcţii, ce a ce a produs o estimare adecvată şi un plan de execuţie

bun.

134

Concluzii

Tendinţa actuală pune mare accent pe capacitatea de reacţie la apariţia unor probleme de performanţă,

având drept coordonată principală timpul de rezolvare.

Se impune existenţa unei metode de investigare / diagnosticare, nu doar a unei analize rapide a

problemelor, şi, in plus, identificarea sursei acestora la momentul apariţie.

Utilizând metoda de diagnosticare prezentată (Identificarea problemei, Verificarea problemei, Analiza,

Determinarea cauzei, Verificarea cauzei, Analiza, Identificarea şi implementarea soluţiei), ar trebui să

putem să ne concentrăm rapid eforturile investigaţiei şi să identificăm expertiza sau seturile de abilităţi

necesare pentru rezolvarea problemei de performanţă.

Aceasta lucrare nu se concentreaza pe soluţii oferite pentru probleme de preformanţă specifice, ci

prezintă doar câteva modele de optimizare. Fiecare tip de problemă în parte necesită o expertiză de nişa

pentru o soluţionare optimă.

Diversificarea aplicaţiilor şi a tehnologiilor utilizate în dezvoltarea lor are implicaţii tot mai mari în

abordarea globală a problemelor de performanţă, adresându-se tuturor componentelor unui sistem.

Calific această abordare ca fiind completă, deoarece este singura modalitate de a investiga probleme de

performanţă complexe ce pot avea implicaţi factori din componente diferite ale sistemului.

Pentru a scurta procesul investigării trebuie să utilizăm o abordare ce evalueaza rapid performanţele

fiecarei componente din sistem (sistemul de operare, reţeaua, aplicaţia, baza de date, library cache, o

interogare particulară, etc, pâna la nivele fine de granularitate). Cu alte cuvinte, fiind o abordare ce

consideră sistemul ca un tot unitar.

Ce metodologie se alege pentru diagnosticarea unei probleme de performanţă, este o decizie personală,

dar metodologia prezentată în acesta lucrare este una verificată şi utilizată cu succes în ultimii ani de

Oracle Corporation.

Această metodologie documentează o abordare bazată pe experienţa reala, ce a evoluat prin rezolvarea

numeroaselor situaţii complexe, dintre care multe critice sau disperate.

Statistic, s-a demostrat că utilizarea acestei metodologii bazate pe principiul cauzalităţii al lui Einstein,

reduce eforturile şi timpul de urmărire şi identificare a problemelor de performanţă din sistem.

Acestă metodologie nu doar formalizeaza procesul de definire a problemei, ci oferă şi avantajul corelării

efectelor cu posibilele consecinţe ale unei performante indezirabile şi pentru alte componente din sistem.

135

Alocarea de resurse fizice sumplimentare este eficientă în prea puţine cazuri de probleme de

performanţă.

O astfel de metodologie este şi un bun mediator între diferitele echipe implicate în funcţionarea sistemului

(dezvoltatorii aplicaţiei, administratorii bazei de date, administratorii de sistem, asistenţa tehnică,

personalul de la vânzări, etc.), acesta metoda oferind dovezi şi o documentare exacta a identificării ariei

de apariţie a problemei şi verificarea acesteia.

O abordare standard a unei probleme de performanţă are doua strategii. Prima, ar fi plasarea problemei

în grija unui expert în tuning/optimizare ce are cunostinţe solide despre utilitarele pentru optimizare

Oracle, iar a doua ar fi suplimentarea resurselor hardware.

Totuşi, nici una dintre aceste două strategii nu este o soluţie pe termen lung, deoarece ele se adresează

simptomelor şi nu cauzelor ce au generat aceste probleme.

Problemele de performanţă pot fi localizate şi identificate relativ usor. Valoarea metodei descrise în

aceasta lucrare este data de faptul că în timp ce este nevoie de un expert într-o anumită tehnologie

pentru rezolvarea problemei, nu trebuie sa fii un expert pentru a identifica o problemă. Obiectivul este de

a orienta scopul investigaţiei spre proceduri cat mai rapide şi eficiente, şi identificarea timpurie a

resurselor necesare pentru rezolvarea problemei (personal: expertul în probleme ale sistemului de

operare, expertul în optimizare SQL, servere de test, timp înafara producţiei pentru testarea soluţiilor

finale, etc.).

Deşi optimizarea unui sistem este o stiinţă, multe din aspectele acestei activităţi ţin de bunul simţ.

Concluzia finală este că o problema definită precis şi complet este cheia identificării problemelor de

performanţă şi va ajuta orientarea eforturilor de optimizare spre atingerea rezultatelor dorite. Cele mai

bune rezultate se obţin prin optimizarea iniţiala a clientului, apoi a bazei de date şi a serverului, în acesta

ordine. Prelucrşrile pe loturi şi rapoartele ar trebui reprogramate pentru minimizarea efectului asupra

utilizatorilon on-line înaintea optimizării interogărilor şi a cailor de acces la date.

Pe parcursul operaţiei de optimizare se vor efectua modificări şi se vor măsura efectele. Când întregul

sistem este optimizat, se vor seta din nou valorile de referinţă (benchmark). Dacă rezultatele dorite încă

nu au fost atinse, se vor re-evalua complet înainte de a propune adăugarea de resurse fizice

sulplimentare. Intotdeauna trebuie să investigăm toate ariile sistemului, adăugarea de resurse

sumplimentare fiind ultima soluţie.

136

Anexa 1

show_blocking_sessions.sql – arata utilizatorii ce aşteaptă o rezervare (lock), agentul de blocare şi

comanda SQL aşteptată, schema şi PID-urile.81

rem -------------------------------------------------------------------------

rem Show users waiting for a lock, the locker and the

rem SQL-Command they are waiting for a lock

rem osuser, schema and PIDs are shown

rem -----------------------------------------------------------------------

rem

--

SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON

COLUMN OS_LOCKER FORMAT A15 TRUNC

COLUMN OS_WAITER FORMAT A15 TRUNC

COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC

COLUMN WAITER_SCHEMA FORMAT A15 TRUNC

COLUMN WAITER_PID FORMAT A10

COLUMN LOCKER_PID FORMAT A10

COLUMN SQL_TEXT_WAITER FORMAT A100 WRAP

COLUMN DATABASE NOPRINT NEW_VALUE DATABASE

COLUMN DATUM_ZEIT NOPRINT NEW_VALUE DATUM_ZEIT

SET TERMOUT OFF ECHO OFF FEED OFF

SET TERMOUT ON

TTITLE CENTER 'Current Lock-Waits' SKIP 2

--

select /*+ ordered

no_merge(L_WAITER)

no_merge(L_LOCKER) use_hash(L_LOCKER)

no_merge(S_WAITER) use_hash(S_WAITER)

no_merge(S_LOCKER) use_hash(S_LOCKER)

use_nl(O)

use_nl(U)

*/

/* first the table-level locks (TM) and mixed TM/TX TX/TM */

S_LOCKER.OSUSER OS_LOCKER,

S_LOCKER.USERNAME LOCKER_SCHEMĂ,

S_LOCKER.PROCESS LOCKER_PID,

S_WAITER.OSUSER OS_WAITER,

S_WAITER.USERNAME WAITER_SCHEMĂ,

S_WAITER.PROCESS WAITER_PID,

81 http://www.akadia.com/download/documents/show_blocking_sessions.sql , accesat la data 25.01.2010

137

http://www.akadia.com/download/documents/show_blocking_sessions.sql

'Table lock (TM): '||U.NAME||'.'||O.NAME||

' - Mode held: '||

decode(L_LOCKER.LMODE,

0, 'None', /* same aş Monitor */

1, 'Null', /* N */

2, 'Row-S (SS)', /* L */

3, 'Row-X (SX)', /* R */

4, 'Share', /* S */

5, 'S/Row-X (SSX)', /* C */

6, 'Exclusive', /* X */

'???: '||to_char(L_LOCKER.LMODE))||

' / Mode requested: '||

decode(L_WAITER.REQUEST,

0, 'None', /* same aş Monitor */

1, 'Null', /* N */

2, 'Row-S (SS)', /* L */

3, 'Row-X (SX)', /* R */

4, 'Share', /* S */

5, 'S/Row-X (SSX)', /* C */

6, 'Exclusive', /* X */

'???: '||to_char(L_WAITER.REQUEST))

SQL_TEXT_WAITER

from

V$LOCK L_WAITER,

V$LOCK L_LOCKER,

V$SESSION S_WAITER,

V$SESSION S_LOCKER,

OBJ$ O,

USER$ U

where S_WAITER.SID = L_WAITER.SID

and L_WAITER.TYPE ÎN ('TM')

and S_LOCKER.sid = L_LOCKER.sid

and L_LOCKER.ID1 = L_WAITER.ID1

and L_WAITER.REQUEST > 0

and L_LOCKER.LMODE > 0

and L_WAITER.ADDR != L_LOCKER.ADDR

and L_WAITER.ID1 = O.OBJ#

and U.USER# = O.OWNER#

union

select /*+ ordered

no_merge(L_WAITER)

no_merge(L_LOCKER) use_hash(L_LOCKER)

138

no_merge(S_WAITER) use_hash(S_WAITER)

no_merge(S_LOCKER) use_hash(S_LOCKER)

no_merge(L1_WAITER) use_hash(L1_WAITER)

no_merge(O) use_hash(O)

*/

/* now the (usual) row-locks TX */

S_LOCKER.OSUSER OS_LOCKER,

S_LOCKER.USERNAME LOCKER_SCHEMĂ,

S_LOCKER.PROCESS LOCK_PID,

S_WAITER.OSUSER OS_WAITER,

S_WAITER.USERNAME WAITER_SCHEMĂ,

S_WAITER.PROCESS WAITER_PID,

'TX: '||O.SQL_TEXT SQL_TEXT_WAITER

from

V$LOCK L_WAITER,

V$LOCK L_LOCKER,

V$SESSION S_WAITER,

V$SESSION S_LOCKER,

V$_LOCK1 L1_WAITER,

V$OPEN_CURSOR O

where S_WAITER.SID = L_WAITER.SID

and L_WAITER.TYPE ÎN ('TX')

and S_LOCKER.sid = L_LOCKER.sid

and L_LOCKER.ID1 = L_WAITER.ID1

and L_WAITER.REQUEST > 0

and L_LOCKER.LMODE > 0

and L_WAITER.ADDR != L_LOCKER.ADDR

and L1_WAITER.LADDR = L_WAITER.ADDR

and L1_WAITER.KADDR = L_WAITER.KADDR

and L1_WAITER.SADDR = O.SADDR

and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE

/

TTITLE OFF

COLUMN OS_LOCKER CLEAR

COLUMN OS_WAITER CLEAR

COLUMN LOCKER_SCHEMA CLEAR

COLUMN WAITER_SCHEMA CLEAR

COLUMN WAITER_PID CLEAR

COLUMN LOCKER_PID CLEAR

COLUMN SQL_TEXT_WAITER CLEAR

COLUMN DATABASE CLEAR

COLUMN DATUM_ZEIT CLEAR

139

Anexa 2

Utllockt.sql82

rem

rem $Header: utllockt.sql 21-jan-2003.16:21:56 bnnguyen Exp $ locktree.sql

rem

Rem Copyright (c) 1989, 2003, Oracle Corporation. All rights reserved.

Rem NAME

REM UTLLOCKT.SQL

Rem FUNCTION - Prinţ out the lock wait-for graph în tree structured fashion.

Rem This is useful for diagnosing systems that are hung on locks.

Rem NOTES

Rem MODIFIED

Rem bnnguyen 01/21/03 - bug2166717

Rem pgreenwa 04/27/95 - fix column definitions for LOCK_HOLDERS

Rem pgreenwa 04/26/95 - modify lock_holders query to use new dba_locks f

Rem glumpkin 10/20/92 - Renamed from LOCKTREE.SQL

Rem jloaiza 05/24/91 - update for v7

Rem rlim 04/29/91 - change char to varchar2

Rem Loaiza 11/01/89 - Creation

Rem

/* Print out the lock wait-for graph în a tree structured fashion.

*

* This script prints the sessions în the system that are waiting for

* locks, and the locks that they are waiting for. The printout is tree

* structured. If a sessionid is printed immediately below and to the right

* of another session, then it is waiting for that session. The session ids

* printed at the left hand side of the page are the ones that everyone îs

* waiting for.

*

* For example, în the following printout session 9 is waiting for

* session 8, 7 is waiting for 9, and 10 is waiting for 9.

*

* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2

* ----------------- ---- ----------------- ----------------- -------- --------

* 8 NONE None None 0 0

* 9 TX Share (S) Exclusive (X) 65547 16

82 http://www.akadia.com/services/scripts/utllockt.txt , accesat la data 25.01.2010

141

http://www.akadia.com/services/scripts/utllockt.txt

* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2

* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2

*

* The lock information to the right of the session id describes the lock

* that the session is waiting for (not the lock it is holding).

*

* Note that this is a script and not a set of view definitions because

* connect-by is used în the implementation and therefore a temporary table

* is created and dropped since you cannot do a join în a connect-by.

*

* This script has two small disadvantages. One, a table is created when

* this script is run. To create a table a number of locks must be

* acquired. This might cause the session running the script to get caught

* în the lock problem it is trying to diagnose. Two, if a session waits on

* a lock held by more than one session (share lock) then the wait-for graph

* is no longer a tree and the conenct-by will show the session (and any

* sessions waiting on it) several times.

*/

/* Select all sids waiting for a lock, the lock they are waiting on, and the

* sid of the session that holds the lock.

* UNION

* The sids of all session holding locks that someone is waiting on that

* are not themselves waiting for locks. These are included so that the roots

* of the wait for graph (the sessions holding things up) will be displayed.

*/

drop table lock_holders;

create table LOCK_HOLDERS /* temporary table */

(

waiting_session number,

holding_session number,

lock_type varchar2(26),

mode_held varchar2(14),

mode_requested varchar2(14),

lock_id1 varchar2(22),

lock_id2 varchar2(22)

);

drop table dba_locks_temp;

create table dba_locks_temp aş select * from dba_locks;

142

/* This is essentially a copy of the dba_waiters view but runs faster since

* it caches the result of selecting from dba_locks.

*/

insert into lock_holders

select w.session_id,

h.session_id,

w.lock_type,

h.mode_held,

w.mode_requested,

w.lock_id1,

w.lock_id2

from dba_locks_temp w, dba_locks_temp h

where h.blocking_others = 'Blocking'

and h.mode_held != 'None'

and h.mode_held != 'Null'

and w.mode_requested != 'None'

and w.lock_type = h.lock_type

and w.lock_id1 = h.lock_id1

and w.lock_id2 = h.lock_id2;

commit;

drop table dba_locks_temp;

insert into lock_holders

select holding_session, null, 'None', null, null, null, null

from lock_holders

minus

select waiting_session, null, 'None', null, null, null, null

from lock_holders;

commit;

column waiting_session format a17;

column lock_type format a17;

column lock_id1 format a17;

column lock_id2 format a17;

/* Print out the result în a tree structured fashion */

select lpad(' ',3*(level-1)) || waiting_session waiting_session,

lock_type,

mode_requested,

143

mode_held,

lock_id1,

lock_id2

from lock_holders

connect by prior waiting_session = holding_session

start with holding_session is null;

drop table lock_holders;

144

Anexa 3

show_dml_locks.sql83

rem -------------------------------------------------------------------------

rem Shows actual DML-Locks (incl. Table-Name)

rem WAIT = YES are users waiting for a lock

rem -----------------------------------------------------------------------

rem

--


COLUMN PROGRAM FORMAT A80 TRUNC

COLUMN LOCKER FORMAT A10 TRUNC

COLUMN T_OWNER FORMAT A10 TRUNC

COLUMN OBJECT_NAME FORMAT A25 TRUNC

COLUMN WAIT FORMAT A4

TTITLE "Actual DML-Locks (TM+TX)"

--

select /*+ rule */

decode(L.REQUEST,0,'NO','YES') WAIT,

S.OSUSER,

S.PROCESS,

S.USERNAME LOCKER,

U.NAME T_OWNER,

O.NAME OBJECT_NAME,

' '||S.PROGRAM PROGRAM

from V$LOCK L,

V$SESSION S,

OBJ$ O,

USER$ U

where U.USER# = O.OWNER#

and S.SID = L.SID

and L.ID1 = O.OBJ#

and L.TYPE = 'TM'

union

select decode(L.REQUEST,0,'NO','YES') WAIT,

S.OSUSER,

S.PROCESS,

S.USERNAME LOCKER,

'-',

'Record(s)',

83 http://www.akadia.com/download/documents/show_dml_locks.sql , accesat la data 25.01.2010

145

http://www.akadia.com/download/documents/show_dml_locks.sql


from V$LOCK L,

V$SESSION S

where S.SID = L.SID

and L.TYPE = 'TX'

order by 7,5,1,2,6

/

ttitle off

col program clear

col locker clear

col t_owner clear

col object_name clear

col wait clear

146

Anexa 4

Show_missing_fk_index.sql84

rem -------------------------------------------------------------------------

rem Shows actual DML-Locks (incl. Table-Name)

rem WAIT = YES are users waiting for a lock

rem -----------------------------------------------------------------------

rem

--


COLUMN PROGRAM FORMAT A80 TRUNC

COLUMN LOCKER FORMAT A10 TRUNC

COLUMN T_OWNER FORMAT A10 TRUNC

COLUMN OBJECT_NAME FORMAT A25 TRUNC

COLUMN WAIT FORMAT A4

TTITLE "Actual DML-Locks (TM+TX)"

--

select /*+ rule */

decode(L.REQUEST,0,'NO','YES') WAIT,

S.OSUSER,

S.PROCESS,

S.USERNAME LOCKER,

U.NAME T_OWNER,

O.NAME OBJECT_NAME,


from V$LOCK L,

V$SESSION S,

OBJ$ O,

USER$ U

where U.USER# = O.OWNER#

and S.SID = L.SID

and L.ID1 = O.OBJ#

and L.TYPE = 'TM'

union

select decode(L.REQUEST,0,'NO','YES') WAIT,

S.OSUSER,

S.PROCESS,

S.USERNAME LOCKER,

'-',

84 http://www.akadia.com/download/documents/show_dml_locks.sql , accesat la data 25.01.2010

147

http://www.akadia.com/download/documents/show_dml_locks.sql

'Record(s)',


from V$LOCK L,

V$SESSION S

where S.SID = L.SID

and L.TYPE = 'TX'

order by 7,5,1,2,6

/

ttitle off

col program clear

col locker clear

col t_owner clear

col object_name clear

col wait clear

148

Anexa 5

bhla.sql85

----------------------------------------------------------------------------------------------------------

--

-- File name: bhla.sql (Buffer Headers by Latch Address)

-- Purpose: Report which blocks are în buffer cache, protected by a cache

-- buffers chains child latch

--

-- Author: Tanel Poder

-- Copyright: (c) http://www.tanelpoder.com

--

-- Usage: @bhla <child latch address>

-- @bhla 27E5A780

--

--

-- Other: This script reports all buffers "under" the given cache buffers

-- chains child latch, their corresponding segment names and

-- touch counts (TCH).

--

-------------------------------------------------------------------------------------------------------

col bhla_object head object for a40 truncate

col bhla_DBA head DBA for a20

select /*+ ORDERED */

trim(to_char(bh.flag, 'XXXXXXXX')) ||':'||

trim(to_char(bh.lru_flag, 'XXXXXXXX')) flg_lruflg,

bh.obj,

o.object_type,

o.owner||'.'||o.object_name bhla_object,

bh.tch,

file# ||' '||dbablk bhla_DBA,

bh.class,

bh.state,

bh.mode_held,

bh.dirty_queue DQ

from

x$bh bh,

dba_objects o

85 http://www.tanelpoder.com/files/scripts/bhla.sql , accesat la data 25.01.2010

149

http://www.tanelpoder.com/files/scripts/bhla.sql

where

bh.obj = o.data_object_id

and hladdr = hextoraw(lpad('&1', vsize(hladdr)*2 , '0'))

order by

tch asc

/

150

Bibliografie

1. Automatic SQL Tuning - SQL Profiles [ID 271196.1], https://support.oracle.com/CSP/main/article?cmd=show&id=271196.1&type=NOT , accesat la data 25.01.2010

2. Best Practices for automatic statistics collection on Oracle 10g [ID 377152.1], https://support.oracle.com/CSP/main/article?cmd=show&id=377152.1&type=NOT , accesat la data 25.01.2010

3. Best Practices for automatic statistics collection on Oracle 10g [ID 377152.1], https://support.oracle.com/CSP/main/article?cmd=show&id=377152.1&type=NOT , accesat la data 25.01.2010

4. Database Initialization Parameters for Oracle Applications Release 12 [ID 396009.1],5. Database Performance - FAQ [ID 402983.1], https://support.oracle.com/CSP/main/article?

cmd=show&id= 402983.1 &type=NOT , , accesat la data 23.01.20106. FAQ- Statspack Complete Reference [ID 94224.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=94224.1&type=NOT , accesat la data 24.01.20107. Forcing a Known Plan Using Hints [ID 221970.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=221970.1&type=NOT , accesat la data 25.01.20108. Forcing a Known Plan Using Hints [ID 221970.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=221970.1&type=NOT , accesat la data 25.01.20109. HANGFG User Guide [ID 362094.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=362094.1&type=NOT , accesat la data 25.01.201010. Histograms: An Overview [ID 1031826.6], https://support.oracle.com/CSP/main/article?

cmd=show&id=1031826.6&type=NOT , accesat la data 25.01.201011. Histograms: An Overview [ID 1031826.6], https://support.oracle.com/CSP/main/article?

cmd=show&id=1031826.6&type=NOT , accesat la data 25.01.201012. How to check what automatic statistics collection is scheduled on 10g [ID 377143.1],


13. How to check what automatic statistics collection is scheduled on 10g [ID 377143.1], https://support.oracle.com/CSP/main/article?cmd=show&id=377143.1&type=NOT , accesat la data 25.01.2010

14. How to Identify Resource Intensive SQL for Tuning [ID 232443.1], https://support.oracle.com/CSP/main/article?cmd=show&id=232443.1&type=NOT

15. How to specify an INDEX Hint [ID 50607.1], https://support.oracle.com/CSP/main/article?cmd=show&id=50607.1&type=NOT , accesat la data 25.01.2010

16. How to specify an INDEX Hint [ID 50607.1], https://support.oracle.com/CSP/main/article?cmd=show&id=50607.1&type=NOT , accesat la data 25.01.2010

17. How to Tune a Query that Cannot be Modified [ID 122812.1], https://support.oracle.com/CSP/main/article?cmd=show&id=122812.1&type=NOT , accesat la data 25.01.2010



20.How To Use Automatic Shared Memory Management (ASMM) În Oracle10g [ID 295626.1], https://support.oracle.com/CSP/main/article?cmd=show&id=295626.1&type=NOT , accesat la data 25.01.2010

21. How to use the Sql Tuning Advisor. [ID 262687.1], https://support.oracle.com/CSP/main/article?cmd=show&id=262687.1&type=NOT , accesat la data 25.01.2010

22. How to use the Sql Tuning Advisor. [ID 262687.1], https://support.oracle.com/CSP/main/article?cmd=show&id=262687.1&type=NOT , accesat la data 25.01.2010

23. http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/ sql_1016.htm#sthref1060 , accesat la data 25.01.2010

24. https://support.oracle.com/CSP/main/article?cmd=show&id=175006.1&type=NOT , accesat la data 25.01.2010

151





































25. https://support.oracle.com/CSP/main/article?cmd=show&id=396009.1&type=NOT , accesat la data 25.01.2010 Oracle® Database Performance Tuning Guide 10g Release 2 (10.2), Chapert 11 – SQL Tuning Overview,

26. Initialization Parameters, http://www.adp-gmbh.ch/ora/misc/init_params.html , accesat la data 25.01.2010

27. Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1], https://support.oracle.com/CSP/main/article?cmd=show&id=215858.1&type=NOT , accesat la data 25.01.2010

28. LTOM - The On-Board Monitor User Guide [ID 352363.1], https://support.oracle.com/CSP/main/article?cmd=show&id=352363.1&type=NOT , accesat la data 25.01.2010



31. Methods for Obtaining a Formatted Explain Plan [ID 235530.1], https://support.oracle.com/CSP/main/article?cmd=show&id=235530.1&type=NOT

32. Oracle Database 10g Automated SGA Memory Tuning [ID 257643.1], https://support.oracle.com/CSP/main/article?cmd=show&id=257643.1&type=NOT, accesat la data 25.01.2010

33. Oracle® Database 2 Day DBA 10g Release 2 (10.2) - Using the SQL Tuning Advisor, http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref784 , accesat la data 25.01.2010

34. Oracle® Database 2 Day DBA 10g Release 2 (10.2) - Using the SQL Tuning Advisor, http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref784 , accesat la data 25.01.2010

35. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12 Automatic SQL Tuning, http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010

36. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12 Automatic SQL Tuning, http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010

37. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 12.2.4 Using SQL Tuning Advisor APIs, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i34827 , accesat la data 25.01.2010

38. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm , accesat la data 25.01.2010

39. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, Automatic Shared Memory Management, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#CNCPT810 , accesat la data 25.01.2010

40. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 8 – Memory Architecture, Using Automatic Shared Memory Management, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#BABGCBHI , accesat la data 25.01.2010

41. Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2), Chapter 7 – Memory Confiiguration and Use, 7.3 – Configuring and Using the Shared Pool and Large Pool, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i30970 , accesat la data 25.01.2010

42. Oracle® Database Performance Tuning Guide 10g Release 1 (10.1), http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#8723 , accesat la data 24.01.2010

43. Oracle® Database Performance Tuning Guide 10g Release 1 (10.1), Using the SQL Trace Facility and TKPROF, http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#8723 , accesat la data 24.01.2010

152



http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i30970

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#BABGCBHI

















http://www.adp-gmbh.ch/ora/misc/init_params.html


44. Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 12 Automatic SQL Tuning http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028, accesat la data 25.01.2010

45. Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 12 Automatic SQL Tuning, http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#PFGRF028 , accesat la data 25.01.2010

46. Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 16 – Using Optimizer Hintes, 16.1.2.1 Hints for Optimization Approaches and Gols, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDFIAJD , accesat la data 25.01.2010

47. Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 16 – Using Optimizer Hintes, 16.1.2.2 Hints for Access Paths, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDJDIAH , accesat la data 25.01.2010

48. Oracle® Database Performance Tuning Guide 10g Release 2 (10.2), http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/perf_overview.htm#i1006218, accesat la data 23.01.2010

49. Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 16 Using Optimizer Hints http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602 , accesat la data 25.01.2010

50. Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 18 Using Plan Stability,


51. Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 16 Using Optimizer Hints, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602 , accesat la data 25.01.2010

52. Oracle® Database Performance Tuning Guide 10g, Release 2 (10.2) – Chapter 18 Using Plan Stability, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#sthref1787 , accesat la data 25.01.2010

53. Oracle® Database Performance Tuning Guide, 10g Release 2 (10.2) – Chapter 12 – Automatic SQL tuning, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i22019

54. Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) – Chapter 101 DBMS_SQLTUNE, http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#sthref7584 , accesat la data 25.01.2010

55. Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) – Chapter 132 – DBMS_XPLAIN – Summary of DBMS_XPLAIN Subprorams http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i996786 , accesat la data 25.01.2010

56. Oracle® Database Reference 10g Release 2 (10.2), Initialization Parameter Description, pagina 97, http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237.pdf

57. Oracle® Enterprise Manager Concepts 10g Release 5 (10.2.0.5), http://download-west.oracle.com/docs/cd/B19306_01/em.102/b31949/host_3rdparty_management.htm#sthref1530 , accesat la data 25.01.2010

58. Oracle® Enterprise Manager Concepts 10g Release 5 (10.2.0.5), http://download-west.oracle.com/docs/cd/B19306_01/em.102/b31949/host_3rdparty_management.htm#sthref1530 , accesat la data 25.01.2010

59. OS Watcher User Guide [ID 301137.1], https://support.oracle.com/CSP/main/article?cmd=show&id=301137.1&type=NOT, accesat la data 25.01.2010

60. OS Watcher User Guide [ID 301137.1], https://support.oracle.com/CSP/main/article?cmd=show&id=301137.1&type=NOT, accesat la data 25.01.2010

61. QREF: SQL Statement HINTS [ID 29236.1], https://support.oracle.com/CSP/main/article?cmd=show&id=29236.1&type=NOT , accesat la data 25.01.2010

62. QREF: SQL Statement HINTS [ID 29236.1], https://support.oracle.com/CSP/main/article?cmd=show&id=29236.1&type=NOT , accesat la data 25.01.2010

63. Recommendations for Gathering Optimizer Statistics on 10g [ID 605439.1], https://support.oracle.com/CSP/main/article?cmd=show&id=605439.1&type=NOT , accesat la data 25.01.2010

153

















http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i996786

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#sthref7584


















64. Recommendations for Gathering Optimizer Statistics on 10g [ID 605439.1], https://support.oracle.com/CSP/main/article?cmd=show&id=605439.1&type=NOT , accesat la data 25.01.2010

65. Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1], https://support.oracle.com/CSP/main/article?cmd=show&id=376442.1&type=NOT , accesat la data 24.01.2010

66. Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1], https://support.oracle.com/CSP/main/article?cmd=show&id=376442.1&type=NOT , accesat la data 25.01.2010

67. Remote Diagnostic Agent (RDA) 4 - Main Man Page [ID 330364.1], https://support.oracle.com/CSP/main/article?cmd=show&id=330364.1&type=NOT , accesat la data 25.01.2010

68. Resolving locking issues using utllockt.sql script [ID 166534.1], https://support.oracle.com/CSP/main/article?cmd=show&id=166534.1&type=NOT , accesat la data 25.01.2010

69. Shared pool sizing in 10g [ID 270935.1], https://support.oracle.com/CSP/main/article?cmd=show&id=270935.1&type=NOT , accesat la data 25.01.2010

70. SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1], https://support.oracle.com/CSP/main/article?cmd=show&id=215187.1&type=NOT

71. Statistics Gathering: Frequency and Strategy Guidelines [ID 44961.1], https://support.oracle.com/CSP/main/article?cmd=show&id=44961.1&type=NOT , accesat la data 25.01.2010

72. Statistics Gathering: Frequency and Strategy Guidelines [ID 44961.1], https://support.oracle.com/CSP/main/article?cmd=show&id=44961.1&type=NOT , accesat la data 25.01.2010

73. Steps to generate HANGANALYZE trace files [ID 175006.1], 74. Stored Outline Quick Reference [ID 67536.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=67536.1&type=NOT , accesat la data 25.01.201075. Stored Outline Quick Reference [ID 67536.1], https://support.oracle.com/CSP/main/article?

cmd=show&id=67536.1&type=NOT , accesat la data 25.01.201076. THE COE PERFORMANCE METHOD [ID 190124.1] , Roger Snowden


77. Understanding and Tuning the Shared Pool [ID 62143.1], https://support.oracle.com/CSP/main/article?cmd=show&id=62143.1&type=NOT , accesat la data 25.01.2010

78. Using Statspack to Record Explain Plan Details [ID 209197.1], https://support.oracle.com/CSP/main/article?cmd=show&id=209197.1&type=NOT

79. VIEW: "V$LOCK" Reference Note [ID 29787.1], https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=29787.1

80. WAITEVENT: "enqueue" Reference Note (Doc ID 34566.1), https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=34566.1 , accesat la data 25.01.2010

81. What is the Oracle Diagnostic Methodology (ODM)? [ID 553866.1] https://support.oracle.com/CSP/main/article?cmd=show&id= 553866.1 &type=NOT , accesat la data 23.01.2010

82. Why didn't my parallel query use the expected number of slaves? [ID 199272.1], https://support.oracle.com/CSP/main/article?cmd=show&id=199272.1&type=NOT , accesat la data 25.01.2010

83. Yet Another Performance Profiling Method (Or YAPP-Method) [ID 148518.1], https://support.oracle.com/CSP/main/article?cmd=show&id=148518.1&type=NOT, accesat la data 25.01.2010

154
























Maria Rus - Tehnici de diagnisticare şi tuning a performanţelor bazelor de date Oracle

Documents

Transcript of Maria Rus - Tehnici de diagnisticare şi tuning a performanţelor bazelor de date Oracle