Oracle mechanics

03.03.2013

Index Join vs Index Bitmap и использование db file parallel read при доступе к блокам таблицы

Filed under: heuristics,hints,Oracle,wait events — Игорь Усольцев @ 21:26
Tags: , ,

Сравнение методов соединения индексов Index Join и Index Bitmap, релизуемых, например, подсказками /*+ INDEX_JOIN */ и /*+ INDEX_COMBINE */ , показывает, что оптимизатор отдаёт предпочтение Index Join, даже в случае проигрыша по стоимости

Далее приведены простые тесты индексных методов, а также попутно полученный тесткейс использования операции db file parallel read на шаге TABLE ACCESS BY INDEX ROWID плана выполнения

Тестовая схема:

SQL> create table t as select mod(rownum,10) x, mod(rownum,100) y from dual connect by level <= 1000   2  / Table created. SQL> create index t_x on t(x)
  2  /

Index created.

SQL> create index t_y on t(y)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

Тесты индексных методов соединения:

11.2.0.3.ORCL112@SCOTT SQL> set autotrace traceonly
SQL> select * from t where x = 3 and y = 33;

10 rows selected.

----------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |     6 |     3  (34)|
|*  1 |  VIEW              | index$_join$_001 |     1 |     6 |     3  (34)| -- без подсказок
|*  2 |   HASH JOIN        |                  |       |       |            | -- CBO выбирает Index Join
|*  3 |    INDEX RANGE SCAN| T_Y              |     1 |     6 |     1   (0)|
|*  4 |    INDEX RANGE SCAN| T_X              |     1 |     6 |     1   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=33 AND "X"=3)
   2 - access(ROWID=ROWID)
   3 - access("Y"=33)
   4 - access("X"=3)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads

SQL> select/*+ INDEX_JOIN(t t_x t_y)*/ * from t where x = 3 and y = 33;

10 rows selected.

----------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |     6 |     3  (34)|
|*  1 |  VIEW              | index$_join$_001 |     1 |     6 |     3  (34)|
|*  2 |   HASH JOIN        |                  |       |       |            |
|*  3 |    INDEX RANGE SCAN| T_Y              |     1 |     6 |     1   (0)|
|*  4 |    INDEX RANGE SCAN| T_X              |     1 |     6 |     1   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=33 AND "X"=3)
   2 - access(ROWID=ROWID)
   3 - access("Y"=33)
   4 - access("X"=3)

SQL> select/*+ INDEX_COMBINE(t t_x t_y) */ * from t where x = 3 and y = 33;

10 rows selected.

-----------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |     6 |     2   (0)| -- более дешёвый план
|   1 |  BITMAP CONVERSION TO ROWIDS    |      |     1 |     6 |     2   (0)| -- не выбирается без подсказки
|   2 |   BITMAP AND                    |      |       |       |            |
|   3 |    BITMAP CONVERSION FROM ROWIDS|      |       |       |            |
|*  4 |     INDEX RANGE SCAN            | T_Y  |       |       |     1   (0)|
|   5 |    BITMAP CONVERSION FROM ROWIDS|      |       |       |            |
|*  6 |     INDEX RANGE SCAN            | T_X  |       |       |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("Y"=33)
   6 - access("X"=3)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads

— по умолчанию оптимизатор отдаёт предпочтение методу Index [Hash] Join, несмотря на то, что стоимость плана с Bitmap меньше (2 < 3), статистика выполнения запроса с Bitmap Join также выглядит лучше — 4 против 5 consistent gets при прочих равных

Объяснение, которое можно найти в 10053 трейсе последнего запроса, использующего хинт /*+ INDEX_COMBINE(t t_x t_y) */:

  Access path: Bitmap index - accepted
    Cost: 2.212921 Cost_io: 2.210240 Cost_cpu: 54595.091546 Sel: 0.001000
    Not Believed to be index-only                               -- возможно, потребуется доступ к таблице
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexBitmap
         Cost: 2.21  Degree: 1  Resp: 2.21  Card: 1.00  Bytes: 0

