MIN/MAX Abfragen und Index-Verwendung

Dieser Tage hat Richard Foote in seinem Blog eine Quizfrage zur Abfrage der minimalen und maximalen Werte einer indizierten Spalte gestellt und inzwischen dazu auf eine ausführliche Antwort geliefert. Kurz zusammengefasst geht es darum, dass eine Query:

select min(col_ind)
     , max(col_ind)
  from ...

den bestehenden Index nur zum INDEX FAST FULL SCAN verwenden kann, während der wünschenswerte INDEX FULL SCAN (MIN/MAX) nur nutzbar ist, wenn man daraus zwei Zugriffe macht, was auf verschiedenen Wegen möglich ist.

Herr Foote und die Kommentatoren sprechen eher über B*Tree-Indizes, aber das Verhalten von Bitmap-Indizes ist identisch.

External Tables und ORA-29913

Bei der Analyse von Zugriffsproblemen auf External Tables bzw. die darunter liegenden Directories und enthaltene Dateien habe ich bei Jonathan Lewis einen sehr nützlichen Artikel zu den Oracle-Fehlern ORA-29913 und ORA-29400 gefunden.

Tanel Poder über Full Scans und Direct Path Reads

Wieder ein Video in Spielfilmlänge von Tanel Poder. Details dazu vielleicht, wenn ich es komplett gesehen habe ...

Nach Betrachtung der ersten 45 min des Videos ein paar erinnerungswürdige Punkte:

• Der Segment Header Block enthält Pointer auf die Extents des Segments.
• In Oracle 7 war die Anzahl der Extents eines Segments begrenzt, da abhängig von der Blockgröße nur eine bestimmte Anzahl von Pointern untergebracht werden konnte. Aus diesem Grund musste man Segmente von Zeit zu Zeit reorganisieren, um die Anzahl der Extents zu reduzieren.
• Seit Oracle 8 gibt es zusätzliche extent map blocks, auf die im Segment Header verwiesen wird.
• In dictionary managed tablespaces wurde die Verwendung von Extents noch explizit in Tabellen des data dictionary gespeichert (SYS.UET$: used extents; SYS.FET$: free extents). In aktuellen Releases (ohne dictionary managed tablespaces) sind diese Tabellen leer.
• Ein Block Dump des Segment Header Blocks liefert weitere Details:
• Der Dump enthält Informationen zur HWM, vor allem die Angabe #blocks below.
• diese Angabe ist die Grundlage für die Entscheidung, ob die Tabelle (bzw. eigentlich das Segment) als long betrachtet wird, worauf wiederum die Entscheidung beruht, ob ein FTS über den Buffer Cache oder über direct path durchgeführt wird.
• Im Header Block folgt dann die Extent Map mit den Hex-Angaben der Blockadressen (die man mit Tanel Poders Script "DBA" auflösen kann).
• Dann folgt die Auxillary Map mit Verweisen auf die zusätzlichen Map Blocks.
• Für jeden Segment-Zugriff wird zunächst ein sequential read durchgeführt, um den Segment Header Block zu lesen (sofern dieser nicht bereits im Buffer Cache vorliegt; deshalb wurde in der Demo zunächst ein Flush des Buffer Caches durchgeführt).
• Die Idee der direct path FTS ist, den sinnvoll gefüllten Buffer Cache nicht durch massive Lese-Operationen für große FTS durcheinander zu bringen.
• im 10046er Trace folgt auf den Header-Zugriff ein Event async descriptor resize über das Tanel Poder schon mal geschreiben hat: "This KCBL module [= direct path loader] tries to dynamically scale up the number of asynch IO descriptors (AIO descriptors are the OS kernel structures, which keep track of asynch IO requests) to match the number of direct path IO slots a process uses. In other words, if the PGA workarea and/or spilled-over hash area in temp tablespace gets larger, Oracle also scales up the number of direct IO slots. Direct IO slots are PGA memory structures helping to do direct IO between files and PGA."
• die Entscheidung für die Verwendung von direct path FTS (oder IFFS) trifft nicht der CBO, sondern die run time engine und die Entscheidung wird für jedes Segment einzeln getroffen (also können die Partitionen eines partitionierten Objekts unterschiedlich behandelt werden).

Teil 2 - bis zur 90. Minute:

• SYS.SEG$ ist die Tabelle, auf der DBA_SEGMENTS basiert.
• Der Root-Block eines Index-Segments ist immer der erste Block nach dem Header.
• In SEG$ finden sich die Positionen aller Segmente - aber wie wird SEG$ lokalisiert?
• Dazu gibt es eine Tabelle SYS.BOOTSTRAP$, die DDL-Informationen zu diversen internen Tabellen enthält (wobei diese DDL nicht ausgeführt wird). In diesen DDL-Informationen sind File- und Blockangaben enthalten:

select *
  from sys.bootstrap$
 order by 1

