FTS Kosten mit WORKLOAD statistics in 11.2

Zur Berechnung der Kosten eines FTS bei Verwendung von Workload-Statistiken hatte ich vor einigen Monaten schon mal einen Test gemacht, aber dabei vermutlich die Rolle des CPU-Elements nicht berücksichtigt. Daher das Ganze noch einmal...

FTS bei WORKLOAD system statistics

Der Tests basiert auf der gleichen Grundlage wie die beiden letzten Tests (also letztlich auf den Definitionen des cbo-Buchs von Jonathan Lewis). Zunächst setze ich die Systemstatistiken - den CPUSPEEDNW-Wert allerdings auf einen extrem hohen Wert, um den CPU-Anteil des costings wieder auszuschalten.

begin
   dbms_stats.set_system_stats('CPUSPEED', 1000000);
   dbms_stats.set_system_stats('SREADTIM', 5.0);
   dbms_stats.set_system_stats('MREADTIM', 30.0);
   dbms_stats.set_system_stats('MBRC', 12);
end;
/

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                        1000000
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                                5
MREADTIM                               30
CPUSPEED                          1000000
MBRC                                   12

Nach Jonathan Lewis errechnen sich die Kosten in diesem Fall als:

• (Anzahl_Blocks / MBRC) * (MREADTIM/SREADTIM)

Also:

• 10000/12 * 30/5 = 5000

Tatsächlich ergibt sich (wieder mit dem Beispiel aus dem ersten Teil der Mini-Serie):

select /*+ cpu_costing */ max(val)
  from t1;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  5000   (0)| 00:00:26 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  5000   (0)| 00:00:26 |
---------------------------------------------------------------------------

Das Ergebnis entspricht also exakt der Erwartung. Jetzt noch einmal der Test mit veränderter MBRC-Angabe, der mir im Januar Kopfzerbrechen bereitete:

dbms_stats.set_system_stats('MBRC', 16);

Die Formel ergibt nun:

• 10000/16 * 30/5 = 3750

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  3750   (0)| 00:00:19 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  3750   (0)| 00:00:19 |
---------------------------------------------------------------------------

Also erneut eine exakte Übereinstimmung. Die Abweichung im Januar ergab sich offenbar daraus, dass ich damals bei den Blocks auch die oberhalb der HWM mitgerechnet hatte. Nun, immerhin lässt sich das aufklären.

FTS Kosten mit NOWORKLOAD statistics in 11.2

In Fortsetzung zur hier begonnenen Untersuchung zum FTS-Costing in 11.2.

FTS bei Default NOWORKLOAD system statistics

Bei Jonathan Lewis auf S. 20 (und in Randolf Geists Blog) findet man die Erklärung, dass der optimizer - sofern keine System-Statistiken vorliegen - die default workload Statistiken verwendet, um daraus Werte für sreadtim und mreadtim zu synthetisieren:

• MBRC: ist der Wert des Parameters DB_FILE_MULTIBLOCK_READ_COUNT
• SREADTIM: IOSEEKTIM + DB_BLOCK_SIZE/IOTRFRSPEED
• MREADTIM: IOSEEKTIM + DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE/IOTRFRSPEED

Dazu die Zahlen meines Testsystems:

PNAME                 PVAL1
---------------- ----------
CPUSPEEDNW         1039,046
IOSEEKTIM                10
IOTFRSPEED             4096 

INIT_PARAMETER                                     SESSION_VALUE
-------------------------------------------------- -------------
db_file_multiblock_read_count                      128
_db_file_optimizer_read_count                      8
db_block_size                                      8192

Demnach wäre:

• MBRC: 128
• SREADTIM: 10 + 8192/4096 = 12
• MREADTIM: 10 + 8 * 8192/4096 = 26

Dass nicht der db_file_multiblock_read_count, sondern der _db_file_optimizer_read_count verwendet wird, hatte ich vor kurzem beim Nachspielen eines Beispiels in Jonathan Lewis' Blog erkannt (jedenfalls gilt das, so lange der db_file_multiblock_read_count nicht explizit gesetzt wird). Für die 10000 Block-Tabelle ergibt die Formel:

cost(FTS) = MRds (= Blocks/Multiblock-Reads) * MREADTIM/SREADTIM

also: 10000/8 * 26/12 = 2708,33333.

Mit dem Beispiel aus dem letzten Beitrag ergibt sich:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  2716   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  2716   (1)| 00:00:33 |
---------------------------------------------------------------------------

Das ist dicht an der Erwartung aber nicht exakt das erwartete Ergebnis (entspricht aber - fast - der 2717, die im cbo Buch auf S. 20 erscheinen). Aber auch da kommt der Herr Geist zur Hilfe, der in seinem Test den CPU-Anteil der Kostenberechnung dadurch ausschaltet, dass er den CPUSPEEDNW-Wert extrem hoch setzt:

exec dbms_stats.set_system_stats('CPUSPEEDNW',1000000);

select /*+ cpu_costing */ max(val)
  from t1;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  2709   (0)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  2709   (0)| 00:00:33 |
---------------------------------------------------------------------------

Damit ist man dann ganz genau beim erwarteten Wert.

FTS bei ermittelten NOWORKLOAD system statistics

Neben den default noworkload Statistiken gibt es noch eine zweite Variante: die ermittelten noworkload Statistiken. Von den workload Statistiken unterscheiden sie sich dadurch, dass ihre Erhebung nicht während einer Phase einer repräsentativen Belastung der Datenbank erfolgen muss. Ihre Erzeugung erfolgt über DBMS_STATS:

exec dbms_stats.delete_system_stats
exec dbms_stats.gather_system_stats('noworkload')

PNAME              PVAL1 
------------- ---------- 
CPUSPEEDNW      1117,307
IOSEEKTIM          9,893
IOTFRSPEED          4096

Auch wenn man es nicht in beiden Fällen sehen kann, erzeugt der Aufruf Statistiken für IOSEEKTIM (9,893 statt 10) und IOTFRSPEED (4096 - also unverändert); der Wert für CPUSPEEDNW wird in jedem Fall ermittelt (und bleibt auch nach der Löschung der system statistics stehen). Diesen Wert setze ich wieder extrem nach oben, um den CPU-Anteil am costing auszuschalten.

Angesichts der relativ geringen Änderung der Werte, erwarte ich nur geringfügige Effekte bei der Berechnung der Kosten.

exec dbms_stats.set_system_stats('CPUSPEEDNW',1000000);
-- diesmal setze ich den db_file_multiblock_read_count explizit
alter session set db_file_multiblock_read_count = 8;
-- ausgehend von der noworkload cost Formel für FTS erwarte ich:
-- 10000/8 * 25.893/11.893 = 2721,4538
select /*+ cpu_costing */ max(val)
  from t1;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  2723   (0)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  2723   (0)| 00:00:33 |
---------------------------------------------------------------------------

Wenn ich den Effekt von _table_scan_cost_plus_one noch berücksichtige und annehme, dass hier mal wieder ceil statt round im Spiel ist, dann passt das Ergebnis auch in diesem Fall zu den Erwartungen.

FTS Kosten bei I/O costing in 11.2

Ein guter Datenbank-Fachmann sollte ein gutes Gedächtnis haben und strukturiert vorgehen. Leider ist mein Gedächtnis dieser Tage eher siebartig und meinem Vorgehen mangelt's an Ordnung. Deshalb komme ich auch erst jetzt auf die Idee, zu prüfen, wie sich die Dinge in 11.2.0.1 verhalten, die Jonathan Lewis in seinem cbo Buch für Releases bis 10.1.0.4 untersucht hatte.

FTS bei I/O costing
Fangen wir an mit dem FTS bei I/O costing (S.11):

-- entspricht dem Beispiel auf S.10 des cbo Buchs
create table t1 tablespace test_ts
pctfree 99
pctused 1
as
select rownum id
     , trunc(100 * dbms_random.normal) val
  , rpad('x', 100) padding
  from all_objects
 where rownum <= 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent => 100)

select /*+ opt_param('_optimizer_cost_model','io') */ max(val)
  from t1;

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1519 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |       |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1519 |
-----------------------------------------------------------

Bei Jonathan Lewis ist das Ergebnis 1518, was möglicherweise einmal mehr ein Effekt der Verwendung von Rundung bzw. Ceil-Funktion ist. Ich sehe gerade, dass Randolf Geist explizit ein

alter session set "_table_scan_cost_plus_one" = false;

in seinen - sehr ähnlich definierten - Test eingebaut hat, um die Formel zu überprüfen. Außerdem sehe ich, dass er statt des opt_param-Hints den klareren Hint nocpu_costing verwendet hat - und ohnehin schon alles gemacht hat, was ich hier ausprobiere - aber das soll mich nicht aufhalten... Mit _table_scan_cost_plus_one=false komme ich jedenfalls auch auf die erwartete 1518.

SMALLINT

Dass Oracle es mit den numerischen Datentypen nicht immer so genau nimmt, hatte ich gelegentlich schon beobachtet, aber das Verhalten von SMALLINT finde ich doch etwas eigenartig:

create table test_smallint
( id smallint );

insert into test_smallint
select rownum
  from dual
connect by level <= 1000000;

1000000 Zeilen wurden erstellt.

insert into test_smallint(id) values (0.75);

1 Zeile wurde erstellt.

select * from test_smallint where id < 1;

Es wurden keine Zeilen ausgewählt

select * from test_smallint where id < 2;

        ID
----------
         1
         1

Demnach kann man also auch Werte deutlich oberhalb von 32767 (oder von mir aus auch 65535) einfügen; und auch mit Kommazahlen gibt es keine Probleme beim Insert, da sie offenbar gerundet werden (was dem Verhalten von NUMBER bei Überschreitung der gewählten SCALE entspricht). Intern wird aus SMALLINT offenbar NUMBER(38,0):

select table_name
     , column_name
     , data_type
     , data_precision
     , data_scale
  from user_tab_cols
 where table_name = 'test_smallint'

TABLE_NAME      COLUMN_NAME   DATA_TYPE   DATA_PRECISION DATA_SCALE
--------------- ------------- ----------- -------------- ----------
TEST_SMALLINT   ID            NUMBER                              0

Angesichts dieser Ergebnisse scheint mir SMALLINT völlig zweckfrei zu sein.

Recursive calclulations in MDX

Jeffrey Wang vom SSAS-Entwicklungsteam hat eine Erläuterung zum Verhalten rekursiver MDX calculations gegeben und darin auch dargelegt, wann der block mode verwendet werden kann und wann auf cell mode zurückgegangen werden muss.

Im Artikel findet sich auch der Satz: "Well, I admit that Analysis Services product team owes the MDX community good diagnostic features, such as MDX query plans, to easily identify such problems." Ohne diese Werkzeuge basiert Performance-Optimierung für MDX-Queries auf Ausprobieren und Umformulieren, was wenig befriedigend ist...

Table Expansion

Eine weitere Erläuterung des CBO-Entwicklungsteams, diesmal zum Thema Table Expansion:

Table expansion allows the optimizer to generate a plan that uses indexes on the read-mostly portion of the data, but not on the active portion of the data. Hence, DBAs can configure a table so that an index is only created on the read-mostly portion of the data, and will not suffer the overhead burden of index maintenance on the active portions of the data. This is the high-level idea of what table expansion does. In practice, the way we can delineate active and inactive portions of the data is using partitioning. A local index can be defined on a table, and marked unusable for certain partitions. The partitions for which the index is unusable are in effect not indexed.

Klingt im ersten Moment vielleicht etwas komplizierter als es ist: man markiert einfach die lokalen Indizes der veränderlichen (also aktuellen) Partition(en) und kann auf historische (also unveränderliche) Daten problemlos über den Index zugreifen, während der Zugriff auf die aktuellen Daten via FTS erfolgt. Intern wird die Query also in zwei Teile gesplittet, was im Exceution Plan klar zu erkennen ist. Das Verfahren ähnelt ein wenig der vor kurzem erläuterten OR-Expansion.

Datentypen in Oracle und SQL Server

Der Titel des Eintrags mag ein wenig in die falsche Richtung weisen. Worum es geht, ist Folgendes: vor kurzem ist mir klar geworden, dass ich (mal wieder?) den Standardfehler der Datenbankentwicklung gemacht habe - nämlich das Verhalten eines RDBMS als das eines anderen RDBMS anzunehmen. Im gegebenen Fall wusste ich, dass Oracle in der Frage numerischer Datentypen nicht sonderlich sensibel ist: intern ist (fast) alles NUMBER und benötigt deshalb auch den gleichen Platz auf der Platte. Dazu ein Beispiel. Zunächst lege ich vier Tabellen an, die sich jeweils nur im Namen und im Datentyp der rn-Spalte unterscheiden:

create table test_ora_number
as
select cast(rownum as number) rn
  from dual
connect by level <= 100000

create table test_ora_number_pc
as
select cast(rownum as number(12,4)) rn
  from dual
connect by level <= 100000

create table test_ora_int
as
select cast(rownum as int) rn
  from dual
connect by level <= 100000;

create table test_ora_binarydouble
as
select cast(rownum as BINARY_DOUBLE) rn
  from dual
connect by level <= 100000

In USER_SEGMENTS sehen alle Fälle gleich aus, aber USER_TABLES liefert (nach Statistikerstellung) unterschiedliche Angaben (die auch den Aussagen von Tom Kytes show_space-Prozedur entsprechen):

select segment_name
     , blocks
     , bytes 
  from user_segments
 where segment_name like 'TEST_ORA%';

SEGMENT_NAME                       BLOCKS      BYTES
------------------------------ ---------- ----------
TEST_ORA_BINARYDOUBLE                 128    2097152
TEST_ORA_INT                          128    2097152
TEST_ORA_NUMBER                       128    2097152
TEST_ORA_NUMBER_PC                    128    2097152

select table_name
     , blocks
     , blocks * 16384 bytes
  from user_tables 
 where table_name like 'TEST_ORA%';

TABLE_NAME                         BLOCKS      BYTES
------------------------------ ---------- ----------
TEST_ORA_BINARYDOUBLE                 103    1687552
TEST_ORA_INT                           83    1359872
TEST_ORA_NUMBER                        83    1359872
TEST_ORA_NUMBER_PC                     83    1359872

Demnach umfassen die Varianten NUMBER, NUMBER mit Angabe von Precision und Scale und INT den gleichen Speicherplatz. Nur der präzisere Datentyp BINARYDOUBLE belegt mehr Platz (was mir neu war - ich aber nachvollziehen kann. Nun zum SQL Server. Ich hatte bis vor kurzem angenommen, dass der Fall hier ähnlich gelagert wäre - aber das trifft nicht zu. Auch dazu ein Beispiel. Zunächst lege ich wieder Testtabellen an - diesmal mit rekursiven CTEs, ein Trick, den ich mir bei Jonathan Lewis ausgeborgt habe:

DECLARE @div INT = 50 ;
DECLARE @mod INT = 2000 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;

WITH    
generator
AS 
( 
SELECT 1 AS id
 UNION ALL
SELECT id + 1
  FROM generator
 WHERE id < @driver
)
SELECT cast(id as decimal) id
  INTO test_decimal
  FROM ( SELECT TOP ( @limit )
                @driver * ( g1.id - 1 ) + g2.id id
           FROM generator g1
          CROSS JOIN 
                generator g2
        ) iv
OPTION ( MAXRECURSION 0, FORCE ORDER );

Anschließend ermittele ich die Objektgrößen über sp_spaceused:

EXEC sp_spaceused test_decimal
EXEC sp_spaceused test_int
EXEC sp_spaceused test_bigint

name            rows     reserved    data  index_size  unused
-------------------------------------------------------------
test_decimal  100000      1800 KB 1792 KB        8 KB    0 KB
test_int      100000      1352 KB 1296 KB        8 KB   48 KB
test_bigint   100000      1736 KB 1696 KB        8 KB   32 KB

Demnach gibt es hier einen deutlichen Unterschied zwischen den verwendeten Datentypen: je kleiner der Typ, desto kompakter wird die Tabelle - und es ist wichtig, einen geeigneten Typ zu wählen - in DWH-Zusammenhängen in der Regel den kleinsten geeigneten Typ.

Who is active

Adam Machanic erläutert in einer epischen Artikelfolge von 30 Beiträgen sein Analysescript Who is Active, mit dem man die aktuell laufenden Sessions eines SQL Servers und ihre Operationen überwachen kann. Das Script ist schon sehr gut, aber fast noch besser gefallen mir die umfassenden Erläuterungen der Blog-Reihe - die mir klar machen, dass ich die interne Arbeitsweise des SQL Servers gelegentlich mal genauer betrachten sollte.

FTS Kosten

Noch einmal zu den Kosten von Full Table Scans: im cbo Buch des Herrn Lewis findet man auf S. 12 eine Tabelle, in der ein adjusted dbf_mbrc abhängig vom Parameter db_file_multiblock_read_count berechnet wird.

I/O costing
Der adjusted dbf_mbrc ist der Wert, der vom cbo - bei Verwendung von I/O-costing - für die Kostenberechnung von Multiblock-Zugriffen verwendet wird, während der db_file_multiblock_read_count die Anzahl der Blocks angibt, die bei Leseoperationen tatsächlich gelesen werden (sollen): aufgrund von extent-Grenzen und bereits im Cache vorliegenden Blocks bleibt die read-size tatsächlich häufig unterhalb des definierten Werts.

Hier noch mal ein Beispiel, das dem des cbo-Buchs erschreckend ähnlich ist (auf 11.1.0.7 und mit einem non-ASSM-Tablespace):

drop table test_fts;

create table test_fts pctused 1 pctfree 90 tablespace test_ts
as
select rownum rn
     , lpad('*', 1000, '*') padding
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'test_fts', estimate_percent=> 100)

-- der Hint sorgt dafür, dass kein cpu costing erfolgt
select /*+ opt_param('_optimizer_cost_model','io') */ count(*)
  from TEST_FTS;

  COUNT(*)
----------
     10000

1 Zeile wurde ausgewählt.


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

-------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost  |
-------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   246 |
|   1 |  SORT AGGREGATE    |          |     1 |       |
|   2 |   TABLE ACCESS FULL| TEST_FTS | 10000 |   246 |
-------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10006  consistent gets
      10000  physical reads
          0  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

alter session set db_file_multiblock_read_count = 4;
--> cost: 2397 
alter session set db_file_multiblock_read_count = 8;
--> cost: 1519
alter session set db_file_multiblock_read_count = 16;
--> cost: 963
alter session set db_file_multiblock_read_count = 32;
--> cost: 611
alter session set db_file_multiblock_read_count = 64;
--> cost: 388
alter session set db_file_multiblock_read_count = 128;
--> cost: 246

select 4 mbrc, round(10006/2397, 2) adjusted_dbf_mbrc from dual
union all
select 8, round(10006/1519, 2) from dual
union all
select 16, round(10006/963, 2) from dual
union all
select 32, round(10006/611, 2) from dual
union all
select 64, round(10006/388, 2) from dual
union all
select 128, round(10006/246, 2) from dual;

MBRC ADJUSTED_DBF_MBRC
---- -----------------
   4              4,17
   8              6,59
  16             10,39
  32             16,38
  64             25,79
 128             40,67

Die Zahlen sind nahezu identisch mit den Ergebnissen bei Jonathan Lewis. Die marginalen Abweichungen mögen sich daraus ergeben, dass meine Bestimmung der HWM fragwürdig sein könnte.

cpu costing (workload statistics)
Zum Verhalten bei Verwendung von Workload-Statistiken hatte ich gelegentlich auch einen Test durchgeführt, der ebenfalls auf den Ergebnissen des cbo Buchs basiert.

Noch einmal FTS und MBRC

Beim Nachvollziehen eines Beispiels auf Jonathan Lewis' Blog habe ich einmal mehr über die Rolle von system statistics und io costing nachdenken müssen. Erinnerungswürdig scheinen mir diesmal folgende Punkte:

• um io costing zu aktivieren, kann man auf Statementebene den folgenden Hint verwenden: /*+ opt_param('_optimizer_cost_model','io') */. Erwähnt wird der Hint in einem deutlich älteren Blog-Eintrag beim Herrn Lewis - möglicherweise ist der Hint inzwischen nicht mehr das Mittel der Wahl, aber er funktioniert.
• Workload-Statistiken kann man über folgendes Kommando löschen: exec dbms_stats.delete_system_stats(). Nach der Löschung der Statistiken erfolgt das costing weiterhin als cpu costing, aber mit NOWORKLOAD-Statistiken. Weitere Details zum Thema findet man in Randolf Geists Blog.
• Wenn der MBRC nicht explizit gesetzt wird, sucht sich Oracle dafür offenbar einen zum OS passenden Wert (auf meinem Windows7-Rechner 128). Für costing und data access werden aber die hidden paramters _db_file_exec_read_count und _db_file_optimizer_read_count verwendet, die durchaus voneinander abweichen können. Die Werte dieser Parameter liefert folgende Query (vgl. dazu auch Kerry Osbornes Blog):

-- auszuführen als sys bzw. jemand, der die x$-Tabellen sieht:
select i.ksppinm name
     , sv.ksppstvl value
  from x$ksppi  i
     , x$ksppsv sv
 where i.indx = sv.indx and upper(i.ksppinm) like upper('%read_count%')

NAME                                     VALUE
---------------------------------------- ------
db_file_multiblock_read_count            128
_db_file_exec_read_count                 128
_db_file_optimizer_read_count            8
_db_file_noncontig_mblock_read_count     11
_sort_multiblock_read_count              2

Apply-Operator in T-SQL

Mal wieder was zum SQL Server. Dem Apply-Operator bin ich beim Verknüpfen von SQL Server DMVs verschiedentlich begegnet, und hatte mir auch eine für meine Zwecke ausreichende Erklärung seiner Funktion zusammengereimt, aber nie die Mühe gemacht, die Dokumentation zu konsultieren. Stattdessen habe ich anscheinend darauf gewartet, dass jemand einen Blog-Eintrag zum Thema liefert, und das hat nun Rob Farley getan:

APPLY, in both its inner (CROSS APPLY) and outer (OUTER APPLY) forms, allows a correlated sub-query or table-valued function to be part of the FROM clause. This is different to a regular JOIN, which is between two sets that are independent of each other, but able to be joined according to some predicate that lives in the ON clause. Using APPLY, that restriction disappears, and the predicate can be built into the sub-query (or TVF) itself.

Interessant scheint vor allem auch die Möglichkeit zu sein, correlated subqueries auf diese Weise zu verknnüpfen, was der Herr Farley anhand diverser Beispiele zeigt.

Trigger und Ladeoperationen

Wahrscheinlich liegt's daran, dass ich in diesem - wie in vielen anderen Punkten - ein Jünger Tom Kytes bin, jedenfalls halte ich Trigger für Teufelswerk und empfehle regelmäßig, sie zu vermeiden, wo immer das möglich ist. Gerade im Hinblick auf die Performance von Ladeprozessen behaupte ich gerne, dass Trigger ein massives Problem darstellen können. Um diese Aussage mal ein wenig zu fundieren, habe ich einen recht harmlosen Test gebastelt, der ein SQL-Script ausführt und dabei SQL-Trace mitlaufen lässt. Der Test erfolgt auf 11.2.0.1 unter Windows7, aber ich vermute, dass die Ergebnisse davon nicht besonders stark abhängen.

