Oracle mechanics

30.06.2012

Подсказки OPTIMIZER_MODE в обзорах

Filed under: CBO,commonplace,hints,Oracle — Игорь Усольцев @ 15:54
Tags: , , , ,

Запрос, использующий (напрямую или через вложенные обзоры) обзор с подсказками OPTIMIZER_MODE типа: RULE, FIRST_ROWS, FIRST_ROWS(n), будет выполняться с указанной в обзоре OPTIMIZER_MODE — что может значительно и не всегда очевидно влиять на план выполнения

Для возвращения оптимизатора к нормальному поведению (состоянию по умолчанию) OPTIMIZER_MODE придётся менять на уровне системы, сессии или запроса (подсказкой «верхнего уровня» на уровне всего запроса), либо убирать подсказку из обзора-виновника, который ещё нужно найти

Например, при создании обзора с подсказкой /*+ RULE */:

11.2.0.3.@ SQL> create or replace view scott.v_emp as select /*+ RULE*/ * from emp;

View created.

SQL> select * from scott.v_emp e, scott.dept d where e.deptno = d.deptno;

Execution Plan
-----------------------------------------------------
Plan hash value: 1984429485

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  NESTED LOOPS                |              |
|   2 |   NESTED LOOPS               |              |
|   3 |    TABLE ACCESS FULL         | EMP          |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0093797 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT         |
-----------------------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets -- **

– всякий запрос, использующий этот обзор явно или опосредованно через другие обзоры, будет выполняться также с OPTIMIZER_MODE = RULE – что отражается в бесценном плане (без колонки Cost) и точным указанием на использование rule based optimizer в секции Note

В этом случае выбирается очевидно не[вы]годный NESTED LOOPS судя по кол-ву consistent gets, почти в 2 раза превосходящему кол-во прочитанных блоков при использовании дефолтной OPTIMIZER_MODE = ALL_ROWS:

SQL> select /*+ ALL_ROWS*/ * from scott.v_emp e, scott.dept d where e.deptno = d.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   798 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets -- *

В трейсе 10053 первого запроса с rule based optimizer можно увидеть что сразу после ситаксического разбора SQL оптимизатор точно определяет модификации OPTIMIZER_MODE и в дальнейшем использует модифицированные параметры при компиляции плана:

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = true
optimizer_mode                      = rule

, а также применяет к запросу некоторые простые трансформации (simple view merging а данном случае) и генерирует соответсвующий RBO_OUTLINE с указанием использовать новую фичу 11g — Nested Loops Join Batching:)

/*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      RBO_OUTLINE
      ...
      NLJ_BATCHING(@"SEL$F5BB74E1" "D"@"SEL$1")
    END_OUTLINE_DATA
  */

При использовании в обзоре подсказки /*+ FIRST_ROWS(n) */ заметить изменения по плану не так просто, как в случае с RULE – простой autotrace explain не поможет:

SQL> create or replace view scott.v_emp as select /*+ FIRST_ROWS(1)*/ * from scott.emp;

View created.

SQL> create or replace view scott.v_emp_l2 as select * from scott.v_emp; -- делаю 2-х уровневый - вложенный обзор

View created.

SQL> set autotrace traceonly
SQL> select * from scott.v_emp_l2 e, scott.dept d where e.deptno = d.deptno;

14 rows selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    57 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |     1 |    57 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP          |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0093797 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("DEPTNO"="D"."DEPTNO")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         34  consistent gets

– используемая OPTIMIZER_MODE может быть получена из секции Outline V$SQL_PLAN.OTHER_XML:

SQL> explain plan for
  2  select * from scott.v_emp_l2 e, scott.dept d where e.deptno = d.deptno;

Explained.

SQL> select * from table(dbms_xplan.display('','','+outline'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
...

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      FIRST_ROWS(1)                        -- here be dragon
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Найти причину (конкретный обзор, использующий подсказки OPTIMIZER_MODE) можно простым перебором:

SQL> set serveroutput on
SQL> declare
  2  v_text varchar2(32000);
  3  begin
  4  for reco in (select view_name, text from dba_views where owner = 'SCOTT')
  5    loop
  6      v_text := reco.text;
  7        if upper(v_text) like '%FIRST\_ROWS%' escape '\'
  8          then dbms_output.put_line(reco.view_name || ' has FIRST_ROWS hins:');
  9               dbms_output.put_line(substr(reco.text,1,100));
 10        end if;
 11     end loop;
 12  end;
 13  /

V_EMP has FIRST_ROWS hins:
select /*+ FIRST_ROWS(1)*/ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from scott.e

PL/SQL procedure successfully completed.

Либо, проверить используемые в запросе обзоры, список которых можно увидеть в плане выполнения, предварительно отключив simple view merging подсказками или параметром:

SQL> select /*+ NO_MERGE(e)*/ * from scott.v_emp_l2 e, scott.dept d where e.deptno = d.deptno;

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    97 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |     1 |    97 |     2   (0)| 00:00:01 |
|   3 |    VIEW                      | V_EMP_L2     |     1 |    77 |     1   (0)| 00:00:01 | -- показан только обзор верхнего уровня
|   4 |     TABLE ACCESS FULL        | EMP          |    14 |   518 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0093797 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

SQL> select /*+ NO_MERGE(e) NO_MERGE(e.v_emp)*/ * from scott.v_emp_l2 e, scott.dept d where e.deptno = d.deptno;

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    97 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |     1 |    97 |     3   (0)| 00:00:01 |
|   3 |    VIEW                      | V_EMP_L2     |     1 |    77 |     2   (0)| 00:00:01 | -- показывает оба использованных обзора
|   4 |     VIEW                     | V_EMP        |     1 |    77 |     2   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | EMP          |     1 |    37 |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0093797 |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

SQL> alter session set "_simple_view_merging" = false;

Session altered.                                                                              -- при отключении на уровне сессии

SQL> select * from scott.v_emp_l2 e, scott.dept d where e.deptno = d.deptno;

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    97 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |     1 |    97 |     3   (0)| 00:00:01 |
|   3 |    VIEW                      | V_EMP_L2     |     1 |    77 |     2   (0)| 00:00:01 | -- показывает все использованные обзоры
|   4 |     VIEW                     | V_EMP        |     1 |    77 |     2   (0)| 00:00:01 | -- simple view merging не выполняется
|   5 |      TABLE ACCESS FULL       | EMP          |     1 |    37 |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0093797 |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

  1. вероятно опечатка «гонерирует»

    комментарий от Юрий — 11.07.2012 @ 12:09 | Ответить

    • Спасибо, исправил

      комментарий от Igor Usoltsev — 11.07.2012 @ 16:27 | Ответить


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