Dienstag, Mai 31, 2011
Latches und Mutexes
Jonathan Lewis hat gerade auf Andrey Nikolaevs Blog Latch, mutex and beyond hingewiesen, der die erwähnten Themen tatsächlich sehr detailliert erläutert. Interessant ist z.B. gleich der erste grundlegende Artikel im Blog: Oracle latches and general Spinlocks.
Sonntag, Mai 29, 2011
Data Loading
Randolf Geist liefert in seinem Blog eine sehr lesenswerte Zusammenfassung der Dinge, die man zur Handhabung von Indizes bei Massendatenladevorgängen wissen sollte - und die eine solidere Grundlage für diverse Behauptungen gibt, die ich hier vor kurzem aufgeschrieben hatte. Neu für mich war, dass ein TRUNCATE einen Index wieder VALID werden lässt:
Hilfreich sind auch die diversen Links auf Richard Footes Erläuterung der Unterschiede zwischen unique und nonunique indexes - und auf des Herrn Geists eigene Untersuchungen zum Verhalten von Constraints.
create table test as select rownum id from dual connect by level <= 1000; create index test_idx on test(id); alter index test_idx unusable; select index_name , status from user_indexes where table_name = 'TEST'; INDEX_NAME STATUS ------------------------------ -------- TEST_IDX UNUSABLE truncate table test; select index_name , status from user_indexes where table_name = 'TEST'; INDEX_NAME STATUS ------------------------------ -------- TEST_IDX VALID
Hilfreich sind auch die diversen Links auf Richard Footes Erläuterung der Unterschiede zwischen unique und nonunique indexes - und auf des Herrn Geists eigene Untersuchungen zum Verhalten von Constraints.
Freitag, Mai 27, 2011
Cardinality Feedback
Im cbo-Entwickler Blog wird erläutert, was Cardinality Feedback leistet. Allerdings klingt die Beschreibung eher nach einer Krücke:
Nachtrag 05.08.2011: Kerry Osborne liefert in seinem Blog ein Beispiel dafür, dass das Cardinality Feedback in manchen Fällen zu recht instablien Plänen führt.
The improved estimates used by cardinality feedback are not persisted. For this reason, it’s always preferable to use other techniques to get cardinality estimates right the first time every time, e.g. extended statistics, dynamic sampling, or SQL profiles. But for cases where these techniques do not apply, cardinality feedback can provide some relief.Grundsätzlich ist das Konzept aber sicher zukunftsweisend, da ein Großteil meiner SQL-Tuning-Operationen auch nur darauf beruht, dass ich Cardinality-Schätzungen mit den actual rows Angaben abgleiche.
Nachtrag 05.08.2011: Kerry Osborne liefert in seinem Blog ein Beispiel dafür, dass das Cardinality Feedback in manchen Fällen zu recht instablien Plänen führt.
Donnerstag, Mai 26, 2011
Excel-Zugriff auf Oracle
Beim Zugriff von Excel auf Oracle via OLEDB habe ich zuletzt beobachtet, dass sich die Zugriffszeiten für den Aufbau der Liste verfügbarer Objekte zwischen den Oracle-Versionen 10.2.0.4 und 11.1.0.7 massiv erhöht haben. Aber der Reihe nach. Das Vorgehen bei der Bestimmung der Effekte sah in meiner deutschsprachigen Oberfläche folgendermaßen aus:
ALL_OBJECTS erscheint hier also dreifach, daneben noch einmal ALL_SYNONYMS. Nun zur Performance des Aufbaus der Auswahlliste:
Ich habe den Fall in Charles Hoopers Blog angesprochen und dort den Vorschlag bekommen, die Query über DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE umzuleiten, was wahrscheinlich funktionieren würde. Dabei könnte man mit CTEs für den ALL_OBJECTS-Zugriff arbeiten, was vor allem für den extrem langsamen Fall mit 11.1.0.7 und Connect vermutlich lohnend wäre. Alternativ verweist der Herr Hooper auf Tom Kytes Motto "that the fastest way to do something is to not do it at all" - das wäre unter Umständen auch ein guter Workaround.
- Excel 2007 starten
- Menü: Daten
- Menü: Aus anderen Quellen
- Auswahl: vom Datenverbindungsassistenten: importiert Daten ... über OLEDB
- Auswahl: Microsoft Data Access - OLEDB-Anbieter für Oracle
- Angabe von Anmeldeinformationen für Oracle-DB
SELECT * FROM (SELECT NULL table_catalog, Decode (o1.owner, 'PUBLIC', NULL, o1.owner) table_schema, o1.object_name table_name, Decode(o1.owner, 'SYS', Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM', Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type) table_type, NULL table_guid, NULL description, NULL table_propid, NULL date_created, NULL date_modified FROM all_objects o1 WHERE ( ( o1.object_type = 'TABLE' AND o1.generated != 'Y' ) OR o1.object_type = 'VIEW' ) UNION SELECT NULL table_catalog, Decode (o2.owner, 'PUBLIC', NULL, o2.owner) table_schema, o2.object_name table_name, o2.object_type table_type, NULL table_guid, NULL description, NULL table_propid, NULL date_created, NULL date_modified FROM all_objects o2, all_objects o3, all_synonyms s WHERE o2.object_type = 'SYNONYM' AND ( ( o3.object_type = 'TABLE' AND o3.generated != 'Y' ) OR o3.object_type = 'VIEW' ) AND o2.owner = s.owner AND o2.object_name = s.synonym_name AND s.table_owner = o3.owner AND s.table_name = o3.object_name) tables ORDER BY 4, 2, 3 ;
ALL_OBJECTS erscheint hier also dreifach, daneben noch einmal ALL_SYNONYMS. Nun zur Performance des Aufbaus der Auswahlliste:
- 10.2.0.4 (DBA): 4 sec. (19.786 rows, 321.193 consistent gets)
- 10.2.0.4 (CONNECT): 72 sec. (2.258 rows, 2.899.642 consistent gets)
- 11.1.0.7 (DBA): 39 sec. (23.152 rows, 383.950 consistent gets)
- 11.1.0.7 (CONNECT): 562 sec. (3.152 rows, 2.728.757 consistent gets)
Ich habe den Fall in Charles Hoopers Blog angesprochen und dort den Vorschlag bekommen, die Query über DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE umzuleiten, was wahrscheinlich funktionieren würde. Dabei könnte man mit CTEs für den ALL_OBJECTS-Zugriff arbeiten, was vor allem für den extrem langsamen Fall mit 11.1.0.7 und Connect vermutlich lohnend wäre. Alternativ verweist der Herr Hooper auf Tom Kytes Motto "that the fastest way to do something is to not do it at all" - das wäre unter Umständen auch ein guter Workaround.
Audit-Unschärfen
Jonathan Lewis zeigt in seinem Blog, dass Auditing Objektzugriffe nicht in allen Fällen korrekt zuordnet, da in manchen Fällen (z.B. bei reinen Index-Joins) nicht die erwarteten Tabellen, sondern interne Objekte (z.B. temporäre Views) aufgeführt werden. Dabei ist das Verhalten auch nicht in sich schlüssig, sondern liefert abhängig vom execution plan unterschiedliche Resultate.
Mittwoch, Mai 25, 2011
HIGH_VALUE für Interval Partitions
Heute ist mir aufgefallen, dass ich seit 10 Jahren einen Account in OTN habe, aber in den Foren noch nie eine Frage gestellt hatte. Das ist jetzt geändert: wobei der Beitrag im Database-General-Forum vermutlich auch als Blog-Eintrag funktioniert hätte. Kurz zusammengefasst geht's darum, dass interval partitions zu extrem hohen Laufzeiten führen, wenn ein weit von den tatsächlichen Inhalten abliegender HIGH_VALUE für die initiale Partition gewählt wurde. Sieht aus wie ein Bug, aber vielleicht gibt's auch noch eine plausible Erklärung für das Verhalten.
Nachtrag 26.05.2011: Jonathan Lewis hat im Thread inzwischen eine Antwort auf meine Frage gegeben, hält das Verhalten auch für einen Bug und verweist auf v$rowcache: "Check v$rowcache activity - that's where the CPU is going. You will see '100,000 - high_value' gets on on the dc_tablespaces and dc_users row cache entries when you run the query (and that's two latch hits on the rowcache latch for every get)." Außerdem hat er den Thread im Oracle Scratchpad verlinkt.
Nachtrag 27.05.2011: Greg Rahn hat das Problem inzwischen als Bug 12597302 erfasst.
Nachtrag 08.12.2011: unter der Bug-Nummer findet man in MOS zur Zeit allerdings nix...
Nachtrag 26.05.2011: Jonathan Lewis hat im Thread inzwischen eine Antwort auf meine Frage gegeben, hält das Verhalten auch für einen Bug und verweist auf v$rowcache: "Check v$rowcache activity - that's where the CPU is going. You will see '100,000 - high_value' gets on on the dc_tablespaces and dc_users row cache entries when you run the query (and that's two latch hits on the rowcache latch for every get)." Außerdem hat er den Thread im Oracle Scratchpad verlinkt.
Nachtrag 27.05.2011: Greg Rahn hat das Problem inzwischen als Bug 12597302 erfasst.
Nachtrag 08.12.2011: unter der Bug-Nummer findet man in MOS zur Zeit allerdings nix...
Dienstag, Mai 24, 2011
Insert Performance mit Bitmap- und B*Tree-Indizes
Vielleicht habe ich mir die Frage schon früher gestellt, vielleicht auch schon beantwortet, vielleicht auch schon hier in diesem Blog - aber was soll's: zur Not eben eine Doublette. Ich gehe davon aus, dass Bitmap Indizes die Insert-Performance stärker beeinträchtigen als B*Tree Indizes (völlig abgesehen von locking, concurrency etc.). Dazu folgender Test (unter 11.2.0.1; EE; Noarchivelog):
Tatsächlich benötigt das Insert im Fall der B*Tree-Indizes 34 sec und im Fall der Bitmap-Indizes 67 sec. Hier die Details:
Man sieht hier zum einen, dass mein Windows7-Rechner mit dem Logging leicht überfordert ist. Darüber hinaus kann man beobachten, dass die CPU-Last für die Bitmap-Indizes deutlich höher ist, und dass für diesen Fall sehr viel mehr LIOs (current gets und consistent gets) erscheinen. Aus v$sesstat geht hervor, dass der Bitmap-Fall deutlich weniger Arbeitsspeicher nutzt (PGA, UGA), und dass er mehr undo und redo hervorruft (was möglicherweise auch mit dem Noarchivelog-Modus zusammenhängt. Sehr viele der consistent gets fallen übrigens in die Kategorie "consistent gets - examination" (und sollten demnach relativ flott sein). Auch in anderen Statistiken zeigt der Bitmap-Fall deutlich höhere Werte, aber ich bleibe erst mal bei der Beobachtung stehen, dass die Maintainance für Bitmap Indizes teurer ist, als für B*Tree Indizes.
drop table test;
create table test
( id number
, col1 number
, col2 number
, pad varchar2(64));
/*
create bitmap index test_col1 on test(col1);
create bitmap index test_col2 on test(col2);
*/
create index test_col1 on test(col1);
create index test_col2 on test(col2);
@ trace
insert /* btree */ into test
select rownum id
, mod(rownum, 40) col1
, round(rownum)/1000 col2
, lpad('*', 40, '*')
from dual
connect by level <= 1000000;
@ trace_end
-- B*Tree
insert /* btree */ into test
select rownum id
, mod(rownum, 40) col1
, round(rownum)/1000 col2
, lpad('*', 40, '*')
from dual
connect by level <= 1000000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 10.81 34.01 0 30292 2993170 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 10.82 34.01 0 30292 2993170 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=0 us)
1 COUNT (cr=0 pr=0 pw=0 time=0 us)
1 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 15 0.48 1.28
log file switch completion 18 1.81 9.33
log buffer space 87 0.31 5.66
reliable message 83 0.00 0.00
rdbms ipc reply 87 1.02 1.67
control file sequential read 84 0.27 1.38
db file sequential read 8 0.01 0.05
Data file init write 4 0.00 0.00
db file single write 4 0.00 0.01
control file parallel write 12 0.00 0.02
enq: CF - contention 2 0.50 0.52
log file sync 1 0.08 0.08
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
-- Bitmap
insert /* bitmap */ into test
select rownum id
, mod(rownum, 40) col1
, round(rownum)/1000 col2
, lpad('*', 40, '*')
from dual
connect by level <= 1000000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 29.10 67.64 0 5759962 5992713 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 29.10 67.64 0 5759962 5992713 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=0 us)
1 COUNT (cr=0 pr=0 pw=0 time=0 us)
1 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 102 0.29 6.17
log file switch completion 26 0.10 1.13
control file sequential read 693 0.45 7.43
db file sequential read 66 0.07 0.62
Data file init write 33 0.00 0.00
db file single write 33 0.00 0.04
control file parallel write 99 0.08 0.48
rdbms ipc reply 33 0.15 0.59
log buffer space 44 0.27 2.24
latch: object queue header operation 1 0.00 0.00
enq: CF - contention 5 0.26 0.36
log file switch (checkpoint incomplete) 12 2.10 9.48
log file sync 1 0.03 0.03
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
Montag, Mai 23, 2011
Join Cardinality
Zu Erinnerungszwecken die Formel aus Jonathan Lewis' Cost Based Oracle (Kapitel 10, S. 265ff.) zur Join Cardinality für einfache Joins mit zwei Tabellen:
-- für einen Join der Tabellen t1 und t2 über die Spalte c1 bzw. c2 gilt: Join Selectivity = ((num_rows(t1) - num_nulls(t1.c1))/num_rows(t1)) * ((num_rows(t2) - num_nulls(t2.c2))/num_rows(t2)) / greater (num_distinct(t1.c1), num_distinct(t2.c2)) Join Cardinality = Join Selectivity * filtered cardinality (t1) * filtered cardinality (t2)
Für die Tabellen des Lewis'schen Beispiels ergibt sich:
Join Selectivity
= ((10000 - 0)/ 10000) *
((10000 - 0)/ 10000) /
greater(30,40)
= 1/40
Join Cardinality
= 1/40 * (400 * 200)
= 2000
Ich habe das Beispiel des Buchs mit 11.2.0.1 durchgespielt (die Scripts kann man übrigens hier downloaden) und bekomme die gleichen cardinalities wie der Herr Lewis, aber eine geringfügig andere cost-Angabe:
----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 2000 | 68000 | 58 | |* 1 | HASH JOIN | | 2000 | 68000 | 58 | |* 2 | TABLE ACCESS FULL| T2 | 200 | 3400 | 28 | |* 3 | TABLE ACCESS FULL| T1 | 400 | 6800 | 28 | -----------------------------------------------------------
Vermutlich ist die abweichende cost-Angabe mal wieder ein round/ceil-Effekt.
Nachtrag 15.02.2012: Für einen Join über mehrere Spalten wird durch die greater-Ausdrücke für alle Spalten dividiert, also:
Join Selectivity= .../ greater (num_distinct(t1.col1), num_distinct(t2.col1))/ greater (num_distinct(t1.col2), num_distinct(t2.col2))
Im Sinne einer doppelt verketteten Liste hier noch der Verweis auf einen jüngeren Eintrag zum gleichen Thema (und mit nahezu gleichem Titel).
Abonnieren
Posts (Atom)