— для приведённого простого запроса не выглядит убедительно — доступ осуществляется только по индексам

Но если немного усложнить тестовую схему, сделав 2-й индекс по 2-м столбцам и увеличив кол-во строк в таблице:

SQL> drop table t
  2  /

Table dropped.

SQL> create table t as select mod(rownum,10) x, mod(rownum,100) y, rpad('x',100) z from dual connect by level <= 1000000   2  / Table created. SQL> create index t_x on t(x)
  2  /

Index created.

SQL> create index t_y on t(y,z)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

легко увидеть идею правила Not Believed to be index-only:

SQL> select * from t where x = 3 and y = 33;

10000 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 2590112968

----------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |  1000 |   104K|   543   (1)|
|*  1 |  VIEW              | index$_join$_001 |  1000 |   104K|   543   (1)|
|*  2 |   HASH JOIN        |                  |       |       |            |
|*  3 |    INDEX RANGE SCAN| T_X              |  1000 |   104K|   198   (2)|
|*  4 |    INDEX RANGE SCAN| T_Y              |  1000 |   104K|   220   (1)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=33 AND "X"=3)
   2 - access(ROWID=ROWID)
   3 - access("X"=3)
   4 - access("Y"=33)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1018  consistent gets
          0  physical reads

SQL> select/*+ INDEX_JOIN(t t_x t_y)*/ * from t where x = 3 and y = 33;

10000 rows selected.

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 1581367360                               -- план отличается от предыдущего из-за разного порядка соединения индексов
                                                          -- и не зависит от порядка индексов в подсказке
----------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |  1000 |   104K|   543   (1)| -- стоимость запроса совпадает с предыдущей,
|*  1 |  VIEW              | index$_join$_001 |  1000 |   104K|   543   (1)| -- несмотря на отличные стоимости индексного доступа
|*  2 |   HASH JOIN        |                  |       |       |            |
|*  3 |    INDEX RANGE SCAN| T_Y              |  1000 |   104K|   165   (0)|
|*  4 |    INDEX RANGE SCAN| T_X              |  1000 |   104K|   198   (2)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=33 AND "X"=3)
   2 - access(ROWID=ROWID)
   3 - access("Y"=33)
   4 - access("X"=3)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1024  consistent gets
          0  physical reads

SQL> select/*+ INDEX_COMBINE(t t_x t_y) */ * from t where x = 3 and y = 33;

10000 rows selected.

Elapsed: 00:00:01.45

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591

------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |  1000 |   104K|   633   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |  1000 |   104K|   633   (1)| -- ***
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |
|   3 |    BITMAP AND                    |      |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |
|*  5 |      INDEX RANGE SCAN            | T_X  |       |       |   197   (1)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |
|   7 |      SORT ORDER BY               |      |       |       |            |
|*  8 |       INDEX RANGE SCAN           | T_Y  |       |       |   165   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("X"=3)
   8 - access("Y"=33)
       filter("Y"=33)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10363  consistent gets
      10000  physical reads

SQL> /

10000 rows selected.

Elapsed: 00:00:01.45

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10363  consistent gets
      10000  physical reads

SQL> /

10000 rows selected.

Elapsed: 00:00:01.42

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10363  consistent gets
      10000  physical reads

— после выполнения Bitmap Join в последнем запросе действительно потребовался доступ к таблице Т при использовании в соединении индекса по более чем одному столбцу. В отличие от метода Index Join, строящего свой виртуальный index$_join$_001 из всех проиндексированных столбцов
И опять же по умолчанию выбирается Index Join — на этот раз обоснованно и по стоимости, и по логике ;)

