Locks und Latches im SQL Server

Bei Thomas Kejser gibt's eine sehr schöne Erläuterung zum Verhalten von Locks und Latches im SQL Server und zu den zugehörigen Analysemöglichkeiten, die die DMVs liefern.

Recyclebin

Über den Recyclebin haben zuletzt Charles Hooper und Timur Akhmadeev geschrieben:

• Hooper zeigt, wie es dazu kommen kann, dass in Zugriffsplänen Indizes mit BIN%-Namen erscheinen: diese Namen ergeben sich, wenn eine Tabelle über flashback wiederhergestellt wird - die Tabelle erhält dann wieder ihren urspünglichen Namen, aber die zugehörigen Indizes behalten ihren Recyclebin-Namen (den man natürlich auch wieder ändern kann). Wie üblich liefert der Autor alle erdenklichen Beispiele und Links zum Thema.
• Ahkmadeev erwähnt einen Fall, in dem ein INSERT INTO ... VALUES-Statement für einen einzelnen Satz über eine Stunde lang hing und CPU verbrannte, weil es mit einem rekursiven DELETE FROM RECYCLEBIN$ kollidierte, das (vermutlich in einem Loop) mehr als eine Million mal ausgeführt wurde.

Beschreibende Index-Hints

Jonathan Lewis erläutert in seinem Blog beschreibende Index-Hints, die seit Version 10 verfügbar sind. Dabei wird nicht der Name eines Index angegeben, sondern die Tabelle, der zugehörige Query-Block und die Reihenfolge der relevanten Spalten.

Details zu DBMS_METADATA

Zwei interessante Notizen zu DBMS_METADATA:

• Neil Johnson zeigt, dass für die Verwendung von DBMS_METADATA die SELECT_CATALOG_ROLE benötigt wird.
• Gary Myers führt vor, wie man mit Hilfe von DBMS_METADATA auf die Hash-Version von Benutzer-Paßwörtern zugreifen kann (was in 11g über DBA_USERS nicht mehr möglich ist)

DROP-Operationen verhindern

Carsten Czarski zeigt in seinem Blog, wie man das versehentliche Löschen von Datenbankobjekten mit Hilfe von Triggern erschweren kann.

LISTAGG

Da ich heute - wahrscheinlich zum wiederholten Mal - die LISTAGG-Funktion in Releases unterhalb von 11.2 verwenden wollte, ehe mir einfiel, dass sie dort noch nicht existiert, hier ein Hinweis auf Tim Halls Erläuterung diverser Methoden zur Aggregation von Strings, die unter anderem auch die handliche WM_CONCAT-Funktion enthält. Weitere Hinweise auch zur Performance verschiedener Verfahren findet man bei Adrian Billington (bei dem ich dieser Tage andauernd lande), der auch einen Link auf Tom Kytes klassische STRAGG-Funktion liefert.

CBO-Schätzungen für PL/SQL

Randolf Geist stellt in seinem Blog eine Liste von Artikeln zusammen, auf die er in seinen DOAG-Unconference-Sessions (die ich gerne gesehen hätte) Bezug genommen hat - vor allem auf Adrian Billingtons Beiträge zum Thema und auf Joze Senegacniks grundlegendes Kapitel im Band Expert Oracle Practices.

Index Organized Tables

Jonathan Lewis hat ein paar Links zu Index Organized Tables (IOT) aufgelistet: vor allem führt er Martin Widlakes Artikelserie zum Thema auf.

Nachtrag 13.12.2011: Noch ein Hinweis vom Herrn Lewis: die including clause stellt nicht notwendig sicher, dass eine Spalte im Index-Segment landet (und nicht im Overflow-Segment), da Oracle die Spalten unter Umständen intern umstellt.

Cardinality Probleme bei Star Transformation

Randolf Geist erläutert in seinem Blog, dass der CBO bei der Star Transformation zu falschen Cardinality-Schätzungen kommt, wenn mehrere Dimensionen in einer einzigen Tabelle zusammengefasst sind.

Instabile Pläne