LINE#       OBJ# SQL_TEXT
----- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   -1         -1 8.0.0.0.0
    0          0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
    2          2 CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144)) SIZE 800
    3          3 CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 168))
    4          4 CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#" NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"PCTFREE$"
    5          5 CREATE TABLE CLU$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTFREE$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"I
    6          6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 176))
    7          7 CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 7 EXTENTS (FILE 1 BLOCK 184))
    8          8 CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 24K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
    9          9 CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLOCK# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 9 EXTENTS (FILE 1 BLOCK 200))
   10         10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 10 EXTENTS (FILE 1 BLOCK 208)) SIZE
   11         11 CREATE INDEX I_USER# ON CLUSTER C_USER# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 11 EXTENTS (FILE 1 BLOCK 216))
   12         12 CREATE TABLE FET$("TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE (  OBJNO 12 TABNO 1) CLUSTER C_TS#(TS#)
   13         13 CREATE TABLE UET$("SEGFILE#" NUMBER NOT NULL,"SEGBLOCK#" NUMBER NOT NULL,"EXT#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE (  OBJNO
   14         14 CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NO
   15         15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,
   16         16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" NUMBER,"BLOCKSIZE" NUMBER NOT
   17         17 CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS$" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"TS#" NUMBER,"RELFILE#" NUMBER,"MAXEXTEND" NUMBER,"INC" NUMBER,"CRSCNWRP" NUMBER,"CRSCNBAS" NUMBER,"OWNERINSTANCE"
   18         18 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL
   19         19 CREATE TABLE IND$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"INDMETHOD#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTF
   20         20 CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#" NUMBER
   21         21 CREATE TABLE COL$("OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"LENGTH" N
   22         22 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"PTIME" DATE,"E
   23         23 CREATE TABLE PROXY_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"CREDENTIAL_TYPE#" NUMBER NOT NULL,"CREDENTIAL_VERSION#" NUMBER NOT NULL,"CREDENTIAL_MINOR#" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL)
   24         24 CREATE UNIQUE INDEX I_PROXY_DATA$ ON PROXY_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 24 EXTENTS (FILE 1 B
   25         25 CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"ROLE#" NUMBER NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
   26         26 CREATE INDEX I_PROXY_ROLE_DATA$_1 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 26 EXTENTS (FIL
   27         27 CREATE UNIQUE INDEX I_PROXY_ROLE_DATA$_2 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#,ROLE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 27
   28         28 CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCT
   29         29 CREATE CLUSTER C_COBJ#("OBJ#" NUMBER) PCTFREE 0 PCTUSED 50 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 29 EXTENTS (FILE 1 BLOCK 296)) SIZE 3
   30         30 CREATE INDEX I_COBJ# ON CLUSTER C_COBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 30 EXTENTS (FILE 1 BLOCK 304))
   31         31 CREATE TABLE CDEF$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COLS" NUMBER,"TYPE#" NUMBER NOT NULL,"ROBJ#" NUMBER,"RCON#" NUMBER,"RRULES" VARCHAR2(3),"MATCH#" NUMBER,"REFACT" NUMBER,"ENABLED" NUMBER,"CONDL
   32         32 CREATE TABLE CCOL$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER,"INTCOL#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5"
   33         33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))
   34         34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
   35         35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328))
   36         36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
   37         37 CREATE UNIQUE INDEX I_OBJ2 ON OBJ$(OWNER#,NAME,NAMESPACE,REMOTEOWNER,LINKNAME,SUBNAME,TYPE#,SPARE3,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 16K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 P
   38         38 CREATE INDEX I_OBJ3 ON OBJ$(OID$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 38 EXTENTS (FILE 1 BLOCK 352))
   39         39 CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK 360))
   40         40 CREATE UNIQUE INDEX I_OBJ5 ON OBJ$(SPARE3,NAME,NAMESPACE,TYPE#,OWNER#,REMOTEOWNER,LINKNAME,SUBNAME,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
   41         41 CREATE UNIQUE INDEX I_IND1 ON IND$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 41 EXTENTS (FILE 1 BLOCK 376))
   42         42 CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 42 EXTENTS (FILE 1 BLOCK 384))
   43         43 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 43 EXTENTS (FILE 1 BLOCK 392))
   44         44 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 44 EXTENTS (FILE 1 BLOCK 400))
   45         45 CREATE UNIQUE INDEX I_TS1 ON TS$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 45 EXTENTS (FILE 1 BLOCK 408))
   46         46 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (FILE 1 BLOCK 416))
   47         47 CREATE UNIQUE INDEX I_USER2 ON USER$(USER#,TYPE#,SPARE1,SPARE2) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 47 EXTENTS (FILE 1 BL
   48         48 CREATE UNIQUE INDEX I_COL1 ON COL$(OBJ#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 48 EXTENTS (FILE 1 BLOCK 432))
   49         49 CREATE INDEX I_COL2 ON COL$(OBJ#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 49 EXTENTS (FILE 1 BLOCK 440))
   50         50 CREATE UNIQUE INDEX I_COL3 ON COL$(OBJ#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 50 EXTENTS (FILE 1 BLOCK 448))
   51         51 CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 51 EXTENTS (FILE 1 BLOCK 456))
   52         52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
   53         53 CREATE UNIQUE INDEX I_CDEF1 ON CDEF$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 53 EXTENTS (FILE 1 BLOCK 472))
   54         54 CREATE INDEX I_CDEF2 ON CDEF$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 54 EXTENTS (FILE 1 BLOCK 480))
   55         55 CREATE INDEX I_CDEF3 ON CDEF$(ROBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 55 EXTENTS (FILE 1 BLOCK 488))
   56         56 CREATE INDEX I_CDEF4 ON CDEF$(ENABLED) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 56 EXTENTS (FILE 1 BLOCK 496))
   57         57 CREATE INDEX I_CCOL1 ON CCOL$(CON#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 57 EXTENTS (FILE 1 BLOCK 504))
   58         58 CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 58 EXTENTS (FILE 1 BLOCK 512))
   59         59 CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENT

• SYS.BOOTSTRAP$ wiederum wird durch den Header Block von File #1 lokalisiert (den man über das event file_hdrs dumpen kann; eine Liste der vorhandenen Dump-Events liefert dabei oradebug dumplist)
• Die data_object_id identifiziert ein Segment, während die object_id das zugehörige logische Objekt angibt (und z.B. bei der Ermittlung von dependencies verwendet wird)
• die data_object_id ist für die Tabellen in einem Cluster identisch.
• sie ändert sich nach einem Neuaufbau (Index rebuild; Table move)
• auch ein truncate table ändert die data_object_id, da dadurch der segment header neu formatiert wird.
• Mit Hilfe eines refcursors kann man auch nach der Löschung einer Tabelle aus den Blocks Daten lesen! Das gilt selbst dann, wenn die Tabelle mit purge Option gelöscht wurde - sofern die Blocks nicht durch ein neues Segment überschrieben werden. Ursache dafür ist, dass das drop table nur eine Änderung im Data Dictionary bewirkt. Allerdings funktioniert der refcursor-Zugriff nur so lange bis der nächste Zugriff auf den Segment Header erforderlich wird.
• Ein Zugriff auf den refcursor nach einem truncate table liefert den Fehler "ORA-08103: object no longer exists", da das truncate den Header neu formatiert (auch wenn man die Option reuse storage verwendet)
• Grundlage der Entscheidung, ob ein FTS über den Buffer Cache oder über DIRECT PATH READ erfolgt ist die _SMALL_TABLE_TRESHOLD
• in 11.1 gilt, dass für ein Objekt, das > 5 * _SMALL_TABLE_TRESHOLD Blocks umfasst, der FTS über DIRECT PATH erfolgt, damit der Buffer Cache nicht mit nutzlosen Daten gefüllt wird; dabei ist die _SMALL_TABLE_TRESHOLD als 2% des Buffer Pool definiert.
• für folgende Releases wird zusätzlich geprüft, wie viele Blocks des betroffenen Segments bereits im Cache vorliegen (Mit Hilfe von X$KCBOQH) und dieser Wert modifiziert dann die Threshold-Arithmetik (wenn das Objekt sowieso schon teilweise im Cache vorliegt, ist der DIRECT PATH Zugriff weniger interessant)

Composite Partitionierung mit mehreren Ebenen

Dieser Tage beschäftige ich mich mit der Frage, wie man eine große Menge von Transaktionsdaten (ca. 5.000.000.000 Sätze) nach möglichst vielen Kriterien partitionieren kann. Nun habe ich in der Vergangenheit häufiger mit composite partitioning gearbeitet, um solche Daten zunächst zeitlich zu partitionieren und darunter dann nach Regionen oder Häusern zu subpartitionieren. Diesmal würde ich aber gerne noch eine dritte Ebene verwenden, nämlich die der Kunden - wobei in diesem Fall nur eine ganz grobe Zuordnung nach Transaktionen mit bzw. Transaktionen ohne Kundennummer erforderlich (und sinnvoll) wäre. Für meinen Test (in Version 11.2.0.1) verwende ich eine ganz einfache Tabelle mit drei Spalten (StoreId, CustomerId, SalesDate), die alle ihre Rolle bei der Partitionierung spielen sollen. Meine erste Wahl wäre eine Range-List-Partitionierung, da die Anzahl der gegebenen Stores nicht sehr groß ist und in einem Subpartition Template untergebracht werden kann:

Versuch 1: Range-List-Partitionierung mit zwei Spalten im Subpartition-Key

create table test_part_range_list
( storeid number
, salesdate date
, customerid number
, customerflag number generated always as (case when customerid = 0 then 0 else 1 end ))
   partition by range (salesdate)
   subpartition by list (storeid, customerflag)
   subpartition template
      (subpartition sp1_cust values (1, 1)
      , subpartition sp2_cust values (2, 1)
      , subpartition sp1_nocust values (1, 0)
      , subpartition sp2_nocust values (2, 0)
      , subpartition sp_others values (default )
      )
  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))
  )
;

   subpartition template
   *
FEHLER in Zeile 8:
ORA-14304: List-Partitionierungsmethode erwartet eine einzelne Partitionierungsspalte

Und schon zeigt sich, dass es bisweilen ratsam ist, die Dokumentation etwas genauer anzuschauen: List-Partitionierung funktioniert offenbar nur mit einer einzelnen Schlüsselspalte, was diesen Versuch beendet...

Versuch 2: Range-List-Partitionierung mit einer virtuellen Spalte im Subpartition-Key

Im ersten Versuch hatte ich bereits eine virtuelle Spalte verwendet, um die Fallunterscheidung zwischen Transaktion mit Kundennummer und Transaktion ohne Kundennummer unterzubringen - ohne dafür eine "echte" Spalte definieren zu müssen, die die Breite der Sätze erhöhen würde. Virtual Column-Based Partitioning ist ein neues Feature in Version 11, scheint aber problemlos zu funktionieren.

create table test_part_range_list
( storeid number
, salesdate date
, customerid number
, subpartcode number generated always as (case when customerid = 0 then storeid * 100 + 1 else storeid * 100 end ))
   partition by range (salesdate)
   subpartition by list (subpartcode)
   subpartition template
      (subpartition st1_nocust values (100)
      , subpartition st1_cust values (101)
      , subpartition st2_nocust values (200)
      , subpartition st2_cust values (201)
      , subpartition sp_others values (default )
      )
  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))
  );

-- Testdaten
insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.12.2010', 4711);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.12.2010', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.01.2011', 4711);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.01.2011', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.12.2010', 1234);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.12.2010', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.01.2011', 1234);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.01.2011', 0);  

-- Statistikerhebung auf Subpartitionsebene
exec dbms_stats.gather_table_stats(user, 'TEST_PART_RANGE_LIST', Granularity => 'SUBPARTITION')

