Oracle mechanics

05.04.2012

SORT UNIQUE NOSORT и кэширование индексных блоков

Filed under: CBO,commonplace,Oracle,parameters — Игорь Усольцев @ 00:14
Tags: ,

Как мне показалось, эта операция считается немного устаревшей (появилась в Oracle 7.3.4) и по умолчанию при запросах неповторяющихся значений (select distinct) активно замещается более современной и, казалось бы, более быстрой операцией HASH UNIQUE, которая в связке с быстрым многоблочным чтением INDEX FAST FULL SCAN должна творить чудеса:)

11.2.0.3.ORCL112@SCOTT SQL> select distinct object_type from t1;

------------------------------------------------
| Id  | Operation             | Name           |
------------------------------------------------
|   0 | SELECT STATEMENT      |                |
|   1 |  HASH UNIQUE          |                |
|   2 |   INDEX FAST FULL SCAN| T1_OBJECT_TYPE |
------------------------------------------------

Однако, такой выбор может быть не всегда оправдан, учитывая недорогую память и, как следствие, высокий процент кэширования индексных блоков, сообщить о котором оптимизатору не всегда просто.

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

SQL> create table t1 as select * from all_objects o, (select * from dual connect by rownum <= 100) c;

Table created.

SQL> create index t1_object_type on t1(object_type);

Index created.

SQL> alter table T1 modify (OBJECT_TYPE not null);

Table altered.

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

PL/SQL procedure successfully completed.

При дефолтных значениях основных параметров, связанных с индексным доступом:

SQL> @param optimizer_index

NAME                                       VALUE
------------------------------------------ -------
optimizer_index_caching                    0
optimizer_index_cost_adj                   100

оптимизатор в расчёте на получение преимуществ от INDEX FAST FULL SCAN перед одноблочным INDEX FULL SCAN, как упоминалось, выбирает HASH UNIQUE

И этот расчёт оправдывается, но только при чтении индексных блоков с диска:

SQL> alter system flush buffer_cache;

System altered.

SQL> select distinct object_type from t1;

Elapsed: 00:00:02.18

Execution Plan
----------------------------------------------------------
Plan hash value: 3780191578

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   1 |  HASH UNIQUE          |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   2 |   INDEX FAST FULL SCAN| T1_OBJECT_TYPE |  7256K|    62M|  5412   (1)| 00:01:05 |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19982  consistent gets
19961  physical reads

SQL> alter system flush buffer_cache;

System altered.

SQL> select/*+ index(T1 T1_OBJECT_TYPE)*/ distinct object_type from t1;

Elapsed: 00:00:04.10

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    39 |   351 | 20244   (3)| 00:04:03 |
|   1 |  SORT UNIQUE NOSORT|                |    39 |   351 | 20244   (3)| 00:04:03 |
|   2 |   INDEX FULL SCAN  | T1_OBJECT_TYPE |  7256K|    62M| 19877   (1)| 00:03:59 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19909  consistent gets
19906  physical reads

— и даже предсказанное в колонке плана Time время выполнения в последнем случае похоже на правду)

Но в «живых» системах полное чтение индекса с диска встречается нечасто, но даже если блоки индекса находятся в SGA, Oracle по-прежнему выбирает уже не столь выигрышный HASH INIQUE:

SQL> select distinct object_type from t1;

39 rows selected.

Elapsed: 00:00:01.12

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   1 |  HASH UNIQUE          |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   2 |   INDEX FAST FULL SCAN| T1_OBJECT_TYPE |  7256K|    62M|  5412   (1)| 00:01:05 |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19982  consistent gets
0  physical reads

— в случае с полностью кэшированным индексом — 0  physical reads — такой расчёт неоптимален, многоблочное чтение из буфера в самом лучшем случае вряд ли даёт заметное преимущество не даёт преимуществ — блоки читаются по-одному, а HASH сортировка потребляет заметное количество ресурсов ЦПУ, в отличие от SORT UNIQUE NOSORT, фильтрующей уникальные значения по мере поштучного поступления блоков от INDEX FULL SCAN:

SQL> select/*+ index(T1 T1_OBJECT_TYPE)*/ distinct object_type from t1;

39 rows selected.

Elapsed: 00:00:00.88

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    39 |   351 | 20244   (3)| 00:04:03 |
|   1 |  SORT UNIQUE NOSORT|                |    39 |   351 | 20244   (3)| 00:04:03 |
|   2 |   INDEX FULL SCAN  | T1_OBJECT_TYPE |  7256K|    62M| 19877   (1)| 00:03:59 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19909  consistent gets
0  physical reads

