--
-- 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