-- Verteilung der Sätze auf die Subpartitionen
select partition_name
     , subpartition_name
     , num_rows 
  from dba_tab_subpartitions 
 where table_name = 'TEST_PART_RANGE_LIST';  

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             P1_ST1_NOCUST                           1
P1                             P1_ST1_CUST                             1
P1                             P1_ST2_NOCUST                           1
P1                             P1_ST2_CUST                             1
P1                             P1_SP_OTHERS                            0
P2                             P2_ST1_NOCUST                           1
P2                             P2_ST1_CUST                             1
P2                             P2_ST2_NOCUST                           1
P2                             P2_ST2_CUST                             1
P2                             P2_SP_OTHERS                            0

Die Partitionierung führt also zunächst zur gewünschten Verteilung der Daten auf die Partitionen (wobei die Default-Sub-Partitionen leer bleiben; in ihnen landen alle Sätze, für die im Subpartition Template keine eigene (Sub-)Partition definiert ist. Nun aber zur Frage, ob diese Partitionierung sinnvolles Partition Pruning erlaubt - also den Ausschluss von Partitionen, die keine relevanten Daten enthalten, im Rahmen der Abarbeitung entsprechender Queries. Dazu liefert Autotrace folgende Ergebnisse:

select *
  from test_part_range_list

   STOREID SALESDAT CUSTOMERID SUBPARTCODE
---------- -------- ---------- -----------
         1 31.12.10       4711         100
         1 31.12.10          0         101
         2 31.12.10       1234         200
         2 31.12.10          0         201
         1 31.01.11       4711         100
         1 31.01.11          0         101
         2 31.01.11       1234         200
         2 31.01.11          0         201

8 Zeilen ausgewõhlt.

Abgelaufen: 00:00:00.04

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1185884213

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     8 |   136 |    21   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     5 |
|   3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     8 |   136 |    21   (0)| 00:00:01 |     1 |    10 |
------------------------------------------------------------------------------------------------------------


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
        917  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

Diese 62 consistent gets sind also der Referenzwert für das Lesen aller Partitionen. Die folgenden Autotrace-Listings sind aus Gründen der Übersichtlichkeit massiv gekürzt:

-- Datumseinschränkung
select *
  from test_part_range_list
 where salesdate = '31.12.2010';

------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows | Pstart| Pstop |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |     4|       |       |
|   1 |  PARTITION RANGE SINGLE|                      |     4|     1 |     1 |
|   2 |   PARTITION LIST ALL   |                      |     4|     1 |     5 |
|*  3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     4|     1 |     5 |
-------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         33  consistent gets
          4  rows processed

--> partition pruning funktioniert (was auf dieser Ebene keine Überraschung ist)

-- Einschränkung auf StoreId
select *
  from test_part_range_list
 where storeid = 1;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     4 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     4 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     4 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     4 |     1 |    10 |
----------------------------------------------------------------------------		  

Statistiken
----------------------------------------------------------
         62  consistent gets
          4  rows processed	  

--> kein Pruning: PARTITION RANGE ALL und PARTITION LIST ALL
--> offenbar kann der Wert der virtuellen Spalte subpartcode nicht aus
    der StoreId abgeleitet werden

-- Einschränkung auf CustomerId
select *
  from test_part_range_list
 where customerid = 4711;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     3 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     3 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     3 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     3 |     1 |    10 |
----------------------------------------------------------------------------		  

Statistiken
----------------------------------------------------------
         62  consistent gets
          2  rows processed

--> gleiches Verhalten wie bei der StoreId, was wiederum konsequent ist

-- Kombinierte Bedingung mit StoreId und CustomerId
select *
  from test_part_range_list
 where storeid = 1
   and customerid = 4711;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     1 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     1 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     1 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     1 |     1 |    10 |
----------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         62  consistent gets
          2  rows processed		  

--> auch die Kombination der beiden Basiswerte, aus denen sich die virtuelle
    Partitionsschlüsselspalte definiert, führt nicht zum Partition Pruning

-- Bedinung mit der virtuellen Subpartitionsspalte
select *
  from test_part_range_list
 where subpartcode = 201;

-------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |     2 |       |       |
|   1 |  PARTITION RANGE ALL   |                      |     2 |     1 |     2 |
|   2 |   PARTITION LIST SINGLE|                      |     2 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     2 |   KEY |   KEY |
-------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         16  consistent gets
          2  rows processed

--> mit dem virtuellen subpartcode funktioniert das Pruning

Demnach sorgt die virtuelle Spalte "subpartcode" zwar für eine sinnvolle Verteilung der Daten auf die Subpartitionen, aber das Partition Pruning funktioniert auf Subpartitionsebene nur, wenn dieses Attribut auch als Bedingung in den zugehörigen Queries erscheint. Damit ist aber auch dieser zweite Fall nur noch von akademischem Interesse, da die Queries, die auf den Daten operieren sollen, nicht notwenig beide Einschränkungen enthalten - und schon gar nicht die virtuelle Zusatzspalte verwenden.

Da der Eintrag jetzt schon recht unübersichtlich geworden ist, verschiebe ich weitere Untersuchungen (Hash-Subpartitioning, Range-Partitioning mit mehreren Spalten) auf ein andermal.

PL/SQL Mythen

Morten Braten räumt in seinem Blog mit allerlei falschen Vorstellungen auf, die über den Einsatz von PL/SQL existieren.

gather_plan_statistics

Dass die Erfassung von Ausführungsstatistiken mit Hilfe des gather_plan_statistics Hints (oder Setzung von statistics_level=all) einen relativ großen Einfluß auf die Performance von Queries besitzt, hatte ich schon häufiger festgestellt. In Jonathan Lewis' Blog findet man dazu eine Erklärung: verantwortlich ist offenbar vor allem die timer Funktion des verwendeten Betriebssystems.

Was man mit den Statistiken anfangen kann, erläutert Maria Colgan dieser Tage - und ihrem Artikel verdanke ich auch den Link auf den Scratchpad-Artikel.

Nachtrag 27.12.2012: Eine umfassendere Analyse des Overheads liefert Alexander Anokhin, dessen Ausführungen ich hier exzerpiert habe.

Logical I/O Optimization

Noch mal ein Link auf Randolf Geists Blog: Logical I/O Evolution - Part 3: 11g. Zum Artikel lässt sich sagen, dass:

• er ziemlich lang ist, was dem Autor auch aufgefallen zu sein scheint ...
• er eine umfassende Untersuchung der in 11g verfügbaren Optimierungen enthält.
• man in 11g die Gestalt eines Nested Loops über die Parameter [NO_]NLJ_BATCHING und [NO_]NLJ_PREFETCH steuern kann. Ein paar Links zum Nested Loop Join Batching hatte ich vor einigen Monaten hier gesammelt.
  
• die LIO-Optimierungen in 11g auch ohne Table Prefetching und Nested Loop Join Batching verfügbar sind (und ohne fastpath consistent gets).
• die Optimierung stark von der physikalischen Sortierung der Daten abhängt.
• das massive Pinning der Blöcke in der optimierten Version anscheinend keine deutlich negative Wirkung auf die Concurrency hat.

Gelegentlich sollte ich den Artikel noch mal lesen, um die Details halbwegs zu fassen ...

Optimizer Trace

Greg Rahn erläutert einige neue Varianten zur Erstellung von Optimizer Traces in 11g, denn dort gibt es neben Event 10053 noch ein paar andere Möglichkeiten.

DBMS_XPLAN in RAC-Datenbanken

Dieser Tage habe ich in Jonathan Lewis' Blog eine Frage nach der Verwendbarkeit von dbms_xplan.display_cursor in RAC-Systemen gestellt, aber zunächst nur einen kurzen Kommentar von Dom Brooks dazu bekommen. Da sich im Thread neben dem Herrn Lewis auch noch die Herren Geist und Rahn aufgehalten haben, hoffe ich darauf, dass da vielleicht noch weitere Bemerkungen folgen. Für's erste aber noch mal meine Beobachtungen:

Beim Versuch, in einer RAC-Datenbank den Zugriffsplan für eine über gv$sql ermittelte sql_id mit Hilfe von dbms_xplan.display_cursor anzeigen zu lassen, bekomme ich kein Ergebnis:

select *
  from table(dbms_xplan.display_cursor(sql_id => '13x0qwkc4xrfv', cursor_child_no=>1));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID: 13x0qwkc4xrfv, child number: 1 cannot be found

In v$sql_plan sehe ich auch keine zugehörigen Einträge, aber in gv$sql_plan finden sich die gesuchten Informationen:

select count(*)
  from v$sql_plan
 where sql_id = '13x0qwkc4xrfv'
   and child_number = 1;
 
  COUNT(*)
----------
         0
 
select count(*)
  from gv$sql_plan
 where sql_id = '13x0qwkc4xrfv'
   and child_number = 1;
 
  COUNT(*)
----------
         4

Offenbar greift dbms_xplan.display_cursor nicht auf die gv$-Views zu: in einer anderen Datenbank (kein RAC), sehe ich über Event 10046 nur rekursive Zugriffe auf v$-Views:

select /* EXEC_FROM_DBMS_XPLAN */ case when upper(sql_text) like
  '%DBMS_XPLAN%' then 0 else 1 end case, SQL_ID, child_number
from
 v$sql where SQL_ID ='8szmwam7fysa3' and child_number =0
 
...
 
Parsing user id: 68     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=536 card=1)

Das entspricht auch den Angaben der Doku: "The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL". Im Widerspruch dazu scheint mir der entsprechende Eintrag in Morgan's Library zu sein: "Display from GV$SQL_PLAN (or GV$SQL_PLAN_STATISTICS_ALL)."

Meiner Meinung nach wäre es günstig, wenn dbms_xplan.display_cursor einen NODE-Parameter beinhalten würde, da die Plan-Informationen über gv$_sql_plan ja offenbar verfügbar wären.

Nachtrag 31.08.2011: in einem Kommentar im Sratchpad wurde ein funktionierender Workaround vorgeschlagen, der die Anzeige der Pläne von anderen Knoten ermöglicht:

select * 
  from table(dbms_xplan.display('gv$sql_plan_statistics_all'
                               , null
                               , null
                               , 'inst_id = &inst_id and sql_id = ''&sql_id'' and child_number = &child_number')
             );

LWGR und Commit

Jonathan Lewis beschreibt in seinem Blog ein recht seltsames (und potentiell gefährliches) Verhalten des Oracle Servers: wenn eine Transaktion ein Commit durchführt und die Information im Log Buffer landet, dann wird sie für andere Sessions sichtbar - auch dann, wenn der LWGR nicht mehr dazu in der Lage ist, die Commit-Informationen in die Redo Log Dateien zu schreiben. Nach einem Neustart wird diese Transaktion aber nicht als commited betrachtet, da eben die persistierten Log-Informationen fehlen.

Dieses Verhalten widerspricht allem, was ich bisher zum Thema gelesen hatte, denn ich ging davon aus, dass die Festschreibung in den redo Logs zum erfolgreichen Commit gehört. Da der Herr Lewis den Sachverhalt aber in mehreren Releases ab 8.1 reproduzieren konnte, ist das offenbar schon seit langer Zeit so gewesen.

Nachtrag 26.08.2011: zu Jonathan Lewis' Artikel gibt's inzwischen 69 Kommentare und ein umfangreiches Update und Tony Hasler, der das Problem als erster beschrieben hatte, hat noch zwei Artikel zur Frage beigesteuert, was dieses Verhalten für Oracles Verhältnis zu ACID bedeutet.

SSAS und Subquery Factoring

Gestern ist einem meiner Kollegen folgendes Problem begegnet: eine Query mit exzessivem Subquery Factoring (also vielen Abfrageblöcken im WITH) lief problemlos und ließ sich auch problemlos in einer View verwenden. Nach Einbindung in die DataSourceView eines SSAS-Projekts brachte das Processing eines auf der View basierenden Objekts dann aber die folgende Fehlermeldung (bzw. die deutschsprachige Version dazu):

ORA-32036: unsupported case for inlining of query name in WITH clause

Über Google landeten wir dann schnell bei Dom Brooks, der das Thema gelegentlich in seinem Blog beleuchtet hat: "According to a couple of metalink articles, the feature/bug/issue/problem is that SQL*Plus, Toad and SQLDeveloper are not reporting the error, rather than the correct behaviour as per ODBC or any other driver raising the error." Dort findet man dann neben den Links auf MOS in einem der Kommentare auch noch einen Workaround, mit dem wir vermeiden konnten, die (halbwegs) übersichtliche View noch einmal komplett neu zu definieren: nämlich die Verwendung des Parameters DistribTx=0 im Connection String der DataSource, mit dem man die Verwendung von verteilten Transaktionen deaktiviert - wobei ich nicht behaupten will, den inneren Zusammenhang von Subquery Factoring und Distributed Transactions im Detail verstanden zu haben ...

Grundsätzlich scheint es doch noch ein paar Fälle zu geben, in denen Subquery Factoring nicht ganz so stabil funktioniert wie entsprechende Inline Views. Vermutlich ist das ein ähnlicher Fall wie der der ANSI-Join-Syntax, die bei Oracle ja auch noch nicht ganz so solide ist wie die traditionelle Schreibweise, obwohl auch sie die Übersichtlichkeit von SQL-Code erhöhen kann.

SQL Monitoring und Plan-Längen

Doug Burns weist darauf hin, dass es ein Limit für die maximale Länge von Plänen gibt, die über dbms_sqltune.report_sql_monitor dargestellt werden können. Das Limit lässt sich über den Parameter _sqlmon_max_planlines steuern (wobei die üblichen Warnungen für das Spielen mit undokumentierten Parametern gelten).

Nachtrag 19.08.2011: offenbar gibt es einen weiteren Parameter _sqlmon_max_plans, der die Anzahl von Plänen beschränkt, die für den SQL Monitor verfügbar sind.

Neue Features im SQL Server Denali

Und noch mal zu Denali: Dan English führt in seinem Blog 10 Gründe für das Upgrade auf die neue SQL Server Version auf. Auf Position 1 erscheint dabei die neue Reporting-Komponente Crescent und auf Platz 2 die Integration der Vertipaq Engine aus PowerPivot in den SSAS. Dort wird er als tablular model neben das traditionelle multidimensional model gestellt. Über die Performance der neuen Engine hat Teo Latchev mehrere Artikel geschrieben und kommt dabei zum Ergebnis, dass sie nicht notwendigerweise performanter ist als das herkömmliche Verfahren:

• Cube vs. VertiPaq Query Performance
• Transactional Reporting with BISM Tabular

LEAD und LAG in SQL Server Denali

Im neuen SQL Server Release Denali gibt es ab CPT 3 - endlich - die analytischen Funktionen LEAD und LAG, die man in Oracle seit etwa 10 Jahren verwenden kann. Ein paar Worte zum Thema finden sich in Jamie Thomsons Blog.

Literale und BINARY_DOUBLE

Thomas Uhren weist in seinem Blog auf unerfreuliche Konvertierungen hin, die sich ergeben, wenn man Literale bei Verwendung des Datentyps BINARY_DOUBLE nicht explizit kennzeichnet.

Der Herr Uhren hat da in den letzten Monaten schon eine ganze Reihe interessanter Artikel untergebracht (mit deutlich mathematischem Einschlag).

Ein paar ergänzende Informationen zu den BINARY_%-Datentypen in 10g und höher findet man in Carsten Czarskis Blog, der dieser Tage auch schon auf den Herrn Uhren verwiesen hat.

Partition Exchange

Ein kleines Syntaxbeispiel zum Partition Exchange und Partition Splitting, das ich als vereinfachte Variante eines selbst bereits relativ einfachen Beispiels aus Tim Halls Sammlungen erstellt habe...

Zunächst das Test-Script:

-- test_part_exchange.sql
-- als minimales Syntax-Beispiel für Partition Exchange und Partition Splitting

column high_value format a100

drop table test_part_exchange_source;
drop table test_part_exchange_dest;

create table test_part_exchange_source
as
select rownum id
     , trunc(sysdate) - 100 + mod(rownum, 100) created_date
  from dual
connect by level <= 1000000;

select trunc(created_date, 'mm') create_month
     , count(*) cnt
  from test_part_exchange_source
 group by trunc(created_date, 'mm')
 order by trunc(created_date, 'mm');

create table test_part_exchange_dest
( id number
, created_date date)
partition by range (created_date)
(partition test_part_exchange_dest_2011 values less than (to_date('01.01.2012', 'dd.mm.yyyy') ) );

alter table test_part_exchange_dest
exchange partition test_part_exchange_dest_2011
with table test_part_exchange_source;

exec dbms_stats.gather_table_stats(user, 'TEST_PART_EXCHANGE_DEST')

select partition_name
	 , num_rows
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PART_EXCHANGE_DEST';

-- @ trace 
 
alter table test_part_exchange_dest
split partition test_part_exchange_dest_2011 at (to_date('30.06.2011 23:59:00', 'dd.mm.yyyy hh24:mi:ss'))
into (partition test_part_exchange_dest_201106,
      partition test_part_exchange_dest_201112);

-- @ trace_end
	  
exec dbms_stats.gather_table_stats(user, 'TEST_PART_EXCHANGE_DEST')

select partition_name
	 , num_rows
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PART_EXCHANGE_DEST';

Das Ergebnis dazu in meiner 11.1.0.7-Datenbank ist dann erwartungsgemäß:

SQL> @ test_part_exchange

Tabelle wurde gelöscht.

Tabelle wurde gelöscht.

Tabelle wurde erstellt.

CREATE_MON        CNT
---------- ----------
01.05.2011     300000
01.06.2011     300000
01.07.2011     310000
01.08.2011      90000

Tabelle wurde erstellt.

Tabelle wurde geändert.

PL/SQL-Prozedur erfolgreich abgeschlossen.

PARTITION_NAME                   NUM_ROWS HIGH_VALUE
------------------------------ ---------- -----------------------------------------------------------------------------------
TEST_PART_EXCHANGE_DEST_2011      1000000 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Tabelle wurde geändert.

PL/SQL-Prozedur erfolgreich abgeschlossen.

