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.