Интерес представляет ещё один момент — при повторных выполнениях последнего запроса с Bitmap Join количество physical reads не уменьшается, что нетипично для операции TABLE ACCESS BY INDEX ROWID
Статистика выполнения подтверждает, что выполняются именно физические чтения блоков таблицы Т, несмотря на то, что после выполнения в буферном кэше присутствует более половины необходимых блоков в статусе current:

SQL> set autotrace off arraysize 5000
SQL> alter session set statistics_level=all;

Session altered.

SQL> select/*+ INDEX_COMBINE(t t_x t_y) */ * from t where x = 3 and y = 33;
...
10000 rows selected.

Elapsed: 00:00:04.10
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','all allstats advanced -memstats +iostats -alias -projection last'));

Plan hash value: 1969528591

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |       |   633 (100)|          |  10000 |00:00:01.44 |   10363 |  10000 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |      1 |  10000 |  1044K|   633   (1)| 00:00:01 |  10000 |00:00:01.44 |   10363 |  10000 | -- чтения из таблицы Т
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |      1 |        |       |            |          |  10000 |00:00:00.06 |     363 |      0 |
|   3 |    BITMAP AND                    |      |      1 |        |       |            |          |      2 |00:00:00.06 |     363 |      0 |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |      1 |        |       |            |          |     11 |00:00:00.05 |     198 |      0 |
|*  5 |      INDEX RANGE SCAN            | T_X  |      1 |        |       |   197   (1)| 00:00:01 |    100K|00:00:00.03 |     198 |      0 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |      1 |        |       |            |          |      2 |00:00:00.01 |     165 |      0 |
|   7 |      SORT ORDER BY               |      |      1 |        |       |            |          |  10000 |00:00:00.01 |     165 |      0 |
|*  8 |       INDEX RANGE SCAN           | T_Y  |      1 |        |       |   165   (0)| 00:00:01 |  10000 |00:00:00.01 |     165 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T"@"SEL$1" AND(("T"."X") ("T"."Y" "T"."Z")))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("X"=3)
   8 - access("Y"=33)
       filter("Y"=33)

Note
-----
   - cardinality feedback used for this statement

SQL> @v$bh SCOTT T

OWNER                          OBJECT_NAME                    STATUS         BLK_COUNT
------------------------------ ------------------------------ ---------- -------------
SCOTT                          T                              xcur                6368 -- блоки таблицы Т в buffer cache
SCOTT                          T                              SUMMARY             6368

SQL> set autotrace traceonly
SQL> select/*+ INDEX_COMBINE(t t_x t_y) */ * from t where x = 3 and y = 33;

10000 rows selected.

Elapsed: 00:00:01.43

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |  1000 |   104K|   633   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |  1000 |   104K|   633   (1)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   3 |    BITMAP AND                    |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T_X  |       |       |   197   (1)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|   7 |      SORT ORDER BY               |      |       |       |            |          |
|*  8 |       INDEX RANGE SCAN           | T_Y  |       |       |   165   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("X"=3)
   8 - access("Y"=33)
       filter("Y"=33)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10363  consistent gets
      10000  physical reads
          0  redo size
      51080  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Кроме того, можно заметить положительную роль cardinality feedback: рельный план выполнения, демонстрируемый процедурой dbms_xplan.display_cursor точно оценивает ожидаемое кол-во строк E-Rows = 10000, в отличие от explain plan / autotrace.

Интересно, что при такой значительной разнице в оценках Plan hash value и стоимости в обоих планах в точности совпадают, т.е. стоимость операции TABLE ACCESS BY INDEX ROWID при выполнении Bimap Join не учитывается

Из статистики и ожиданий сессии можно видеть, что 10000 физических чтений реализуются с помощью 250 операций db file parallel read ~ по 40 блоков:

SQL> @sessof 11

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                        84893696
cell physical IO interconnect bytes                                  81920000
physical read bytes                                                  81920000
physical read total bytes                                            81920000
file io wait time                                                       78273
bytes sent via SQL*Net to client                                        51080
session logical reads                                                   10363
consistent gets                                                         10363
consistent gets from cache                                              10363
no work - consistent read gets                                          10358
consistent gets from cache (fastpath)                                   10107
free buffer inspected                                                   10052
buffer is not pinned count                                              10006
sorts (rows)                                                            10000
physical read total IO requests                                         10000
physical reads                                                          10000
physical read IO requests                                               10000
free buffer requested                                                   10000
table fetch by rowid                                                    10000
physical reads cache                                                    10000
buffer is pinned count                                                   9997
physical reads cache prefetch                                            9499 -- большая часть чтений засчитываются как prefetch
non-idle wait count                                                       510
bytes received via SQL*Net from client                                    365
DB time                                                                   142
non-idle wait time                                                        127
user I/O wait time                                                        126
hot buffers moved to head of LRU                                           82
CPU used by this session                                                   42
CPU used when call started                                                 28
dirty buffers inspected                                                    14
consistent gets - examination                                               5
user calls                                                                  4
Requests to/from client                                                     3
SQL*Net roundtrips to/from client                                           3
index scans kdiixs1                                                         2
workarea executions - optimal                                               2
enqueue requests                                                            1
enqueue releases                                                            1
parse count (hard)                                                          1
sorts (memory)                                                              1
execute count                                                               1
opened cursors cumulative                                                   1
calls to get snapshot scn: kcmgss                                           1
sql area evicted                                                            1
parse count (total)                                                         1
recursive calls                                                             1

Session Wait Events

NAME                              WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------- ---------- ------------ ------------ -----------
SQL*Net message from client           3        26613            0      8871,1
db file parallel read               250         1188            0         4,8
db file sequential read             251           78            0         0,3
SQL*Net more data to client           6            0            0         0,1
SQL*Net message to client             3            0            0           0

SQL трейс подтверждает что операция db file parallel read используется только для блоков таблицы Т (obj#=98344) и выполняется парами ожиданий: db file sequential read читает 1 блок + db file parallel read читает 39 блоков таким же кол-вом I/O запросов при arraysize=5000 и _table_lookup_prefetch_size=40 (статистика такого выполнения представлена выше):

WAIT #305124296: nam='db file sequential read' ela= 286 file#=4 block#=75203 blocks=1 obj#=98344 tim=406328133273
WAIT #305124296: nam='db file parallel read' ela= 6012 files=1 blocks=39 requests=39 obj#=98344 tim=406328139405
WAIT #305124296: nam='db file sequential read' ela= 292 file#=4 block#=76291 blocks=1 obj#=98344 tim=406328139897
WAIT #305124296: nam='db file parallel read' ela= 5856 files=1 blocks=39 requests=39 obj#=98344 tim=406328145867

при arraysize=5000, _table_lookup_prefetch_size=100 параллельно читается по 99 блоков тем же кол-вом запросов:

WAIT #1: nam='db file scattered read' ela= 367 file#=4 block#=290 blocks=2 obj#=98344 tim=426442611707
WAIT #1: nam='db file parallel read' ela= 22796 files=1 blocks=99 requests=99 obj#=98344 tim=426442634901
WAIT #1: nam='db file scattered read' ela= 315 file#=4 block#=446 blocks=2 obj#=98344 tim=426442636034
WAIT #1: nam='db file parallel read' ela= 22660 files=1 blocks=99 requests=99 obj#=98344 tim=426442659065

при arraysize = 15, _table_lookup_prefetch_size=40 (значения по умолчанию) параллельно читается по 14 блоков с частыми FETCH-ами по 15 строк:

FETCH #4:c=0,e=3896,p=15,cr=15,cu=0,mis=0,r=15,dep=0,og=1,plh=1969528591,tim=425236293168
WAIT #4: nam='SQL*Net message from client' ela= 223 driver id=1111838976 #bytes=1 p3=0 obj#=98344 tim=425236293429
WAIT #4: nam='db file parallel read' ela= 3009 files=1 blocks=14 requests=14 obj#=98344 tim=425236296507
WAIT #4: nam='db file sequential read' ela= 230 file#=4 block#=158 blocks=1 obj#=98344 tim=425236296856
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=98344 tim=425236296955
FETCH #4:c=0,e=3562,p=15,cr=15,cu=0,mis=0,r=15,dep=0,og=1,plh=1969528591,tim=425236297023

В последнем случае можно заметить ожидание в ASH:

SQL> select sample_time, current_obj#, p1, p1text, p2, p2text, p3, p3text
  2    from v$active_session_history
  3   where sql_id = '3gqgw2y0jm1w4'
  4     and event = 'db file parallel read'
  5     and sql_exec_id = (select max(sql_exec_id)
  6                          from v$active_session_history ash
  7                         where sql_id = '3gqgw2y0jm1w4')
  8  /

SAMPLE_TIME               CURRENT_OBJ#  P1 P1TEXT  P2 P2TEXT  P3 P3TEXT
------------------------- ------------ --- ------ --- ------ --- --------
29-FEB-13 04.17.16.798 PM        98344   1 files   14 blocks  14 requests

SQL> select object_type, object_name from dba_objects where object_id = 98344
  2  /

OBJECT_TYPE  OBJECT_NAME
------------ -----------
TABLE        T

В описанных тестах ожидание db file parallel read отражает одновременное физическое чтение по min(arraysize, _table_lookup_prefetch_size)-1 блоков таблицы с помощью того же количества запросов (requests) после одного одноблочного чтения — db file sequential read

При использовании db file parallel read считываются только необходимые блоки таблицы:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) from t where x = 3 and y = 33;
 
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
                         10000 -- кол-во блоков таблицы, которые нужно считать для выполнения запроса

