Tuning of SQL Statements

Performanceverbesserung von SQL Statements 

... indem man sich die Ausführungsstatistiken (engl. execution statistics oder rowsource statistics) anzeigen lässt:

Warum ist dieses SQL-Statement so langsam? Kann man es schneller machen? Aber wie?

Mit Hilfe zweier einfacher SQL-Abfragen kann man feststellen, warum das Statement so langsam ist:

1. Das kritische SQL-Statement mit dem Hint /*+ GATHER_PLAN_STATISTICS */ ausführen

set termout off
variable v1 number;
exec :v1 := 1001;
select /*+ GATHER_PLAN_STATISTICS */ *
from CUSTOMERS C, ORDERS O
where O.CUSTOMER_ID = C.CUSTOMER_ID
and C.customer_id = :v1
and O.order_status = 'PENDING'
order by order_date
;

2. SQL_ID ermitteln

Als sys ausführen. Im SQL Navigator muss man das im gleichen Fenster ausführen wie die Query, sonst zeigt er die A-Rows nicht mit an.

select sql_id
, sql_text
, child_number as child
, plan_hash_value as phv
, buffer_gets/executions as gets_per_exe
, disk_reads/executions as disk_per_exe
, elapsed_time/executions ela_per_exe
, executions as exe
from v$sql
where sql_text like 'select /*+ GATHER_PLAN%'
order by first_load_time desc
;

Buffer Gets = Anzahl der DB-Blöcke die gelesen wurden (egal on aus dem DB Buffer Cache oder von Platte)

Disk Reads = Anzahl der DB-Blöcke die von Platte gelesen wurden

Elapsed Time = for parsing, executing and fetching (in Mikrosekunden)

3. Erweiterten Ausführungsplan anzeigen lassen

Dazu den SQL_ID und CHILD in folgendes Statement einsetzen:

select * from table(dbms_xplan.display_cursor('cwsru5tu0saym', 0,'ALLSTATS LAST PEEKED_BINDS'));

Was am weitesten eingerückt ist, macht Oracle zuerst. Bei gleicher Einrückung von oben nach unten lesen (Ausführung erfolgt aber evtl. parallel).

E-Rows = estimated rows: Das hat der Optimizer bei der Generierung des Ausführungsplans geschätzt ...

Die beiden folgenden Spalten werden erst angezeigt, wenn man das kritische SQL-Statement ein zweites Mal ausführt:

A-Rows = actual rows: ... und so viele Zeilen wurden tatsächlich gelesen.

A-Time = actual time (immer aufsummiert) ... !!! Achtung: Wenn der Parameter _rowsource_statistics_sampfreq z.B. auf 128 steht (default) ist diese Zeit oft nicht korrekt. Um korrekte Zeiten zu erhalten muss man ihn niedriger setzen, idealerweise auf 1, was angeblich einiges an Overhead erzeugt. Ich habe die beste Erfahrung mit 4 gemacht (bei 1 war die kumulierte Zeit im Plan nur halb so groß wie real, der Overhead lag aber nur bei ein paar Prozent). Aber auch bei 4 scheinen mir die im Plan angegebenen Zeiten nicht plausibel zu sein. Parameter anzeigen lassen mit:

  SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND  
  ksppinm = '_rowsource_statistics_sampfreq' ORDER BY ksppinm;
Ändern mit:
  alter system set "_rowsource_statistics_sampfreq" = 4;

Buffers = Buffer Gets = Anzahl der DB-Blöcke die gelesen wurden (immer aufsummiert)

Gibt es große Diskrepanzen zwischen A-Rows und E-Rows, so ist der Ausführungsplan nicht optimal. => Durch Hints, zusätzliche Index etc. lassen sich Verbesserungen erreichen.

In diesem Link ist dazu auch nochmal einiges erklärt und dort wird ein Artikel zu Oracle 9i und Memory Management erwähnt, der - obschon älter - lesenswert ist!

Anhänge