V Konferencja PLOUG
Zakopane
Październik 1999

Poszukiwanie zabójców procesora

Jarosław Łagowski

IFS Poland

Autor:

Absolwent Wydziału Matematyki, Informatyki i Mechaniki Uniwersytetu Warszawskiego (1992). Od lipca
1992 zatrudniony w firmie UNIDATAX (VAR Oracle) jako programista, później jako administrator danych.
Od grudnia 1994 w IFS Poland (VAR Oracle) jako programista. Obecnie zatrudniony na stanowisku
specjalisty ds. Oracle.

Streszczenie:

Referat pokaże jak. przy pomocy standardowych narzędzi dostępnych dla DBA, takich jak tablice
dynamiczne (V$...), pakiety standardowe (DMBS_...) i TKPROF wskazać w czasie działania na bazie danych
wielu sesji równoległych, te sesje które są "zabójcami" procesora i ustalić miejsca w kodzie aplikacji
wymagające poprawienia. Referat ograniczy się do wskazania metody wyszukiwania kodu wymagającego
strojenia. Strojenie kodu będzie ograniczone do pokazania przykładu.

2

Zintegrowany system obsługujący przedsiębiorstwo oznacza najczęściej
współdziałanie systemów Finansowego, Kadrowego, Magazynowego,
Remontowego i innych na jednym serwerze, na jednej bazie danych. Serwer
został wyposażony w odpowiednią ilość pamięci operacyjnej, mocne procesory,
wystarczającą ilość dysków i jest wspaniale - do czasu. W miarę gromadzenia
danych, administrator z przerażeniem zauważa, że czas wolny procesorów coraz
częściej jest bliski zera. W końcu, zdarza się sytuacja, kiedy system "stoi",
wykazując przez kilka lub kilkanaście minut 100 - procentowe wykorzystanie
procesorów. Strojenie ogólne bazy danych (SGA, równoważenie obciążenia
dysków itp.) nie pomaga. Należy zatem odszukać miejsca w aplikacji (raczej w
aplikacjach) które wymagają dostrojenia. Czasami wystarczy porozmawiać z
użytkownikami końcowymi i twórcami aplikacji. Ale co zrobić, jeżeli użytkowników
jest kilkuset a twórcy nie chcą, lub nie potrafią wskazać krytycznych miejsc
aplikacji?

Doświadczenie wskazuje, że w aplikacjach pisanych dla bazy danych Oracle,
wąskimi gardłami są źle skonstruowane polecenia SQL. Zła konstrukcja nie
oznacza błędów merytorycznych. Z punktu widzenia wydajności zła konstrukcja
oznacza, że ten sam zestaw rekordów z bazy danych, można wybrać w inny
szybszy sposób. Może to wymagać zmian w kodzie (przepisania polecenia na
nowo), może tez oznaczać zmiany w strukturze bazy danych (dodanie lub
rekonstrukcja indeksów, dodanie kolumn redundantnych do tablicy).

Istnieje wiele gotowych pakietów oprogramowania dla administratora bazy danych
pomagających wykryć słabe punkty w kodzie aplikacji. Ich wadami są na ogół:
wysoka cena oraz, wbrew pozorom, skomplikowany sposób użycia. A przecież,
administrator bazy danych Oracle, posiada gotowy zestaw narzędzi
diagnostycznych. Wprawdzie, nie posiadają one wspólnego, efektownego
interfejsu, ale dzięki swej prostocie są niezwykle elastyczne. Do narzędzi tych
zaliczamy: perspektywy słownika danych, tablice dynamiczne, standardowe
pakiety PL/SQL jak również języki SQL i PL/SQL. W poniższym referacie
pokażemy jak wykorzystać:
- tablice

dynamiczne

V$SESSION i V$SESSTAT,

- polecenia

ALTER SYSTEM, EXPLAIN PLAN,

- pakiety

standardowe

DBMS_LOCK i DBMS_SYSTEM,

- standardowy program pomocniczy TKPROF
aby wykryć zabójców procesora.

1. V$SESSION, czyli „Kto tam?”

Pierwsze, potężne narzędzie diagnostyczne, które dostaje do ręki administratora
bazy danych Oracle to zespół tzw. tablic dynamicznych (dynamic performance
tables). Zawartość tych tablic, zmieniając się dynamicznie, pokazuje stan instancji
bazy danych Oracle, prezentując dane dotyczące przed wszystkim wydajności.
Tak jak tablice Słownika Bazy Danych, tablice dynamiczne tworzone są
automatycznie w schemacie użytkownika SYS w trakcie tworzenia bazy danych.
Dostęp do nich zapewniony jest poprzez perspektywy, których nazwy zaczynają
się od przedrostka V_$. Synonimy publiczne, wskazujące perspektywy V_$,