PARTITION_NAME                   NUM_ROWS HIGH_VALUE
------------------------------ ---------- -----------------------------------------------------------------------------------
TEST_PART_EXCHANGE_DEST_201106     600000 TO_DATE(' 2011-06-30 23:59:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PART_EXCHANGE_DEST_201112     400000 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Alles also recht übersichtlich. Wenn man einen Blick ins 10046er Trace wirft, sieht man dort neben den zu erwartenden Operationen am Data Dictionary auch noch ein LOCK TABLE ... PARTITION ... IN EXCLUSIVE MODE NOWAIT, was auch keine große Überraschung ist, aber andeutet, dass man beim Splitting großer Partitionen Vorsicht walten lassen sollte.

LOCK TABLE "TEST_PART_EXCHANGE_DEST" PARTITION 
  ("TEST_PART_EXCHANGE_DEST_2011")  IN EXCLUSIVE MODE  NOWAIT 

...

********************************************************************************

alter table test_part_exchange_dest
split partition test_part_exchange_dest_2011 at (to_date('30.06.2011 23:59:00', 'dd.mm.yyyy hh24:mi:ss'))
into (partition test_part_exchange_dest_201106,
      partition test_part_exchange_dest_201112)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.79       0.94          1       1354       2692     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.79       0.94          1       1354       2692     1000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 68  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  LOAD AS SELECT  (cr=1735 pr=0 pw=1232 time=0 us)
1000000   PARTITION RANGE SINGLE PARTITION: 1 1 (cr=1237 pr=0 pw=0 time=0 us cost=480 size=12000000 card=1000000)
1000000    TABLE ACCESS FULL TEST_PART_EXCHANGE_DEST PARTITION: 1 1 (cr=1237 pr=0 pw=0 time=0 us cost=480 size=12000000 card=1000000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    5        0.00          0.00
  direct path write                              99        0.01          0.11
  direct path sync                                2        0.05          0.06
  enq: RO - fast object reuse                     1        0.01          0.01
  db file sequential read                         1        0.00          0.00
  rdbms ipc reply                                 1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Die eigentliche Arbeit der Reorganisation scheint also über direct path zu erfolgen, was auch wieder ein gutes Argument für das exklusive LOCK ist.

Stored Outline Migration

Maria Colgan erläutert im Blog der cbo Entwickler, warum man von stored outlines (deprecated in 11g) zum SQL Plan Management übergehen sollte und wie diese Migration durchgeführt werden kann.

NO_INDEX Hint

Charles Hooper zeigt in seinem Blog am Fall des NO_INDEX-Hints und anhand einiger Beispiele, was man bei der Verwendung von Hints alles falsch machen kann.

dbms_xmlgen

Marco Gralike liefert eine Query zum Zählen der Sätze aller Tabellen eines Schemas, die mit dem dbms_xmlgen-Package arbeitet (obwohl der Herr Gralike damit nicht so ganz glücklich zu sein scheint). Das Package war mir dieser Tage schon beim Auslesen von LONG-Angaben in den HIGH_VALUEs von USER_TAB_PARTITIONS behilflich.

Cursor: Pin S Waits

Andrey Nikolaev erläutert in seinem Blog die Abgründe des dubiosen cursor:pin s Events, das in den undurchsichtigen Zusammenhang der Mutex-Operationen gehört:

• Mutex waits. Part 1. “Cursor: Pin S” in Oracle 10.2-11.1. Invisible and aggressive: "This is CPU consuming busy wait, almost invisible to Oracle Wait Interface and standard diagnostics tools. Patch 6904068 converts this wait into "normal" wait" 

Weitere Artikel sollen folgen.

Statistik-Views zum Systemzustand

Auch Kyle Haley gehört zu den Leuten, bei denen ich eigentlich jeden Artikel verlinken könnte. Im Beitrag Mining AWR: Statistics Metrics verses Statistics liefert er zunächst eine Liste relevanter v$-, dba_hist-Views, die auch noch mal in aller Klarheit vor Augen führt, dass die dba_hist-Views jeweils eine Historie zu einer entsprechenden v$-View darstellen (wobei die dba_hist_%_history-Views dann noch mal etwas unübersichtlicher zu sein scheinen).

Für die SYSMETRIC-Tabellen beispielsweise gilt Folgendes:

• V$SYSMETRIC – last 15 and 60 seconds
• V$SYSMETRIC_SUMMARY – values last hour (last snapshot) like avg, max, min etc
• V$SYSMETRIC_HISTORY – last hour for 1 minute, last 3 mintes for 15 second deltas
• DBA_HIST_SYSMETRIC_SUMMARY – hour summaries for last week.

Nachtrag 09.05.2014: leider verweist auch dieser Link inzwischen ins Nirwana.

HIGH_VALUE-Angaben für Partitionen

Dass Oracle regelmäßig darauf hinweist, dass man vom Datentyp LONG Abstand nehmen sollte, ist bekannt. Und bekannt ist auch, dass im data dictionary auch in aktuellen Releases (hier 11.1.0.7) immer noch eine ganze Reihe von LONG-Spalten zu finden ist:

select owner
     , count(*) cnt
  from dba_tab_columns
 where data_type = 'LONG'
 group by owner
 order by count(*) desc

OWNER                                 CNT
------------------------------ ----------
SYS                                   229
SYSTEM                                 12

Ich hatte an dieser Stelle gelegentlich schon mal auf Adrian Billingtons umfassende Erläuterung der Möglichkeiten, mit LONGs fertig zu werden, hingewiesen - und darin findet man neben diversen PL/SQL-Optionen auch eine reine SQL-Variante zum Auslesen der LONGs, die mit dem DBMS_XMLGEN-Package operiert. Als ich dieser Tage ein Script basteln wollte, mit dem ich prüfen kann, ob es in einer Datenbank partitionierte Tabellen gibt, die Bewegungsdaten bereits in eine MAXVALUE-Partition schreiben, habe ich damit experimentiert:

-- Anlage einer partitionierten Tabelle
-- im gegebenen Fall ist die Tabelle interval partitioned,
-- was die Frage nach der MAXVALUE-Partition natürlich hinfällig macht,
-- aber das ist für den Test unerheblich
create table test_partition_high_value
  partition by range (id)
  interval (1)
  (partition test_p1 values less than (1))
as
select rownum id
  from dual
connect by level <= 3;

select table_name
     , partition_name
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PARTITION_HIGH_VALUE';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      TEST_P1                        1
TEST_PARTITION_HIGH_VALUE      SYS_P1161                      2
TEST_PARTITION_HIGH_VALUE      SYS_P1162                      3
TEST_PARTITION_HIGH_VALUE      SYS_P1163                      4

Wenn ich jetzt nach dem maximalen HIGH_VALUE suchen will, treffe ich auf die Beschränkungen des LONG-Typs:

select table_name
     , partition_name
     , max(high_value) max_high_value
  from user_tab_partitions
 where table_name = 'TEST_PARTITION_HIGH_VALUE'
 group by table_name
        , partition_name
, max(high_value) max_high_value
      *

FEHLER in Zeile 3:
ORA-00997: Unzulässige Verwendung des Datentyps

Und das ist dann der Moment, wo das DBMS_XMLGEN-Package interessant wird:

select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual

XML
------------------------------------------------------
<?xml version="1.0"?>
<rowset>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>1</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>2</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>3</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>4</HIGH_VALUE>
  </ROW>
</ROWSET>

Die GETXML-Funktion liefert dabei - wenig überraschend - eine XML-Repräsentation der Ergebnisse einer übergebenen Abfrage. Mit Hilfe weiterer Funktionen, die ebenfalls bei Adrian Billington erläutert werden, kann man dieses XML dann wieder parsen:

with
xml
as
(
select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual)
,
high_values
as
(
select extractValue(test.object_value, '/ROW/TABLE_NAME') table_name
     , extractValue(test.object_value, '/ROW/HIGH_VALUE') high_value
  from xml x
     , table(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) test
)
select * from high_values

TABLE_NAME                     HIGH_VALUE
------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      1
TEST_PARTITION_HIGH_VALUE      2
TEST_PARTITION_HIGH_VALUE      3
TEST_PARTITION_HIGH_VALUE      4

Und diese Ergebnisse kann man dann gruppieren:

with
xml
as
(
select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual)
,
high_values
as
(
select extractValue(test.object_value, '/ROW/TABLE_NAME') table_name
     , extractValue(test.object_value, '/ROW/HIGH_VALUE') high_value
  from xml x
     , table(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) test
)
,
max_values
as
(select table_name
      , max(high_value) high_value
   from high_values
  group by table_name
)
select * from max_values

TABLE_NAME                     HIGH_VALUE
------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      4

Und damit wäre ich wieder einmal um PL/SQL herum gekommen ... - besten Dank an Adrian Billington, dessen Webseite sehr viele relativ komplexe Themen mit sehr handlichen Beispielen erläutert.

Temporary Tablespace Groups

Im DBA Kevlar Blog von Kellyn Pot’Vin wird detailliert erläutert, wie man Temporary Tablespace Groups verwenden kann, um die I/O-Last bei großen Workarea-Operationen zu parallelisieren. Randolf Geist hat übrigens gelegentlich auch schon ein paar Beobachtungen zum Thema aufgeschrieben.