Для выполнения кейса необходимо, чтобы объём буферного кэша не превышал 75% от объёма таблицы

Например, описанный тесткейс:

SQL> select segment_name, blocks, bytes from user_segments where segment_name = 'T';

SEGMENT_NAME     BLOCKS      BYTES
------------ ---------- ----------
T                 16384  134217728 -- 128 MB

— при размере таблицы 128 МБ — предсказуемо воспроизводится при  db_cache_size <= 96M и не поспроизводится при бОльшем размере буфферного кэша:

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_cache_size                        big integer 96M

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
shared_pool_size                     big integer 188M

SQL> alter system set shared_pool_size=187M scope=spfile;

System altered.

SQL> alter system set db_cache_size=97M scope=spfile; -- увеличение кэша

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  334073856 bytes
Fixed Size                  2254904 bytes
Variable Size             222300104 bytes
Database Buffers          104857600 bytes
Redo Buffers                4661248 bytes
Database mounted.
Database opened.

SQL> select/*+ INDEX_COMBINE(t t_x t_y) */ * from t where x = 3 and y = 33;

10000 rows selected.

Elapsed: 00:00:01.50

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591                               -- тот же план выполнения

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |  1000 |   104K|   633   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |  1000 |   104K|   633   (1)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   3 |    BITMAP AND                    |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T_X  |       |       |   197   (1)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|   7 |      SORT ORDER BY               |      |       |       |            |          |
|*  8 |       INDEX RANGE SCAN           | T_Y  |       |       |   165   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("X"=3)
   8 - access("Y"=33)
       filter("Y"=33)

Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
      10429  consistent gets
      10653  physical reads
...
      10000  rows processed

SQL> /

10000 rows selected.

Elapsed: 00:00:00.12

SQL> /

10000 rows selected.

Elapsed: 00:00:00.11         -- отличное время выполнения

Execution Plan
----------------------------------------------------------
Plan hash value: 1969528591

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10363  consistent gets
          0  physical reads  -- при отсутствии физических чтений

— в этом случае данные таблицы читаются по одному блоку традиционной операцией db file sequential read с использованием стандартных механизмов кэширования

6 комментариев »

  1. Игорь, здравствуйте!
    Не могли бы Вы прояснить: что вообще из себя представляет этот самый «parallel read»?
    Это prefetch в прямом смысле слова (считать следующие N блоков; например: _table_lookup_prefetch_size / arraysize), или же из каждого index leaf block’а вычитываются rowid табличных записей, после чего делается запрос на все блоки, ссылаемые через эти rowid? Если последнее: то как Oracle’у удаётся считать в вашем случае 10К требуемых блоков (по статистике physical reads) всего за 250 parallel read’ов? Ведь тогда бы, по идее, табличные блоки приходилось бы перечитывать не раз, поскольку на один табличный блок ссылались бы записи из нескольких индексных блоков, а блоки полученные в результате parallel read’а, как показывает ваш эксперимент, не кэшируются. Либо же требовалось бы считывать сначала все необходимые блоки из индекса и сортировать rowid записей, чтобы получить список уникальных табличных локов для дальнейшей обработки. Но такой способ обработки (сбор и последующая сортировка rowid из индекса) было бы слегка странным для обработки больших (больше буфферного кэша) таблиц: потребовались бы очень значимые объёмы памяти, к примеру, в PGA.

    комментарий от Пётр — 06.03.2013 @ 10:36 | Ответить

    • Приветствую, Пётр

      db file parallel read при доступе к блокам таблицы — это не традиционный prefetch, имхо:
      в процессе чтения/объединения индексов получается список ROWID, на шаге BITMAP CONVERSION TO ROWIDS этот список упорядочивается/сортируется
      >> , после чего делается запрос на все блоки, ссылаемые через эти rowid
      — в смысле, несколько запросов

      >> как Oracle’у удаётся считать в вашем случае 10К требуемых блоков (по статистике physical reads) всего за 250 parallel read’ов
      каждая операция db file parallel read функционально состоит из 39 (или точнее, min(_table_lookup_prefetch_size / arraysize)-1) отдельных асинхронных запросов на одноблочное чтение — в соответствии с параметром requests ожидания и завершается когда все эти операции будут выполнены
      Соответсвенно, среднее время ожидания db file parallel read — 4.8 мс — намного превышает ср.время db file sequential read — 0.3 мс в примере

      >> такой способ обработки (сбор и последующая сортировка rowid из индекса) было бы слегка странным для обработки больших (больше буфферного кэша) таблиц
      возможно, поэтому Oracle нечасто использует db file parallel read при доступе к блокам таблицы.
      Понятно, что при больших объёмах есть методы (FULL сканы, hash join, и т.д) гораздо более эффективные любых индексных доступов

      комментарий от Igor Usoltsev — 06.03.2013 @ 13:43 | Ответить

  2. Игорь,
    Большое спасибо за ответ!
    Но позвольте уточнить, правильно ли я Вас понял: db file parallel read для табличных блоков будут использоваться только в том случае, если на предыдущей стадии запроса были операции, сортирующие/объединяющие rowid записей (как BITMAP CONVERSION TO ROWIDS в данном случае)?
    Вообще говоря, сортировка/объединение rowid производится и в результате NESTED LOOPS batching’а (http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94641); следует ли ожидать parallel read’ов и в таком случае?

    комментарий от Пётр — 06.03.2013 @ 16:45 | Ответить

    • я бы сказал, что в описанном воспроизводимом случае db file parallel read для табличных блоков действительно используются после операции, сортирующей/объединяющей rowid записей (как BITMAP CONVERSION TO ROWIDS в данном случае). В любом случае перед выполнением операция parallel read должна получить готовый список rowid

      В случае NESTED LOOPS batching’а — не уверен, т.к. эта оптимизация, судя по описанию, перед выполнением анализирует наличие блоков в buffer cache. А вот предыдущая инкарнация — Nested Loops Prefetching — точно умеет использовать — см. Table Prefetching causes intermittent Wrong Results in 9iR2,10gR1 and 10gR2 [ID 406966.1]: «If prefetching is used some IO reads could be done with wait event ‘db file parallel reads’…»

      комментарий от Igor Usoltsev — 07.03.2013 @ 00:44 | Ответить

    • ошибся, в случае NESTED LOOPS batching’а db file parallel read используется:

      11.1.0.7.@ SQL> select sql_id,
        2         sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options,
        3         p1text, p2, p2text, p2, p3text, p3,
        4         count(*)
        5    from v$active_session_history
        6   where event = 'db file parallel read'
        7   group by sql_id,
        8            sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options,
        9            p1text, p2, p2text, p2, p3text, p3
       10   having count(*) > 100
       11   order by count(*) desc
       12  /
       
      SQL_ID        SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS P1TEXT  P2 P2TEXT  P2 P3TEXT    P3 COUNT(*)
      ------------- ------------------- ---------------- ------------------ ---------------- ------ --- ------ --- -------- --- --------
      82pwc5tfqy6tk            46819910                8 TABLE ACCESS       BY INDEX ROWID   files    2 blocks   2 requests   2     1459
      82pwc5tfqy6tk            46819910                8 TABLE ACCESS       BY INDEX ROWID   files    3 blocks   3 requests   3      851
      82pwc5tfqy6tk            46819910                8 TABLE ACCESS       BY INDEX ROWID   files    4 blocks   4 requests   4      363
      82pwc5tfqy6tk            46819910                8 TABLE ACCESS       BY INDEX ROWID   files    5 blocks   5 requests   5      118
      3vjyd8vyhg0k3           332395911                1 DELETE                              files    2 blocks   2 requests   2      114
      
      SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('82pwc5tfqy6tk',46819910));
      
      ---------------------------------------------------------------------------------------------------
      | Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                |                 |       |       |     3 (100)|          |
      |   1 |  SORT ORDER BY                  |                 |     1 |   109 |     3  (34)| 00:00:01 |
      |   2 |   FILTER                        |                 |       |       |            |          |
      |   3 |    NESTED LOOPS                 |                 |       |       |            |          |
      |   4 |     NESTED LOOPS                |                 |     1 |   109 |     2   (0)| 00:00:01 |
      |   5 |      TABLE ACCESS BY INDEX ROWID| ENTITY          |     1 |    10 |     1   (0)| 00:00:01 |
      |   6 |       INDEX RANGE SCAN          | SYS_C00100646   |     1 |       |     1   (0)| 00:00:01 |
      |   7 |      INDEX RANGE SCAN           | IDX_ENTITY_ATTR |     1 |       |     1   (0)| 00:00:01 |
      |   8 |     TABLE ACCESS BY INDEX ROWID | ENTITY_ATTR     |     1 |    99 |     1   (0)| 00:00:01 | -- DB FILE PARALLEL READ используется здесь
      ---------------------------------------------------------------------------------------------------
      
      SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('3vjyd8vyhg0k3',332395911));
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------
      SQL_ID 3vjyd8vyhg0k3
      --------------------
      delete from entity_attr where entity_id = :1
      
      Plan hash value: 332395911
      
      -------------------------------------------------------------------------------------
      | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------
      |   0 | DELETE STATEMENT  |                 |       |       |     1 (100)|          |
      |   1 |  DELETE           | ENTITY_ATTR     |       |       |            |          | -- и здесь, фактически выполняется TABLE ACCESS BY INDEX ROWID
      |   2 |   INDEX RANGE SCAN| IDX_ENTITY_ATTR |    15 |   435 |     1   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------

      Особенности: таблица ENTITY_ATTR содержит CLOB, блоки таблицы читаются небольшими порциями — от 2 до 5 блоков из разных файлов бд, т.е. действительно batching

      комментарий от Igor Usoltsev — 08.03.2013 @ 00:13 | Ответить

      • Игорь,
        Огромное спасибо за разъяснения!

        комментарий от Пётр — 11.03.2013 @ 14:02 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: