Oracle Troubleshooting TV show

Tanel Poder hat eine Fernsehshow zum Thema Shared Pool produziert. Oder, wenn's keine Fernsehshow ist, dann doch zumindest ein extrem erhellendes Video. Normalerweise bin ich kein besonderer Freund von technischen Videos, aber der Herr Poder ist einfach großartig und liefert jede Menge Details aus der Tiefe des Systems (z.B. über die x$ksmsp - was wohl für "kernel service memory shared pool" steht - , eine Tabelle, die Informationen zu den Memory Chunks im Shared Pool liefert; und auf die man in der Produktion lieber nicht zugreifen sollte - eine Sammlung von Details zu den X$-Objekten findet man übrigens hier, wobei ich zur Qualität der Aussagen wenig sagen kann, aber zumindest werden plausible Quellen herangezogen).

ANALYZE mit FAST-Option

Richard Foote führt ein besonders eindrucksvolles Beispiel dafür vor, was passieren kann, wenn Oracle neue FAST-Optionen anbietet - in diesem Fall als Ergänzung zu ANALYZE TABLE VALIDATE STRUCTURE CASCADE:

When using the so-called “FAST” option, the performance was consistently significantly slower, not faster, when compared to using the default method. Perhaps the default option is “FASTER-STILL” ?

Das Ergebnis ließ sich auf meinen Systemen nachvollziehen. Wenn keine Indizes im Spiel sind, scheint FAST aber tatsächlich halbwegs flott zu sein - was dann allerdings vermutlich nutzlos ist...

Framework für Script-Tests

Von Jonathan Lewis lernen, heißt siegen lernen... Diesmal zeigt der Herr Lewis, wie man mit einfachsten Mitteln eine beliebig häufige Wiederholung eines SQL-Statements aufrufen kann.

SSAS Wiki

Wer sich mit den SQL Server Analysis Services (SSAS) beschäftigt, kann eine Menge Zeit damit verbringen, im Netz nach brauchbaren Erläuterungen zu suchen. Jetzt hat sich jemand die Mühe gemacht, die verstreuten Beiträge in einem SSAS-WIKI zu erfassen und zu ordnen. Dem Initiator sei hiermit in aller Form gedankt.

MOATS

Wenn die Herren Poder und Billington zusammen ein Monitoring-Script schreiben und dem Herrn Geist für seine Mitarbeit danken, kann man davon ausgehen, dass das Ergebnis sich sehen lassen kann. Das Ergebnis nennt sich MOATS: The Mother of All Tuning Scripts und liefert in sqlplus eine TOP-artige Sicht auf die aktuelle Aktivität in einer Datenbank. Der Aufruf erfolgt (nach Vergabe von Rechten, Installation und Settings) einfach über ein:

select * from top;

Einfacher geht es kaum.

LastNonEmpty

Chris Webb zeigt in seinem Blog eine recht performante Version einer LastNonEmpty-Operation, mit der man NULL-Werte durch den letzten gegebenen Wert einer Kennzahl überschreiben kann. Das entspricht also in etwa dem Ergebnis der analytischen Funktion LAST_VALUE in Oracle-SQL:

select t.*, last_value(id ignore nulls) over(order by salesdate ) lv_id
  from ...

SALESDATE          ID      LV_ID
---------- ---------- ----------
05.01.2009
12.01.2009
19.01.2009
26.01.2009
09.02.2009
16.02.2009
23.02.2009
02.03.2009
09.03.2009
23.03.2009
30.03.2009    9145280    9145280
06.04.2009               9145280
13.04.2009               9145280
11.05.2009               9145280
01.06.2009               9145280
22.06.2009               9145280
29.06.2009               9145280
06.07.2009               9145280
13.07.2009               9145280
27.07.2009               9145280
10.08.2009               9145280
17.08.2009               9145280
31.08.2009               9145280
14.09.2009               9145280
21.09.2009               9145280
05.10.2009               9145280
12.10.2009               9145280
19.10.2009               9145280
26.10.2009    9445280    9445280
02.11.2009               9445280
09.11.2009               9445280
16.11.2009               9445280
30.11.2009               9445280

Parallel conventional insert in 11g

Laut Randolf Geist (und Greg Rahn - im Kommentarteil) ist Parallel conventional (NOAPPEND) insert ein neues Feature in 11g, das eingeführt wurde, um Fälle zu unterstützen, in denen paralleles Insert as Select sinnvoll ist, aber keine direct path Operation erwünscht ist.

Nested Loops Optimierung in 11g

Coskan Gundogar schreibt in seinem Blog über Veränderungen im Verhalten von Nested Loops Operationen, die sich in 11g ergeben haben und die die Lesbarkeit von NL-Operationen beeinträchtigen: statt einer simplen Verknüpfung der äußeren Tabelle an die innere, deren Sätze dabei via Index eingelesen werden, wird jetzt die äußere Tabelle mit dem Index der inneren Tabelle über NL verknüpft und das Ergebnis dieser Operation wird dann in einem zweiten NL an die innere Tabelle geknüpft. Der Herr Gundogar ist mit dem Ergebnis nicht recht zufrieden, da es sich zwar angeblich um eine Performance-Optimierung handelt, davon in seinen Testergebnissen aber nichts zu sehen ist. Ich frage mich, ob es sich vielleicht nur um eine andere Visualisierung der Operation durch dbms_xplan handeln könnte, da in beiden Fällen die gleiche Arbeit anzufallen scheint.

Nachtrag 06.04.2011: ein Blick in die Doku beantwortet meine abschließende Überlegung negativ:

Oracle Database 11g Release 1 (11.1) introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. In Oracle Database 11g Release 1 (11.1), Oracle Database can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time. As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.

Also offenbar doch eine inhaltliche Änderung. Den Link habe ich übrigens bei Timur Akhmadeev entdeckt, der darauf hinweist, dass diese Implementierungsänderung auch Einfluß auf Sortierungen haben kann.

Nachtrag 21.08.2011: in einem Kommentar zum Blog von Coskan Gundogar weist Randolf Geist darauf hin, dass row source statistics sampling die Verwendung von Vector bzw. Batched I/O deaktiviert - was so ziemlich alle Trace-Operationen betrifft (Gather_Plan_Statistics Hint, STATISTICS_LEVEL=ALL, SQL_Trace). Wenn sie wirksam wird (was über die “Batched IO%” session statistics überprüfbar ist), kann die Optimierung offenbar recht deutliche Verbesserungen bewirken. (Ähnliches schreibt der Herr Geist auch in seinen aktuellen Blog-Artikeln zum Thema "Logical I/O")

Index Costing

Als Erinnerungsstütze: der cbo berechnet die Kosten eines Index Zugriffs als:

cost = blevel 
     + ceiling(leaf_blocks * effective index selectivity)
     + ceiling(clustering_factor * effective table selectivity)  

Anders ausgedrückt: Höhe des Index + Kosten des Index-Zugriffs + Kosten des Tabellenzugriffs. Dabei gilt:

• effective index selectivity: ix_sel im 10053er Trace; basiert nur auf der Selektivität jener Index-Spalten, die für den Zugriff tatsächlich genutzt werden können (nur access Prädikate, verwendete führende Spalten im Index): "which may have to use a subset of the predicates based on the index's leading columns" (Jonathan Lewis, Cost-Based Oracle Kapitel 4, S. 74). Dies repräsentiert die Folge von Leaf Blocks, die in der Index-Struktur gelesen werden.
• effective table selectivity: ix_sel_with_filters im 10053er Trace; Multiplikation der Selektivität der Einzelspalten des Index (access + filter Prädikate): "combines all predicates available on the index's columns" (Jonathan Lewis, Cost-Based Oracle Kapitel 4, S. 74). Das ist die Repräsentation der Tabellenblockzugriffe, die über den Index erfolgen (weitere Filterprädikate schränken das Ergebnis dann ggf. weiter ein, was aber für das Costing nicht relevant ist, da die entsprechenden Blocks zunächst gelesen werden müssen)

Alles nachzulesen bei Jonathan Lewis in Cost-Based Oracle Kapitel 4, S. 62ff.

Extended Statistics

Vor ein paar Tagen hatte ich darüber nachgedacht, mich etwas genauer über das Thema Extended Statistics zu informieren - und schon hat Maria Colgan darüber einen neuen Artikel im cbo Entwickler Blog geschrieben.

Grundsätzlich dienen Extended Statistics dazu, um dem cbo Informationen über die Abhängigkeit von Werten mehrerer Spalten zu geben: da der cbo überlicherweise von vollständiger Unabhängigkeit der Werte ausgeht, kommt er ohne diesen Hinweis zu einer Überschätzung der Selektivität von Einschränkungen. Dier Erfassung der Statistiken erfolgt auf Basis von virtual columns.

Nachtrag 24.03.2011: in einem weiteren Artikel erläutert Frau Colgan auch noch, wie in 11.2 mögliche Kandidaten für Extended Statistics automatisch bestimmt werden können.