— что стабильно отражается в лучшем времени выполнения (и статистиках CPU usage), при естественно практически совпадающем количестве буферных чтений. Точнее, число consistent gets при операции HASH UNIQUE всегда немного больше — дополнительные чтения блоков индекса выполняются в процессе hash-сортировки функционально, имхо. Кроме того, в статистике сессии, выполняющей HASH UNIQUE можно найти:

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
calls to kcmgcs                                                            22

— вызовы процедуры получения current SCN, отсутствующие при выполнении запроса, использующего SORT UNIQUE NOSORT

Для сообщения оптимизатору % кэшированных блоков индекса было бы логично использовать параметр optimizer_index_caching:

SQL> alter session set optimizer_index_caching = 100;

Session altered.

SQL> select distinct object_type from t1;

39 rows selected.

-----------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |    39 |   351 |  5779   (8)|
|   1 |  HASH UNIQUE          |                |    39 |   351 |  5779   (8)|
|   2 |   INDEX FAST FULL SCAN| T1_OBJECT_TYPE |  7256K|    62M|  5412   (1)|
-----------------------------------------------------------------------------

— к сожалению, это никак не влияет ни на стоимость, ни на выбор методов доступа.

Остаётся буквально поверить документации:

You can modify the optimizer’s assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter…

— параметр OPTIMIZER_INDEX_CACHING влияет на расчёты, касающиеся только перечисленных операций:

SQL> alter session set optimizer_index_caching = 100;

Session altered.

SQL> select distinct object_type from t1 where object_type in ('PACKAGE','PACKAGE BODY');

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     2 |    18 |    20 (100)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|                |     2 |    18 |    20 (100)| 00:00:01 |
|   2 |   INLIST ITERATOR  |                |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| T1_OBJECT_TYPE |   372K|  3270K|     4 (100)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> alter session set optimizer_index_caching = 0;

Session altered.

SQL> select distinct object_type from t1 where object_type in ('PACKAGE','PACKAGE BODY');

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     2 |    18 |  1031   (2)| 00:00:13 |
|   1 |  SORT UNIQUE NOSORT|                |     2 |    18 |  1031   (2)| 00:00:13 |
|   2 |   INLIST ITERATOR  |                |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| T1_OBJECT_TYPE |   372K|  3270K|  1015   (1)| 00:00:13 |
-------------------------------------------------------------------------------------

На поведение первоначального запроса хорошо влияет изменение параметра OPTIMIZER_INDEX_COST_ADJ:

SQL> alter session set optimizer_index_cost_adj = 10;

Session altered.

SQL> select distinct object_type from t1;

39 rows selected.

Elapsed: 00:00:00.92

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    39 |   351 |  2355  (16)| 00:00:29 |
|   1 |  SORT UNIQUE NOSORT|                |    39 |   351 |  2355  (16)| 00:00:29 |
|   2 |   INDEX FULL SCAN  | T1_OBJECT_TYPE |  7256K|    62M|  1988   (1)| 00:00:24 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19909  consistent gets
0  physical reads

SQL> select/*+ index_ffs(T1 T1_OBJECT_TYPE)*/ distinct object_type from t1;

39 rows selected.

Elapsed: 00:00:01.15

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   1 |  HASH UNIQUE          |                |    39 |   351 |  5779   (8)| 00:01:10 |
|   2 |   INDEX FAST FULL SCAN| T1_OBJECT_TYPE |  7256K|    62M|  5412   (1)| 00:01:05 |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
19982  consistent gets
0  physical reads

— пропорционально снижающий стоимость операции одноблочного чтения INDEX FULL SCAN, и никак не меняющий стоимость многоблочного INDEX FAST FULL SCAN, использование которого теперь приходится стимулировать подсказкой INDEX_FFS