Zunächst das Script:

-- SQL-Trace aktivieren
EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE)

-- Anlage einer ersten Testtabelle 
-- für ein Insert ohne Trigger
drop table test1;
create table test1
( rn number
, insert_date date);

insert into test1
select rownum rn
     , sysdate insert_date
  from dual
connect by level <= 1000000;

-- Anlage einer zweiten Testtabelle 
-- für ein Insert mit Trigger
drop table test2;
create table test2
( rn number
, insert_date date);

-- der Trigger leistet für das zweite Insert das,
-- was das erste Insert ohne Hilfe schaffte:
-- er ergänzt das Datum für die Spalte insert_date
create or replace trigger test2_trigger
before insert
on test2
for each row

begin
  select sysdate
  into :new.insert_date
  from dual;

end test2_trigger;
/

insert into test2 (rn)
select rownum rn
  from dual
connect by level <= 1000000;

EXEC DBMS_MONITOR.session_trace_disable

Im (über tkprof) formatierten Trace für test1 (ohne Trigger) sieht man nun Folgendes:

********************************************************************************
insert into test1
select rownum rn
     , sysdate insert_date
  from dual
connect by level <= 1000000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.15       1.26          1       4748      26400     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.15       1.26          1       4748      26400     1000000

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: CR - block range reuse ckpt                1        0.00          0.00
  log file switch completion                      2        0.05          0.09
  log file sync                                   1        0.02          0.02
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Und hier das Ergebnis für die Variante test2 (mit Trigger):

********************************************************************************
insert into test2 (rn)
select rownum rn
  from dual
connect by level <= 1000000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     96.72      96.41          1       4757      26387     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     96.72      96.41          1       4757      26387     1000000

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: CR - block range reuse ckpt                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
********************************************************************************

SQL ID: c749bc43qqfz3
Plan Hash: 1388734953
SELECT SYSDATE 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute 999992      9.40      10.12          0          0          0           0
Fetch   999992      2.65       3.12          0          0          0      999992
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total  1999986     12.05      13.24          0          0          0      999992

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 75     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

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

Also 1,26 sec. Laufzeit für die Variante ohne Trigger gegenüber fast 2 Minuten für die Variante mit dem before insert Trigger; laut sqlplus benötigte das insert für test2 1:49.72 min, was sich offenbar aus den 96.41 sec. für das insert und den 13.24 sec. für die dual-Zugriffe zusammensetzt. Zusätzlich erschreckend ist dabei, dass das insert für test2 nicht wartet, sondern massiv CPU konsumiert. Interessant ist noch, dass der dual-Zugriff nur 999992 mal erfolgt; wieso man ihn 8 mal sparen konnte, ist mir ein Rätsel (aber keines, dem ich heute noch nachgehen mag). Das Beispiel ist, ich geb's zu, ein wenig an den Haaren herbeigezogen. Außerdem liefert es auch nicht das gleiche Ergebnis, da alle Sätze in test1 mit dem gleichen Zeitstempel belegt werden, während in test2 viele unterschiedliche Werte ankommen - was im gegebenen Fall aber keine inhaltliche Bedeutung hätte:

select count(distinct insert_date) date_count from test1;

DATE_COUNT
----------
         1

select count(distinct insert_date) date_count from test2;

DATE_COUNT
----------
       111

Ein zweiter relevanterer Einwand gegen des Test wäre, dass die Trace-Erstellung massive Auswirkungen auf Fall2 hat: in einem weiteren Lauf ohne SQL-Trace benötigt das Insert "nur" 36,91 sec. Grundsätzlich sehe ich meine Abneigung gegen Trigger allerdings bestätigt. Natürlich gibt es Fälle, in denen sie benötigt werden, aber dort, wo man auch mit anderen Mitteln arbeiten kann, werde ich weiterhin dankend auf sie verzichten. Gerade hat übrigens Tim Hall eine schöne Einführung zum Thema geschrieben (und darin noch ein paar weitere Argumente gegen den Einsatz von Triggern aufgeführt).

ITL Waits - Implementierung

Christian Antognini erläutert in seinem Blog, wie die Mechanismen der ITL Waits in den letzten Releases verändert wurden.

Buffer Cache Inhalt

Arup Nanda erläutert im vierten Teil seiner hochinteressanten Reihe zu grundlegenden Konzepten des Oracle Servers die Nutzung des Buffer Caches. So wie's aussieht, werde ich hier auf jeden Beitrag dieser Serie verweisen ...