3

zaczynają się z kolei od znaków V$ i w takiej formie są udostępniane zwykłym
(poza SYS) administratorom bazy danych.

Interesującym nas przykładem tablicy dynamicznej jest V$SESSION. Tablica ta
zawiera listę wszystkich, bieżących podłączeń (również dla procesów
drugoplanowych). Dzięki niej, możemy dowiedzieć się kto aktualnie podłączony
jest do bazy danych, oraz uzyskać wstępne informacje, co ten „ktoś” robi.
Szczególnie istotne wydają się być następujące kolumny:
• SID, SERIAL# -

para kolumn jednoznacznie identyfikująca sesję

• PADDR -

adres procesu systemu operacyjnego, związanego z sesją (pomaga w
dalszym śledzeniu)

• USERNAME -

nazwa użytkownika działającego w sesji

• COMMAND -

identyfikator wykonywanej komendy

• PROGRAM -

linia wywołania programu otwierającego sesję w systemie operacyjnym

• TYPE -

typ sesji; wartość ‘USER’ wskazuje na „normalną” sesję użytkownika

Lista pełnych nazw komend, wskazywanych kolumną COMMAND, obejmuje około
setki pozycji i może zmieniać swoją długość w zależności od wersji Oracle’a.
Znajdziemy tam takie popularne komendy jak SELECT (numer 3), czy CREATE
INDEX (numer 9). Pełna lista komend jest dostępna w dokumentacji odpowiedniej
do danej wersji oprogramowania. Warto jednak wiedzieć, że można zdekodować
komendę nie tylko za pomocą dokumentacji. Pomocna jest tutaj tablica,
przeznaczona właściwie do innych celów (celów polecenia AUDIT) –
AUDIT_ACTIONS. W tablicy tej (właściwie: perspektywie) znajdziemy kolumny:
• ACTION -

identyfikator wykonywanej komendy, odpowiadający kolumnie COMMAND
tablic V$SESSION

• NAME -

nazwa wykonywanej komendy, np. SELECT

Warto zauważyć, że dzięki połączeniu tablic V$SESSION i AUDIT_ACTION
możemy uzyskać tylko nazwę wykonywanej komendy a nie jej treść.

Przykład 1.1

Wstępne rozpoznanie – „Kto tam?”

SQL> set lines 132
SQL> column username format a20
SQL> column sid format 9999
SQL> column name format a20
SQL>
SQL> select A.sid,
2 A.serial#,
3 A.username,
4 A.program,
5 B.name

4

6 from v$session A ,
7 audit_actions B
8 where A.command = B.action
9 and type = 'USER';

SID SERIAL# USERNAME PROGRAM NAME
----- ---------- -------------------- ------------------------------------------------ ----
----------
7 38156 IFSAPP
UNKNOWN
12 39096
UNKNOWN
13 3194 SYSTEM sqlplus@sequent (TNS V1-V2)
UNKNOWN
15 184 USER01 C:\ifsapp99\client\fin.exe
PL/SQL EXECUTE
16 39771 IFSAPP
UNKNOWN
18 47 IFSAPP C:\ifsapp99\admin\Admin.exe
SELECT
19 90 USER02 C:\ifsapp99\client\dist.exe
UNKNOWN

Uwagi:
- sesje bez opisu programu pochodzą od zadań zakolejkowanych za pomocą

pakietu DBMS_JOB,

- sesja o identyfikatorze 13 jest sesją wykonującą zapytanie

2. V$SESSTAT, czyli „Co porabiacie?”

Kolejna tablica dynamiczna, która pomoże nam ustalić kto obciąża procesor to

V$SESSTAT.
W tablicy tej znajdziemy dla każdej, bieżącej sesji zestaw ponad 160 wskaźników
statystycznych, służących głównie do badania wydajności. Kolumny tablicy to:
• SID -

identyfikator sesji

• STATISTIC# -

identyfikator statystyki

• VALUE -

wartość statystyki

Inną tablicą zbierającą statystyki jest V$SYSSTAT, która zawiera podsumowanie
dla całej bazy danych. Nazwy statystyk dostępne są z kolei poprzez
V$STATNAME. Statystyki gromadzone w tablicach dynamicznych liczą
wystąpienia lub czas różnych zdarzeń (na przykład ilość potwierdzonych
transakcji, czy łączny czas trwania sesji).

Statystyka, którą zainteresujemy się bliżej ma identyfikator 12. W tablicy

V$SESSTAT, znajdziemy w wierszu wskazywanym przez ten identyfikator, czas
CPU wykorzystany przez daną sesję (CPU used by this session). Czas ten
mierzony jest w setnych częściach sekundy od rozpoczęcia do zakończenia
obsługi żądania. Zatem, dzięki zaokrągleniom, możemy zaobserwować pracujące
sesje, które wykazują 0 sekund użycia czasu procesora (jeżeli jest wiele żądań,
obsłużonych poniżej 0.005 sekundy), oraz sesje wykazujące użycie procesora
nieco większe niż czas obserwacji. Tego typu błędy zaokrągleń nie są czymś
niezwykłym, również w programach zbierających statystyki w systemach

5

operacyjnych, ale nie przeszkadzają nam w wykryciu sesji, które naprawdę
obciążają procesor.

Pewnym zaskoczeniem, może być fakt, że część statystyk (w tym również ta o
identyfikatorze 12) uparcie pokazuje zero, mimo iż baza danych pracuje „pełną
parą”. Dlaczego tak się może zdarzyć – o tym w następnym punkcie.

3. TIMED_STATISTISC=TRUE, czyli „W kręgu
podejrzanych”

Aby statystyki zbierające informacje na temat czasu były aktywne należy ustawić
parametr TIMED_STATISTICS na wartość TRUE. Parametr może być ustawiony:
- w pliku startowym (init.ora) bazy danych, ze skutkiem dla całej instancji,
- poprzez

polecenie

ALTER SYSTEM SET TIMED_STATISTICS=TRUE, ze

skutkiem dla całej instancji,

- poprzez

polecenie

ALTER SYSTEM SET TIMED_STATISTICS=TRUE, ze

skutkiem dla sesji wykonującej polecenie

Wartością domyślną parametru jest FALSE. Wynika to z narzutów, które
generowane są przy włączeniu statystyk odnośnie czasu. Dlatego nie jest dobrym
pomysłem włączenie tego parametru na stałe. Zbieranie statystyk odnośnie czasu
powinno odbywać się tylko wtedy, jeżeli chcemy badać wydajność. Najprostszym
rozwiązaniem wydaje się być użycie polecenia ALTER SYSTEM jeżeli chcemy w
danym momencie wychwycić sesje obciążające procesor (jeszcze nie wiemy
które).

Od momentu włączenia zbierania statystyk, wartość statystyki 12 będzie rosła w
miarę jak sesje będą potrzebowały czasu procesora. Włączenie czy wyłączenie
statystyk nie powoduje wyzerowania wartości aktualnie zebranych w
V$SESSTAT. Zatem, jeżeli chcemy zbadać która sesja aktualnie, najmocniej
obciąża procesor, nie możemy opierać się na bezwzględnej wartości, figurującej w
kolumnie VALUE. Należy brać pod uwagę różnicę pomiędzy wartościami
statystyki z momentu rozpoczęcia obserwacji i jej zakończenia.

Pożyteczna może okazać się tablica pomocnicza, która będzie przechowywać
wartości startowe statystyk. Tablica powinna zawierać kolumny jednoznacznie
identyfikujące sesję (SID i SERIAL#) oraz wartość statystyki 12. Załóżmy, że
mamy przygotowaną taka tablicę. Wtedy, możemy wytypować „krąg
podejrzanych” w następujący sposób:
- włączamy zbieranie statystyk odnośnie czasu,
- zachowujemy

wartości startowe statystyk w tablicy pomocniczej,

- czekamy

określony czas (np. 15 sekund),

- wybieramy z tablicy V$SESSION tylko te sesje, które w badanym odstępie

czasu korzystały z procesora

Jak wyliczyć żądany interwał czasowy? Można za pomocą zegarka z
sekundnikiem. Można za pomocą poleceń systemu operacyjnego. Można również
skorzystać z funkcji dostarczanej w ramach standardowego pakietu
DBMS_LOCK. Pakiet zakładany jest poprzez skrypt catproc.sql, który z kolei

6

powinien być uruchomiony w schemacie użytkownika SYS, po stworzeniu nowej
bazy danych. Pakiet posiada stowarzyszony synonim publiczny o tej samej
nazwie. Aby udostępnić pakiet użytkownikom innym niż DBA, użytkownik SYS
musi „ręcznie” nadać odpowiednie uprawnienia. Pakiet służy do implementacji
własnego mechanizmu blokad. My użyjemy jednej, pomocniczej funkcji z pakietu
– SLEEP. Funkcja zawiesza przetwarzanie w wywołującej ją sesji na określony
czas. Jedynym argumentem tej funkcji jest interwał podany w sekundach (może
być wartość ułamkowa).

Przykład 3.1

Typowanie kręgu podejrzanych.

Przygotowanie tablicy pomocniczej:

SQL> create table cpu_killer_tab
2 ( sid number(8),
3 serial_no number,
4 cpu_usage number )
5 tablespace tools;

Tabela została utworzona.

Skrypt cpu_killer.get :

set echo off
set termout on
set pages 1000
set verify off
set feedback off

define CPU_KILLER_TAB_

= cpu_killer_tab

define SLEEP_

= 10

define CPU_USAGE_STAT_

= 12

delete from &CPU_KILLER_TAB_;
commit;

set heading off

prompt Preparation...
alter system set timed_statistics = true;
execute dbms_lock.sleep(5);

select 'Start time: '||to_char(sysdate, 'YYYY.MM.DD HH24:MI:SS') ||
', collecting period: &SLEEP_ sec. - please wait...'
from dual;

set heading on

insert into &CPU_KILLER_TAB_
select A.sid,

B.serial#,
A.value

from v$sesstat A,
v$session B
where A.sid = B.sid
and A.statistic# = &CPU_USAGE_STAT_
and B.type = 'USER';

execute dbms_lock.sleep(&SLEEP_);

set lines 132
column username format a20
column sid format 9999
column name format a20

select A.sid,

7

B.serial#,
B.username,
B.program,

C.name,

(A.value - D.cpu_usage ) / 100 "CPU in sec./period"
from v$sesstat A,
v$session B ,
audit_actions C,
&CPU_KILLER_TAB_ D
where A.sid = B.sid
and A.statistic# = &CPU_USAGE_STAT_
and B.sid = D.sid
and B.serial# = D.serial_no
and B.command = C.action
and (A.value - D.cpu_usage) > 0
order by 6;

alter system set timed_statistics = false;

rollback;

Wywołanie skryptu:

SQL> @cpu_killer.get
Preparation...

Start time: 1999.09.15 16:10:57, collecting period: 10 sec. - please wait...

SID SERIAL# USERNAME PROGRAM NAME CPU in
sec./period
----- ---------- -------------- -------------------------------- ------------------- ------
------------
12 3 SYSTEM sqlplus@sequent (TNS V1-V2) UNKNOWN
.01
20 24 USER02 C:\ifsapp99\client\dist.exe UNKNOWN
3.69
16 27 USER01 C:\ifsapp99\client\fin.exe PL/SQL EXECUTE
9.53

Uwagi:
- na maszynach wieloprocesorowych (jak jest w przykładzie), suma czasu

wykorzystanego przez wszystkie sesje może przekraczać interwał w którym
statystyki są zbierane,

- pięciosekundowe przygotowanie, daje czas na stabilizację po włączeniu

zbierania statystyk odnośnie czasu

4. TKPROF, czyli „Mam Cię!”

W poprzednim punkcie pokazaliśmy jak można uzyskać odpowiedź, które sesje
najbardziej obciążają procesor w danym momencie. To ciągle za mało aby
przystąpić do strojenia, ponieważ nadal nie wiemy, jakie polecenia powodują taki
stan rzeczy. Niezastąpionym wręcz narzędziem do śledzenia komend SQL i
wywołań PL/SQL w sesji jest standardowy program tkprof . Oto wybrane
parametry wywołania programu:

tkprof plik_wejściowy plik_wyjściowy

[explain=użytkownik/hasło] [table=nazwa_tablicy]

[sort=opcja_sortowania]

• plik_wejściowy -

plik zapisu sesji

8

• plik_wyjściowy –

plik tekstowy zawierający pełna informację o poleceniach SQL i
wywołaniach PL/SQL w sesji

• użytkownik/hasło –

użytkownik, który ma dostęp do tablicy potrzebnej do działania polecenia
EXPLAIN PLAN

• nazwa_tablicy –

nazwa tablicy potrzebnej do działania polecenia EXPLAIN PLAN

• opcja_sortowania –

nazwa tablicy potrzebnej do działania polecenia EXPLAIN PLAN

Teraz trochę wyjaśnień.

4.1 Plik zapisu sesji - trace

Każda sesja może generować specjalny plik zapisujący zdarzenia w celu badania
pod kątem poprawienia wydajności aplikacji. Aby tak się działo należy ustawić
parametr SQL_TRACE na wartość TRUE. Parametr może być ustawiony:
- w pliku startowym (init.ora) bazy danych, ze skutkiem dla całej instancji,
- poprzez

polecenie

ALTER SSESSION SET SQL_TRACE=TRUE, ze skutkiem

dla sesji wykonującej polecenie

- poprzez

funkcję SET_SQL_TRACE_IN_SESSION z pakietu

DBMS_SYSTEM, ze skutkiem dla sesji wskazanej przez administratora.

Wartością domyślną parametru jest FALSE. Gdyby było inaczej, trzeba by
posiadać ogromne zapasy przestrzeni dyskowej na pliki śladu. Im więcej sesja
wykonuje działań tym większy plik powstaje. Dlatego nie jest dobrym pomysłem
włączenie tego parametru na stałe, dla całego systemu.

Rozmiar pojedynczego pliku śladu można ograniczyć poprzez parametr
MAX_DUMP_FILE_SIZE (do ustawienia w pliku startowym oraz poprzez ALTER
SYSTEM i ALTER SESSION). Wartością domyślną jest 10000 bloków systemu
operacyjnego. Dla bloków 512 bajtowych daje to ograniczenie do 5Mb. Jeżeli
chcemy wychwycić „poważnych zabójców” procesora, możemy potrzebować
zwiększenia tego parametru co najmniej dziesięć razy (do 50Mb). Oryginalny plik
śladu, o rozmiarze, powiedzmy 40Mb, mimo iż jest plikiem tekstowym, nie nadaje
się do analizy przez DBA. Dopiero zastosowanie tkprof, pozwala obejrzeć
informację zagregowaną i uporządkowaną, czytelną dla administratora.

Miejsce tworzenia pliku zapisu sesji określa parametr USER_DUMP_DEST.
Wartość domyślna tego parametru wskazuje na jeden z podkatalogów wewnątrz
struktury katalogów z oprogramowaniem Oracle i nie powinna być używana.
Nazwa pliku śladu jest komponowana w nieco różny sposób, w zależności o
systemu operacyjnego. Zawsze jednak, znajdziemy tam identyfikator procesu
związanego z sesją. Identyfikator ten możemy odnaleźć w tablicy V$PROCESS, w
kolumnie SPID . Znajdowanie dokładnej nazwy pliku zapisu sesji dla systemów
typu UNIX jest ujęte w przykładzie 4.1.

9

4.2 EXPLAIN PLAN

Zastosowanie programu tkprof, bez uwzględnienia działania komendy EXPLAIN
PLAN jest bezsensowne jeżeli chodzi o strojenie aplikacji. Polecenie to pokazuje
sposób realizacji polecenia SQL, tzn. jakie indeksy są wykorzystywane, jak jest
realizowane złączenie tablic itp. EXPLAIN PLAN jest podstawowym narzędziem
diagnostycznym dla stroiciela aplikacji. Uproszczona składnia polecenia wygląda
następująco:

EXPLAIN PLAN SET STATEMENT_ID = ‘identyfikator’
FOR
polecenie_SQL;

• identyfikator -

pomaga odnaleźć opis realizacji konkretnego polecenia

• ploecenie_SQL –

polecenie z grupy DML (SELECT, INSERT, UPDATE, DELETE)

Wykonanie polecenia EXPLAIN PLAN oznacza wstawienie zespołu rekordów do
specjalnej tablicy. Nazwa domyślna tej tablicy to PLAN_TABLE. Składania
polecenia, przewiduje również możliwość wskazania tablicy o innej nazwie. Ściśle
określona jest jednak struktura tej tablicy. Aby zapewnić zgodność tablicy z
poleceniem, należy ją utworzyć za pomocą skryptu o nazwie utlxplan.sql,
dostarczanego wraz z oprogramowaniem Oracle (na Unix’ie, ścieżka
$ORACLE_HOME/rdbms/admin). Warto zaznaczyć, że tablica ta nie jest
zakładana automatycznie. Należy zwrócić uwagę, że w skrypcie ultxplan.sql nie
jest wskazana nazwa przestrzeni tablic w której tablica ma powstać. Aby nie
„śmiecić gdzie popadnie” trzeba dodać wskazanie wybranej przestrzeni tablic (np.
TOOLS, jeżeli istnieje). W bazie danych może istnieć jedna taka tablica, dostępna
dla wszystkich (potrzebne są wtedy odpowiednie uprawnienia). Dobrze wtedy jest
przygotować dodatkowo publiczny synonim o tej samej nazwie. Każdy z
użytkowników może mieć też własną tablicę dla polecenia EXPLAIN_PLAN.

Opis wykonania polecenia generowany przez EXPLAIN PLAN wymaga następnie
wykonania „sprytnego” polecenia SELECT, które wyświetli kroki realizacji w
odpowiedniej kolejności i jeszcze lepiej, formie. Program tkprof potrafi
przygotować w pliku wyjściowym odpowiednio sformatowany opis realizacji
plecenia SQL pod warunkiem, że podamy parametry wywołania explain i table .
Pierwszy parametr zawiera użytkownika i jego hasło. Użytkownik musi mieć prawa
zapisu do tabeli wskazywanej parametrem table, odpowiedniej do realizacji
polecenia EXPLAIN PLAN.

4.3 Opcje sortowania

Opcje sortowania dla komendy tkprof, pozwalają uporządkować plik wyjściowy
pod kątem naszych wymagań. Dostępnych opcji jest kilkadziesiąt. Polecenia
SQL, wymagające strojenia, charakteryzują się dużym czasem pobierania
rekordów z bazy danych. Interesujące zatem wydają się dwie opcje:
fchcpu

- czas procesora zużyty na pobieranie rekordów

10

i
fchela

- całkowity czas pobierania rekordów

Którą opcję wybrać? Oczywiście – fchela . Okazuje się, że czas procesora,
rejestrowany w pliku trace, jest różny od zera tylko wtedy, kiedy włączone jest
zbieranie statystyk odnośnie czasu (parametr TIMED_STATISTICS, patrz punkt
3). Całkowity czas jest liczony zawsze. Wybranie drugiej, przytoczonej wyżej
opcji, powoduje, że „zabójców procesora” mamy namierzonych. W pliku
wynikowym programu tkprof, będą to pierwsze (jedno lub więcej) polecenia SQL.

Przykład 4.1

Namierzanie „zabójców”

Skrypt trace.on

set echo off
set termout on
set pages 1000
set verify off
set feedback off

accept SID_ prompt "Enter session id: "
accept SERIAL_ prompt "Enter session serial number: "

EXECUTE sys.dbms_system.set_sql_trace_in_session(&SID_, &SERIAL_, TRUE);

select value ||
'/ora_' ||
B.spid ||
'.trc'
"Session trace file name"
from v$parameter A,
v$process B
where A.name = 'user_dump_dest'
and B.addr = ( select paddr
from v$session
where sid = &SID_
and serial# = &SERIAL_ );

Wywołanie skryptu trace.on

SQL> @trace.on
Enter session id: 16
Enter session serial number: 27

Session trace file name
-------------------------------------------------------------------------------------------
---
/u11/oracle/admin/E/udump/ora_11992.trc

Skrypt trace.off

set echo off
set termout on
set pages 1000
set verify off
set feedback off

accept SID_ prompt "Enter session id: "
accept SERIAL_ prompt "Enter session serial number: "

EXECUTE sys.dbms_system.set_sql_trace_in_session(&SID_, &SERIAL_, FALSE);

11

Wywołanie skryptu trace.off (po 30 minutach)

SQL> @trace.off
Enter session id: 16
Enter session serial number: 27

Wywołanie programu tkprof

tkprof /u11/oracle/admin/E/udump/ora_11992.trc showme.txt explain=ifsapp/malibu3 \
table=SYS.PLAN_TABLE sort=fchela

Początek pliku wynikowego showme.txt

TKPROF: Release 7.3.4.0.0 - Production on Thu Sep 16 15:40:47 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.

Trace file: ora_11910.trc
Sort options: fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT CURR_AMOUNT,DISC_CURR_AMOUNT,DOM_AMOUNT,DISC_DOM_AMOUNT,SERIES_ID,
PAYMENT_ID
FROM
LEDGER_TRANSACTION2 WHERE COMPANY = :b1 AND PARTY = :b2 AND PARTY_TYPE_DB
= PARTY_TYPE_API.ENCODE(:b3) AND LEDGER_ITEM_SERIES_ID = :b4 AND
LEDGER_ITEM_ID = :b5 AND LEDGER_ITEM_VERSION = :b6 AND IS_NEW_LEDGER_ITEM
= 'FALSE'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2941 0.00 0.45 0 0 0 0
Fetch 3164 0.00 2029.61 0 40109581 5882 223
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6105 0.00 2030.06 0 40109581 5882 223

12

Początek pliku wynikowego showme.txt, ciąg dalszy

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 14 (IFSAPP) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 TABLE ACCESS (FULL) OF 'COMPANY_FINANCE_TAB'
0 TABLE ACCESS (BY ROWID) OF 'PAYMENT_TRANSACTION_TAB'
0 INDEX (RANGE SCAN) OF 'PAYMENT_TRANSACTION_PK' (UNIQUE)

********************************************************************************
...

5. Podsumowanie - przykład strojenia

Jak zneutralizować zabójcę? Nie ma na to prostej odpowiedzi, ponieważ strojenie
poleceń SQL obejmuje wiele aspektów i wiele możliwych rozwiązań. Poznanie
tych aspektów i rozwiązań wymaga... przeczytania jednej książki oraz wielu,
samodzielnych prób (i błędów). Na podsumowanie tego referatu chciałbym
pokazać jedno, konkretne rozwiązanie dla przypadku pokazanego w przykładach.

Popatrzmy jeszcze raz na opis realizacji polecenia:

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 TABLE ACCESS (FULL) OF 'COMPANY_FINANCE_TAB'
0 TABLE ACCESS (BY ROWID) OF 'PAYMENT_TRANSACTION_TAB'
0 INDEX (RANGE SCAN) OF 'PAYMENT_TRANSACTION_PK' (UNIQUE)

Sprawdźmy liczbę rekordów w dwóch, ww. tablicach:

SQL> select count(*)
2 from company_tab;

COUNT(*)
----------
1

SQL> select count(*)
2 from payment_transaction_tab;

COUNT(*)
----------
13583

No cóż, właściwie powinno być świetnie. Mała, jednorekordowa tablica jest
pobierana przez „full scan”, natomiast większa (ale też niezbyt duża) poprzez
klucz główny (primary key). Trzeba zatem, dokładniej przyjrzeć się warunkowi
zapytania oraz unikalności użytych kolumn.

13

WHERE COMPANY

= :b1

AND PARTY

= :b2

AND PARTY_TYPE_DB

= PARTY_TYPE_API.ENCODE(:b3)

AND LEDGER_ITEM_SERIES_ID

= :b4

AND LEDGER_ITEM_ID

= :b5

AND LEDGER_ITEM_VERSION

= :b6

AND IS_NEW_LEDGER_ITEM

= 'FALSE'

Zauważmy, też, że zapytanie bazuje, na LEDGER_TRANSACTION2, nie
występującym w drzewie realizacji – zatem to musi być perspektywa (ze swoim
warunkiem złączenia).

Sprawdźmy zapytanie tworzące perspektywę:

SQL> select text
2 from user_views
3 where view_name = 'LEDGER_TRANSACTION2';

TEXT
-------------------------------------------------------------------------------------------
---------------
SELECT company company,
series_id series_id,
payment_id payment_id,
trans_id trans_id,
party party,
substr(Party_Type_API.Decode(party_type),1,20) party_type,
party_type party_type_db,
ledger_item_series_id ledger_item_series_id,
ledger_item_id ledger_item_id,
ledger_item_version ledger_item_version,
is_new_ledger_item is_new_ledger_item,
rolledback rolledback,
rollback_of_series_id rollback_of_series_id,
rollback_of_payment_id rollback_of_payment_id,
rollback_of_trans_id rollback_of_trans_id,
curr_amount curr_amount,
dom_amount dom_amount,
disc_curr_amount disc_curr_amount,
disc_dom_amount disc_dom_amount
FROM payment_transaction_tab
WHERE rowtype = 'LedgerTransaction' AND company IN (SELECT company FROM COMPANY_FINANCE)
WITH read only

Dzięki temu możemy zidentyfikować, że wszystkie kolumny występujące w
warunku zapytania – „zabójcy” pochodzą z tablicy
PAYMENT_TRANSACTION_TAB.

Sprawdźmy indeksy założone na tej tablicy:

SQL> select index_name,
2 column_name
3 from user_ind_columns
4 where table_name = 'PAYMENT_TRANSACTION_TAB'
5 order by index_name,
6 column_position;

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
PAYMENT_TRANSACTION_IX1 PARTY
PARTY_TYPE
COMPANY
PAYMENT_TRANSACTION_PK COMPANY
SERIES_ID
PAYMENT_ID
TRANS_ID

No właśnie! Klucz główny pasuje do warunku zapytania tylko na jednej pozycji –
kolumnie COMPANY, która przyjmuje tylko jedną wartość. Możliwych rozwiązań
jest wiele. Popatrzmy na jedno z nich.

14

Sprawdzimy unikalność kolumn tablic PAYMENT_TRANSACTION_TAB

SQL> analyze table payment_transaction_tab estimate statistics;

Tabela została zanalizowana.

SQL> select column_name,
2 num_distinct
3 from user_tab_columns
4 where table_name = 'PAYMENT_TRANSACTION_TAB'
5 order by 2 desc;

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
LEDGER_ITEM_ID 8053
DOM_AMOUNT 2823
CURR_AMOUNT 2823
PARTY 683
PAYMENT_ID 521
ROLLBACK_OF_TRANS_ID 85
TRANS_ID 76
INTEREST_DATE 47
SHORT_NAME 19
ASSOCIATED_SHORT_NAME 19
LEDGER_ITEM_SERIES_ID 6
ROLLBACK_OF_PAYMENT_ID 6
CURR_RATE 5
ROLLBACK_OF_SERIES_ID 3
CURRENCY 3
SERIES_ID 3
ROWTYPE 3
IS_NEW_LEDGER_ITEM 2
ROWVERSION 2
PARTY_TYPE 2
DIV_FACTOR 2
ROLLEDBACK 2
LEDGER_ITEM_VERSION 1
COMPANY 1
AMOUNT 0
NOTE 0
DISC_CURR_AMOUNT 0
DISC_DOM_AMOUNT 0

28 wierszy zostało wybranych.

SQL> analyze table payment_transaction_tab delete statistics;

Tabela została zanalizowana.

Dodajmy nowy indeks:

SQL> create index payment_transaction_tix1
2 on payment_transaction_tab
3 ( ledger_item_id,
4 party,
5 ledger_item_series_id,
6

company )

7 tablespace fnd_index;

Indeks został utworzony.

Po co tak dużo kolumn? – aby indeks jak najlepiej „pasował” do zapytania.

15

Fragment pliku wynikowego tkprof: showme.txt, zawierający strojone polecenie:

...
SELECT CURR_AMOUNT,DISC_CURR_AMOUNT,DOM_AMOUNT,DISC_DOM_AMOUNT,SERIES_ID,
PAYMENT_ID
FROM
LEDGER_TRANSACTION2 WHERE COMPANY = :b1 AND PARTY = :b2 AND PARTY_TYPE_DB
= PARTY_TYPE_API.ENCODE(:b3) AND LEDGER_ITEM_SERIES_ID = :b4 AND
LEDGER_ITEM_ID = :b5 AND LEDGER_ITEM_VERSION = :b6 AND IS_NEW_LEDGER_ITEM
= 'FALSE'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 3857 0.00 0.52 0 0 0 0
Fetch 4198 0.00 4.02 88 16407 7714 341
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8055 0.00 4.54 88 16407 7714 341

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 14 (IFSAPP) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 TABLE ACCESS (FULL) OF 'COMPANY_FINANCE_TAB'
0 TABLE ACCESS (BY ROWID) OF 'PAYMENT_TRANSACTION_TAB'
0 INDEX (RANGE SCAN) OF 'PAYMENT_TRANSACTION_TIX1'
(NON-UNIQUE)

********************************************************************************
...

Uwagi:
- poprzednia obserwacja trwała krócej niż wykonanie raportu, druga obserwacja,

po dodaniu indeksu, objęła cały czas trwania raportu,

- przed dodaniem nowego indeksu, strojone zapytanie pobrało 223 rekordów w

2030.06 sekundy, po dodaniu indeksu: 341 rekordów w 4.54 sekundy

Rezultat – strojone zapytanie straciło pierwszą pozycje w rankingu „zabójców”
procesora. Cały raport, poprzednio wykonujący się około 45 minut, obecnie
generuje się w niespełna 4 minuty. Nie oznacza to końca strojenia. Program
tkprof wskazuje nam następne polecenia, które należy rozważyć. Czy da się je
łatwo nastroić – nie wiadomo (wiadomo, że przykład pokazywał szczególnie łatwy
przypadek).

Pokazany sposób postępowania jest jednym z wielu. Każdy administrator –
stroiciel powinien dopracować się własnych metod, przy użyciu najwygodniejszych
dla niego narzędzi. Zanim jednak zdecydujemy się wydać poważne pieniądze na
programy które „same stroją”, pamiętajmy: podręcznik, V$, tkprof...