3 комментария »

  1. Я думаю несколько неправильно так сравнивать sort unique nosort и hash unique, т.к. доступ может быть не только IFS и IFFS, а операции разные и в целом наиболее важным является скорее NOSORT, а не выбор одноблочных или многоблочных чтений, которые имеет смысл сравнивать только в строго конкретных случаях.
    В целом имхо важнее сравнивать механизм самого SORT UNIQUE и HASH UNIQUE, вне зависимости от характера чтений, т.к. и тот и другой могут быть и scattered и sequential.

    Например, Hash unique по IRS:

    DB11G/XTENDER> explain plan for
      2  select distinct b
      3  from t
      4  where a @xplan
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    Plan hash value: 3538597068
    -----------------------------------------------------------------------------
    | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |         |   100 |   800 |     5  (20)| 00:00:01 |
    |   1 |  HASH UNIQUE      |         |   100 |   800 |     5  (20)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN| IX_T_AB |   998 |  7984 |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          USE_HASH_AGGREGATION(@"SEL$1")
          INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A" "T"."B"))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.1')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("A"<1000)
    

    Или sort unique по IFFS и IRS:

    DB11G/XTENDER> explain plan for
      2  select
      3   b
      4  from t
      5  where a700 and b @xplan
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------
    
    Plan hash value: 3972410813
    
    ----------------------------------------------------------------------------------
    | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |         |   998 | 88360 |    80  (95)| 00:00:01 |
    |   1 |  MINUS                 |         |       |       |            |          |
    |   2 |   SORT UNIQUE          |         |   998 |  7984 |     5  (20)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN    | IX_T_AB |   998 |  7984 |     4   (0)| 00:00:01 |
    |   4 |   SORT UNIQUE          |         | 10047 | 80376 |    75   (3)| 00:00:01 |
    |*  5 |    INDEX FAST FULL SCAN| IX_T_AB | 10047 | 80376 |    74   (2)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A" "T"."B"))
          INDEX_FFS(@"SEL$2" "T"@"SEL$2" ("T"."A" "T"."B"))
          OUTLINE_LEAF(@"SET$1")
          OUTLINE_LEAF(@"SEL$2")
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.1')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("A"<1000)
       5 - filter("B"700)
    
    

    комментарий от xtender — 06.04.2012 @ 01:02 | Ответить

    • Тестовая табличка на которой показывал пример:

      drop table t purge;
      create table t nologging
      as 
      select 
             level a
            ,mod(level,100) b
            ,date'2000-01-01'+level/24 c
            ,lpad('a',100,'a') d
      from dual
      connect by level user,                     
           tabname           => 'T',
           estimate_percent  => 30,           
           method_opt        => 'FOR ALL COLUMNS SIZE 1'
        );
      END;
      /
      

      комментарий от xtender — 06.04.2012 @ 01:06 | Ответить

      • Спасибо, Саян, за пример HASH UNIQUE с INDEX RANGE SCAN,
        но я сравнивал именно sort unique NOSORT (без сортировки) и сортировку hash unique
        Немного исправив пример для соответствующего сравнения можно заметить другие особенности:

        11.2.0.3.ORCL112@SCOTT SQL> create table t nologging
          2  as
          3  select
          4         level a
          5        ,mod(level,100) b
          6        ,to_date('2000-01-01','YYYY-MM-DD')+level/24 c
          7        ,lpad('a',100,'a') d
          8  from dual
          9  connect by level  create index IX_T_AB on T(a,b);
        
        Index created.
        
        SQL> alter table T modify (A not null);
        
        Table altered.
        
        SQL> exec dbms_stats.gather_table_stats('','T');
        
        PL/SQL procedure successfully completed.

        При стандартных значениях ARRAYSIZE и параметров оптимизатор выбирает HASH UNIQUE и планирует для этого использовать 15M временного табличного пр-ва:

        SQL> show arraysize
        arraysize 15
        SQL> @param optimizer_index
        
        NAME                                       VALUE
        ------------------------------------------ -----
        optimizer_index_caching                    0    
        optimizer_index_cost_adj                   100  
        
        SQL> select/*+ gather_plan_statistics*/ distinct a,b from t;
        
        1000000 rows selected.
        
        Elapsed: 00:00:05.87
        
        -----------------------------------------------------------------------------------------
        | Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
        -----------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT      |         |  1000K|  7812K|       |  4362   (2)| 00:00:53 |
        |   1 |  HASH UNIQUE          |         |  1000K|  7812K|    15M|  4362   (2)| 00:00:53 |
        |   2 |   INDEX FAST FULL SCAN| IX_T_AB |  1000K|  7812K|       |   725   (1)| 00:00:09 |
        -----------------------------------------------------------------------------------------
        
        
        Statistics
        ----------------------------------------------------------
        ...
               2660  consistent gets
                  0  physical reads
        ...
              66668  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
            1000000  rows processed

        Однако TEMP не требуется, запрос использует 34M PGA:

        SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'g1h3p7a5f5t20','1','basic allstats last'));
        
        ----------------------------------------------------------------------------------------------------------------------
        | Id  | Operation             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
        ----------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT      |         |      1 |        |   1000K|00:00:00.78 |    2660 |       |    |             |
        |   1 |  HASH UNIQUE          |         |      1 |   1000K|   1000K|00:00:00.78 |    2660 |    33M|  6593K|   34M (0)|
        |   2 |   INDEX FAST FULL SCAN| IX_T_AB |      1 |   1000K|   1000K|00:00:00.29 |    2660 |       |    |             |
        ----------------------------------------------------------------------------------------------------------------------

        После изменения optimizer_index_cost_adj с целью стимулировать использование SORT UNIQUE NOSORT:

        SQL> alter session set optimizer_index_cost_adj = 10;
        
        Session altered.
        
        SQL> select/*+ gather_plan_statistics*/ distinct a,b from t;
        
        1000000 rows selected.
        
        Elapsed: 00:00:06.20
        
        ------------------------------------------------------------------------------
        | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |         |  1000K|  7812K|  3903   (2)| 00:00:47 |
        |   1 |  SORT UNIQUE NOSORT|         |  1000K|  7812K|  3903   (2)| 00:00:47 |
        |   2 |   INDEX FULL SCAN  | IX_T_AB |  1000K|  7812K|   266   (1)| 00:00:04 |
        ------------------------------------------------------------------------------
        
        Statistics
        ----------------------------------------------------------
        ...
              69314  consistent gets
                  0  physical reads
        ...
              66668  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
            1000000  rows processed
        
        SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'g1h3p7a5f5t20','0','basic allstats last'));
        
        ----------------------------------------------------------------------------------------
        | Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
        ----------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |         |      1 |        |   1000K|00:00:00.91 |   69314 |
        |   1 |  SORT UNIQUE NOSORT|         |      1 |   1000K|   1000K|00:00:00.91 |   69314 |
        |   2 |   INDEX FULL SCAN  | IX_T_AB |      1 |   1000K|   1000K|00:00:00.51 |   69314 |
        ----------------------------------------------------------------------------------------

        — запросу не требуется дополнительная память при выполнении — ни в PGA, ни в TEMP, что также может быть фактором экономии ресурсов — но количество прочитанных блоков buffer cache
        возрастает с 2660 consistent gets (при выполнении с HASH UNIQUE) до 69314 для SORT UNIQUE NOSORT !
        Что является забавным следствием влияния маленького ARRAYSIZE для запроса, использующего SORT UNIQUE NOSORT и возвращающего много строк:

        SQL> set arraysize 5000
        SQL> select/*+ gather_plan_statistics*/ distinct a,b from t;
        
        1000000 rows selected.
        
        Elapsed: 00:00:01.22
        
        ------------------------------------------------------------------------------
        | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |         |  1000K|  7812K|  3903   (2)| 00:00:47 |
        |   1 |  SORT UNIQUE NOSORT|         |  1000K|  7812K|  3903   (2)| 00:00:47 |
        |   2 |   INDEX FULL SCAN  | IX_T_AB |  1000K|  7812K|   266   (1)| 00:00:04 |
        ------------------------------------------------------------------------------
        
        Statistics
        ----------------------------------------------------------
        ...
               2846  consistent gets
                  0  physical reads
                  0  redo size
        ...
                201  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
            1000000  rows processed

        — получается, что на каждый дополнительный SQL*Net roundtrips to/from client выполняется дополнительный consistent get при использовании в плане выполнения SORT UNIQUE NOSORT !
        Ну и кроме того, SORT UNIQUE NOSORT по-прежнему, как минимум, не медленнее HASH UNIQUE для запроса, использующего индекс с кэшированными блоками:

        SQL> alter session set optimizer_index_cost_adj = 100;
        
        Session altered.
        
        SQL> select/*+ gather_plan_statistics*/ distinct a,b from t;
        
        1000000 rows selected.
        
        Elapsed: 00:00:01.33
        
        -----------------------------------------------------------------------------------------
        | Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
        -----------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT      |         |  1000K|  7812K|       |  4362   (2)| 00:00:53 |
        |   1 |  HASH UNIQUE          |         |  1000K|  7812K|    15M|  4362   (2)| 00:00:53 |
        |   2 |   INDEX FAST FULL SCAN| IX_T_AB |  1000K|  7812K|       |   725   (1)| 00:00:09 |
        -----------------------------------------------------------------------------------------

        комментарий от Igor Usoltsev — 07.04.2012 @ 21:23 | Ответить


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 такие блоггеры, как: