Reichlich spät fällt mir auf, dass mein Blog die Marke von 100.000 Besuchen überschritten hat - ich sage bewusst Besuche, da es sich natürlich nicht um so viele Besucher handelt. Wenn ich davon ausgehe, dass davon ca. 75.000 durch meine eigenen Aufrufe begründet sind und 20.000 von Leuten stammen, die direkt wieder umgekehrt sind, als sie feststellten, dass nur die SQL-Kommandos als Englisch durchgehen mögen, bleiben doch ein paar tausend Klicks, die ich nicht problemlos wegdiskutieren kann. Dafür danke ich allen Besuchern ganz herzlich und hoffe, dass hier gelegentlich jemand etwas Interessantes gefunden hat.
Dienstag, Januar 28, 2014
Montag, Januar 27, 2014
Noch ein paar nette 12c Features
Julian Dontcheff nennt in seinem Blog zwölf neue Features, die mit 12c verfügbar geworden sind. Die meisten habe ich hier wahrscheinlich schon mal erwähnt, aber sehr interessant finde ich:
- die Option DISABLE_ARCHIVE_LOGGING mit der man die Erzeugung von redo log Informationen beim Import massiv reduzieren kann (was nach dem Import natürlich ein neues Basis-Backup erforderlich macht).
- die TRUNCATE CASCADE Option, die für alle über referentielle Constraints verbundenen Child-Tabellen ein Truncate ausführt, wenn die Parent-Tabelle truncated wird.
Dabei räumt TRUNCATE CASCADE die abhängigen Tabellen komplett leer, führt also tatsächlich auch dort ein Truncate und kein Delete durch, wie das folgende Beispiel zeigt:
drop table t_c;
drop table t_p;
create table t_p (id number primary key);
create table t_c (id number primary key, p_id number);
alter table t_c add constraint t_c_p_fk foreign key (p_id) references t_p(id) on delete cascade;
insert into t_p (id) values (1);
insert into t_c (id, p_id) values (1, 1);
insert into t_c (id, p_id) values (2, null);
commit;
select * from t_c;
ID P_ID
---------- ----------
1 1
2
truncate table t_p cascade;
select * from t_c;
Es wurden keine Zeilen ausgewählt
Hier verschwindet auch der Datensatz mit der Id 2 aus der Child-Tabelle t_c, obwohl er für die FK-Spalte einen NULL-Wert enthält. Jenseits der Inhalte lässt sich das Verhalten auch daran erkennen, dass die DATA_OBJECT_ID der Tabelle in USER_OBJECTS erhöht wird.
Servicepacks für den SQL Server
Brent Ozar zeigt in seinem Blog eine interessante Auswertung: seit November 2012 hat Microsoft keine neuen Servicepacks mehr veröffentlicht - und er stellt die Frage:
what if Microsoft just stopped releasing SQL Server service packs altogether, and the only updates from here on out were hotfixes and cumulative updates? How would that affect your patching strategy? Most shops I know don’t apply cumulative updates that often, preferring to wait for service packs. There’s an impression – correct or not – that service packs are better-tested than CUs.
Interessant ist auch die recht lebendige Diskussion, die sich in den Kommentaren anschließt.
Samstag, Januar 25, 2014
Optimierung durch Umformulierung
Mein Kollege Christian hat mir dieser Tage den Hinweis auf eine neue Webseite zum Thema SQL Optimierung geschickt, den High-Performance SQL Blog, dessen erster Artikel sich mit der Möglichkeit der Optimierung von Count Distinct Operationen durch Auslagerung in In-Line Views beschäftigt. Vorgeführt werden die Effekte am Beispiel Postgres und führen dort nach Aussage der Autoren zu einer 50 fachen Beschleunigung der Operation (was sich im etwas reißerischen Titel Use Subqueries to Count Distinct 50X Faster wiederfindet).
Optimierung durch Umformulierung ist wahrscheinlich die am häufigsten verwendete Optimierungstechnik in relationalen Datenbanken - insbesondere in solchen, deren Instrumentierung nicht besonders umfassend ist, und in RDBMS, deren Optimizer nicht besonders geschickt in der Transformation von Queries zu syntaktisch äquivalenten Varianten ist, die sich besser optimieren lassen. Eine schöne Einführung zu solchen Transformationen für den Fall Oracle hat Stefan Köhler kürzlich in seinem SAP on Oracle Blog veröffentlicht. Dass auch Oracles Optimizer (der das vermutlich besser macht als die meisten Artgenossen in anderen RDBMS) in solchen Fällen nicht unbedingt immer extrem erfolgreich ist, hat Iggy Fernandez gelegentlich gezeigt (anhand eines uralten Beispiels von Fabian Pascal).
Angesichts des Postgres-Beispiels und der dramatischen Performance-Unterschiede habe ich mir jedenfalls die Frage gestellt, was Oracle daraus macht - wobei meine Vermutung war, dass sich alle Varianten gleich verhalten. Dazu der folgende kleine Test:
-- 11.2.0.1
drop table t1;
drop table t2;
create table t1
as
select rownum dashboard_id
, 'Dashboard ' || rownum name
from dual
connect by level <= 100;
create table t2
as
with basedata as (
select mod(rownum, 100) dashboard_id
, mod(rownum, 10000) user_id
from dual
connect by level < 1000000
)
,
generator as (
select *
from dual
connect by level <= 10
)
select basedata.*
from basedata
, generator;
exec dbms_stats.gather_table_stats(user, 'T1')
exec dbms_stats.gather_table_stats(user, 'T2')
set autot trace
select t1.name
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
order by 2 desc;
select t1.name
, log_counts.ct
from t1
join (select dashboard_id
, count(distinct user_id) as ct
from t2 time_on_site_logs
group by dashboard_id
) log_counts
on log_counts.dashboard_id = t1.dashboard_id
order by log_counts.ct desc ;
select
t1.name,
log_counts.ct
from t1
join (
select distinct_logs.dashboard_id,
count(1) as ct
from (
select distinct dashboard_id, user_id
from t2
) distinct_logs
group by distinct_logs.dashboard_id
) log_counts
on log_counts.dashboard_id = t1.dashboard_id
order by log_counts.ct desc ;
set autot off
Nun ein Blick auf die Pläne und Laufzeiten:
SQL> select t1.name
2 , count(distinct t2.user_id)
3 from t1
4 join t2
5 on t1.dashboard_id = t2.dashboard_id
6 group by t1.name
7 order by 2 desc;
99 Zeilen ausgewählt.
Abgelaufen: 00:00:03.34
Ausführungsplan
----------------------------------------------------------
Plan hash value: 344611024
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | | 17232 (1)| 00:01:27 |
| 1 | SORT ORDER BY | | 100 | 2600 | | 17232 (1)| 00:01:27 |
| 2 | HASH GROUP BY | | 100 | 2600 | | 17232 (1)| 00:01:27 |
| 3 | VIEW | VM_NWVW_1 | 707K| 17M| | 17229 (1)| 00:01:27 |
| 4 | HASH GROUP BY | | 707K| 15M| 306M| 17229 (1)| 00:01:27 |
|* 5 | HASH JOIN | | 9999K| 219M| | 659 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL| T1 | 100 | 1600 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| T2 | 9999K| 66M| | 656 (0)| 00:00:04 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
Statistiken
----------------------------------------------------------
0 recursive calls
0 db block gets
16595 consistent gets
16389 physical reads
0 redo size
3531 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> select t1.name
2 , log_counts.ct
3 from t1
4 join (select dashboard_id
5 , count(distinct user_id) as ct
6 from t2 time_on_site_logs
7 group by dashboard_id
8 ) log_counts
9 on log_counts.dashboard_id = t1.dashboard_id
10 order by log_counts.ct desc ;
99 Zeilen ausgewählt.
Abgelaufen: 00:00:02.24
Ausführungsplan
----------------------------------------------------------
Plan hash value: 2847152331
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3200 | | 8948 (1)| 00:00:45 |
| 1 | SORT ORDER BY | | 100 | 3200 | | 8948 (1)| 00:00:45 |
|* 2 | HASH JOIN | | 100 | 3200 | | 8947 (1)| 00:00:45 |
| 3 | TABLE ACCESS FULL | T1 | 100 | 1600 | | 2 (0)| 00:00:01 |
| 4 | VIEW | | 100 | 1600 | | 8945 (1)| 00:00:45 |
| 5 | HASH GROUP BY | | 100 | 1600 | | 8945 (1)| 00:00:45 |
| 6 | VIEW | VM_NWVW_1 | 707K| 10M| | 8945 (1)| 00:00:45 |
| 7 | HASH GROUP BY | | 707K| 4833K| 153M| 8945 (1)| 00:00:45 |
| 8 | TABLE ACCESS FULL| T2 | 9999K| 66M| | 656 (0)| 00:00:04 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOG_COUNTS"."DASHBOARD_ID"="T1"."DASHBOARD_ID")
Statistiken
----------------------------------------------------------
0 recursive calls
0 db block gets
16595 consistent gets
16422 physical reads
0 redo size
3508 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> select
2 t1.name,
3 log_counts.ct
4 from t1
5 join (
6 select distinct_logs.dashboard_id,
7 count(1) as ct
8 from (
9 select distinct dashboard_id, user_id
10 from t2
11 ) distinct_logs
12 group by distinct_logs.dashboard_id
13 ) log_counts
14 on log_counts.dashboard_id = t1.dashboard_id
15 order by log_counts.ct desc ;
99 Zeilen ausgewählt.
Abgelaufen: 00:00:02.12
Ausführungsplan
----------------------------------------------------------
Plan hash value: 2377635410
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3200 | | 8948 (1)| 00:00:45 |
| 1 | SORT ORDER BY | | 100 | 3200 | | 8948 (1)| 00:00:45 |
|* 2 | HASH JOIN | | 100 | 3200 | | 8947 (1)| 00:00:45 |
| 3 | TABLE ACCESS FULL | T1 | 100 | 1600 | | 2 (0)| 00:00:01 |
| 4 | VIEW | | 100 | 1600 | | 8945 (1)| 00:00:45 |
| 5 | HASH GROUP BY | | 100 | 300 | | 8945 (1)| 00:00:45 |
| 6 | VIEW | | 707K| 2071K| | 8945 (1)| 00:00:45 |
| 7 | HASH UNIQUE | | 707K| 4833K| 153M| 8945 (1)| 00:00:45 |
| 8 | TABLE ACCESS FULL| T2 | 9999K| 66M| | 656 (0)| 00:00:04 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOG_COUNTS"."DASHBOARD_ID"="T1"."DASHBOARD_ID")
Statistiken
----------------------------------------------------------
0 recursive calls
0 db block gets
16595 consistent gets
16362 physical reads
0 redo size
3508 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed
Die Pläne zeigen: auch Oracle schafft es nicht, die unterschiedlichen Varianten in eine Version zu transformieren - allerdings gibt es keinen großen Unterschied in der Performance: die Version, in der der Join vor der distinkten Sortierung ausgeführt wird, ist etwas langsamer (3,34 sec. gegenüber 2,24 sec. bzw. 2,12 sec.), aber offenbar ist das HASH GROUP BY bei der Sortierung in allen Fällen sehr effizient. Dabei liegen die Unterschiede erwartungsgemäß nicht auf der IO-Seite (also bei den consistent gets), sondern bei der CPU-Nutzung (wobei ich eine genauere Untersuchung der Session-Statistiken ausklammere).
Immerhin ergeben sich folgende Erkenntnisse:
- die Möglichkeiten der Transformationen sind in Oracle groß, aber es ist nicht schwer, Fälle zu finden, in denen sie nicht genügen.
- es bleibt eine gute Idee, Filterungen und (eindeutige) Sortierungen möglichst früh durchzuführen, um Zwischenergebnisse klein zu halten.
- durch HASH GROUP BY sind Gruppierungsoperationen in Oracle sehr effizient (und das gilt sicher auch für den Fall von HASH UNIQUE und Sortierungen) - andere RDBMS haben damit unter Umständen größere Schwierigkeiten.
Was meinem Test fehlt (insbesondere um den dritten Punkt zu untermauern), ist ein Vergleich mit einem identischen Postgres-Beispiel auf gleicher Hardware, aber dazu fühle ich mich gerade nicht verpflichtet (zumal auch die gleiche Hardware nur ein erster Schritt auf dem Weg zur Herstellung vergleichbarer Voraussetzungen wäre - die Konfigurationen der Datenbanken wäre ein zweiter).
Nachtrag 26.01.2014: wahrscheinlich sollte ich gelegentlich damit anfangen, meine Blog-Einträge vollständig zu durchdenken, ehe ich sie veröffentliche. Beim erneuten Nachdenken über mögliche Fälle, in denen die Variante "eindeutige Sortierung vor dem Join" deutlich schneller sein könnte als die "eindeutige Sortierung nach dem Join" fiel mir jedenfalls ein, dass eine deutlich größere Projektion (also Spaltenauswahl im Ergebnis) aus der kleinen Tabelle T1 das Datenvolumen des Joins deutlich vergrößert. Im Beispiel wäre dazu in T1 eine weitere sehr breite Spalte (padding) zu ergänzen:
create table t1
as
select rownum dashboard_id
, 'Dashboard ' || rownum name
, lpad('*', 1000, '*') padding
from dual
connect by level <= 1000;
Dazu gehören dann die folgenden geringfügig angepassten Queries für die Fälle 1 und 2 mit den zugehörigen Autotrace-Statistiken:
select t1.name
, t1.padding
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
, t1.padding
order by 3 desc;
99 Zeilen ausgewählt.
Abgelaufen: 00:00:26.18
Ausführungsplan
----------------------------------------------------------
Plan hash value: 344611024
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 72917 | | 495K (1)| 00:41:15 |
| 1 | SORT ORDER BY | | 71 | 72917 | | 495K (1)| 00:41:15 |
| 2 | HASH GROUP BY | | 71 | 72917 | | 495K (1)| 00:41:15 |
| 3 | VIEW | VM_NWVW_1 | 500K| 489M| | 494K (1)| 00:41:15 |
| 4 | HASH GROUP BY | | 500K| 488M| 10G| 494K (1)| 00:41:15 |
|* 5 | HASH JOIN | | 9999K| 9765M| | 659 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL| T1 | 100 | 99K| | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| T2 | 9999K| 66M| | 656 (0)| 00:00:04 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
Statistiken
----------------------------------------------------------
1 recursive calls
0 db block gets
16611 consistent gets
15969 physical reads
0 redo size
5612 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed
select t1.name
, t1.padding
, log_counts.ct
from t1
join (select dashboard_id
, count(distinct user_id) as ct
from t2 time_on_site_logs
group by dashboard_id
) log_counts
on log_counts.dashboard_id = t1.dashboard_id
order by log_counts.ct desc ;
99 Zeilen ausgewählt.
Abgelaufen: 00:00:02.22
Ausführungsplan
----------------------------------------------------------
Plan hash value: 1087308416
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 100K| | 8948 (1)| 00:00:45 |
| 1 | SORT ORDER BY | | 100 | 100K| | 8948 (1)| 00:00:45 |
|* 2 | HASH JOIN | | 100 | 100K| | 8947 (1)| 00:00:45 |
| 3 | VIEW | | 100 | 1600 | | 8945 (1)| 00:00:45 |
| 4 | HASH GROUP BY | | 100 | 1600 | | 8945 (1)| 00:00:45 |
| 5 | VIEW | VM_NWVW_1 | 707K| 10M| | 8945 (1)| 00:00:45 |
| 6 | HASH GROUP BY | | 707K| 4833K| 153M| 8945 (1)| 00:00:45 |
| 7 | TABLE ACCESS FULL| T2 | 9999K| 66M| | 656 (0)| 00:00:04 |
| 8 | TABLE ACCESS FULL | T1 | 100 | 99K| | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOG_COUNTS"."DASHBOARD_ID"="T1"."DASHBOARD_ID")
Statistiken
----------------------------------------------------------
24 recursive calls
0 db block gets
16613 consistent gets
16168 physical reads
0 redo size
5589 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed
Während sich die zweite Query nahezu unverändert verhält, wird die erste Query massiv kostspieliger. Der Grund dafür ist, dass der Hash Join in Step 5 ein Zwischenergebnis liefert, das diesmal nicht nur sehr viele Datensätze enthält (knapp 10M rows), sondern auch sehr breite (pro Datensatz kommen die 1000 Byte für padding hinzu). Auf der Basis dieses großen Ergebnisses wird die HASH GROUP BY Operation in Step 4 dann deutlich aufwändiger und erfordert eine sehr viel umfangreichere Zwischenspeicherung während der gruppierenden Sortierung (TempSpc = 10G statt 306M in der ursprünglichen Variante). Im Ergebnis führt diese Mehrarbeit zu einer Erhöhung der Laufzeit über 25 Sekunden - was gegenüber den 2 Sekunden der umformulierten Version dann doch schon recht dramatisch ist. Um die Untersuchung diesmal nicht zu früh abzuschließen hier noch der Plan der beiden Varianten der ersten Query mit rowsource Statistiken:
select /*+ gather_plan_statistics */
t1.name
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
order by 2 desc;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:04.31 | 16611 | 16104 | | | |
| 1 | SORT ORDER BY | | 1 | 100 | 99 |00:00:04.31 | 16611 | 16104 | 9216 | 9216 | 8192 (0)|
| 2 | HASH GROUP BY | | 1 | 100 | 99 |00:00:04.31 | 16611 | 16104 | 991K| 991K| 1341K (0)|
| 3 | VIEW | VM_NWVW_1 | 1 | 707K| 9900 |00:00:04.31 | 16611 | 16104 | | | |
| 4 | HASH GROUP BY | | 1 | 707K| 9900 |00:00:04.31 | 16611 | 16104 | 1224K| 1224K| 22M (0)|
|* 5 | HASH JOIN | | 1 | 9999K| 9900K|00:00:02.86 | 16611 | 16104 | 1079K| 1079K| 1255K (0)|
| 6 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 18 | 0 | | | |
| 7 | TABLE ACCESS FULL| T2 | 1 | 9999K| 9999K|00:00:00.92 | 16593 | 16104 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
select /*+ gather_plan_statistics */
t1.name
, t1.padding
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
, t1.padding
order by 3 desc
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:27.90 | 16611 | 16169 | | | |
| 1 | SORT ORDER BY | | 1 | 71 | 99 |00:00:27.90 | 16611 | 16169 | 124K| 124K| 110K (0)|
| 2 | HASH GROUP BY | | 1 | 71 | 99 |00:00:27.90 | 16611 | 16169 | 684K| 684K| 1310K (0)|
| 3 | VIEW | VM_NWVW_1 | 1 | 500K| 9900 |00:00:27.87 | 16611 | 16169 | | | |
| 4 | HASH GROUP BY | | 1 | 500K| 9900 |00:00:27.87 | 16611 | 16169 | 11M| 1824K| 34M (0)|
|* 5 | HASH JOIN | | 1 | 9999K| 9900K|00:00:04.59 | 16611 | 16169 | 684K| 684K| 1269K (0)|
| 6 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 18 | 0 | | | |
| 7 | TABLE ACCESS FULL| T2 | 1 | 9999K| 9999K|00:00:01.84 | 16593 | 16169 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
Hier bestätigt sich, dass die HASH GROUP BY Operation mit den deutlich vergrößerten Datensätzen mehr Arbeit hat, was sich auf die Laufzeit der Queries auswirkt. Bleibt die Frage, ob das auch meine Behauptung der Effektivität von HASH GROUP BY gegenüber anderen Gruppierungsverfahren in Frage stellt? Dazu ein letzter Versuch: ich deaktiviere HASH GROUP BY für die Session und vergleiche das Verhalten der gerade untersuchten Zugriffe noch einmal:
select /*+ gather_plan_statistics */ t1.name
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
order by 2 desc;
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:12.16 | 16611 | 16140 | | | |
| 1 | SORT ORDER BY | | 1 | 100 | 99 |00:00:12.16 | 16611 | 16140 | 9216 | 9216 | 8192 (0)|
| 2 | SORT GROUP BY | | 1 | 100 | 99 |00:00:12.16 | 16611 | 16140 | 761K| 761K| 676K (0)|
|* 3 | HASH JOIN | | 1 | 9999K| 9900K|00:00:03.47 | 16611 | 16140 | 1079K| 1079K| 1236K (0)|
| 4 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 18 | 0 | | | |
| 5 | TABLE ACCESS FULL| T2 | 1 | 9999K| 9999K|00:00:01.62 | 16593 | 16140 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
select /*+ gather_plan_statistics */
t1.name
, t1.padding
, count(distinct t2.user_id)
from t1
join t2
on t1.dashboard_id = t2.dashboard_id
group by t1.name
, t1.padding
order by 3 desc;
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:01:33.00 | 16611 | 16078 | | | |
| 1 | SORT ORDER BY | | 1 | 71 | 99 |00:01:33.00 | 16611 | 16078 | 124K| 124K| 110K (0)|
| 2 | SORT GROUP BY | | 1 | 71 | 99 |00:01:33.00 | 16611 | 16078 | 11M| 2942K| 10M (0)|
|* 3 | HASH JOIN | | 1 | 9999K| 9900K|00:00:03.26 | 16611 | 16078 | 684K| 684K| 1233K (0)|
| 4 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 18 | 0 | | | |
| 5 | TABLE ACCESS FULL| T2 | 1 | 9999K| 9999K|00:00:01.44 | 16593 | 16078 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."DASHBOARD_ID"="T2"."DASHBOARD_ID")
Zunächst das Positive: mit dem alten SORT GROUP BY kommt der Optimizer zu akkurateren Schätzungen der cardinalities (jedenfalls bei der Query mit geringer Satzbreite). Dafür wird die Gruppierung aber deutlich langsamer: für den Fall der geringen Satzbreite steigt die Laufzeit von 4,31 sec. auf 12,16 sec. (dass die Laufzeiten bei Erzeugung von rowsource Statistiken höher werden, liegt übrigens an den verwendeten OS-Timer-Funktionen, wie hier erläutert wird). Für die breiteren Datensätze ergeben sich 1:33 min statt 27,9 sec. Im gegebenen Fall ist das also eine Verdreifachung der Laufzeit bei Verzicht auf das HASH GROUP BY.
Was bleibt also festzuhalten?
- HASH GROUP BY ist ein sehr effektives Gruppierungsverfahren.
- die bewusste Steuerung von SQL-Verabeitungsschritten bleibt ein wichtiges Mittel der Optimierung. Transformationen sind in ihren Möglichkeiten noch immer beschränkt.
- Für Performance-Vergleiche ist es immer sinnvoll, die Testszenarien detailliert zu beschreiben, da eine relativ geringfügige Änderung der Versuchsanordnung zu massiven Verhaltensänderungen führen kann. Der im Namen des zugrunde liegenden Artikels erscheinende Faktor 50 mag unter bestimmten Umständen erreichbar sein, ist aber stark vom Szenario abhängig.
Nachtrag 28.01.2014: inzwischen gibt es auch im High Performance SQL Blog einen Nachtrag zum Thema, der sich mit dem Verhalten anderer RDBMS (MySQL, SQL Server, Oracle) beschäftigt und zu ähnlichen Ergebnissen kommt, wie denen, die ich hier veröffentlicht habe: Oracle und der SQL Server sind bei diesen Fragen grundsätzlich sehr viel flotter als die postgres und MySQL, was aber nicht an einer klugen Transformation liegt. Auch für die kommerziellen Systeme bringt die Umformulierung eine - eher moderate - Beschleunigung. Bemerkenswert ist allerdings, dass Postgres bei der Ursprungs-Query dramatisch schlechter abschneidet als alle anderen RDBMS.
Nachtrag 03.11.2014: dass man auch mit postgres eine sehr viel bessere Performance für den speziellen Fall des ursprünglichen Beispiels erreichen kann, hat Hubert Lubaczewski gelegentlich gezeigt.
Nachtrag 03.11.2014: dass man auch mit postgres eine sehr viel bessere Performance für den speziellen Fall des ursprünglichen Beispiels erreichen kann, hat Hubert Lubaczewski gelegentlich gezeigt.
Freitag, Januar 24, 2014
Online -Reorganisation für Partitionierte Tabellen mit 12c
Richard Foote schreibt in seinem Blog über die Möglichkeiten der Online Reorganisation von Tabellen in den Releases 11 und 12:
- 12c Online Partitioned Table Reorganisation Part I (Prelude) erklärt das Verhalten in Version 11:
- ALTER TABLE ... MOVE ONLINE ist keine gültige Option, da die ONLINE-Option nur für IOTs verfügbar ist.
- ein ALTER TABLE ... MOVE kann nicht erfolgen, wenn andere Sessions offene Transaktionen mit DML auf die Tabelle durchführen ("ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired").
- ein laufendes ALTER TABLE ... MOVE hindert umgekehrt andere Sessions an den Durchführung von DML-Operationen und hinterlässt alle zugehörigen Indizes im Status UNUSABLE.
- ein ähnliches Verhalten ergibt sich bei der Reorganisation von Tabellen-Partitionen.
- die Alternative dbms_redefinition hat ihre eigenen Probleme und ist deutlich unhandlicher.
- 12c Online Partitioned Table Reorganisation Part II (Move On) erläutert die Änderungen, die sich mit 12c ergeben haben:
- Tabellen-Partitionen können jetzt online reorganisiert werden, wobei die zugehörigen Indizes aktualisiert werden und verfügbar bleiben.
- erforderlich ist dafür die Syntax: ALTER TABLE ... PARTITION ... UPDATE INDEXES ONLINE.
- Die MOVE-Operation beim Vorliegen offener Transaktionen scheitert nun nicht mehr an ORA-00054, sondern wartet auf das Commit der zugreifenden Sessions, um ein exklusives table partition lock bekommen zu können.
- die zugehörigen Indizes bleiben im Status USABLE.
- nach dem Start des MOVE-Kommandos abgesetzte DML-Operationen anderer Sessions können problemlos durchgeführt werden.
- Leider betrifft die Möglichkeit der Online Reorganisation zur Zeit nur partitionierte Tabellen, während nicht-partitionierte Tabellen davon ausgeschlossen sind - was ein Argument dafür sein könnte, solche Tabellen als partitionierte Objekte mit einer Partition anzulegen.
Dienstag, Januar 21, 2014
Probleme mit table functions und cardinality Hints
Matthias Rogel weist auf ein Problem hin, das sich bei der Kombination der nützlichen (allerdings auch undokumentierten) Hints cardinality (bzw. der entsprechenden aktuellen Variante opt_estimate - mit denen man dem Optimizer Informationen zur tatsächlichen Größe eines Zwischenergebnisses geben kann) und materialize (zur Materialisierung einer CTE) mit table functions ergibt (für die die Größenabschätzungen bekanntlich immer problematisch sind - Details zum Thema und zu den möglichen Lösungen findet man bei Adrian Billington): in einem solchen Fall wird der cardinality Hint nur berücksichtigt, wenn er zusammen mit dem materialize Hint in der CTE-Definition (also WITH-clause) erscheint, was die Möglichkeiten der Einflussnahme über den Hint deutlich beschränkt.
Das waren jetzt ziemlich viele Klammern und wahrscheinlich ist der vorangehende Abschnitt dadurch nicht lesbarer geworden, zumal es ihm an Details und Beispielen mangelt - ohne Lektüre des Basisartikels kann man damit vermutlich nicht viel anfangen. Dafür ergänze ich hier noch den praktischen Hinweis, dass im CBO-Trace (aka Event 10053) für 12c die Angaben zu den in der Query verwendeten Hints explizit aufgeführt werden und zwar am Ende des Trace-Files. Damit kann man endlich sehen, ob der Optimizer einen Hint aus inhaltlichen oder syntaktischen Gründen ignoriert hat:
-- 12.1.0.1
alter session set events '10053 trace name context forever, level 1';
SQL> select /*+ cardinality (t 10) */ count(*) from t;
COUNT(*)
----------
91600
Abgelaufen: 00:00:00.07
SQL> select /*+ cardianlity (t 10) */ count(*) from t;
COUNT(*)
----------
91600
alter session set events '10053 trace name context off';
Im erzeugten Trace-File ergeben sich folgende Angaben:
... Dumping Hints ============= atom_hint=(@=0000000018424FD8 err=0 resol=1 used=1 token=1018 org=1 lvl=3 txt=OPT_ESTIMATE (TABLE "T"@"SEL$1" ROWS=10.000000 ) ) ====================== END SQL Statement Dump ====================== ... Dumping Hints ============= ====================== END SQL Statement Dump ======================
Der korrekte Hint der ersten Query wird demnach angezeigt, während der falsch geschriebene Hint im zweiten Fall ignoriert wird.
Nachtrag 21.01.2014: Randolf Geist zeigt in einem Kommentar zum Artikel, dass es tatsächlich doch noch ein paar Möglichkeiten gibt, mit denen man einen cardinality Hint außerhalb der CTE platzieren kann.
Samstag, Januar 18, 2014
Schnelle Datensatz-Generierung
Im OTN Forum hat sich dieser Tage ein Thread mit einer recht interessanten Diskussion zum Thema der schnellsten Möglichkeit zur Erzeugung einer großen Mengen von Test-Datensätzen ergeben. Ausgangspunkt war, dass der Fragesteller des Threads mit der Performance eines INSERT mit einer connect by level Generierung von 10 Millionen Datensätzen nicht zufrieden war und nach schnelleren Optionen suchte. David Berger schlug eine PL/SQL-Lösung mit Collections und Bulk Insert vor, die tatsächlich im System des OP zu einer deutlichen Beschleunigung führte, und erklärte "There are cases where the PL/SQL can be faster then a pure SQL". Obwohl ich der Meinung bin, dass SQL fast immer die schnellere Lösung liefert, will ich der Aussage nicht grundsätzlich widersprechen, da man entsprechende Fälle gewiss konstruieren kann, hatte aber die Vermutung, dass im gegebenen Fall auch schnelle SQL-Lösungen möglich sein sollten und die schlechte Performance der connect by level Variante auf der Größe der Level-Angabe beruhte - entsprechend der Ausführungen von Tanel Poder, der vor einigen Jahren auf die großen UGA-Belastungen durch die massiven Rekursionen hinwies und alternativ eine Lösung mit einem cartesischen Produkt kleiner Generator-Queries vorschlug. Diese Variante brachte im System des OP allerdings nur eine geringfügige Beschleunigung, was mich wunderte und Jonathan Lewis dazu brachte, das Thema genauer zu untersuchen. Später hat dann auch Randolf Geist zusätzliche Informationen beigesteuert. Hier folgen ein paar Punkte, die mir besonders erinnerungswürdig scheinen:
- die PL/SQL-Lösung skaliert nicht, da sie den Aufbau der kompletten Ergebnismenge im Speicher erfordert, während die Speichernutzung der SQL-Variante mit cartesian joins moderat bleibt.
- die im Testszenario des OP verwendete dbms_random-Funktion verursachte bei Jonathan Lewis ca. 85% der Laufzeit, weshalb er eine Parallelisierung des Funktionsaufruf vorschlug (oder auch den Verzicht auf den Aufruf und die Verwenung von rownum).
- eine Parallelisierung der PL/SQL-Lösung wäre über ein Splitting der ursprünglichen Prozedur in mehrere Teile möglich, die jeweils bestimmte Datenbereiche abarbeiten. Entscheidend ist dabei, dass es nicht darum geht, das INSERT zu parallelisieren, sondern den teuren Funktionsaufruf.
- bei der Parallelisierung der Funktionsaufrufe ist (natürlich) die Anzahl der CPUs entscheidend.
- die Verwendung von ROWNUM scheint einen Bug bei der Parallelisierung hervorzurufen. Durch Vermeidung der Pseudo-Column konnte Randolf Geist die tatsächliche Parallelisierung der Operation deutlich verbessern.
- Zur den Performance-Vorteilen von PL/SQL zu SQL auf seiner Workstation schreibt Jonathan Lewis: "My guess about the PL/SQL running faster (on my machine, at any rate) is that it's a measure of the inter-process communication costs of VMWare which appears in the SQL but doesn't appear when I run two independent serial processes for the pl/sql."
Sollte der Thread noch weitere interessante Details hervorbringen, werde ich sie ergänzen.
Sonntag, Januar 12, 2014
Informationen zu den X$-Tabellen
Tanel Poder hat dieser Tage offenbar eine Artikelserie zu den X$-Tabellen begonnen - so jedenfalls deute ich die Angabe Part 1 im Namen des ersten Artikels -, die den dynamischen Performance-Views (V$) zugrunde liegen:
- Oracle X$ tables – Part 1 – Where do they get their data from? erklärt, dass die übliche Interpretation der X$-Objekte als Repräsentation von Memory-Strukturen nicht falsch, aber auch nicht ganz vollständig ist. Für manche Objekte (z.B. x$ksuse unter v$session) ist die Beziehung tatsächlich unmittelbar und die Adressangabe in der x$-Tabelle verweist direkt auf einen zugehörigen Speicherort in der SGA, aber in anderen Fällen (etwa x$kcccp) führt der Adress-Verweis in die UGA, weil interne Helper-Funktionen die erforderlichen Daten dorthin kopieren. In diesen Fällen ist demnach zusätzliche Arbeit zur Präsentation der Daten erforderlich.
Die hoffentlich folgenden Artikel zum Thema werde ich hier gelegentlich ergänzen.
Nachtrag 15.01.2014: Mit Verweis auf Tanel Poders Artikelserie hat Jonathan Lewis in seinem Blog eine table function veröffentlicht, mit der sich bestimmen lässt, welche X$ Objekte auf welche Memory-Bereiche Bezug nehmen.
Nachtrag 15.01.2014: Mit Verweis auf Tanel Poders Artikelserie hat Jonathan Lewis in seinem Blog eine table function veröffentlicht, mit der sich bestimmen lässt, welche X$ Objekte auf welche Memory-Bereiche Bezug nehmen.
Mittwoch, Januar 08, 2014
Full Table Scan für sehr kleine Tabellen
Jonathan Lewis zeigt in seinem Blog, dass in einem ASSM-Tablespace auch für eine Tabelle, die nur eine geringe Zeilenanzahl enthält (im Beispiel genau einen Datensatz), abhängig von mehr oder minder zufälligen Faktoren (vor allem der process id) relativ viele consistent gets für den full table scan erforderlich sein können (im Beispiel 22 consistent gets and nicht die 3, die man vielleicht in einem MSSM-Tablespace erwarten würde). Ein derart unglücklich erzeugte Tabelle kann man durch ein ALTER TABLE ... MOVE auf eine weniger unerfreuliche Größe reduzieren, da in diesem Fall andere Mechanismen aktiv werden.
Montag, Januar 06, 2014
Intervallbildung mit Tabibitosan
Auf die vom japanischen Oracle ACE Aketi Jyuuzou vor einigen Jahren im OTN-Forum vorgestellte Tabibitosan-Technik zur Bildung von Intervallen und insbesondere zur Ermittlung von Intervall-Lücken, habe ich hier vor einiger Zeit schon einmal hingewiesen. Jetzt hat Rob van Wijk in seinem Blog ein deutlich umfassenderes Beispiel zu den Möglichkeiten dieses nützlichen Werkzeugs veröffentlicht, und dabei auch die Performance des Verfahrens untersucht.
Samstag, Januar 04, 2014
Zugriff über Index-Kombinationen
Jonathan Lewis hat in den letzten Tagen zwei Artikel veröffentlicht, die sich mit den Möglichkeiten der Kombination von Indizes beim Zugriff beschäftigen. Üblicherweise entscheidet sich der Optimizer bekanntlich für einen der existierenden Indizes, der ihm am besten geeignet erscheint, um einen selektiven Zugriff zu gewährleisten und verwendet weitere Bedingungen als Filter für die über den Index ermittelte Menge von Datensätzen:
- Conditional SQL – 4: beschäftigt sich mit einem besonderen Fall konditionaler Ausführungspläne, bei denen abhängig vom Vorliegen eines Bindewerts ein gefiltertes Ergebnis oder alle Datensätze geliefert werden sollen (eine Standardvariante dazu wäre z.B.: where t1.n1 = nvl(:n1,t1.n1); wenn ein Bindewert vorliegt, wird mit diesem verglichen, sonst erfolgt der - immer true liefernde - Vergleich der Spalte mit sich selbst). Im speziellen Fall wurden zwei über index concatenation verknüpfbare Bedingungen mit einer dritten Bedingung (LENGTH ( :b7) IS NULL) OR-verknüpft, was den Optimizer dann zum full table scan übergehen lässt. Dieses Verhalten ändert sich auch nicht, wenn man auf die dubiose LENGTH-Verwendung verzichtet. Insgesamt bleibt festzuhalten: "the optimizer’s ability to introduce concatenation is limited".
- Index Hash: zeigt, dass das costing für HASH JOINS, bei denen die Inhalte zweier Indizes einer Tabelle verknüpft werden (um auf den teureren Zugriff auf eine relativ breite Tabelle verzichten zu können), offenbar ein paar recht massive Inkonsistenzen - und Fehler - enthält: nicht immer wird der günstigste Plan verwendet und die Kosten der einzelnen Index-Zugriffe verändern sich deutlich, wenn sie im index join verwendet werden sollen (statt einzeln angesprochen zu werden). Plausibel sieht das Verhalten jedenfalls nicht aus.
Abonnieren
Posts (Atom)