Oracle 12c new features: optimizer automatic reoptimization

Посмотрим на такую новую возможность, как automatic reoptimization.


Есть две исходных таблицы:

SQL> select segment_name,sum(bytes)/1024/1024 SIZE_MB from dba_segments where owner='TEST1' and segment_name in ('T1','T0') group by segment_name; SEGMENT_NAME                              SIZE_MB -------------------------------- ---------------- T0                                           2119 T1                                           2326 SQL> select count(c0) from t1 where c0<900000;        COUNT(C0) ----------------            80184 SQL> select count(c0) from t0 where c0<900000;        COUNT(C0) ----------------              712

        
И выполняем небольшой select:

SQL> set autotrace trace exp stat SQL> select * from t0 inner join t1 using(c0) where c0<900000; 128 rows selected. Elapsed: 00:00:17.85 Execution Plan ---------------------------------------------------------- Plan hash value: 1792085932 ----------------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ----------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      | 48300 |    95M|       |   160K  (1)| 00:00:07 | |*  1 |  HASH JOIN         |      | 48300 |    95M|    48M|   160K  (1)| 00:00:07 | |*  2 |   TABLE ACCESS FULL| T0   | 48300 |    47M|       | 73343   (1)| 00:00:03 | |*  3 |   TABLE ACCESS FULL| T1   | 84844 |    84M|       | 80523   (1)| 00:00:04 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("T0"."C0"="T1"."C0")    2 - filter("T0"."C0"<900000)    3 - filter("T1"."C0"<900000) Note -----    - dynamic statistics used: dynamic sampling (level=2)    - this is an adaptive plan Statistics ----------------------------------------------------------         458  recursive calls           0  db block gets      561438  consistent gets      560643  physical reads           0  redo size       70441  bytes sent via SQL*Net to client         364  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client          54  sorts (memory)           0  sorts (disk)         128  rows processed

Как видим, оптимизатор построил нам план с hash join'ом. Взглянем на результат dbms_xplan.display_cursor:

SQL> select * from table(dbms_xplan.display_cursor('6tbfdhsas6d87',0,'report')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- SQL_ID  6tbfdhsas6d87, child number 0 ------------------------------------- select * from t0 inner join t1 using(c0) where c0<900000 Plan hash value: 1792085932 ----------------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ----------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |       |       |       |   160K(100)|          | |*  1 |  HASH JOIN         |      | 48300 |    95M|    48M|   160K  (1)| 00:00:07 | |*  2 |   TABLE ACCESS FULL| T0   | 48300 |    47M|       | 73343   (1)| 00:00:03 | |*  3 |   TABLE ACCESS FULL| T1   | 84844 |    84M|       | 80523   (1)| 00:00:04 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("T0"."C0"="T1"."C0")    2 - filter("T0"."C0"<900000)    3 - filter("T1"."C0"<900000) Note -----    - dynamic statistics used: dynamic sampling (level=2)    - this is an adaptive plan Reoptimized plan: ----------------- This cursor is marked for automatic reoptimization.  The plan that is expected to be chosen on the next execution is displayed below. Plan hash value: 613901866 ------------------------------------------------------------------------------------- | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |      |   128 |   260K| 74768   (1)| 00:00:03 | |   1 |  NESTED LOOPS                |      |       |       |            |          | |   2 |   NESTED LOOPS               |      |   128 |   260K| 74768   (1)| 00:00:03 | |*  3 |    TABLE ACCESS FULL         | T0   |   712 |   724K| 73343   (1)| 00:00:03 | |*  4 |    INDEX RANGE SCAN          | IT1  |     1 |       |     1   (0)| 00:00:01 | |   5 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |  1042 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter("T0"."C0"<900000)    4 - access("T0"."C0"="T1"."C0")        filter("T1"."C0"<900000) Note -----    - dynamic statistics used: dynamic sampling (level=2)    - 1 Sql Plan Directive used for this statement

Оптимизатор, по выполнении запроса, решил что эффективней его выполнять по иному плану, nested loops + index range scan.

SQL> select plan_hash_value,child_number,executions,is_reoptimizable ir, is_resolved_adaptive_plan irp  from v$sql where sql_id='6tbfdhsas6d87' order by 2;  PLAN_HASH_VALUE     CHILD_NUMBER       EXECUTIONS IR  IRP ---------------- ---------------- ---------------- --- ---       1792085932                0                1 Y   Y

Такая картина у нас после первого выполнения.
План помечен как реоптимизируемый(is_reoptimizable=Y) и оптимизатор выбрал новый план для последующих запусков этого запроса(is_resolved_adaptive_plan=Y).
Прогоним запрос еще пару раз и посмотрим на v$sql:

PLAN_HASH_VALUE     CHILD_NUMBER       EXECUTIONS IR  IRP ---------------- ---------------- ---------------- --- ---       1792085932                0                1 Y   Y        613901866                1                2 N

Был сгенерирован новый child для запроса, который помечен как не реоптимизируемый, все последующие выполнения идут по новому плану:

SQL> select * from table(dbms_xplan.display_cursor('6tbfdhsas6d87',1)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- SQL_ID  6tbfdhsas6d87, child number 1 ------------------------------------- select * from t0 inner join t1 using(c0) where c0<900000 Plan hash value: 613901866 ------------------------------------------------------------------------------------- | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |      |       |       | 74768 (100)|          | |   1 |  NESTED LOOPS                |      |       |       |            |          | |   2 |   NESTED LOOPS               |      |   128 |   260K| 74768   (1)| 00:00:03 | |*  3 |    TABLE ACCESS FULL         | T0   |   712 |   724K| 73343   (1)| 00:00:03 | |*  4 |    INDEX RANGE SCAN          | IT1  |     1 |       |     1   (0)| 00:00:01 | |   5 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |  1042 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter("T0"."C0"<900000)    4 - access("T0"."C0"="T1"."C0")        filter("T1"."C0"<900000) Note -----    - dynamic statistics used: dynamic sampling (level=2)    - statistics feedback used for this statement    - 1 Sql Plan Directive used for this statement

Во время первого запуска этого запроса, оптимизатор, сравнив ожидаемое и реально полученное количество строк, решил что статистику, собранную в ходе этого выполнения стоит использовать в будущем.
В нашем случае сыграло роль что по таблицам отсутствует основная статистика.
Все следующие запуски этого запроса используют эту сохраненную статистику, о чем и подсказывает фраза "statistics feedback used for this statement".