Jonathan Lewis hat dieser Tage eine Quiz-Frage formuliert, bei der es darum ging, wie es dazu kommen kann, dass der verfügbare Speicherplatz im (Daten-)Block einer Heap-Tabelle (angegeben als avsp – available space bzw. tosp – total space; der Unterschied der beiden Angaben wird in einer Fußnote erklärt - man findet eine ähnliche Erklärung aber auch schon in einer Erläuterung von Steve Adams vom Dezember 2000; ungefähr zu dieser Zeit habe ich zum ersten Mal eine Oracle-Datenbank gesehen) deutlich kleiner ist als der freie Bereich in der Blockmitte, der durch die Angaben fsbo (free space, beginning of: das Ende des block headers) und fseo (free space, end of: der Beginn der row Einträge am Ende des Blocks) bestimmt ist. Die Antwort darauf lautet: Oracle benötigt für jeden Satz mindestens 11 byte (2 im row directory und 9 im row heap). Wenn ein Datensatz diese Größe unterschreitet, dann wird die Differenz für den Fall späterer row migration reserviert. Interessant ist auch Randolf Geists Hinweis auf die Rolle von direct path/conventional path beim Insert.
Montag, Dezember 31, 2012
Donnerstag, Dezember 27, 2012
Rowsource Statistics für SQL Operationen
Alexander Anokhin hat dieser Tage zwei sehr interessante Artikel zum Thema der Rowsource Statistics veröffentlicht, also jener detaillierten Informationen zur Laufzeit und Ressourcennutzung von Ausführungsschritten im Execution Plan, deren Erfassung über den Hint GATHER_PLAN_STATISTICS, den Parameter STATISTICS_LEVEL oder SQL-Trace aktiviert werden kann:
Teil 1: Timing: query execution statistics (rowsource statistics). Part 1: How it works
- Aktivierung der Erfassung von Rowsource Statistics
- Darstellung der Ergebnisse mit Hilfe von DBMS_XPLAN.DISPLAY_CURSOR, das allerdings nur die Optionen ALL und LAST anbietet, also Durchschnittswerte und die Ergebnisse der letzten Ausführung.
- Darstellung der internen Funktionsaufrufe mit Anokhins DIGGER-Tool, das zeigt, dass alle Aufrufe in einer Snapshot-Routine gewrapped sind (qerstFetch mit: qerstSnapStats und qerstUpdateStats)
- die Funktionen werden für jede einzelne Ergebniszeile aufgerufen, was dann schon zum zweiten Artikel überleitet, der Aussagen zur Genauigkeit der Ergebnisse und zum Performance-Overhead liefert.
- die Häufigkeit der Timestamp-Aufrufe wird über der Parameter _rowsource_statistics_sampfreq (0 = keine Statistik, 1 = Timing für jeden Aufruf von qerstSnapStats()/qerstUpdateStats, N = jeder N-te Aufruf wird protokolliert) gesteuert. Wenn der Parameter nur ein Sample der Aufrufe protokolliert, können die Ergebnisse unpräzise werden, da dann ein (hoffentlich) repräsentativer Wert für N Ausführungen eingesetzt wird.
- Andererseits führt das Timing für jeden Aufruf zum höchsten Overhead.
- mehrere Tabellen zeigen, dass der Overhead der Rowsource Statistics mit niedrigerer _rowsource_statistics_sampfreq signifikant wächst.
- die Größe des Overheads hängt ab von:
- Anzahl der Time-Calls, die wiederum von der Set-Größe und dem Sampling abhängt
- Implementierung der Funktion zur Ermittlung der Timestamps, die je nach OS unterschiedlich performant ist
- genaue Ausführung innerhalb eines OS (denn auch dort gibt's unterschiedliche Varianten, was anhand von Linux vorgeführt wird)
- interessant ist auch noch die ausführliche Antwort auf einen Kommentar von Nikolay Savvinov
Nachtrag 08.01.2012: hier noch ein Link auf Randolf Geists Beobachtung, dass row source statistics sampling die Verwendung von Vector bzw. Batched I/O deaktiviert (was in der Nested Loops Optimierung in 11g eine Rolle spielt).
Sonntag, Dezember 23, 2012
String Aggregation in Oracle
Philipp Salvisberg von Trivadis hat eine schöne Zusammenstellung diverser Optionen zur Zusammenfassung von String-Werten in einer konkatenierten Liste veröffentlicht - also jener Anforderung, für die Tom Kyte vor vielen Jahren die STRAGG-Funktion lieferte: der Verknüpfung der String-Werte einer Gruppe (etwa der Mitarbeiter eines Departments in der EMP-Tabelle) in einer Komma-separierten Liste. In dieser Zusammenstellung erscheinen verschiedene PL/SQL-Versionen, user-defined aggregate functions (des ODCIAggregate interface), XML-Varianten und schließlich die LISTAGG-Aggregat-Funktion aus 11.2, jeweils mit einer Angabe ihrer Verfügbarkeit in den Oracle-Releases und einem Performance-Vergleich (bei dem die XML-Lösungen schlecht und LISTAGG am besten abschneidet).
Eine ähnliche Zusammenstellungen solcher String-Aggregationsfunktionen findet man auch bei Tim Hall, der außerdem noch die (undokumentierte) WM_CONCAT Funktion erwähnt und darüber hinaus auf eine von William Robertson vorgeschlagene Variante mit hierarchischen Queries und auf die Collect-Funktion, die Adrian Billington gelegentlich genauer erläutert hat, verweist.
Freitag, Dezember 21, 2012
TEMP usage Angabe in ASH
Eine kurze Notiz, da ich in letzter Zeit häufiger nach historischen Informationen zur Ressourcennutzung gesucht habe, die es (noch) nicht in ASH bzw. AWR gab: Marc Fielding weist darauf hin, dass DBA_HIST_ACTIVE_SESS_HISTORY ab 11.2 eine Spalte TEMP_SPACE_ALLOCATED enthält ("Amount of TEMP memory (in bytes) consumed by this session at the time this sample was taken"). Das sollte die nachträgliche Analyse entsprechender Probleme massiv erleichtern.
Costing für ROWNUM-Queries
Mit dem folgenden Eintrag bin ich eher weniger glücklich, weil es mir nicht gelungen ist, das beobachtete Verhalten in einem Test nachzustellen. Leider habe ich zur Zeit auch nur den Execution Plan, aber keinen Zugriff auf das System, in dem ich das Verhalten beobachtet habe. Aber vielleicht sollte ich erst mal erklären, wovon ich überhaupt rede: dieser Tage wurde mir eine Query mit einer Einschränkung WHERE rownum < 10 gezeigt, bei der der CBO in 11.1.0.7 ganz offenbar zu einer recht abwegigen Ausführungsstrategie gelangt war:
select * from view_xyz where rownum < 10;
-- dazu der Plan (anonymisiert und ohne Time-Angabe) ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 1440 | 36 (3)| | | | 1 | PARTITION LIST SINGLE | | 2 | 52 | 9534 (1)| 1 | 1 | |* 2 | TABLE ACCESS FULL | DIM1 | 2 | 52 | 9534 (1)| 1 | 1 | |* 3 | COUNT STOPKEY | | | | | | | | 4 | NESTED LOOPS OUTER | | 10 | 1600 | 36 (3)| | | | 5 | NESTED LOOPS OUTER | | 5 | 765 | 32 (4)| | | | 6 | NESTED LOOPS OUTER | | 5 | 610 | 17 (6)| | | |* 7 | HASH JOIN OUTER | | 5 | 555 | 7 (15)| | | | 8 | PARTITION LIST ALL | | 5 | 505 | 2 (0)| 1 | 1523 | | 9 | TABLE ACCESS FULL | FAKT1 | 5 | 505 | 2 (0)| 1 | 1523 | | 10 | TABLE ACCESS FULL | DIM2 | 171 | 1710 | 4 (0)| | | | 11 | TABLE ACCESS BY INDEX ROWID | DIM3 | 1 | 11 | 2 (0)| | | |* 12 | INDEX RANGE SCAN | DIM3_IX | 1 | | 1 (0)| | | | 13 | PARTITION LIST SINGLE | | 1 | 31 | 3 (0)| 1 | 1 | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID| DIM4 | 1 | 31 | 3 (0)| 1 | 1 | |* 15 | INDEX RANGE SCAN | DIM4_IX | 1 | | 2 (0)| 1 | 1 | | 16 | VIEW | | 2 | 14 | 1 (0)| | | | 17 | CONCATENATION | | | | | | | | 18 | TABLE ACCESS BY INDEX ROWID | DIM5 | 1 | 19 | 2 (0)| | | |* 19 | INDEX RANGE SCAN | DIM5_IX | 1 | | 1 (0)| | | | 20 | TABLE ACCESS BY INDEX ROWID | DIM5 | 1 | 19 | 2 (0)| | | |* 21 | INDEX UNIQUE SCAN | DIM5_IX | 1 | | 1 (0)| | | -----------------------------------------------------------------------------------------------------
Das entscheidende Problem dabei ist der Zugriff auf die Faktentabelle FAKT1 in step 8 und 9: die Tabelle wird ohne jede Einschränkung als BUILD table für den HASH JOIN OUTER verwendet, muss also komplett in den Speicher gelesen werden, ehe der Zugriff auf die Dimensionstabelle DIM2 erfolgt. Da die Faktentabelle 1523 Partitionen umfasst und mehr als 140M rows enthält, kann man auf die gewünschten 9 Ergebniszeilen lange warten. Dabei ergibt sich die cardinality 5 offenbar aus der halbierten Rownum-Einschränkung (9 geteilt durch die 2 aus step 16). Anscheinend vergisst der CBO in diesem Fall an irgendeiner Stelle, dass die rownum-Einschränkung im Fall eines HASH JOINs, bei dem die komplette Faktentabelle (PARTITION LIST ALL) in den Speicher gelesen werden muss, ehe der folgende Operation beginnen kann, nicht wirklich viel bringt.
Ein interessanter Fall, aber leider einer, den ich nicht reproduzieren konnte. Möglicherweise kann ich gelegentlich noch mal einen Blick auf das fragliche System werfen. Bis dahin bleibt's erst mal eine Geschichte mit offenem Schluss.
Sonntag, Dezember 16, 2012
SSAS: Test und Konfiguration
Christian Bracchi hat dieser Tage zwei interessante Artikel zum Thema SSAS veröffentlicht:
- Analysis Services Testing Best Practices: mit einem plausiblen Vorschlag zur Durchführung von Performance-Tests für SSAS-Installationen (welcher concurrency Grad? Welches Test-Query-Tool? Welche Perfmon-Counter? Und abschließend der wichtige Hinweis: "BACKUP your test result!").
- SQL 2012 Configuration Tuning: mit Informationen zur Wahl sinnvoller Konfigurationseinstellungen für relativ große Server mit NUMA. Interessant sind auch die Links auf weiterführende Artikel.
Freitag, Dezember 14, 2012
Cost für Group By
Alexandr Antonov weist in seinem Blog auf eine Änderung des Costings für GROUP BY Operationen in 11g hin und nennt dafür folgende Formel:
GROUP BY CARD = JOIN CARD * SEL(t1.fil1) / SEL(t2.fil2)
Ich habe daraufhin noch mal darüber nachgedacht, wie die Formel für das Costing von GROUP BY vorher ausgesehen haben könnte und, nachdem mir dazu nicht viel eingefallen war, noch mal in Jonathan Lewis' Cost Based Oracle (S. 388) nachgeschlagen und dort folgende Erklärung gefunden:
Dazu ein kleiner Test mit 11.1.0.7:In general, the optimizer estimates the number of distinct combinations of N columns by multiplying the individual num_distinct values, and then dividing by the square root of 2 (N-1) times.
drop table t3;
create table t3
as
select mod(rownum, 10) col1
, mod(rownum, 20) col2
from dual
connect by level <= 10000;
exec dbms_stats.gather_table_stats(user, 't3', method_opt => 'for all columns size 1')
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
select col1, col2, count(*)
from t3
group by col1, col2;
ALTER SESSION SET EVENTS '10053 trace name context OFF';
Im erzeugten CBO-Trace sieht man dann unter anderem Folgendes:
Access path analysis for T3
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T3[T3]
Table: T3 Alias: T3
Card: Original: 10000.000000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 7.11 Resp: 7.11 Degree: 0
Cost_io: 7.00 Cost_cpu: 1842429
Resp_io: 7.00 Resp_cpu: 1842429
Best:: AccessPath: TableScan
Cost: 7.11 Degree: 1 Resp: 7.11 Card: 10000.00 Bytes: 0
Grouping column cardinality [ COL1] 10
Grouping column cardinality [ COL2] 20
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T3[T3]#0
GROUP BY sort
GROUP BY adjustment factor: 0.707107
GROUP BY cardinality: 142.000000, TABLE cardinality: 10000.000000
Diese Angaben deuten an, dass die Aussagen aus Cost-Based Oracle grundsätzlich noch zutreffen: die GROUP BY cardinality ergibt sich als: (10 * 20)/1,4142 = 141,42. Was einerseits zur 142 im Trace passt und andererseits zeigt, dass mein Beispiel etwas zu symmetrisch ausgefallen ist (weil 2 durch die square root von 2 natürlich wieder square root von 2 ergibt). Der GROUP BY adjustment factor ist dabei anscheinend einfach 1,4142/2 = 0,7071 - was wiederum zeigt, dass meine Beispielwerte eher unglücklich gewählt sind, denn der Wert hängt nicht von den distinkten Werten ab, sondern von der Anzahl der GROUP BY Spalten:
- 2 Spalten: GROUP BY adjustment factor: 0,7071 = 1,4142/2
- 3 Spalten: GROUP BY adjustment factor: 0,5 = 1,4142/1,4142/2
- 4 Spalten: GROUP BY adjustment factor: 0,3535 = 1,4142/1,4142/1,4142/2
Das ist nicht uninteressant, hat aber erst einmal noch nicht allzu viel mit den Ausführungen des Herrn Antonov zu tun. Daher noch ein Blick in die CBO-Traces für das Antonov'sche Beispiel. Dort findet man für den Fall der Verwendung des neuen Verfahrens eine andere GROUP BY cardinality als für den Fall der Verwendung des alten Verfahrens (_optimizer_improve_selectivity => false):
-- default Verhalten GROUP BY adjustment factor: 1.000000 GROUP BY cardinality: 125.000000, TABLE cardinality: 500.000000 -- _optimizer_improve_selectivity => false GROUP BY adjustment factor: 1.000000 GROUP BY cardinality: 500.000000, TABLE cardinality: 500.000000
Da nur eine Spalte im GROUP BY erscheint, überrascht der GROUP BY adjustment factor 1 nicht. Die unterschiedlichen GROUP BY cardinality-Angaben sind, so weit ich sehe, schon die einzigen (relevanten) Unterschiede der CBO-Traces für beide Versionen. Eine Erklärung für die innere Logik, die der durch _optimizer_improve_selectivity repräsentierten Berechnung zugrunde liegt, findet ich dort nicht (was allerdings auch nicht überrascht, da die Inhalte des CBO-Traces in aller Regel nicht unbedingt verbose erläutert sind). Dazu ein weiterer Test, der das Beispiel auf Alexandr Antonovs Blog behutsam erweitert:
drop table t1;
drop table t2;
CREATE TABLE t1 AS
SELECT LEVEL AS id1,
MOD(LEVEL, 10) fil1,
MOD(LEVEL, 5) fil3,
rpad('x', 1000) padding
FROM dual
CONNECT BY LEVEL < 10000
;
CREATE TABLE t2 AS
SELECT LEVEL AS id2,
MOD(LEVEL, 20) fil2,
MOD(LEVEL, 15) fil4,
rpad('x', 1000) padding
FROM dual
CONNECT BY LEVEL < 10000
;
exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size 1')
exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'for all columns size 1')
-- Fall 1
explain plan for
SELECT t1.fil1
, t2.fil2
FROM t1,
t2
WHERE t2.id2 = t1.id1
and t1.fil3 = 1
AND t2.fil4 = 1
GROUP BY t1.fil1
, t2.fil2;
-- Fall 2
explain plan for
SELECT /*+ OPT_PARAM('_optimizer_improve_selectivity' 'false') */
t1.fil1
, t2.fil2
FROM t1,
t2
WHERE t2.id2 = t1.id1
and t1.fil3 = 1
AND t2.fil4 = 1
GROUP BY t1.fil1
, t2.fil2;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 142 | 2840 | 797 (1)| 00:00:10 |
| 1 | HASH GROUP BY | | 142 | 2840 | 797 (1)| 00:00:10 |
|* 2 | HASH JOIN | | 667 | 13340 | 796 (1)| 00:00:10 |
|* 3 | TABLE ACCESS FULL| T2 | 667 | 6670 | 398 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| T1 | 2000 | 20000 | 398 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."ID2"="T1"."ID1")
3 - filter("T2"."FIL4"=1)
4 - filter("T1"."FIL3"=1)
-- Fall 3
explain plan for
SELECT t1.fil1
, t2.fil2
FROM t1,
t2
WHERE t2.id2 = t1.id1
and t1.fil3 = 1
-- AND t2.fil4 = 1
GROUP BY t1.fil1
, t2.fil2;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 142 | 2414 | 797 (1)| 00:00:10 |
| 1 | HASH GROUP BY | | 142 | 2414 | 797 (1)| 00:00:10 |
|* 2 | HASH JOIN | | 2000 | 34000 | 796 (1)| 00:00:10 |
|* 3 | TABLE ACCESS FULL| T1 | 2000 | 20000 | 398 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| T2 | 9999 | 69993 | 398 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."ID2"="T1"."ID1")
3 - filter("T1"."FIL3"=1)
Mit und ohne OPT_PARAM-Hint ergibt sich für diesen Fall jeweils der gleiche Plan, was ich als Indiz dafür nehme, dass hier keine "improved selectivity" wirksam wird: die Join-Cardinality (667) ist dabei absolut akkurat, aber das GROUP BY liefert tatsächlich nur 4 Sätze. Dabei ergibt sich die 142 - wie Fall 3 zeigt - unabhängig von allen Filter-Bedingungen und folglich auch unabhängig von der Größe der Ergebnismenge des HASH JOINs. Hier greift demnach die von Jonathan Lewis beschriebene Logik auf der Basis der Tabellenstatistiken. Über die Plausibilität der Annahme, dass eine Reduzierung der Satzanzahl nicht unmittelbar mit der Anzahl distinkter Wertkombinationen zu tun hat, kann man sicher diskutieren. Ohne behaupten zu wollen, ein völlig klares Bild der Zusammenhänge bekommen zu haben, ist mein Eindruck, dass die Logik der Bestimmung von cardinialities für GROUP BY Operationen insgesamt eine recht fehleranfällige ist. Außerdem frage ich mich, ob das Schlüsselwort "improved" bei Oracle ähnlich wie die Angabe "fast" etwas ist, das grundsätzlich Anlass zur Vorsicht geben sollte...
Donnerstag, Dezember 13, 2012
V$SQLFN_METADATA
Carsten Czarski weist in seinem Blog auf zwei interessante dynamische Performance-Views hin:
- V$SQLFN_METADATA: "contains metadata about operators and built-in functions. Note that this view does not contain information about arguments because the number of arguments will be different for various functions. Information about arguments is contained in V$SQLFN_ARG_METADATA, which can be joined with V$SQLFN_METADATA to get information about any function and its arguments."
- V$SQLFN_ARG_METADATA: "contains metadata about function arguments. There is one row for each argument of every function found in V$SQLFN_METADATA. There are no rows for functions that do not have any arguments."
Aus V$SQLFN_METADATA geht unter anderem hervor, ob es sich bei einer Funktion um eine analytische oder eine Aggregatfunktion handelt und seit welchem Release die Funktion (bzw. der Operator) im Oracle Server verfügbar ist.
Mittwoch, Dezember 12, 2012
Fehlende Column Statistics
Die nachträgliche Ergänzung neuer Spalten in bestehenden Tabellen kann einige unerwünschte Effekte mit sich bringen - in erster Linie besteht die Gefahr, dass neu hinzu kommende Werte die Satzlänge vergrößern, was dann unter Umständen zu row migration führen kann. Ein Aspekt, über den ich bisher nicht intensiver nachgedacht hatte, ist das Fehlen von column statistics, das sich in diesem Fall ergibt, wenn man im Anschluss an die Ergänzung der Spalte keine Aktualisierung der Statistiken durchführt. Dazu ein Beispiel:
-- 11.2.0.1 create table test_new_col as select rownum id from dual connect by level <= 10000; exec dbms_stats.gather_table_stats(user, 'test_new_col'); alter table test_new_col add col1 number; update test_new_col set col1 = 1 ; commit;
Ein Blick in user_tab_cols zeigt, dass in diesem Fall (natürlich) keine Statistiken für col1 vorliegen.
select column_name
, num_distinct
, num_nulls
, last_analyzed
from user_tab_cols
where table_name = upper('test_new_col');
COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANALYZED
------------------------------ ------------ ---------- -------------------
ID 10000 0 12.12.2012 08:06:04
COL1
Was macht der CBO aus dieser Information - bzw. aus ihrem Fehlen:
explain plan for
select count(*)
from test_new_col
where col1 = 0;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| TEST_NEW_COL | 100 | 400 | 12 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=0)
explain plan for
select count(*)
from test_new_col
where col1 = 1
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| TEST_NEW_COL | 100 | 400 | 12 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
Offensichtlich verwendet der CBO hier eine standard selectivity von 1%, was im Beispiel weder für Fall col1 = 0 (=> 0 rows) noch für Fall col1 = 1 (=> 10000 rows) passend ist. Der passende dbms_stats-Aufruf sollte folglich nach der Ergänzung einer neuen Spalte obligatorisch sein - besonders dann, wenn man über diese Spalte Einschränkungen durchführen möchte.
Freitag, Dezember 07, 2012
CTEs und NL Cardinalities
Dass CTEs (also Oracles subquery factoring mit einer WITH-clause) zu seltsamen Costing Effekten führen können, ist keine neue Beobachtung. Randolf Geist hat z.B. mehrere Artikel zum Thema geschrieben, in denen er u.a. auf der Basis von CBO Traces aufzeigt, dass für Queries mit CTEs bestimmte Transformationen nicht durchgeführt werden können (im Trace findet sich dann der Hinweis "CBQT: copy not possible on query block [...] because linked to with clause"). Einige der angesprochenen Probleme wurden offenbar in 11.2.0.3 behoben. Aber auf diesem Release-Stand bin ich noch nicht angekommen.
In 11.1.0.7 ist mir gestern folgender Effekt begegnet:
drop table test_dim;
drop table test_fact;
create table test_dim
as
with
generator as (
select to_date('01.01.2012', 'dd.mm.yyyy') - 1 + rownum a_date
from dual
connect by level <= 366
)
select to_char(trunc(a_date, 'mm'), 'mm') a_month
, min(a_date) min_date
, max(a_date) max_date
from generator
group by trunc(a_date, 'mm')
order by trunc(a_date, 'mm')
;
create table test_fact
as
with
generator as (
select to_date('01.10.2012', 'dd.mm.yyyy') - 1 + rownum a_date
from dual
connect by level <= 92
)
,
facts as (
select 1000 val
from dual
connect by level <= 10000
)
select a_date
, 1000 col1
from generator
, facts;
exec dbms_stats.gather_table_stats(user, 'test_dim')
exec dbms_stats.gather_table_stats(user, 'test_fact')
Also eine Tabelle test_dim mit 12 Sätzen (je einer für jeden Monat im Jahr 2012), einem Monatsnamen und dem ersten ersten und letzten Tag des Monats und eine Tabelle test_fact mit jeweils 10000 Sätzen für jeden Tag im letzten Quartal des Jahres - insgesamt also 92 * 10000 = 920000 rows - und einer bedeutungslosen Kennzahl col1. Dazu gibt's dann eine Query, die einige Angaben aus test_dim in einer CTE abruft und mit den Fakten joint:
-- Fall 1: materialize
explain plan for
with
date_range as (
select /*+ materialize */
a_month
, min_date
, max_date
from test_dim
where a_month >= 10
)
select a_month
, count(*)
from date_range
join test_fact
on (test_fact.a_date between date_range.min_date and date_range.max_date)
group by a_month
;
select * from table(dbms_xplan.display);
-- Fall 2: inline
explain plan for
with
date_range as (
select /*+ inline */
a_month
, min_date
, max_date
from test_dim
where a_month >= 10
)
select a_month
, count(*)
from date_range
join test_fact
on (test_fact.a_date between date_range.min_date and date_range.max_date)
group by a_month
;
select * from table(dbms_xplan.display);
Der Unterschied der beiden Queries liegt in der Behandlung der CTE, die in Fall 1 im temporary tablespace materialisiert und in Fall 2 textuell an der vorgesehenen Stelle integriert wird. Die beiden Varianten liefern folgende Pläne:
-- Fall 1: Materialize
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 580 (2)| 00:00:07 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | TEST_FACT | | | | |
|* 3 | TABLE ACCESS FULL | TEST_DIM | 1 | 19 | 3 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 29 | 577 (2)| 00:00:07 |
| 5 | NESTED LOOPS | | 2300 | 66700 | 576 (2)| 00:00:07 |
| 6 | VIEW | | 1 | 21 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_271DA033 | 1 | 19 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | TEST_FACT | 2300 | 18400 | 574 (2)| 00:00:07 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("A_MONTH")>=TO_DATE(' 2012-10-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
8 - filter("TEST_FACT"."A_DATE">="DATE_RANGE"."MIN_DATE" AND
"TEST_FACT"."A_DATE"<="DATE_RANGE"."MAX_DATE")
-- Fall 2: inline
Plan hash value: 423103375
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 582 (3)| 00:00:07 |
| 1 | HASH GROUP BY | | 1 | 27 | 582 (3)| 00:00:07 |
| 2 | NESTED LOOPS | | 84235 | 2221K| 577 (2)| 00:00:07 |
|* 3 | TABLE ACCESS FULL| TEST_DIM | 1 | 19 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEST_FACT | 140K| 1096K| 574 (2)| 00:00:07 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("A_MONTH")>=TO_DATE(' 2012-10-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - filter("TEST_FACT"."A_DATE">="MIN_DATE" AND
"TEST_FACT"."A_DATE"<="MAX_DATE")
Erwähnenswert ist dabei vielleicht auch noch, dass der CBO ohne Hint den inline-Plan verwendet, obwohl dessen Kosten geringfügig höher sind als die der Variante mit der Materialisierung (582 zu 580). Davon abgesehen sind die Cardinality-Schätzungen für test_fact in beiden Fällen ziemlich weit von den Ausführungs-Realitäten entfernt:
- tatsächlich werden alle 920.000 Sätze der Tabelle gelesen
- für die Version mit dem Materialize-Hint errechnet sich die Cardinality offenbar wieder einmal auf Grundlage der Standard-Arithmetik für Bound-Ranges, also jeweils 5% als Faktor für die untere und die oberer Grenze: 920000 * 0,05 * 0,05 = 2300.
- weniger klar ist mir, woher die 140K kommen.
Ein Blick ins CBO-Trace verrät zumindest, welche Werte in die Rechnung eingehen:
NL Join
Outer table: Card: 3.19 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 24
Access path analysis for TEST_FACT
Inner table: TEST_FACT Alias: TEST_FACT
Access Path: TableScan
NL Join: Cost: 1720.93 Resp: 1720.93 Degree: 1
Cost_io: 1693.00 Cost_cpu: 458426063
Resp_io: 1693.00 Resp_cpu: 458426063
Best NL cost: 1720.93
resc: 1720.93 resc_io: 1693.00 resc_cpu: 458426063
resp: 1720.93 resp_io: 1693.00 resc_cpu: 458426063
Join Card: 447155.913681 = = outer (3.185075) * inner (920000.000000) * sel (0.152599)
Die sel (0.152599) ist dabei offenbar der Faktor, der von der Satzanzahl zur cardinality von 140K führt: 920000 * 0,152599 = 140391,08. Dabei erscheint die cardinality-Angabe selbst nicht im Trace (weder als 140K noch als 140391). Aber was bedeutet diese selectivity? Bisher habe ich dafür noch keine Erklärung. Der Zusammenhang wird auch dadurch nicht klarer, dass die in der CTE verwendete Einschränkung auf a_month offenbar gar keine Auswirkung auf die selectivity-Bestimmung hat:
- a_month >= 1: tatsächlich: 920K; rows(test_fact) = 140K; rows(NL): 84235
- a_month >= 10: tatsächlich: 920K; rows(test_fact) = 140K; rows(NL): 84235
- a_month >= 12: tatsächlich: 310K; rows(test_fact) = 140K; rows(NL): 84235
- a_month >= 13: tatsächlich: 0; rows(test_fact) = 140K; rows(NL): 84235 !!
- a_month >= 20: tatsächlich: 0; rows(test_fact) = 140K; rows(NL): 84235 !!
Und auch die Auswirkung einer Änderung der Join-Bedingung zwischen CTE und test_fact ist nicht unmittelbar plausibel (bestimmt jeweils mit a_month >= 10 in der CTE, was wie gerade angesprochen vermutlich in keinem Fall relevant ist):
- (test_fact.a_date between date_range.min_date and date_range.max_date): rows(test_fact) = 140K
- (test_fact.a_date >= date_range.min_date): rows(test_fact) = 835K
- (test_fact.a_date > date_range.min_date): rows(test_fact) = 833K
- (test_fact.a_date < date_range.min_date): rows(test_fact) = 76326
- (test_fact.a_date <= date_range.min_date): rows(test_fact) = 78828
Insgesamt scheint die Behandlung von Range-Prädikaten an dieser Stelle etwas unübersichtlich zu sein - ein nachvollziehbares Muster sehe ich da zunächst nicht. Und auf Anhieb habe ich auch weder in Cost-Based Oracle noch im Netz eine Erklärung der Berechnung gefunden - wobei ich sicher bin, dass die Herren Lewis, Geist etc. diese Erklärung haben.
Mittwoch, Dezember 05, 2012
Häufige Problemquellen für Zugriffspläne im SQL Server
Joe Sack liefert im SQLPerformance.com-Blog eine Liste mit zehn Gründen für suboptimale Ausführungspläne im SQL Server, die letztlich - wie bei Oracle - vor allem mit falschen cardinalities zusammenhängen. Die Liste enthält:
- fehlende oder veraltete Statistiken
- nicht repräsentative Samples bei der Statistikerfassung (data skew)
- Korrelation von Spaltenwerten: dagegen helfen im SQL Server multi-column stats (im Fall von Oracle: extended stats)
- intra-table column comparisions, die man durch vorberechnete Spalten ersetzen kann/sollte
- Verwendung von table variables (deren cardinality immer 1 ist - klingt problematisch, aber um das genauer einordnen zu können, müsste ich eine klarere Vorstellung von der Rolle von table variables haben)
- Funktionen (multi-statement table-valued and scalar)
- Datentypen (also vor allem implizite Typ-Konvertierungen)
- komplexe Prädikate
- komplexe Queries
- verteilte Queries (mit remote-Zugriff)
Ich will die Unterschiede nicht marginalisieren, aber grundsätzlich sähe eine entsprechende Aufstellung für Oracle durchaus ähnlich aus.
Intra-Block Row Chaining für row pieces
Ein paar - relativ ungeordnete - Beobachtungen zum Intra-Block Row Chaining. Zunächst: worum handelt es sich dabei überhaupt? Im Abschnitt Row Format and Size des Concept Guides findet sich der Hinweis: Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks." Das Wort "typically" deutet dabei schon an, dass mehrere row pieces durchaus auch in einem einzigen Block gespeichert werden können. Dazu ein kleines (und gekürztes) Beispiel:
-- Anlage einer Test-Tabelle mit 1000 Spalten in einem MSSM-Tablespace create table test_chaining ( col_1 number , col_2 number , col_3 number ... , col_998 number , col_999 number , col_1000 number ) tablespace test_ts; -- Insert eines einzelnen Datensatzes insert into test_chaining values ( 1 , 2 , 3 ... , 998 , 999 , 1000 );
Also eine Tabelle mit 1000 Spalten - mehr sind nicht möglich: "ORA-01792: Höchstzahl für Spalten in einer Tabelle oder einer View ist 1000" - und einem einzigen Datensatz. Zu diesem Satz ermittle ich nun den zugehörigen Block, den ich anschließend per Dump ausgeben lasse:
select dbms_rowid.rowid_relative_fno(rowid) file_nr
, dbms_rowid.rowid_block_number(rowid) block_nr
from test_chaining;
alter system dump datafile 7 block 1414;
Der erstellte Block-Dump enthält (unter anderem) folgende Informationen (der Beginn des Dumps und die col-Listen sind gekürzt):
Start dump data blocks tsn: 8 file#:7 minblk 1414 maxblk 1414
...
tsiz: 0x1f68
hsiz: 0x1a
pbl: 0x0c408294
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1017
avsp=0xffd
tosp=0xffd
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1b6c
0x14:pri[1] offs=0x176a
0x16:pri[2] offs=0x1367
0x18:pri[3] offs=0x1017
block_row_dump:
tab 0, row 0, @0x1b6c
tl: 1020 fb: -----L-- lb: 0x1 cc: 255
col 0: [ 3] c2 08 2f
col 1: [ 3] c2 08 30
col 2: [ 3] c2 08 31
col 3: [ 3] c2 08 32
...
col 252: [ 3] c2 0a 63
col 253: [ 3] c2 0a 64
col 254: [ 2] c2 0b
tab 0, row 1, @0x176a
tl: 1026 fb: -------- lb: 0x1 cc: 255
nrid: 0x01c00586.0
col 0: [ 3] c2 05 5c
col 1: [ 3] c2 05 5d
col 2: [ 3] c2 05 5e
col 3: [ 3] c2 05 5f
...
col 252: [ 3] c2 08 2c
col 253: [ 3] c2 08 2d
col 254: [ 3] c2 08 2e
tab 0, row 2, @0x1367
tl: 1027 fb: -------- lb: 0x1 cc: 255
nrid: 0x01c00586.1
col 0: [ 3] c2 03 25
col 1: [ 3] c2 03 26
col 2: [ 3] c2 03 27
col 3: [ 3] c2 03 28
...
col 252: [ 3] c2 05 59
col 253: [ 3] c2 05 5a
col 254: [ 3] c2 05 5b
tab 0, row 3, @0x1017
tl: 848 fb: --H-F--- lb: 0x1 cc: 235
nrid: 0x01c00586.2
col 0: [ 2] c1 02
col 1: [ 2] c1 03
col 2: [ 2] c1 04
col 3: [ 2] c1 05
...
col 232: [ 3] c2 03 22
col 233: [ 3] c2 03 23
col 234: [ 3] c2 03 24
end_of_block_dump
End dump data blocks tsn: 8 file#: 7 minblk 1414 maxblk 1414
Offensichtlich enthält der Block also 4 row pieces, von denen die ersten drei jeweils 255 Spalten umfassen, während das vierte nur 235 Spalten enthält. Interessant ist dabei auch, dass dieses vierte Stück offenbar die ersten Spalten ab col_1 enthält (was man am Inhalt c1 02 => 1 zu erkennen ist). Hemant Chitale hat vor einigen Jahren zwei Artikel zum Thema in seinem Blog veröffentlicht und dort auch ein paar Beobachtungen zu den zugehörigen Angaben in v$sesstat (bzw. v$mystat) vermerkt. Außerdem findet sich dort ein Verweis auf einen Oracle-L thread, in dem die Herren Poder und Antognini wichtige Ergänzungen liefern. Und wenn ich schon dabei bin hier noch ein paar Links:
- Jonathan Lewis: Analyze this! liefert Informationen zum CHAIN_CNT, der migrated und chained rows umfasst, aber intra-row-chaining nicht vermerkt; nach einem ANALYZE TABLE test_chaining COMPUTE STATISTICS; bleibt der CHAIN_CNT = 0, was insofern plausibel ist, da die Verkettung nicht block-übergreifend ist
- Tanel Poder: Detect chained and migrated rows in Oracle – Part 1; einen Part 2 habe ich nicht gefunden ...; darin wird die Semantik der Statistiken table fetch by rowid ("how many times Oracle took a ROWID (for example from an index) and went to a table to lookup the actual row") und table fetch continued row ("when we didn’t find all that we wanted from the original row piece and had to follow a pointer to the new location of the migrated row (or next row piece of a chained row)") erläutert.
Ausgehend von den Ausführungen des Herrn Poder noch ein kleiner Versuch:
select col_1 from TEST_CHAINING;select col_500 from TEST_CHAINING;
select col_1000 from TEST_CHAINING; -- v$sesstat: NAME COL_1 COL_500 COL_1000 -------------------------------------- ----- ------- -------- session logical reads 10 12 13 consistent gets from cache 10 12 13 consistent gets 10 12 13 consistent gets from cache (fastpath) 7 9 10 table scan blocks gotten 5 5 4 no work - consistent read gets 5 7 8 table scan rows gotten 4 4 4 buffer is not pinned count 2 4 5 table fetch by rowid 1 1 1 table scans (short tables) 1 1 1
Daraus ziehe ich im Moment nur zwei Schlüsse:
- Intra-Block Row Chaining wird nicht als table fetch continued row vermerkt (ist also auch in dieser Perspektive kein "echtes" Chaining)
- die erforderliche Arbeit unterscheidet sich für den Zugriff auf die erste, eine mittlere bzw. die letzte Spalte der Tabelle deutlich - und sie erhöht sich für weiter hinten liegende Spalten
Ich gebe zu: mal wieder mangelt es meinen Ausführungen an Struktur. Vielleicht sollte ich doch mal dazu übergehen meine Gedanken zu ordnen, ehe ich etwas schreibe...
Samstag, Dezember 01, 2012
ASH-Analyse von TX Lock contention
Kyle Hailey erläutert in seinem Blog, wie man in ASH protokollierte Wait Events vom Type 'enq: TX – row lock contention' ihren Ursachen zuordnen kann. Entscheidend ist dabei ist der lock mode:
- mode 6 (exclusive) deutet in der Regel auf ein klassisches row lock hin, bei dem zwei Sessions den gleichen Satz ändern wolle.
- mode 4 (share) kann mehrere wahrscheinliche Ursachen haben:
- insert eines unique key, der bereits in einer anderen Session angelegt, aber noch nicht per commit festgeschrieben wurde (andernfalls bekäme man ja nur einen UK-Verletzungsfehler)
- insert eines child records zu einem (FK-)parent, der gerade neu hinzugefügt oder gelöscht, aber nicht commited wurde
- contention bei einer Änderung in einem bitmap index
Zur Analyse der tatsächlichen Ursache liefert der Herr Hailey eine Query, die durch den Join von v$active_session_history und all_objects unterschiedliche Ergebnismuster liefert. ASH ist einfach ein großartiges Werkzeug zur nachträglichen Analyse von Systemzuständen.
Mittwoch, November 28, 2012
Statistiktransfer von PROD nach DEV
Maria Colgan liefert im Oracle Optimizer Blog ein sehr kompaktes Beispiel für den Transfer von Optimizer-Statistiken aus einem Produktiv-System in ein Entwicklungs-System. Die Schritte dabei sind:
- Anlage einer Hilfstabelle in PROD mit dbms_stats.create_stat_table, in der die PROD-Statistiken gespeichert werden können
- Übertragen der PROD-Statistiken in die Hilfstabelle mit dbms_stats.export_schema_stats
- Anlage eines Directories in PROD (falls nicht schon eines vorhanden ist)
- Export der Hilfstabelle via expdp
- Transfer des Dumps nach DEV
- Import der Hilfstabelle aus dem Dump in die DEV-DB
- Kopieren der Statistiken der Hilfstabelle ins dictionary via dbms_stats.import_schema_stats
Dass das Übertragen möglich ist, war mir bekannt, aber dass es so einfach ist, hatte ich offenbar vergessen (oder nie gewusst).
Eine (geringfügig komplexere) Variante für den Transfer der für eine einzelne Query relevanten Statistiken hat übrigens gerade Yury Velikanov im Pythian Blog erläutert.
Eine (geringfügig komplexere) Variante für den Transfer der für eine einzelne Query relevanten Statistiken hat übrigens gerade Yury Velikanov im Pythian Blog erläutert.
Freitag, November 23, 2012
Sonderfälle der Plan-Interpretation
Jonathan Lewis hat dieser Tage zwei Fälle geschildert, in denen die Standard-Regeln der Plan-Interpretation nicht gelten:
- Plan timing: skalare Subqueries, die in einer SELECT-Liste verwendet werden, erscheinen im Plan oberhalb des Query Blocks, der sie aufruft. Eigentlich geht es im Artikel um das Verständnis der Zeitangaben in den erweiterten (rowsource execution) Plan-Statistiken, aber die klären sich, wenn die Verabeitungsreihenfolge deutlich wird. Der Artikel enthält auch noch einen Verweis auf das Phänomen des scalar subquery caching, das mir zuletzt häufiger begegnet ist, und das dafür sorgt, dass eine skalare Subquery nicht für jeden Satz, sondern nur für jeden distinkten Wert der Ergebnismenge aufgerufen wird.
- Plan Order: der Index-Zugriff einer konstanten Subquery erscheint im Plan unterhalb eines HASH JOINs, aber die rowsource execution statistics zeigen deutlich, dass die Ausführung nach der ergebnislosen Ausführung der Subquery abbricht (Starts = 0 für alle folgenden Schritte). Mit den rowsource execution statistics und dem sqlmonitor lassen sich solche Effekte inzwischen relativ leicht bestimmen.
Status einer Materialized View
Ein kleiner Test zur Semantik der Status-Angaben für Materialized Views in den relevanten Dictionary-Tabellen. Dabei geht es zunächst nur um die einfachsten Fälle (kein Query Rewrite, kein Fast Refresh):
-- 11.1.0.7
-- Aufbau Datenbasis
drop table test_mpr;
drop materialized view test_mv_mpr;
create table test_mpr
as
select rownum id
, mod(rownum, 10) col1
from dual
connect by level <= 1000;
create materialized view test_mv_mpr
as
select col1
, count(*) row_count
from test_mpr
group by col1;
-- Analyse-Queries
select object_name
, object_type
, status
from dba_objects
where object_name = 'TEST_MV_MPR';
select mview_name
, invalid
, known_stale
, unusable
from dba_mview_analysis
where mview_name = 'TEST_MV_MPR';
select mview_name
, staleness
, compile_state
from dba_mviews
where mview_name = 'TEST_MV_MPR';
Dazu liefern die befragten Dictionary-Tabellen zunächst folgende Angaben:
-- dba_objects OBJECT_NAME OBJECT_TYPE STATUS --------------- ------------------- ------- TEST_MV_MPR MATERIALIZED VIEW VALID TEST_MV_MPR TABLE VALID -- dba_mview_analysis MVIEW_NAME INVALID KNOWN_STALE UNUSABLE --------------- ---------- ----------- ---------- TEST_MV_MPR N N N -- dba_mviews MVIEW_NAME STALENESS COMPILE_STATE --------------- ------------------- ------------- TEST_MV_MPR FRESH VALID
So weit keine Überraschungen: die MV ist frisch aufgebaut und alle Status-Angaben sind folglich im grünen Bereich. Was passiert, wenn ich die Basistabelle lösche:
drop table test_mpr; -- dba_objects OBJECT_NAME OBJECT_TYPE STATUS --------------- ------------------- ------- TEST_MV_MPR MATERIALIZED VIEW INVALID TEST_MV_MPR TABLE VALID -- dba_mview_analysis MVIEW_NAME INVALID KNOWN_STALE UNUSABLE --------------- ---------- ----------- ---------- TEST_MV_MPR Y N N -- dba_mviews MVIEW_NAME STALENESS COMPILE_STATE --------------- ------------------- ------------- TEST_MV_MPR NEEDS_COMPILE NEEDS_COMPILE
Auch diese Angaben erscheinen mir völlig nachvollziehbar: nach der Löschung der Basistabelle ist die MV tatsächlich in einer unglücklichen Situation, ein Refresh ist nicht mehr möglich, und der Status INVALID beschreibt das zutreffend. Nun ein weniger massiver Eingriff: ich füge in der Basis-Tabelle ein paar neue Datensätze ein, ändere an den Strukturen aber nichts:
insert into test_mpr
select rownum id
, mod(rownum, 10) col1
from dual
connect by level <= 1000;
commit;
-- dba_objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- -------
TEST_MV_MPR MATERIALIZED VIEW INVALID
TEST_MV_MPR TABLE VALID
-- dba_mview_analysis
MVIEW_NAME INVALID KNOWN_STALE UNUSABLE
--------------- ---------- ----------- ----------
TEST_MV_MPR Y N N
-- dba_mviews
MVIEW_NAME STALENESS COMPILE_STATE
--------------- ------------------- -------------
TEST_MV_MPR NEEDS_COMPILE NEEDS_COMPILE
Die Status-Angaben sind in diesem Fall die gleichen wie im Fall der Löschung der Basis-Tabelle - und das finde ich nicht völlig plausibel, denn eigentlich würde ich erwarten, dass hier eine Unterscheidung möglich sein sollte. Die Einführung von weiteren Zustandsangaben wäre aus meiner Sicht kein Luxus gewesen.
Aus Gründen der Vollständigkeit hier noch die zugehörigenDefinitionen der Dokumentation:
Aus Gründen der Vollständigkeit hier noch die zugehörigenDefinitionen der Dokumentation:
- DBA_OBJECTS
- STATUS: Status of the object VALID, INVALID, N/A
- DBA_MVIEW_ANALYSIS
- INVALID: "Indicates whether this materialized view is in an invalid state (inconsistent metadata)"
- KNOWN_STALE: "Indicates whether the data contained in the materialized view is known to be inconsistent with the master table data because that has been updated since the last successful refresh"
- UNUSABLE: "Indicates whether this materialized view is UNUSABLE (inconsistent data) [...]. A materialized view can be UNUSABLE if a system failure occurs during a full refresh"
- DBA_MVIEWS:
- STALENESS: "Relationship between the contents of the materialized view and the contents of the materialized view's masters" Es folgen 5 Zustandsangaben, unter denen NEEDS_COMPILE allerdings nicht aufgeführt ist.
- COMPILE_STATE: "Validity of the materialized view with respect to the objects upon which it depends". Dazu gibt's 3 Zustände. Zu NEEDS_COMPILE heisst es: " Some object upon which the materialized view depends has changed. An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view"
Mittwoch, November 21, 2012
Parallelisierung (Randolf Geist) - Teil 1
In der Reihe der OTN-Artikel zum Thema Database Performance & Availability wurde zuletzt eine zweiteilige Serie Understanding Parallel Execution von Randolf Geist veröffentlicht, die einen sehr guten Überblick zu den Voraussetzungen und Leistungen paralleler Operationen liefert (wobei die Aussagen für Exadata, aber auch für "normale" Datenbanken gelten).
Mein Exzerpt erhebt dabei mal wieder keinen Anspruch auf Vollständigkeit, sondern soll mir in erster Linie als Erinnerungshilfe dienen. Grundsätzlich würde ich ohnehin jedem, der sich mit Parallelisierung beschäftigt, die komplette Lektüre der beiden OTN-Artikel empfehlen. Außerdem ist das Thema mal wieder eines, bei dem ich am Übersetzen der technischen Begriffe scheitere, so dass kein wirklich konsistenter Text daraus wird:
Mein Exzerpt erhebt dabei mal wieder keinen Anspruch auf Vollständigkeit, sondern soll mir in erster Linie als Erinnerungshilfe dienen. Grundsätzlich würde ich ohnehin jedem, der sich mit Parallelisierung beschäftigt, die komplette Lektüre der beiden OTN-Artikel empfehlen. Außerdem ist das Thema mal wieder eines, bei dem ich am Übersetzen der technischen Begriffe scheitere, so dass kein wirklich konsistenter Text daraus wird:
Im ersten Artikel erklärt der Autor die Voraussetzungen für einen sinnvollen Einsatz paralleler Operationen:
- wenn der serielle Plan nichts taugt (falsche Join Reihenfolge, ungeeignete Zugriffsverfahren), wird auch der parallele Plan keine Wunder bewirken
- wenn PL/SQL-Funktionen eingesetzt werden, die nicht explizit als parallelisierbar definiert wurden, kann es vorkommen, dass im Plan ein Schritt PX COORDINATOR FORCED SERIAL erscheint, der bedeutet, dass der Plan letztlich seriell ausgeführt wird, obwohl PX-Operationen darin erscheinen (es gibt offenbar neben den Funktionen noch andere Gründe für dieses Verhalten). Da aber das Costing die Parallelisierung berücksichtigt, kann dieser Effekt zu massiven Fehlkalkulationen führen.
- durch das verwendete Consumer/Producer-Modell kommt es vor, dass beide Gruppen paralleler Slave-Prozesse beschäftigt sind, wenn im Plan eigentlich eine parallele Weiterverarbeitung vorgesehen ist. In solchen Fällen treten blocking operations auf, die im Plan als BUFFERED oder BUFFER SORT ausgewiesen sind (wobei BUFFER SORT in seriellen Plänen eine andere Semantik hat). Dieses Abwarten ist inhaltlich nicht immer nachvollziehbar (der Autor zeigt das Problem am Beispiel eines HASH JOINs), aber anscheinend unvermeidlich: "It looks like that the generic implementation always generates a Parallel execution plan under the assumption for the final step that there is potentially another Parallel Slave Set active that needs to consume the data via re-distribution. This is a pity as it quite often implies unnecessary blocking operations as shown above."
- die Verabeitungsreihenfolge für parallele Operationen entspricht nicht unbedingt der Reihenfolge, die für serielle Operationen gilt (und wo üblicherweise zuerst der im Plan am weitesten oben aufgeführte Step ausgeführt wird, zu dem keine untergeordneten Steps existieren: also der erste Leaf-Step), da sich auch hier die Begrenzung auf zwei aktive parallel slave Gruppen auswirkt.
- die BUFFER-Operationen aufgrund von blocking operations können zur Auslagerung auf die Platte führen, was natürlich der Performance schadet; auch ohne Auslagerung kann der Memory-Bedarf hoch sein.
- Parallel Distribution Methods: Für den HASH JOIN (das übliche Join-Verfahren bei Parallelisierung) gibt es drei Verarbeitungs-Varianten, die in der Spalte "PQ Distrib" im Plan erscheinen:
- Hash Distribution: die beiden Quelldatenmengen (row sources) werden über den Join-Key Hash-verteilt, was zwei aktive Slave-Gruppen erfordert, und der eigentliche Join wird wiederum von einer Slave-Gruppe durchgeführt, so dass sich (in der Regel) eine Buffered Operation ergibt
- Broadcast Distribution: der Join (bzw. sein Probe Phase) wird zusammen mit einer der row source Operationen durchgeführt. Da keine Verteilung der Daten auf den Join-Key erfolgte, müssen die Ergebnisse der zweiten row source an alle Slaves, die den Join durchführen, weitergereicht werden (Broadcast). Dies führt zu einer Vervielfachung der intern verarbeiteten Datenmengen. Effizient ist das Verfahren, wenn die erste row source relativ klein ist.
- Partition Distribution: wenn beide row sources in gleicher Weise partitioniert sind, ist ein partition-wise-Join möglich, der keine hash distribution der Daten erfordert, und deshalb von einer einzigen Slave-Gruppe ausgeführt werden kann und keine blocking operation hervorruft. Der partition-wise-Join ist damit das effizienteste der erwähnten Verfahren. Auch ohne Parallelisierung ist der partion-wise-Join sehr nützlich, da er die Größe der Join-Operationen reduziert.
- MERGE JOIN und NESTED LOOPS sind ebenfalls parallelisierbar, kommen aber sehr viel seltener vor.
- Für den partition-wise-Join sollte der DOP höchstens der Anzahl der Partitionen entsprechen.
- mit Hilfe des Hints PQ_DISTRIBUTE lässt sich das Verhalten beeinflussen. Dabei lassen sich die Syntax-Details aus den OUTLINE-Informationen von DBMS_XPLAN entnehmen.
- Der Abschnitt "Distribution of load operations" beschäftigt sich mit der Beeinflussung interner Sortierungen (z.B. zum Zweck einer möglichst effizienten Komprimierung)
- "Plans Including Multiple Data Flow Operations (DFOs)" erläutert Fragen des geeigneten DOP und der Effekte einer Verknüpfung mehrerer Operationen mit unterschiedlichem DOP.
IOTs, CTAS und Sortierungen
Connor McDonald (auf dessen Blog Jonathan Lewis vor kurzem hingewiesen hatte - und dessen PL/SQL-Buch immer noch an meinem Arbeitsplatz steht) hat dieser Tage in seinem Blog ein paar interessante Effekte aus dem Kontext der IOTs angesprochen:
- um LOGGING beim Aufbau einer IOT zu vermeiden, muss man CTAS verwenden. Bei Verwendung von INSERT /*+ APPEND */ wird auch für eine als NOLOGGING definierte Tabelle massiv redo erzeugt.
- Der Execution Plan beim IOT-Aufbau über CTAS taugt nicht viel. Im gegebenen Beispiel zeigt der Plan einen INDEX FULL SCAN ohne Sortierungen, aber tatsächlich erfolgen für den zugehörigen Indes-Aufbau massive Sortier-Operationen.
Nachtrag 28.11.2012: Jonathan Lewis hat inzwischen auch noch einen Artikel zum Thema geschrieben und zeigt darin, wie das Logging durch spooling der Quelldaten in eine Datei und Einfügen ins Ziel per SQL-Loader vermieden werden kann.
Freitag, November 16, 2012
SQL Performance Explained von Markus Winand
Dass ich gerne mal ein Buch über Indizes von Richard Foote hätte, habe ich wahrscheinlich gelegentlich schon mal erwähnt, aber leider scheint damit auch weiterhin nicht zu rechnen zu sein - zumal die Herr Foote niemals versprochen hat, etwas Derartiges zu veröffentlichen. Stattdessen habe ich dieser Tage den im Sommer 2012 erschienenen Band SQL Performance Explained von Markus Winand gelesen, auf dessen interessante Seite Use The Index, Luke! ich hier auch schon verwiesen habe. Im ersten Moment ist es etwas ungewohnt, über Indizes zu lesen, ohne regelmäßigen Referenzen auf das Werk David Bowies zu begegnen, aber daran gewöhnt man sich ziemlich schnell ...
Um es vorweg zu nehmen: das Buch ist aus meiner Sicht eine ausgesprochen empfehlenswerte Lektüre und liefert einen sehr zugänglichen Einstieg ins Thema SQL-Performance-Optimierung. Dabei wendet sich der relativ schmale Band (196 S.) in erster Linie an die Entwickler, die der Autor als die Gruppe betrachtet, die aufgrund ihrer Kenntnis der Applikationen (und - hoffentlich auch - der Daten) am besten dazu in der Lage ist, eine sinnvolle Indizierung durchzuführen, während DBAs und externen Beratern dieses Wissen in der Regel fehlt. Ich will an dieser Stelle nicht massiv widersprechen, denke aber, dass man viele SQL-Zugriffsprobleme auch ohne Kenntnis der Applikationslogik bestimmen kann (jedenfalls in Oracle und im SQL Server, da für diese RDBMS gilt, dass das data dictionary und die dynamischen Performance-Views sehr viele relevante Informationen liefern). Dass die Entwickler ein gutes Verständnis der Arbeitsweise von Indizes haben sollten, stimmt aber in jedem Fall.
Das Thema des Buches sind B*Tree-Indizes und ihre Rolle in OLTP-Systemen. Diese starke Fokussierung auf eine zentrale - und beschränkte - Fragestellung und eine klare Strukturierung der Erklärungen sorgen dafür, dass die Darstellung sich nicht in Details verliert. Diese Struktur leidet auch nicht darunter, dass die Erläuterungen nicht auf ein einziges RDBMS beschränkt sind - neben Oracle und SQL Server werden MySQL und PostgreSQL untersucht -, im Gegenteil: durch den Vergleich der Systeme wird deutlich, wie viele Übereinstimmungen es in den grundsätzlichen Verfahrensweisen der Datenbanken im Bereich der Indizierung und der SQL-Optimierung gibt. Das Buch gliedert sich in acht Kapitel:
- Anatomy of an Index: erläutert die Struktur von B*Tree-Indizes.
- The Where Clause: erklärt die Rolle unterschiedlicher Operatoren (Equality, Range), Funktionen (und FBIs), NULL-Werten, Datentypen, Statistiken, Bindewerten und liefert dabei zahlreiche Antworten auf die klassische Frage, warum ein Index nicht verwendet wird. Einer der wichtigsten Punkte ist aus meiner Sicht die prägnante Erklärung von access und filter Prädikaten. Nützlich sind auch die Hinweise auf das unterschiedliche Verhalten unterschiedlicher RDBMS (z.B. Oracles fragwürdige Behandlung von Leerstrings als NULL).
- Performance und Scalability: zeigt den Einfluss von Datenvolumen und Contention auf die Performance.
- The Join Operation: behandelt die drei Join-Verfahren (Nested Loops, Hash Join, Merge Join) und ihre Nutzung von Indizes. Dabei wird auch das Thema der Code-Generierung von ORM-Tools angesprochen und vorgeführt, wie man deren traurige Leistungen in bestimmten Fällen korrigieren kann.
- Clustering Data: erklärt den clustering factor und die Leistungsfähigkeit von index-only scans (covering indexes; "the second power of indexing"); außerdem wird die Struktur von IOTs (bzw. clustered indexes) erläutert.
- Sorting and Grouping: erklärt die Möglichkeiten zur Vermeidung von Sortierungen bei ORDER BY und GROUP BY Operationen durch die Nutzung geeigneter Indizes ("the third power of indexing", wobei die Verarbeitung "pipelined" erfolgt: der nächste Verarbeitungsschritt muss also nicht das Ende der Sortierung abwarten). Außerdem werden die Sortierreihenfolge (ASC, DESC) und die Position von NULL-Werten (FIRST, LAST) beim Sortieren thematisiert.
- Partial Results: zeigt effiziente Verfahren zur Ausgabe paginierter Ergebnisse und geht (knapp) auf analytische Funktionen ein.
- Modifiying Data: erklärt die Wirkung von Indizes auf DML-Operationen.
- Appendix A: mit Hinweisen zur Darstellung und Interpretation von Ausführungsplänen in den behandelten RDBMS.
Gut gefällt mir wohl auch, dass die Einschätzungen des Autors in nahezu allen wichtigen Punkten mit den meinen übereinstimmen. Ein Punkt, den ich vielleicht anders akzentuieren würde, ist die Rolle von Bindewerten: natürlich sind sie in OLTP-Systemen zur Vermeidung von contention extrem wichtig, aber andererseits nehmen sie dem Optimizer relevante Informationen. Da ich mich aber auch eher mit ETL-Fragen im DWH-Kontext beschäftige, lässt sich dieser Aspekt vermutlich ziemlich schnell abhaken (ich glaube, das ist ein Punkt in dem auch die Propheten Kyte und Lewis leicht abweichende Positionen einnehmen). Ein paar kleinere Details habe ich in den Ausführungen vermisst (z.B. den INDEX SKIP SCAN, obwohl, so richtig vermisse ich den eigentlich nicht; den FIRST_ROWS_n-Modus für den CBO; den rowid-guess in IOTs und deren Overflow-Segment), aber das Erstaunliche ist viel mehr, was hier alles auf weniger als 200 Seiten angesprochen wird. Eine Frage, die mich noch interessieren würde, wäre, wo um alles in der Welt man Indizes mit einer tree depth von 6 findet? (mehr als 4 habe ich auch auf relativ großen Tabellen mit mehreren Milliarden Sätzen noch nicht gesehen, aber vielleicht ist das jenseits der Oracle-Welt anders)
Ich denke, dass SQL Performance Explained ein ungeheuer nützliches Buch für jeden ist, der beginnt, sich ernsthaft mit Fragen der SQL-Optimierung auseinander zu setzen - und das sollte aus meiner Sicht eigentlich jeder Entwickler, der SQL-Code schreibt. Im Bereich der SQL-Zugriffe lassen sich Laufzeiten häufig um Größenordnungen reduzieren, wenn man den richtigen Index benutzt (bzw. im DWH-Kontext eher: nicht benutzt, denn dort sind es mir schöner Regelmäßigkeit die Index-getriebenen NL-Joins, die zu Problemen führen) - um solche Verbesserungen in anderen Teilen des Codes zu erreichen, muss man sich schon sehr viel einfallen lassen. Selbst, wenn man sich schon länger mit Fragen der SQL-Optimierung beschäftigt, wird man hier noch allerlei nützliche Hinweise finden: für mich waren das vor allem die Erläuterungen zum Verhalten anderer RDBMS, mit denen ich seltener zu tun habe (SQL Server), bzw. fast nie (MySQL, PostgreSQL). Auch habe ich mir noch nie ernsthaft darüber Gedanken gemacht, dass Indizes auf einer SQL Server-Tabelle mit clustered index notwendigerweise die gleichen Probleme haben wie sekundäre Indizes auf IOTs. Ich kenne kein anderes Buch, dass die Grundlagen der SQL-Performance-Optimierung ähnlich gut erläutern würde (vielleicht am ehesten Christian Antogninis Troubleshooting Oracle Performance, das allerdings ein größeres Vorwissen voraussetzt und auch Aspekte anspricht, die eher in den DBA-Bereich fallen). Würde ich in diesem Blog Kaufempfehlungen aussprechen, dann wäre SQL Performance Explained ein Kandidat für eine solche.
Freitag, November 09, 2012
Hekaton
Microsoft hat dieser Tage eine neue in-memory Technologie vorgestellt, die in der nächsten Version des SQL Servers verfügbar sein soll, und den schönen Namen Hekaton trägt:
Hekaton is from the Greek word ἑκατόν for “hundred”. Our design goal for the Hekaton original proof of concept prototype was to achieve 100x speedup for certain TP operations.
Das wird sicher eine interessante technische Neuerung, der Trend zur Verlagerung von I/O-Operationen in den Speicher setzt sich fort - aber irgendwie ruft die Benamung bei mir die falschen Assoziationen auf:
Als Hekatombe [...] bezeichnete man im antiken Griechenland ursprünglich ein Opfer von 100 Rindern. Der Begriff entwickelte sich aber schon frühzeitig zur allgemeinen Bezeichnung für jedes reichere, aus Tieren bestehende Opfer, das sich in Bezug auf die Zahl und Art derselben nach den Vermögensumständen der Opfernden richtete sowie nach der Gottheit, der man es weihte, und nach dem Ort oder Fest, an dem es dargebracht wurde. [...] Im übertragenen Sinn spricht man auch bei einer erschütternd großen Zahl von Menschen, die einem Unglück zum Opfer gefallen sind, von einer Hekatombe.
Nun ja, vielleicht ist meine Sensibilität an dieser Stelle übertrieben, aber ich habe im Jahr 2004 für eine Firma gearbeitet, die damals ihr (internes) Projekt Tsunami umbenennen musste...
Notizen zur Performance von DELETE-Operationen
Gute Erklärungen für die folgenden Beobachtungen könnte ich mit hoher Wahrscheinlichkeit aus der einschlägigen Literatur bekommen, aber bekanntlich ist solide Recherche der Tod der Kreativität. Daher notiere ich einfach mal ein paar Punkte, die mir dieser Tage aufgefallen sind.
Seit Jahren warne ich die Entwickler in meiner Umgebung regelmäßig vor DELETE- und UPDATE-Operationen auf großen Datenmengen, da man daran in aller Regel keine Freude hat. Sofern es möglich ist, wandele ich solche Operationen gerne in INSERTs in neue Tabellen um, die ich dann anschließend gegen die Ursprungsobjekte austausche. Aber erst in den letzten Tagen ist mir klar geworden, wie viel Arbeit DELETE tatsächlich hervorruft. Dazu ein kleines Beispiel (11.1.0.7, ASSM, 8K Blockgröße, Noarchivelog auf einem nicht ganz neuen Windows-Desktop-PC):
drop table test_delete; create table test_delete as select rownum id from dual connect by level <= 1000000; --> Abgelaufen: 00:00:00.89 exec dbms_stats.gather_table_stats(user, 'TEST_DELETE') alter system flush buffer_cache; -- Statistikerfassung über Snapshots aus v$sesstat -- vor und nach dem delete (erzeugt in einer anderen Session) delete from test_delete; --> Abgelaufen: 00:00:12.51
Während die Anlage der einspaltigen Tabelle mit 1M rows also weniger als 1 sec. benötigt, läuft das DELETE-Kommando über 12 sec. Ein Blick auf die Statistiken (bzw. die Deltas) in v$sesstat zeigt dabei ein paar interessante Werte, zu denen ich im Folgenden behutsame Interpretationen anschließe und dabei die Erläuterungen der Dokumentation berücksichtige:
-- Zeitangaben NAME DIFF -------------------------------------------- ---------- DB time 1250 CPU used by this session 761 redo log space wait time 383 change write time 165 user I/O wait time 61
Demnach ist der eigentliche I/O-Anteil an der Laufzeit sehr bescheiden. Hoch sind hingegen die CPU-Nutzung und die Wartezeiten auf Platz im Log-Buffer (in meiner Test-Datenbank sind die Größe von log buffer und online redo logs nicht optimiert). Dass hier Wartezeiten entstehen, liegt nicht zuletzt an den Datenmengen, die in diesem Zusammenhang bewegt werden:
NAME DIFF -------------------------------------------- ---------- redo size 237244384 -- ca. 226 MB undo change vector size 103994836 -- ca. 99 MB physical read bytes 12804096 -- ca. 12 MB
Dabei entsprechen die 12 MB physical read bytes nahezu exakt der tatsächlichen Größe des Segments, das laut USER_TABLES 1557 Blocks umfasst (1557 * 8192 = 12754944). Das Löschen einer Tabelle von 12 MB führt demnach zur Erzeugung von über 200 MB redo und fast 100 MB undo! Dabei ist das Verhältnis von redo zu undo erst einmal keine besondere Überraschung, da auch zu jedem undo record redo-Informationen erzeugt werden müssen (in einem früheren Test-Lauf hatte ich auch noch IMU(also in-memory-undo)-Angaben, die anzeigen, dass das DELETE zunächst den in-memory-undo-Mechanismus verwendete, aber beim Erreichen eines gewissen Volumens davon wieder Abstand nahm - so jedenfalls deute ich den Wert IMU undo allocation size: 63996; habe aber gerade Jonathan Lewis' Oracle Core nicht zur Hand, wo das Verhalten im Detail geschrieben wird. Ein paar grundlegende Aussagen zu diesem Thema hat Jonathan Lewis auch gelegentlich in einem OTN-Thread gemacht. Im aktuellen Test-Lauf spielte IMU dann aber keine Rolle). Aber das Verhältnis der Netto-Datenmenge in der Tabelle zu undo und redo ist doch deutlich extremer als ich es erwartet hätte. Interessant sind auch die Statistiken zu den Block-Zugriffen, hier kombiniert mit allen Angaben, deren Wert im Umkreis von 1M liegt:
NAME DIFF -------------------------------------------- ---------- db block changes 2019636 session logical reads 1039698 db block gets 1037897 db block gets from cache 1037897 redo entries 1006269 HSC Heap Segment Block Changes 1000000 table scan rows gotten 1000000 buffer is pinned count 998477 free buffer requested 16246 consistent gets 1801 consistent gets from cache 1801 consistent gets from cache (fastpath) 1701 no work - consistent read gets 1658 physical reads 1563 physical reads cache 1563 switch current to new buffer 1523 table scan blocks gotten 1523 physical reads cache prefetch 1495 buffer is not pinned count 271 db block gets from cache (fastpath) 200
Keine Überraschungen sind die table scan rows gotten. Ansonsten sehe ich diverse Werte, die im Bereich der Anzahl der Tabellenblocks (1557) liegen und andere, die eher der Satzanzahl entsprechen. Eine Ausnahme sind die db block changes, die die Dokumentation folgendermaßen erklärt: "the total number of changes that were part of an update or delete operation that were made to all blocks in the SGA. Such changes generate redo log entries and hence become permanent changes to the database if the transaction is committed." Blocks in der SGA sind natürlich auch die undo Blöcke, so dass die Verdopplung des Werts gegenüber 1M verständlich wird. Festzustellen ist in jedem Fall, dass die Anzahl der db block gets ("Number of times a CURRENT block was requested") in der Nähe der Satzanzahl liegt: offenbar ist hier keine BULK-artige Zusammenfassung mehrerer Änderungen eines Blocks möglich - und tatsächlich ändern sich redo size und undo change vector size nicht, wenn man die Löschung der Daten satzweise durchführt:
-- basierend auf dem ersten Test create index ix_test_delete on test_delete(id); -- Fall 1: delete from test_delete; --> Abgelaufen: 00:00:28.60
-- die Laufzeit ist also gegenüber dem ursprünglichen Test -- ohne Index mehr als verdoppelt -- Fall 2: begin for i in 1..1000000 loop delete from TEST_DELETE where id = i; end loop; end; / --> Abgelaufen: 00:01:23.49
In beiden Fällen kommt man auf > 400MB redo und > 200MB undo, was angesichts des zusätzlichen Index in der Relation plausibel erscheint. Davon abgesehen ist das DELETE über alle Sätze natürlich schneller als die Löschung über Einzelkommandos: unter anderem steigt die Anzahl der consistent gets (1954 -> 3002599), da für jedes einzelne DELETE noch einmal ein für den Start der Transaktion konsistenter Zustand des Blocks, der geändert werden soll, erzeugt werden muss.
Viel weiter komme ich an dieser Stelle heute nicht mehr, aber zumindest die Beobachtung, dass die Performance von DELETE-Operationen weniger vom Datenvolumen als von der Anzahl geänderter Sätze abhängt, ist etwas, das ich mir merken sollte.
Mittwoch, November 07, 2012
Partitioned Outer Join
Beim Blick auf die nützliche Zusammenfassung der SQL-Optionen für Analyse und Reporting in der Dokumentation ist mir dieser Tage der (in 10g eingeführte) Partitioned Outer Join wieder ins Bewusstsein gekommen, dem ich wohl in der Vergangenheit schon mal begegnet war, damals aber noch nicht so genau wusste, wozu man ihn denn gebrauchen sollte. Da mir das inzwischen klarer ist, hier ein Beispiel:
Gegeben sind eine Fakten-Tabelle mit den jährlichen Umsätzen von Abteilungen (DEPTNO) und eine Datums-Dimension - beide im Beispiel so schmal wie möglich:
drop table d_test;
drop table f_test;
create table d_test (
year number
);
insert into d_test(year) values (2010);
insert into d_test(year) values (2011);
insert into d_test(year) values (2012);
insert into d_test(year) values (2013);
create table f_test (
deptno number
, year number
, turnover number
);
insert into f_test (deptno, year, turnover) values (10, 2010, 500);
insert into f_test (deptno, year, turnover) values (10, 2011, 600);
insert into f_test (deptno, year, turnover) values (10, 2012, 500);
insert into f_test (deptno, year, turnover) values (20, 2011, 500);
insert into f_test (deptno, year, turnover) values (20, 2012, 700);
select * from d_test;
YEAR
----------
2010
2011
2012
2013
select * from f_test;
DEPTNO YEAR TURNOVER
---------- ---------- ----------
10 2010 500
10 2011 600
10 2012 500
20 2011 500
20 2012 700
Um aus diesen Daten einen Bericht zum machen, in dem für jede Abteilung und jedes Jahr ein Datensatz enthalten ist - also Sätze mit einem Turnover = 0 zu ergänzen für alle Abteilungen, zu denen in der Faktentabelle für ein Jahr kein Datensatz vorliegt -, gibt's verschiedene Möglichkeiten. Eine Variante wäre, zunächst eine Referenz als Kreuzprodukt aller Jahre und Abteilungen zu bilden und diese dann per outer join mit den Fakten zu verbinden. Das funktioniert, ist aber relativ sperrig. Nicht möglich ist in diesem Fall der einfache Outer Join:
select r.year
, t.year
, t.deptno
, t.turnover
from f_test t
right outer join
d_test r
on (t.year = r.year);
YEAR YEAR DEPTNO TURNOVER
---------- ---------- ---------- ----------
2010 2010 10 500
2011 2011 10 600
2012 2012 10 500
2011 2011 20 500
2012 2012 20 700
2013
Damit wird zwar das in den Fakten fehlende Jahr ergänzt, aber nur einmal und ohne Bezug zu den Abteilungen. Um das gewünschte Ergebnis zu erhalten, muss das Jahr an jede einzelne Abteilung gejoint werden: und genau das leistet der Partitioned Outer Join, bei dem die (zusätzliche Partitions-) Join-Bedingung in einer PARTITION BY-Klausel eingesetzt wird - womit sich PARTITION BY in die Reihe jener Oracle-Begriffe stellt, die je nach Kontext sehr viele unterschiedliche Dinge bezeichnen können ...
select t.deptno
, t.year
, r.year
, t.turnover
from f_test t partition by (deptno)
right outer join
d_test r
on (t.year = r.year);
DEPTNO YEAR YEAR TURNOVER
---------- ---------- ---------- ----------
10 2010 2010 500
10 2011 2011 600
10 2012 2012 500
10 2013
20 2010
20 2011 2011 500
20 2012 2012 700
20 2013
-- mit etwas Glättung:
select t.deptno
, r.year
, coalesce(t.turnover, 0) turnover
from f_test t partition by (deptno)
right outer join
d_test r
on (t.year = r.year)
order by t.deptno
, r.year
DEPTNO YEAR TURNOVER
---------- ---------- ----------
10 2010 500
10 2011 600
10 2012 500
10 2013 0
20 2010 0
20 2011 500
20 2012 700
20 2013 0
Hier bekomme ich also die gewünschten acht Ergebnissätze (2 Abteilungen * 4 Jahre). Im Plan sieht man für diesen Fall einen klaren Hinweis auf die durchgeführte Operation:
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 9 (34)| 00:00:01 |
| 1 | VIEW | | 1 | 65 | 8 (25)| 00:00:01 |
| 2 | MERGE JOIN PARTITION OUTER| | 1 | 52 | 8 (25)| 00:00:01 |
| 3 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | D_TEST | 4 | 52 | 3 (0)| 00:00:01 |
|* 5 | SORT PARTITION JOIN | | 5 | 195 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | F_TEST | 5 | 195 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."YEAR"="R"."YEAR")
filter("T"."YEAR"="R"."YEAR")
-- zum Vergleich der Plan für eine Query mit einer
-- per cartesian join erzeugten Referenz (bei der
-- ich auch noch eine zweite Dimension ergänze und
-- feststelle, dass meine Namensgebung im Test mal
-- wieder nichts taugt ...
create table d_dept (
deptno number
);
insert into d_dept (deptno) values (10);
insert into d_dept (deptno) values (20);
with
reference as (
select d1.deptno
, d2.year
from d_dept d1
, d_test d2
)
select r.deptno
, r.year
, coalesce(t.turnover, 0) turnover
from f_test t
right outer join
reference r
on (t.year = r.year and t.deptno = r.deptno)
order by t.deptno
, r.year;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 520 | 12 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 8 | 520 | 12 (17)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 8 | 520 | 11 (10)| 00:00:01 |
| 3 | VIEW | | 8 | 208 | 7 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN| | 8 | 208 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | D_DEPT | 2 | 26 | 3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 4 | 52 | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | D_TEST | 4 | 52 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | F_TEST | 5 | 195 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."DEPTNO"(+)="R"."DEPTNO" AND "T"."YEAR"(+)="R"."YEAR")
Offensichtlich ist, dass für den Partitioned Outer Join spezielle Schritte erscheinen (MERGE JOIN PARTITION OUTER, SORT PARTITION JOIN) und dass die Cardinality nur im Fall der Query mit Referenz korrekt (= 8) bestimmt wird. Über Hints konnte ich für den Partitioned Outer Join den MERGE JOIN auch in einen NESTED LOOPS umwandeln, allerdings habe ich es auf Anhieb nicht geschafft, einen HASH JOIN daraus zu machen. Auf der Suche nach einer Erklärung dafür, bin ich bei einem recht interessanten Thread (von 2005) in oracle-l gelandet, in dem Christian Antognini die Frage stellt, ob der Partitioned Outer Join die Verwendung der Join-Bedingung als access-Prädikat ausschließt und Lex de Haan die ANSI-Definition der Funktionalität zitiert. Die dort aufgeworfenen Fragen zeigen in jedem Fall, dass das Performance-Verhalten des Partitioned Outer Join ein Thema ist, das sich nicht ohne umfassendere Untersuchung erläutern lässt.
Außerdem habe ich dann auf der Suche nach Aussagen zur Arbeitsweise des Partitioned Outer Join auch noch einen Artikel von Adrian Billington entdeckt, der so ziemlich alles enthält, was ich hier aufgeschrieben habe, und noch ein paar zusätzliche Punkte - anders hätte ich's mir beim Herrn Billington auch nicht vorstellen können.
Außerdem habe ich dann auf der Suche nach Aussagen zur Arbeitsweise des Partitioned Outer Join auch noch einen Artikel von Adrian Billington entdeckt, der so ziemlich alles enthält, was ich hier aufgeschrieben habe, und noch ein paar zusätzliche Punkte - anders hätte ich's mir beim Herrn Billington auch nicht vorstellen können.
Dienstag, November 06, 2012
IFFS und Filterprädikate
Diesmal habe ich den Titel nicht - wie üblich - aus mangelnder Sorgfalt, sondern mit Bedacht vage gewählt, um dem Eintrag nicht seine ohnehin schon bescheidene Pointe zu nehmen... Davon abgesehen ist mir der beschriebene Effekt bestimmt schon ziemlich häufig begegnet, ohne dass ich darüber nachgedacht hätte. Aber genug der Vorrede: heute habe ich in einem Execution Plan ungefähr Folgendes gesehen:
-- 11.2.0.1
explain plan for
select *
from test
where c = 1;
select * from table(dbms_xplan.display);
Plan hash value: 850129961
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 38000 | 22 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| PK_TEST | 1000 | 38000 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"=1)
Daraufhin habe ich einen Blick auf die Index-Spalten geworfen:
select column_name
, column_position
from user_ind_columns
where index_name = 'PK_TEST';
COLUMN_NAME COLUMN_POSITION
------------------------------ ---------------
A 1
Anschließend habe ich mich einen Moment lang gewundert: ein IFFS mit einem Filterkriterium, das gar nicht im Index enthalten ist? Ein Blick auf die Objekt-Definition hat die Überraschung dann beendet - und auch ich spare mir hier jetzt die dramatische Steigerung:
create table test (
a
, b
, c
, constraint pk_test primary key(a)
) organization index
as
select rownum a
, 'xxxxxxxxxx' b
, mod(rownum, 10) c
from dual
connect by level <= 10000
Es handelt sich also um eine IOT und deren Auftreten wird im Execution Plan offenbar immer als IFFS des PK-Index dargestellt, was durchaus einleuchtet, da das Index-Segment im Fall meiner Test-Tabelle ohne Overflow-Segment ja das einzige physikalische Objekt ist, auf das man sich beziehen kann. Und somit ist dann auch die Filteroperation einleuchtend, da das Index-Segment natürlich alle Tabellen-Attributen enthält - nur die Spaltenangabe der user_ind_columns passt nicht ganz zu den anderen Informationen. Da aber auch nur die PK-Spalte A im Beispiel als access-Prädikat verwendet werden kann, bin ich auch mit dieser Klassifizierung zufrieden.
Freitag, November 02, 2012
Oracle System Architektur (James Morle)
James Morle hat eine Serie zum Thema System Architecture gestartet, die eine ganze Reihe von Aspekten beleuchten soll, die beim Design einer Oracle Datenbank relevant sind.Wie der Autor bin auch ich gespannt, ob er dieses Projekt abschließen wird: "Now I’m looking at the list it seems more like a multi-volume book than a handful of blog posts." Eine andere Frage ist, ob ich die Serie hier komplett verlinken (und kommentieren?) werde, aber ich will mich bemühen:
- System Architecture Series: Introduction to the Series and Licensing: beschäftigt sich mit der Frage der erforderlichen Oracle-Lizenzen. Dabei weist der Autor auf die Rolle der CPU cores (und der gewichtenden core factors) hin. Außerdem erklärt er, dass von aktuelleren Lizenzbedingungen in aller Regel nur Oracle profitiert und man deshalb nach Möglichkeit bei den alten Lizenzen bleiben sollte. Auch zeigt sein Beispiel, wie schnell Oracle-Lizenzen furchtbar teuer werden können (im Beispiel: 16.000$ Hardware zu 1.224.000$ für die Lizenzen)
Bestimmung von Intervallüberschneidungen
Im OTN-Forum SQL und PL/SQL hat Frank Kulash eine sehr schöne Lösung für das Problem der Bestimmung der Schnittmenge mehrerer Zeitintervalle geliefert, die eine Kombination den analytischen Funktionen LEAD, COUNT und SUM verwendet. Entscheidend ist die Betrachtung der Intervallanfänge als Events, die gezählt und bewertet werden.
Ich hatte ich Thread eine sehr viel sperrigere Variante angeboten, die die Intervalle in ihre Einzelelemente splittete (in diesem Fall Sekunden) - für längere Intervalle könnte man damit ziemlich effektiv Last auf dem Server erzeugen ... (was mir schon klar war, als ich das SQL schrieb: ich wollte erst einmal sehen, ob mir überhaupt eine Lösung für das, wie ich fand, interessante Problem einfallen würde).
Data Guard
Data Guard ist auch eine Technik, mit der ich mich noch nie ernsthaft beschäftigt habe - aber in diesem Fall könnte ich das (anders als im Exadata-Fall) auch auf einem simplen Desktop-System. Wie man einen solchen Test durchführen kann, erklärt Howard Rogers in seinem Blog - und mir fällt niemand ein, der bessere Anleitungen schreibt als der Herr Rogers. Die Beantwortung der Frage, was Data Guard eigentlich ist, überlasse ich ihm dann auch gleich:
Data Guard is Oracle’s premium “high availability” database architecture. “Premium” in this context means “Enterprise Edition Only”, so it costs big dollars. Happily, you are allowed to set it up and test it out for free. “High Availability” means that, unlike RAC, it uses two (or more) databases to store identical copies of your data in different physical locations. Should the proverbial meteorite strike, one of them should still be in existence and thus access to your data remains assured.
Data Guard is, therefore, a highly-interesting technology that is pricey but invaluable. Getting a taste of it on a desktop is therefore something budding DBAs should definitely be interested in. This article will accordingly show you how to go about setting up an 11g Release 2 physical Data Guard configuration and what’s involved in managing it to cope with disasters and other management needs.Nachtrag 19.02.2015: wieder ein Link auf eine Webseite von Howard Rogers, der keine Dauer beschieden war...
Donnerstag, November 01, 2012
Exadata Storage Indizes
Da ich noch nicht mit Exadata gearbeitet habe - und nicht damit experimentieren kann - fällt es mir schwer, mich ohne Praxisbezug mit dem Thema zu beschäftigen. Trotzdem hier ein paar Links auf Richard Footes Erklärung der Exadata Storage Indizes:
- Exadata Storage Indexes – Part I (Beginning To See The Light): "A Storage Index basically maintains summary information about database table data down on the Exadata storage servers. The information consists of the minimum value, the maximum value and a flag to denote whether any Nulls exist for up to 8 different columns within each 1M storage region of disk." (Was mich an die Rolle von DataIds im SSAS erinnert). Storage Indizes sind keine persistierten Objekte, sondern werden komplett im Memory gehalten. Sie dienen der Vor-Filterung von Daten auf Storage-Ebene: nur potentiell relevante Daten werden an die Datenbank geliefert.
- Exadata Storage Indexes Part II – Differences With Database Indexes (Space Dementia): mit einer umfangreichen Liste der Unterschiede zwischen Storage Indizes und "normalen" Indizes.
- Exadata Storage Indexes Part III – Similarities With Database Indexes (Same Old Scene): auch hier sagt der Titel schon ziemlich genau, was drin ist: eine Untersuchung der Gemeinsamkeiten von Storage Indizes und "normalen" Indizes.
- Exadata Storage Indexes Part IV – Fast Full Table Scans (Speed of Life): mit einem praktischen Beispiel.
- Exadata Storage Indexes Part V: Warming Up (Here Come The Warm Jets): erklärt, dass storage indexes ähnlich wie database indexes eine "warm up" Phase durchlaufen, bis ihre Cache-Effekte optimal wirksam werden.
Und noch eine Serie mit dem Vergleich von herkömmlichen und Storage-Indizes, deren Grundgedanke ist, dass Storage-Indizes nicht alles leisten können, was die traditionellen Indizes leisten:
- Storage Indexes vs Database Indexes Part I MIN/MAX (Maxwell’s Silver Hammer): die Abkürzung des INDEX FULL SCAN (MIN/MAX) steht mit Storage-Indizes nicht zur Verfügung.
- Storage Indexes vs Database Indexes Part II: Clustering Factor (Fast Track): zeigt, dass die Clusterung der Tabellen-Daten für Storage-Indizes sogar noch wichtiger ist als für traditionelle Indizes: ein Index mit schlechtem CF funktioniert bei entsprechender Selektivität immer noch sehr gut - für Storage-Indizes gilt das nicht unbedingt.
Abonnieren
Posts (Atom)