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.
Abonnieren
Posts (Atom)