FTS cost

Dieser Tage lese ich noch mal Cost-Based Oracle von Jonathan Lewis - und diesmal glaube ich sogar, einigermaßen genau zu verstehen, wovon der Mann da redet...

Um mir die Details besser merken zu können, spiele ich ein paar der Beispiele hier nach:

-- Setzen der system stats
begin
   dbms_stats.set_system_stats('CPUSPEED', 500);
   dbms_stats.set_system_stats('SREADTIM', 5.0);
   dbms_stats.set_system_stats('MREADTIM', 30.0);
   dbms_stats.set_system_stats('MBRC', 12);
end;
/

-- Anlage eines TS ohne ASSM
CREATE TABLESPACE test_ts
datafile 'C:\ORADATA\TESTDB\TEST_TS01.DBF' size 100M autoextend on maxsize unlimited
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;

-- Anlage einer Testtabelle + Statistikerhebung
create table test tablespace test_ts
as
select case when rownum <= 10000 then mod(rownum, 10) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test')

-- Infos zur Tabelle
SQL> exec show_space('TEST')

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.19
SQL> set serveroutput on
SQL> exec show_space('TEST')
Free Blocks.............................               0
Total Blocks............................          15,360
Total Bytes.............................     125,829,120
Total MBytes............................             120
Unused Blocks...........................             207
Unused Bytes............................       1,695,744
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          14,464
Last Used Block.........................             817

PL/SQL-Prozedur erfolgreich abgeschlossen.

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

(Anzahl_Blocks / MBRC) * (MREADTIM/SREADTIM)

also:

SQL> select (15360/12) * (30/5) from dual;

(15360/12)*(30/5)
-----------------
             7680

-- Testquery
SQL> select count(*) from test;

Abgelaufen: 00:00:00.06

Ausf³hrungsplan
--------------------------------------------------------
Plan hash value: 1950795681

--------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  7680   (2)|
|   1 |  SORT AGGREGATE    |      |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  7680   (2)|
--------------------------------------------------------

Das Ergebnis entspricht also genau den Erwartungen. Jetzt noch eine kleine Variante: ich setze MBRC auf 16:

exec dbms_stats.set_system_stats('MBRC', 16)

SQL> select (15360/16) * (30/5) from dual;

(15360/16)*(30/5)
-----------------
             5760


SQL> select count(*) from test;

Abgelaufen: 00:00:00.07

Ausf³hrungsplan
---------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5786   (2)|
|   1 |  SORT AGGREGATE    |      |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  5786   (2)|
---------------------------------------------------------

Keine große Abweichung, aber doch eine, über die ich noch nachdenken muss.

Nachtrag 29.04.2011: Bei erneuter Untersuchung des Themas ist mir der Rechenfehler aufgefallen: ich hatte die Blocks oberhalb der HWM eingerechnet. Mit der richtigen Rechnung erhält man dann auch das passende Ergebnis: (15360-207)/16 * 30/5 = 5682,375. Das entspricht dem autotrace-Ergebnis bei Auswahl eines extremen CPU-Speeds (ein Trick, den man in Randolf Geists Artikeln zum Thema findet):

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5682   (0)| 00:00:29 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  1000K|  5682   (0)| 00:00:29 |
-------------------------------------------------------------------