Nanda zeigt, wie es dazu kommt, dass zu einem Tabellenblock zahlreiche Kopien im Buffer Cache erscheinen. Die kurze Antwort lautet "Versionierung", aber die lange Fassung ist aufgrund der griffigen Beispiele deutlich spannender.

Beim Nachspielen des Beispiels gab's in meinem Testsystem übrigens auch Buffer mit dem Status 1st level bmb und 2nd level bmb, die anscheinend zum Thema ASSM gehören.

Nachtrag 19.04.2011: manchmal kommt es zu erstaunlichen thematischen Häufungen in der Blog-Welt: http://jonathanlewis.wordpress.com/2011/04/18/consistent-reads/, worin Jonathan Lewis erläutert, dass die LIOs beim Lesezugriff nicht nur mit der Anzahl der Blocks eines Objekts zusammenhängen, sondern sehr stark von der Existenz von undo-Informationen beeinflusst werden.

Nachtrag 10.11.2014: eine erweiterte Fassung des älteren Artikels liefert der Artikel Cache Buffer Chains Demystified.

SCD2-Änderung in einem SQL-Statement

Zu den Dingen, die ich mit SQL (d.h. mit einem einzelnen Statement) bisher nicht geschafft habe, gehört die Aktualisierung von SCD2-Tabellen mit Statusangabe und Änderungsdatum. Für alle, die die Details gerade nicht parat haben, aber trotzdem weiterlesen wollen: http://de.wikipedia.org/wiki/Slowly_Changing_Dimensions.

In Oracle und MS SQL (und auch in anderen SQL-Dialekten) kann man über das MERGE-Statement UPDATE- und INSERT-Operationen kombinieren: wenn ein entsprechender Satz in der (im USING definierten) Referenzquery existiert (WHEN MATCHED), wird die Basistabelle via update aktualisiert (oder es wird via delete aus ihr gelöscht), wenn nicht (WHEN NOT MATCHED), dann wird ein neuer Satz eingefügt. Was dabei aber (zumindest soweit ich es verstanden habe) nicht funktioniert, ist die Kombination von INSERT und UPDATE im „WHEN MATCHED“-Fall; man kann also nicht einen neuen Satz einfügen UND zugleich die Gültigkeitsangaben eines bestehenden Satzes ändern (Status auf inaktiv; Ende der Gültigkeit auf aktuelles Datum). Um eine SCD2 komplett zu aktualisieren, müsste man auf diesem Weg also anschließend ein zweites Update-Statement verwenden, um die Gültigkeit zu korrigieren.

Für den SQL Server 2008 gibt es aber eine Möglichkeit, die mir bisher entgangen war, und die hier beschrieben wird: http://sqlblogcasts.com/blogs/atulthakor/archive/2011/01/10/t-sql-scd-slowly-changing-dimension-type-2-using-a-merge-statement.aspx. Dabei wird das MERGE-Statement als INLINE-View für ein darauf aufbauendes INSERT verwendet – eine Syntax, die ein wenig gewöhnungsbedürftig, aber durchaus nachvollziehbar ist. Voraussetzung sind die MERGE-Erweiterungen von Microsoft, die die Definition einer OUTPUT-clause mit einer Action-Angabe erlauben.

Für Oracle scheint es derzeit keine ähnlich elegante Lösung zu geben, aber unter http://www.business-intelligence-quotient.com/?p=66 findet man einen Workaround (man könnte auch sagen: einen dreckigen Hack), um das Problem durch Verdopplung der Referenzdaten anzugehen (man erzeugt in der Referenz zu jedem Datensatz ein nahezu identisches Duplikat: einen Satz für das INSERT und einen für das UPDATE), was allerdings nur unter bestimmten Voraussetzungen funktioniert. Im Oracle-Fall bleibe ich deshalb lieber bei einer mehrschrittigen Variante.

PCTFREE

In Jonathan Lewis' cbo Buch findet man auf Seite 64 eine schöne Zusammenfassung der Wirkung der PCTFREE-Angabe für Tabellen und Indizes. Dort heisst es:

For indexes, pctfree is only relevant as an index is created, rebuild, or coalesced; and it only applies to leaf blocks.

For a table, the pctfree storage parameter tells Oracle when to stop inserting new rows into a block, so that some space in each block can be left for updates to existing rows in that block. But entries in indexes are never updated - when you change an index entry it (usually) belongs somewhere else in the index, thus an update to an index is really a delete followed by an insert - so you don't reserve space for updates, you reserve space for new rows.

Neu für mich war die Einschränkung auf Leaf-Blocks für den Fall der Indizes - oder zumindest hatte ich sie vergessen.

OR Expansion

Wieder ein Blog-Artikel des Optimizer-Teams zu einer internen Rewrite-Strategie des cbo, der OR Expansion:

OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain OR clauses). The basic idea in OR expansion is to transform a query containing disjunctions into the form of a UNION ALL query of two or more branches. This is done by splitting the disjunction into its components and associating each component with a branch of a UNION ALL query.

There are many reasons for performing OR expansion. It can enable more efficient access paths (index accesses, partition pruning), open up alternative join methods (avoid Cartesian product).

Klingt recht harmlos und scheint das auch zu sein.

Zeitintervallbildung aus Einzeldaten

Wieder kein brauchbarer Titel, aber was soll's...

Ein schönes Beispiel dafür, wie man mit Hilfe von Analytics (in diesem Fall der Funktion ROW_NUMBER) aus Einzelsätzen Intervalle bilden kann, findet man hier. Eine ähnliche Lösung hatte mir ein Kollege gelegentlich gezeigt - und ehe ich in solchen Fällen noch mal üble Dinge mit LEAD und LAG anstelle, sollte ich mich an diese übersichtliche Varinate erinnern.

Escape Ampersand in LIKE-Bedinungen

Angesichts des Titels frage ich mich mal wieder, ob ich nicht anfangen sollte, ins Englische überzugehen - dann bräuchte ich mich nicht dafür zu entschuldigen, dass ich keine brauchbare Übersetzung für "Escape" und "Ampersand" gefunden habe...

Dieser Tage wurde ich gefragt, wie man ein Ampersand-Symbol (&) in einer LIKE-Bedingung escapen kann (ja: he does it again), und glaubte, dass das einfach über eine ESCAPE-Klausel im LIKE funktionieren würde, was aber ein Schlag ins Wasser war:

select *
  from test;

MY_STRING
-----------
BLA&BLA

select *
  from test
 where my_string like '%/&BLA' escape '/';

Geben Sie einen Wert für bla ein:

Da das & für sqlplus als Ersetzungsvariable fungiert, wird hier eine Eingabe gefordert. Um das Verhalten zu ändern, kann man die Auswertung der Variable deaktivieren:

set define off

select *
  from test
 where my_string like '%&BLA';

MY_STRING
----------
BLA&BLA

In diesem Fall würde das Escape sogar einen Fehler hervorrufen ("ORA-01424: Zeichen nach dem Escape-Zeichen fehlen oder sind ungültig"), da das &-Zeichen nun in der Tat ein ganz gewöhnliches Element ist.

Alternativ kann man auch mit dem Kommando "SET ESCAPE ON" und dem default-Escape-Charakter Backslash (\) arbeiten:

set define on
set escape on
select *
  from test
 where my_string like '%\&BLA';

MY_STRING
----------
BLA&BLA

Scripts von Tanel Poder

Tanel Poder verwendet in seinen Blog-Artikeln und auch in seinem Web-Seminar jede Menge interessanter Analyse-Scripts, für die er jetzt eine Art Index liefert. Dort findet man auch einen Link zu den Scripts.

Oracle 11.2 XE Beta

Wie man schon in jedem anderen Blog lesen konnte, steht eine neue XE mit 11.2 als Beta-Version zum Download.

Parallel + Nologging

Randolf Geist hat wieder ein paar grundsätzliche Erläuterungen zu Parallelisierung und Logging zusammengefasst. Erinnern sollte ich mich vor allem daran, dass:

• NOLOGGING kein Hint, sondern ein DDL-Element ist
• NOLOGGING in CTAS-Operationen hinter dem CREATE-Teil erscheinen muss - und nicht hinter dem FROM des folgenden Selects, da es sonst nur als Tabellen-Alias interpretiert wird; den Hinweis habe ich auch bei AskTom gelegentlich gesehen
• bis 10.2 musste dem PARALLEL-Hint in Klammern ein Tabellenname/-alias folgen; ab Version 11 sind auch PARALLEL-Hints ohne folgende Tabellenangabe möglich, die dann auf statement-level gelten (Details zum Verhalten merke ich mir da eher nicht ...)
• sofern er nicht explizt angegeben wird, wird der parallel degree bei der Verwendung eines Hints bestimmt als PARALLEL_THREADS_PER_CPU * CPU_COUNT * INSTANCE_COUNT (und nicht über den parallel degree des Objekts, der nur relevant ist, wenn kein Hint verwendet wird)

DDL Logging

Laurent Schneider erläutert in seinem Blog das neue 11.2er Auditing für DDL-Operationen, das über den Parameter enable_ddl_logging gesteuert wird. In der Alert.log bzw. dem neuen XML-Tracefile findet man dann die DDL-Operationen und zugehörige Informationen.