Kerry Osborne hat vor einiger Zeit ein extrem nützliches Script zur Bestimmung von Queries veröffentlicht, deren Ressourcen-Nutzung nach einer Planänderung dramatisch verändert ist. Hintergrundinformationen zum Thema (und weitere Analysescripte) findet man in seinem Blog. Ich habe das Script behutsam erweitert (und weniger behutsam formatiert) und meiner eigenen Sammlung hinzugefügt:

• Kerry Osbornes Script
• Meine Version

Basierend auf dem Ergebnis kann man dann in DBA_HIST_SQLSTAT prüfen, ob die Änderung eine Verbesserung oder eine Verschlechterung darstellte. Und anschließend kann mann dann mit Hilfe von DBMS_XPLAN.DISPLAY_AWR die im Automatic Workload Repository vorliegenden Pläne ermitteln.

Statistik-Erfassung für große partitionierte Tabellen

Eine kurze Notiz zur Frage: wie sollten Statistiken für große partitionierte Tabellen aktualisiert werden? Darauf gibt es viele Anworten, zwei davon findet man im Blog der cbo-Entwickler. Darin erläutert Maria Colgan die Optionen für 10.2.0.4 und 11:

• In Version 10.2.0.4 kann man mit Hilfe von DBMS_STATS.COPY_TABLE_STATS die Statistiken einer Partition in eine andere Partition kopieren, wobei die Statistiken der partition key Spalte sinnvoll angepasst werden; so wie ich's verstehe, bleiben die übrigen Spalten-Statistiken identisch, was aber in der Regel kein Problem sein sollte, da die meisten Spalten der für das Feature in Frage kommenden großen historisch partitionierten Faktentabellen pro Partition ähnliche Eigenschaften haben sollten

• In Version 11 kann man die globale Statistik-Erhebung für große partitionierte Objekte auf INCREMENTAL umstellen, was bedeutet, dass nur noch die Deltas analysiert werden müssen und distinkte Anzahlen über eine Synopsis bestimmt werden. Damit sollte das Thema in Version 11 deutlich unproblematischer sein. Weitere Details zum Thema findet man bei Greg Rahn, der auch noch mal auf ein weiterführendes White-Paper verweist (das auch bei Frau Colgan erwähnt wird).

Doug Burns hat zum Thema der Statistiken für partitionierte Tabellen eine ganze Serie von Blog-Artikeln geschrieben, die neben weiteren relevanten Artikeln von Randolf Geist und Kerry Osborne im Oracle Sratchpad verlinkt sind. Dort stellt Jonathan Lewis fest: "It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables." Und da hat er offenbar mal wieder Recht.

Nachtrag 18.01.2012: inzwischen hat Randolf Geist ein paar wichtige Hinweise zur inkrementellen Erhebung von Partitionsstatistiken veröffentlicht - und vor allem auch ein paar potentielle Probleme des Features aufgeführt.

Evil Events

Tanel Poder erwähnt in seinem Blog ein paar Events, die Oracle offenbar moralisch bewertet, nämlich Inject Evil Literals und Inject Evil Identifiers. Nahe liegend ist dann die von Oracle vorgeschlagene Action zu den Fehlermeldungen: "never set this event".

TKPROF mit External Table anzeigen

Adrian Billington erläutert, wie man TKPROF-Ausgabe-Dateien mit Hilfe des External Table Preprocessors in sqlplus verfügbar machen kann:

Using the simple techniques described in this article, we are now able to find a trace file, read it using the TRACEFILE_XT read-only external table, profile it with preprocessor external tables over TKProf or OraSRP and read the reports without leaving our IDE once. We can even read HTML reports in a browser window generated from within SQL*Plus without any additional keystrokes. For readers who work regularly with trace files, these are good productivity improvements!

Logging in Error-Tabelle

Dieser Tage wurde ich an die Möglichkeit erinnert, Fehler bei DML-Operationen in einer DML-Error-Logging-Tabelle zu protokollieren, was ich mal wieder als Option betrachte, an PL/SQL vorbei zu kommen...

Der Database Data Warehousing Guide erläutert diese Option grundsätzlich folgendermaßen:

DML error logging extends existing DML functionality by enabling you to specify the name of an error logging table into which Oracle Database should record errors encountered during DML operations. This enables you to complete the DML operation in spite of any errors, and to take corrective action on the erroneous rows at a later time.

This DML error logging table consists of several mandatory control columns and a set of user-defined columns that represent either all or a subset of the columns of the target table of the DML operation using a data type that is capable of storing potential errors for the target column.

Ein detailliertes Beispiel für das Vorgehen gibt's bei Tim Hall; für den eiligen Nutzer hier eine kleinere Demonstration:

-- Anlage einer Tabelle TEST
create table test
( id number
, name varchar2(10)
, description varchar2(32)
, some_data number(8));

-- Anlage einer passenden ERROR-Table zur Tabelle TEST
-- mit Hilfe des dbms_errorlog-Packages
exec dbms_errlog.create_error_log (dml_table_name => 'test');

-- Die erzeugte ERROR-Tabelle besitzt das Präfix ERR$_
-- und enthält alle Spalten der Zieltabelle als VARCHAR2(4000)
-- und zusätzlich diverse Spalten zur Speicherung von Fehler-
-- Informationen
desc ERR$_TEST

Name                                      Null?    Typ
----------------------------------------- -------- -------------------
ORA_ERR_NUMBER$                                    NUMBER
ORA_ERR_MESG$                                      VARCHAR2(2000)
ORA_ERR_ROWID$                                     ROWID
ORA_ERR_OPTYP$                                     VARCHAR2(2)
ORA_ERR_TAG$                                       VARCHAR2(2000)
ID                                                 VARCHAR2(4000)
NAME                                               VARCHAR2(4000)
DESCRIPTION                                        VARCHAR2(4000)
SOME_DATA                                          VARCHAR2(4000)

Details zum Format der ERROR-Tabelle liefert der Administrator's Guide:

• ORA_ERR_NUMBER$: Oracle error number
• ORA_ERR_MESG$: Oracle error message text
• ORA_ERR_ROWID$: Rowid of the row in error (for update and delete)
• ORA_ERR_OPTYP$: Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
• ORA_ERR_TAG$: Value of the tag supplied by the user in the error logging clause

Mit Hilfe der Klausel LOG ERRORS INTO ... kann man nun eine DML-Operation zum erfolgreichen Abschluss bringen, die sonst auf Fehler gelaufen wäre:

-- mein Insert klappt erst mal nicht
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000;

     , rownum * 1000000
              *
FEHLER in Zeile 5:
ORA-01438: Wert größer als die angegebene Gesamststellenzahl, die für diese Spalte zulässig ist

-- aber mit der LOG ERRORS INTO Klausel geht's durch
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000
log errors into err$_test ('insert') reject limit unlimited;

99 Zeilen wurden erstellt.
--> die übrigen 9900 Sätze landen dabei in ERR$_TEST

Offenbar wird dabei nur der erste auftretende Fehler protokolliert:

-- Löschung der Daten der ERROR-Tabelle:
truncate table err$_test;

-- dazu ein INSERT, das für mehrere Spalten unverdauliche Daten enthält:
insert into test 
values ( 1
       , 'blaaaaaaaaaaaaaaaaaaaaaa'
       , 'ssssssssssssssssssssssssssspppppppppppppppppppaaaaaaaaaaaaaaaaaaaaaaaaammmmmmmmmmmmmmmmm'
       , 123456789000000000000)
log errors into err$_test ('insert') reject limit unlimited;

0 Zeilen wurden erstellt.

--> was für die Spalten 2 - 4 nicht klappen kann

-- Die Metadaten zum Fehler sind dann:

select ora_err_number$
     , ora_err_mesg$
     , ora_err_rowid$
     , ora_err_optyp$
     , ora_err_tag$
  from err$_test;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                  ORA_ERR_ROWID$  ORA_ERR_OPTYP$  ORA_ERR_TAG$
--------------- ------------------------------ --------------- --------------- ---------------
          12899 ORA-12899: Wert zu groß für Sp                 I               insert
                alte "DBADMIN"."TEST"."NAME" (
                aktuell: 24, maximal: 10)

-- dann folgen in err$_test die Werte des gescheiterten INSERTs.

Die Dokumentation (in diesem Fall die SQL Language Reference) nennt noch folgende Fälle, in denen der Mechanismus nicht verwendbar ist - also ein Fehler auftritt und ein Rollback durchgeführt wird:

• Verletzung von deferred constraints.
• Direct-Path-Operationen, die eine unique constraint oder index violation hervorrufen.
• Update oder Merge Operationen, die eine unique constraint oder index violation hervorrufen.

Für LONG, LOB und Objekt-Typen gelten spezielle Bedingungen, deren Aufzählung ich mir hier aber mit Verweis auf die Doku spare.

Auto DOP in 11.2

Dieser Tage haben sich Uwe Hesse, Randolf Geist und Gwen Shapira mit dem neuen Auto Degree Of Parallelism (DOP) Feature in 11.2 beschäftigt.

Eine grundlegende Diskussion des Features liefert Frau Shapira in ihrem Blog:

at least in the white papers and presentations it sounds like a very attractive solution. There are two levels to Auto DOP:

• Limited – when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources.
• Auto – Oracle will decide on degree of parallelism for every query. In addition two exciting new features are enabled: parallel statement queuing and in-memory parallel execution.

Der Herr Hesse erläutert die Wirkung des Parameters parallel_degree_policy und die daraus resultierende Wirksamkeit von Hints:

parallel_degree_policy=LIMITED will give you the DOP you request and compute an appropriate DOP only with a parallel degree of DEFAULT as an attribute of the table. Use this parameter if you trust that your applications/designers know why they use a certain DOP. parallel_degree_policy=AUTO will overrule any specific DOP you gave – except the new 11g parallel (n) Hint – and consider to do things in parallel for all tables even without a Hint or Degree.

Beim Herrn Geist geht's um die Beobachtung, dass ein parallel_degree_policy=auto die Verwendung von direct path inserts hervorrufen kann, was zwar in gewisser Weise folgerichtig ist, aber die üblichen Nebeneffekte des direct path hervorruft (INSERT oberhalb der HWM und "ORA-12838: cannot read/modify an object after modifying it in parallel"), wobei besonders ORA-12838 ungünstige Wirkungen haben kann:

An existing application logic might break because it attempts to re-access the object after the now direct-path insert within the the same transaction which will end up with an "ORA-12838: cannot read/modify an object after modifying it in parallel".

Bei den Herren Hesse und Geist gibt's die dort üblichen Testfällen, mit deren Hilfe die Effekte nachvollzogen und überprüft werden können.

Nachtrag 12.11.2011: In seinem Kommentar weist Randolf Geist darauf hin, dass Uwe Hesses Beobachtung der Ausschaltung von Parallel-Hints durch parallel_degree_policy=AUTO wahrscheinlich auf Bug 10628995 beruht. In seinem Kommentar zu Uwe Hesses Blog kommt Greg Rahn zur gleichen Einschätzung.

SQL Sentry Plan Explorer

Dieser Tage bin ich auf den kostenlosen SQL Sentry Plan Explorer gestossen, ein Tool, mit dessen Hilfe sich Execution Plans im SQL Server deutlich klarer visualisieren lassen als mit den Bordmitteln im SSMS (die selbst aber schon ganz brauchbar sind). Das Tool lässt sich als Add-In zum SSMS einrichten und erfordert eine Installation von .Net 4.0. Zu den Features des Tools gehören das Plan Diagram mit einer klaren Hervorhebung der kostspieligsten Abschnitte eines Plans, der Join Tree mit einer Visualisierung der Tabellenverknüpfungen und nicht zuletzt der Plan Tree, der den Zugriffsplan so darstellt, wie man es aus Oracle z.B. vom dbms_display-Package kennt. Nett und - wie gesagt - kostenlos.

Mutex Waits

Andrey Nikolaev hat mal wieder einen interessanten Artikel über das Verhalten von Mutexes veröffentlicht. Dieses Verhalten hat sich offenbar in 11.2 deutlich verändert, so dass Mutex Waits nun ziemlich gut analysierbar und damit auch optimierbar sind. Für das Verhalten vor 11.2 galt dabei Folgendes (die Aufzählungen sind dabei jeweils Zitate aus der Quelle):

• “Cursor: pin S” was pure wait for CPU. Long “cursor: pin S” waits indicated CPU starvation.
•  Mutex contention was almost invisible to Oracle Wait Interface.
•  Spin time to acquire mutex was accounted as CPU time. It was service time, not waiting time.

In 11.2 gilt:

• Oracle 11.2 Wait Interface computes “wait time” as a duration between the first spin and successful mutex acquisition. As a result we observe only one wait in v$system_event.
• Oracle 11.2 mutex wait includes both spinning and waiting.
• the 11.2.0.2 the “cursor: pin S” wait event no more starve CPU. This reduces probability of CPU thrashing due to mutex waits.

Es folgen noch allerlei Informationen zu den (underscore) Parametern, die das Verhalten der Mutexe steuern, aber die Details will ich hier nicht wiederholen 

OWB und ODI

Peter Scott von Rittman Mead Consulting verspricht eine Artikelserie, in der er die Unterschiede zwischen Oracle Warehouse Builder (OWB) und Oracle Data Integrator (ODI) erläutern will und liefert zunächst eine Zusammenfassung der konzeptionellen Gemeinsamkeiten und Unterschiede:

• "Both ODI and OWB have a similar (I am being very simplistic here) three-component design of: a metadata repository, a development environment where the developer defines the processes and data flows and a runtime component that executes the code and flows."
• Speicherung des Repositories:
• OWB: Repository ist vorinstalliert in einer Oracle-DB
• das ODI Repository wird mit Hilfe des Oracle Fusion Middleware’s Repository Creation Utility in einer unterstützten DB angelegt (nicht notwendig Oracle) 
• Standard-Operationen:
• "with OWB the key parts of the IDE are those for the development of MAPPINGS and (optionally) the design of process flows to orchestrate mappings."
• "In the ODI world think INTERFACES for mappings and PACKAGES for process flows. This is simplistic though as ODI also has PROCEDURES (code developed in one of the ODI supported languages) and LOAD PLANS (multiple packages orchestrated to execute in serial or parallel)"
• Umsetzung der Operationen:
• OWB mappings require the developer to include all of the components needed to facilitate the mapping – we connect source columns to target columns through a logic flow of joiners, filters, expressions, aggregates and a whole palette of other activities. Typically, this would generate a single, but large, SQL statement with much use of in-line views." (immer PL/SQL)
• "ODI interfaces are simply about connecting source columns to target columns in a logical relationship (we also create expressions, joins and filters here) and allowing the physical implementation to be supplied by a knowledge module." (kann auch einfach SQL sein)

Meine Erfahrungen mit dem OWB sind sehr beschränkt und mit dem ODI habe ich noch nie ernsthaft gearbeitet, aber aus meiner Sicht sind die gravierendsten Einschränkungen des OWB (abgesehen von seiner fehlenden Zukunft), dass er oft recht suboptimales SQL (in PL/SQL-Verpackung) erzeugt und viele neuere Datenbankfeatures kalt lächeln ignoriert - und das scheint im Fall des ODI dann mit Hilfe des knowledge modules stärker beeinflußbar zu sein. Dass grafische ETL-Tools bei der Definition relativ einfacher Transformationen oft sehr umständliche GUI-Darstellungen erzeugen, ist vermutlich eher ein allgemeines Problem solcher Werkzeuge. Und dass der PL/SQL-Code des OWB in nahezu jedem Fall ausufernd groß wird, ist vermutlich nur für Kommandozeilen-Fetischisten wie mich ein Problem.