Tuning of SQL Statements: Example

--
-- Kritisches Statement:
--

-- als demo in sql+:
set termout off
variable v1 number;
exec :v1 := 1001;
select *
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
;

-- als sys:
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 * from CUSTOMERS%'
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)
-- 22300 gets sind für eine Zeile ziemlich viel, normal wären 10 ...

-- Wie sieht der Ausführungsplan aus?
select * from table(dbms_xplan.display_cursor('41v45z3dtqa2s', 0,'TYPICAL +PEEKED_BINDS'));
-- Reihenfolge = 1. Einrückung, 2. von oben nach unten (??)
--    Ziel: möglichst früh möglichst viele Rows eliminieren
-- 1. Zeile 4+3, da nur ein Datensatz
-- 2. Zeile 6+5: Der Optimizer weiß, dass er 64237 verschiedene customer_ids in der Tabelle orders hat.
--    => 16 Rows pro customer_id
-- 3. Zeile 2: Outer Loop (Zeile 3-4) und einem Inner Loop (Zeile 5-6). Für jede zutreffende Zeile des
--    Outer-Loops wird der Inner Loop einmal ausgeführt. Die Anzahl der Wiederholungen wird in der Spalte
--    „Starts“ = Anzahl der Wiederholungen = 1 da PK
--    d.h. der Inner Loop wird nur einmal ausgeführt.
-- 4. Sortieren
-- Bedeutung der Spalten
-- Bytes ist aufsummiert
-- Ergebnis ist immer: Plan sieht gut aus! (Sofern die Statistiken stimmen)

--
-- Detaillierteren Ausführungsplan ermitteln:
--

-- als demo in sql+:
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
;

-- als sys:
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
;

select * from table(dbms_xplan.display_cursor('cwsru5tu0saym', 0,'ALLSTATS LAST'));
-- Wieso Actual-Rows = 700K?
-- Buffers = Buffer Gets = Anzahl der DB-Blöcke die gelesen wurden (immer aufsummiert)

SELECT customer_id, count(*)
FROM demo.orders a
group by customer_id
order by 2 desc
;
-- Folien 20-22

--
-- Idee 1: Histogramme erzeugen
--
ALTER SYSTEM FLUSH SHARED_POOL;
alter system flush buffer_cache;

-- Histogramm mit 254 verschiedenen Werte ermitteln:
exec dbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'ORDERS',method_opt=>'FOR COLUMNS CUSTOMER_ID SIZE 254');

-- als demo in sql+:
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
;

-- als sys:
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
;

select * from table(dbms_xplan.display_cursor('d64pskw2p2qmd', 0,'ALLSTATS LAST PEEKED_BINDS'));
-- Estimated-Rows = 348K kommt aus dem Histogramm (700K sind dem Kunden 1001 zugeordnet, Oracle nimmt an, dass die Hälfte davon PENDING ist)
-- wg. "bind peeking" - d.h. der Optimizer guckt sich den Inhalt von :v1 an!
-- Buffers = Buffer Gets (immer aufsummiert)
-- actual Time ist aufsummiert (vgl. elapsed_time aus voriger Query)
-- Wenig Verbesserung ...

-- ... und was passiert, wenn ich nach einem anderen Customer suche?



-- als demo in sql+:
set termout off
variable v1 number;
exec :v1 := 1039;
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
;

-- als sys:
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
;

select * from table(dbms_xplan.display_cursor('d64pskw2p2qmd', 0,'ALLSTATS LAST PEEKED_BINDS'));
-- Da haben wir also den Salat: Jetzt ist es bei allen Kunden langsam
-- Riesenproblem: Ausführungsplan ist vom Kunden abhängig, der zuerst kommt.
-- Also Vorsicht bei der Kombination Histogramm + Bind Variable

-- Histogramme besser wieder löschen
exec dbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'ORDERS', method_opt=>'FOR ALL COLUMNS SIZE 1');


--
-- Idee 2: Reorganisation
--

-- Folie 23+24

-- Warum 6328? Full Table Scan hat 21266 Buffers. Ich hätte hier jetzt erwartet, dass er 70% davon angucken muss.
-- Der Filter auf order_status wird in Zeile 5 berücksichtigt. Aber dann muss ich ja trotzdem in den Block gucken.


--
-- Idee 3: Index auf order_status
--

-- Folie 25

create index DEMO.ORDERS_CUST_STATUS
on DEMO.ORDERS(ORDER_STATUS,CUSTOMER_ID)
COMPRESS 1;

ALTER SYSTEM FLUSH SHARED_POOL;
alter system flush buffer_cache;

-- als demo in sql+:
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
;

-- als sys:
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
;

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

-- da kann man nicht meckern ...





















No comments:

Post a Comment