Nachtrag 18.11.2011: für die virtuelle Spalte, die die Spaltenkombination der extended statistics repräsentiert, sollten Histogramme erzeugt werden, da sonst weiterhin die Statisiken der Einzelspalten herangezogen werden, wenn für sie Histogramme existieren (oder kurz: Histogramme haben Vorrang vor extended statistics). Auch diese Auskunft findet man in Frau Colgans Artikel.

numwidth

Schon erstaunlich, wie viele Dinge es gibt, die ich über sqlplus nicht weiß - obwohl ich das Tool seit zehn Jahren nahezu täglich nutze. Vor kurzem hatte Eddie Awad ein paar recht interessante show-Optionen erwähnt, aber viel überraschender war für mich, dass sqlplus Nachkommastellen nur bis zu einer bestimmten Anzahl darstellt. Bisher habe ich offenbar nie mehr Stellen als den default-Wert (10) benötigt ...

drop table number_test;

create table number_test (a number (13,12));

insert into number_test values (.99999);
insert into number_test values (.999999);
insert into number_test values (.9999999);
insert into number_test values (.99999999);
insert into number_test values (.999999999);
insert into number_test values (.9999999999);
insert into number_test values (.99999999999);
insert into number_test values (.999999999999);
insert into number_test values (.9999999999999);
insert into number_test values (.99999999999999);
insert into number_test values (.999999999999999);
select length(a), vsize(a), a from number_test;

set numwidth 12

select length(a), vsize(a), a 
  from number_test;

   LENGTH(A)     VSIZE(A)            A
------------ ------------ ------------
           6            4       ,99999
           7            4      ,999999
           8            5     ,9999999
           9            5    ,99999999
          10            6   ,999999999
          11            6  ,9999999999
          12            7 ,99999999999
          13            7            1
           1            2            1
           1            2            1
           1            2            1

11 Zeilen ausgewählt.

set numwidth 8

select length(a), vsize(a), a 
  from number_test;

LENGTH(A) VSIZE(A)        A
--------- -------- --------
        6        4   ,99999
        7        4  ,999999
        8        5 ,9999999
        9        5        1
       10        6        1
       11        6        1
       12        7        1
       13        7        1
        1        2        1
        1        2        1
        1        2        1

11 Zeilen ausgewählt.

Oracle DWH Best Practices

Von Maria Colgan, die ein paar sehr interessante Artikel im Blog der cbo Entwickler geschrieben hat, gibt es ein White-Paper Best Practices for a Data Warehouse on Oracle Database 11g, das eine ganze Reihe wichtiger Basisinformationen zum Thema liefert (von der physikalischen über die logische Struktur bis hin zu Partitionierungsstrategien, Statistikerfassung und Zugriffsanalyse).

Inhalte von OS-Directories über External Table anzeigen - 2

Nachdem ich bei einem Kunden gesehen hatte, wie leicht man die Liste der in einem OS-Verzeichnis vorliegenden Dateien in 11g über External Tables anzeigen kann, und hier einen Link zu Adrian Billingtons Erläuterung des Features unterbrachte, folgt nun ein praktisches Beispiel für 11.2 und Windows 7 (basierend auf den Ausführungen des Herrn Billington):

Zunächst benötigt man ein Directory-Objekt, das auf ein OS-Verzeichnis verweist:

create directory data_dir as 'c:\temp';

In diesem Verzeichnis legt man nun eine Batch-Datei an - hier directory_list.bat - , die den Befehl zur Anzeige der Verzeichnis-Inhalte enthält:

@echo off
dir /N c:\temp

In der Datenbank kann man nun eine External Table anlegen, die die Batch-Datei über einen PREPROCESSOR-Befehl ausführt:

CREATE TABLE directory_list
( file_date VARCHAR2(50)
, file_time VARCHAR2(50)
, file_size VARCHAR2(50)
, file_name VARCHAR2(255)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
  (
     RECORDS DELIMITED BY NEWLINE
     LOAD WHEN file_size != '
'
     PREPROCESSOR data_dir: 'directory_list.bat'
     FIELDS TERMINATED BY WHITESPACE
  )
  LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED;

Da die External Table neben den Datei-Informationen auch noch Header- und Footer-Elemente enthält, ist es sinnvoll, diese Anteile mit Hilfe einer View zu filtern:

CREATE VIEW dir_list
AS
SELECT file_name
     , to_char(TO_DATE(file_date||','||file_time,'DD/MM/YYYY HH24:MI'), 'dd.mm.yyyy hh24:mi:ss') AS file_time
     , TO_NUMBER(file_size,
             'fm999,999,999,999') AS file_size
  FROM directory_list
 WHERE REGEXP_LIKE( file_date, '[0-9]{2}.[0-9]{2}.[0-9]{4}');

Die erzeugte View enthält dann die Informationen des DIR-Kommandos:

FILE_NAME                                FILE_TIME            FILE_SIZE
---------------------------------------- ------------------- ----------
directory_list.bat                       01.03.2011 20:28:00         25
DIRECTORY_LIST_3192_3532.bad             13.03.2011 15:10:00         62
DIRECTORY_LIST_3192_3532.dsc             13.03.2011 15:10:00        149
DIRECTORY_LIST_3192_3532.log             13.03.2011 15:10:00       3152
DIRECTORY_LIST_3932_3424.bad             01.03.2011 20:31:00         62
DIRECTORY_LIST_3932_3424.dsc             01.03.2011 20:31:00        149
DIRECTORY_LIST_3932_3424.log             01.03.2011 20:31:00      14380
EXT_UPTIME_3416_2432.bad                 16.02.2011 14:32:00         62
EXT_UPTIME_3416_2432.log                 16.02.2011 14:32:00       6433
EXT_UPTIME_412_1316.bad                  15.02.2011 20:55:00         65
EXT_UPTIME_412_1316.log                  15.02.2011 20:55:00        919
EXT_UPTIME_412_2632.bad                  15.02.2011 21:23:00         65
EXT_UPTIME_412_2632.log                  15.02.2011 21:23:00      22975
test.txt                                 01.03.2011 20:16:00          0
uptime.csv                               16.02.2011 14:11:00      30258
uptime_.csv                              15.02.2011 20:41:00      54541

Nützlich ist eine solche Möglichkeit z.B. dann, wenn man keinen direkten Zugriff auf den Serverrechner besitzt.

Planüberschreibung mit DBMS_SPM

Im Blog der cbo-Entwickler zeigt Maria Colgan, wie man ein mit irreführenden Hints versehenes SQL-Statement einer 3rd Party Applikation auf einen Plan ohne Hints umleiten kann. Basis des Verfahrens sind das SQL Plan Management (SPM) und das zugehörige Package DBMS_SPM.

Result Caching

Einmal mehr hat Rob van Wijk einen interessanten Blog-Eintrag zu einem Thema geschrieben, mit dem ich mich noch nicht ernsthaft beschäftigt habe: nämlich zum result cache in 11g. Wahrscheinlich wäre die dort verlinkte Präsentation sogar noch interessanter.

Inhalte von OS-Directories über External Table anzeigen

Adrian Billington erläutert hier, wie man die Inhalte eines Betriebssystem-Verzeichnisses via External Table anzeigen lassen kann. Voraussetzung für das Verfahren ist Oracle 11, da man den preprocessor dieser Version benötigt.

Set Role

Nur als kurze Notiz: gestern habe ich mir für einen Account die SELECT_CATALOG_ROLE geben lassen und war dann leicht verwundert, dass ich trotzdem nicht auf v$- und data dictionary Views zugreifen konnte. Noch mehr überraschte mich, dass ich die Views in all_objects sogar sehen konnte - nur eben nicht abfragen. Eine kurze Google-Recherche ergab dann, dass die Rolle offenbar nicht als default-Rolle definiert war und deshalb in der Session explizit aktiviert werden musste (der Hinweis fand sich in einem Foren-Beitrag von Joel Garry); vermutlich habe ich das irgendwann mal gewusst, aber wieder komplett vergessen. Das Vorgehen zur Aktivierung ist:

-- Prüfung, ob die Rollen aktiviert sind
select *
  from session_roles;
--> lieferte kein Ergebnis

set role none;
--> Deaktivierung aller Rollen: zu testen wäre noch, ob das tatsächlich nötig ist

set role all;
--> Aktivierung aller Rollen; hier könnte man auch einzelne Rollen aktivieren
--   (während die Deaktivierung nur für alle Rollen durchführbar ist)

Die Oracle-Doku erläutert die zugrunde liegende Idee dann in aller wünschenswerten Klarheit:

When a user logs on to Oracle Database, the database enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to enable or disable the roles currently enabled for the session.

Weitere Details liefert wie immer auch die PSOUG-Referenz.

Was mich allerdings wundert, ist, dass die Rolle nach der Zuweisung nicht automatisch als default betrachtet wurde. Das wäre gelegentlich noch zu überprüfen.