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.