FTS и количество блоков

Есть

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

Некоторые существенные параметры

SQL> select name, value from v$parameter where name in ('db_block_size', 'db_file_multiblock_read_count');

NAME                                                             VALUE
---------------------------------------------------------------- 
db_block_size                                                    8192
db_file_multiblock_read_count                                     8

Тестовые данные

SQL> create table t(x int, s varchar2(100));

Table created

SQL> insert into t(x) select level from dual connect by level <= 1000000;

1000000 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => true);

PL/SQL procedure successfully completed

SQL> select avg_row_len, blocks, num_rows from user_tables a where a.table_name = 'T';

AVG_ROW_LEN     BLOCKS   NUM_ROWS
----------- ---------- ----------
          4       1519    1000000

Т.е. в табличке у нас 1519 блоков.
Смотрим планы "фигурантов"

SQL> explain plan for select count(*) from t;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |       |   232 |
|   1 |  SORT AGGREGATE      |             |     1 |       |       |
|   2 |   TABLE ACCESS FULL  | T           |  1000K|       |   232 |
--------------------------------------------------------------------

Note: cpu costing is off

10 rows selected

SQL> rollback;

Rollback complete

SQL> explain plan for select * from t;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1000K|  3906K|   232 |
|   1 |  TABLE ACCESS FULL   | T           |  1000K|  3906K|   232 |
--------------------------------------------------------------------

Note: cpu costing is off

9 rows selected

SQL> rollback;

Rollback complete

Все, вроде, понятно - стоимость получена как ceil(1519/6.59 (скорректированный mbrc)) + _tablescan_cost_plus_one = 232. Т.е. Oracle предполагает в обоих случаях чтение 1519 блоков.
Теперь попытаемся выполнить.

1 Вариант с count(*)

SQL> select count(*) from t;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=232 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=232 Card=1000000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1523  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

1523 LIO - ну плюс, минус трамвайная остановка ~ 1519 блокам в таблице

2 Вариант с select *

SQL> select * from t;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=232 Card=1000000 Byt
          es=4000000)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=232 Card=1000000 Bytes=40
          00000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      68190  consistent gets
          0  physical reads
          0  redo size
   18046856  bytes sent via SQL*Net to client
     733825  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

LIO 68190 (!!!) - (где он их взял то?)

Усомнился, что-то я в секции Statistics, и решил посмотреть еще

select distinct
    last_cr_buffer_gets 
from 
    v$sql_plan_statistics_all 
where 
    address = (select address from v$sqlarea a where a.SQL_TEXT like 'select * from t');

LAST_CR_BUFFER_GETS
-------------------
              68190

Ладно, снимаем трассировку

SQL> alter session set max_dump_file_size=unlimited; 

Session altered.

SQL> alter session set tracefile_identifier = 'FTS';

Session altered.

SQL> alter session set timed_statistics=true; 

Session altered.

SQL> alter session set events '10046 trace name context forever, level 8'

Session altered.

SQL> select * from t;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=232 Card=1000000 Byt
          es=4000000)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=232 Card=1000000 Bytes=40
          00000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      68190  consistent gets
          0  physical reads
          0  redo size
   18046856  bytes sent via SQL*Net to client
     733825  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> alter session set events '10046 trace name context off';

Смотрим файл:

APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=234 oct=42 lid=234 tim=14063328572 hv=2212335334 ad='25100598'
alter session set events '10046 trace name context forever, level 8'
END OF STMT
EXEC #1:c=0,e=142,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=14063227872
WAIT #1: nam='SQL*Net message to client' ela= 7 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 7377751 p1=1413697536 p2=1 p3=0
*** SESSION ID:(30.3288) 2007-02-26 09:46:26.250
=====================
PARSING IN CURSOR #1 len=15 dep=0 uid=234 oct=3 lid=234 tim=14070921320 hv=520543201 ad='3def1454'
select * from t
END OF STMT

...
тыщ 200 строк вида
FETCH #1:c=0,e=144,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=4,tim=14070922996
WAIT #1: nam='SQL*Net message from client' ela= 450 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0

Других событий ожидания нет
...

STAT #1 id=1 cnt=1000000 pid=0 pos=1 obj=62556 op='TABLE ACCESS FULL T (cr=68190 r=0 w=0 time=1526322 us)'

...
ниже тоже ничего интересного нет - формирование вывода autotrace'a

Т.е. select * from t читает 68190, хотя их в таблице всего то 1519. Собственно, вопрос: где он их берет и главное - зачем?

Спасибо за проскролливание :-)

Part

Собственно, вопрос: где он их берет и главное - зачем?

set arraysize <N>

поиграйте с разными N :)

В блоке примерно 658 строк, один вызов FETCH выбирает N строк, всего выбирать 1М строк, LIO=1519*(658/N)

видимо здесь такая арифметика

YAP

В блоке примерно 658 строк, один вызов FETCH выбирает N строк, всего выбирать 1М строк, LIO=1519*(658/N) видимо здесь такая арифметика

Каждый фетч это LIO.

Дефолтовый arraysize = 15, 1000000/15 ~ 66666 LIO.

Остальное LIO (68190 - 66666) пришлось на "строки из одного фетча в двух блоках" и прочие "мелочи".

YAP

В блоке примерно 658 строк, один вызов FETCH выбирает N строк, всего выбирать 1М строк, LIO=1519*(658/N) видимо здесь такая арифметика

Судя по

FETCH #1:c=0,e=144,p=0,cr=4,cu=0,mis=0, --->r=1<---,dep=0,og=4,tim=14070922996

N=1
Тогда LIO=1519*(658/1) = 999502, что в 14.66 раза больше продемонстрированных 68190 ;)

Q u a d r o

Part Собственно, вопрос: где он их берет и главное - зачем? set arraysize <N> поиграйте с разными N :)

Спасибо, вопрос снят.
Совсем забыл про arraysize :(.

contr

YAP В блоке примерно 658 строк, один вызов FETCH выбирает N строк, всего выбирать 1М строк, LIO=1519*(658/N) видимо здесь такая арифметика Судя по FETCH #1:c=0,e=144,p=0,cr=4,cu=0,mis=0, --->r=1<---,dep=0,og=4,tim=14070922996 N=1 Тогда LIO=1519*(658/1) = 999502, что в 14.66 раза больше продемонстрированных 68190 ;)

Это я первый fetch "куснул", в остальных r = 15

учитывая разнообразные "краевые" эффекты - эта арифметика приблизительная

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
Thomas Kyte

p. 442

THE EFFECT OF ARRAYSIZE ON LOGICAL I/O It is interesting to note the effect of the ARRAYSIZE on logical I/O performed. ARRAYSIZE is the number of rows Oracle returns to a client when they ask for the next row. The client will then buffer these rows and use them before asking the database for the next set of rows. The ARRAYSIZE may have a very material affect on the logical I/O performed by a query, resulting from the fact that if you have to access the same block over and over again across calls (across fetch calls specifically in this case) to the database, Oracle must retrieve that block again from the buffer cache. Therefore, if you ask for 100 rows from the database in a single call, Oracle might be able to fully process a database block and not need to retrieve that block again. If you ask for 15 rows at a time, Oracle might well have to get the same block over and over again to retrieve the same set of rows. In the example earlier in this section, we were using SQL*Plus’s default array fetch size of 15 rows (if you divide the total rows fetched by the number of fetch calls, the result is very close to 15). If we were to compare the execution of the previous queries using 15 rows per fetch versus 100 rows per fetch, we would observe the following for the COLOCATED table:...