Data generator

Yesterday I had fallen to new issue with bad performance of "reusable code". It was some piece of code seemed to be very elegant from programmer point of view.
Complicated select statement contained the some kind of "data generator" as inline view like following:

select add_months(:p_begin_date, level - 1) dt
  from dual
connect by level < months_between(:p_end_date, :p_begin_date) + 2

This subquery aimed to generate a list of month between two dates which are specified as bind variables.
As a result, the complex query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string" approximately at 13 step of plan (see below)

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                                     |                     |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   2 |   NESTED LOOPS OUTER                        |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |    NESTED LOOPS                             |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  4 |     HASH JOIN                               |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   740K|   740K|          |
|   5 |      VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       SORT GROUP BY                         |                     |      1 |      1 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  7 |        HASH JOIN OUTER                      |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   742K|   742K|          |
|   8 |         VIEW                                |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |          NESTED LOOPS                       |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |           NESTED LOOPS                      |                     |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 11 |            HASH JOIN                        |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   723K|   723K|          |
|* 12 |             HASH JOIN                       |                     |      1 |      1 |      0 |00:00:00.01 |       0 |   776K|   776K|          |
|* 13 |              HASH JOIN                      |                     |      1 |    524 |    301M|04:06:15.61 |     257 |    82M|  5277K|  143M (0)|
|  14 |               MERGE JOIN CARTESIAN          |                     |      1 |  39178 |   1781K|00:00:00.04 |     161 |       |       |          |
|  15 |                VIEW                         |                     |      1 |      1 |     48 |00:00:00.01 |       0 |       |       |          |
|  16 |                 CONNECT BY WITHOUT FILTERING|                     |      1 |        |     48 |00:00:00.01 |       0 |       |       |          |
|  17 |                  FAST DUAL                  |                     |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|  18 |                BUFFER SORT                  |                     |     48 |  39178 |   1781K|00:00:00.04 |     161 |  1753K|   641K| 1558K (0)|
|  19 |                 INDEX FAST FULL SCAN        | XIF_TCCP_SET1       |      1 |  39178 |  37124 |00:00:00.01 |     161 |       |       |          |
|  20 |               TABLE ACCESS FULL             | TMP_TAR_DATES_DAY   |      1 |  41996 |  16274 |00:00:00.07 |      96 |       |       |          |
|  21 |              INLIST ITERATOR                |                     |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  22 |               TABLE ACCESS BY INDEX ROWID   | TMP_CALC_PROWS      |      0 |  92152 |      0 |00:00:00.01 |       0 |       |       |          |
|* 23 |                INDEX RANGE SCAN             | XIF_TCPR_SET2       |      0 |   1750 |      0 |00:00:00.01 |       0 |       |       |          |
|* 24 |             TABLE ACCESS FULL               | TMP_PTO_CALC        |      0 |  19983 |      0 |00:00:00.01 |       0 |       |       |          |
|* 25 |            TABLE ACCESS BY INDEX ROWID      | TMP_TAR_DATES       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 26 |             INDEX RANGE SCAN                | XIF_TTD_TMP_SET1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  27 |           TABLE ACCESS BY INDEX ROWID       | TMP_PRT_CALC_PARAMS |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 28 |            INDEX RANGE SCAN                 | XIF_TPCP_SET1       |      0 |    449 |      0 |00:00:00.01 |       0 |       |       |          |
|* 29 |         TABLE ACCESS FULL                   | TMP_CALC_IDX_ROWS   |      0 |   2166 |      0 |00:00:00.01 |       0 |       |       |          |
|* 30 |      TABLE ACCESS FULL                      | TMP_PTO_CALC        |      0 |  19983 |      0 |00:00:00.01 |       0 |       |       |          |
|* 31 |     TABLE ACCESS BY INDEX ROWID             | ERRD                |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 32 |      INDEX RANGE SCAN                       | XIF1661_ERRD        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  33 |    TABLE ACCESS BY INDEX ROWID              | MPMNT               |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 34 |     INDEX UNIQUE SCAN                       | XPK_PMONTH          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

The main cause of problem is very bad cardinality estimating for "data generator".

SQL> explain plan for
  2  select add_months(:p_begin_date, level - 1) dt
  3    from dual
  4  connect by level < months_between(:p_end_date, :p_begin_date) + 2;

Explained.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

CBO will decide to assume the single row cardinality for such subquery without any hint. But it is wrong for most combinations of values which are in use.
My recommendation was to add the CARDINALITY hint with enough high value, 100 for example. It will cover most of cases at this time and in future, when the distance will rise between P_BEGIN_DATE and P_END_DATE.

Древовидные справочники в качестве таблиц-размерностей или анализ “что-если” применительно к “TUNING BY CARDINALITY FEEDBACK METHOD”

Недавно консультировал коллегу по настройке запроса. Суть решаемой запросом задачи состояла в следующем: таблица фактов (далее facts) связана с таблицей размерности (далее dic$territory), а та в свою очередь, являлась древовидным справочником административно-территориального устройства. Интересующий моего коллегу запрос должен был выдавать некоторые суммарные значения по таблице facts, отфильтрованной с помощью таблицы dic$territory. Из таблицы facts отбирались строки, ссылающиеся на некоторую часть дерева справочника dic$territory, корень необходимой для фильтрации части дерева указывался параметром :code.
Запрос имеел вид аналогичный приведенному ниже:

select sum(human_cnt) from facts
 where territory_id in (select territory_id
                          from dic$territory
                         start with territory_code = :code
                       connect by prior territory_id = territory_parent);

Первый подход к анализу запроса (explain plan) показал - прогнозируемая кардинальность фильтрующего подзапроса (шаг 4) определяется крайне неточно. Следует отметить, что реальная кардинальность подзапроса зависит от значения параметра, указывающего на корневой узел начала перебора дерева, и может изменяться начиная от нескольких единиц для узлов дерева близких к листовым, до нескольких тысяч для узлов дерева близких к корню.

------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |    22 |    39   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE                    |                 |     1 |    22 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID      | FACTS           |     9 |    81 |    12   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                    |                 |    27 |   594 |    39   (3)| 00:00:01 |
|   4 |     VIEW                           | VW_NSO_1        |     3 |    39 |     2   (0)| 00:00:01 |
|   5 |      HASH UNIQUE                   |                 |     3 |    30 |            |          |
|*  6 |       CONNECT BY WITH FILTERING    |                 |       |       |            |          |
|   7 |        TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |     1 |    26 |     2   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN           | IDX_TERR_CODE   |     1 |       |     1   (0)| 00:00:01 |
|   9 |        NESTED LOOPS                |                 |       |       |            |          |
|  10 |         CONNECT BY PUMP            |                 |       |       |            |          |
|  11 |         TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |     3 |    30 |     2   (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN          | IDX_TERR_PARENT |     3 |       |     1   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN               | IDX_FACTS_TERR  |     9 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   6 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
   8 - access("TERRITORY_CODE"=:CODE)
  12 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
  13 - access("TERRITORY_ID"="$nso_col_1")

Интересно было бы понять, каким образом вычисляется кардинальность фильтрующего подзапроса. В файле трассировки 10053 имеется следующий фрагмент:

  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): TERRITORY_PARENT(NUMBER)
    AvgLen: 5.00 NDV: 12110 Nulls: 23 Density: 8.2576e-005 Min: 0 Max: 42164
  Table: DIC$TERRITORY  Alias: DIC$TERRITORY     
    Card: Original: 42188  Rounded: 3  Computed: 3.48  Non Adjusted: 3.48
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------

на основе которого можно сделать вывод, что кардинальность подзапроса есть не что иное как кардинальность выборки из таблицы dic$territory, отфильтрованой предикатом territory_parent=:id.
Статистика времени выполнения этого запроса при значении параметра :code, указывающим на «близкий» к корню дерева узел, подтверждает, некорректность прогноза кардинальности подзапроса (шаг 4):

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                    |                 |      1 |      1 |      1 |00:00:00.55 |   28858 |
|   2 |   TABLE ACCESS BY INDEX ROWID      | FACTS           |      1 |      9 |  21051 |00:00:00.43 |   28858 |
|   3 |    NESTED LOOPS                    |                 |      1 |     27 |  21051 |00:00:00.32 |    7807 |
|   4 |     VIEW                           | VW_NSO_1        |      1 |      3 |   2339 |00:00:00.15 |    3081 |
|   5 |      HASH UNIQUE                   |                 |      1 |      3 |   2339 |00:00:00.14 |    3081 |
|*  6 |       CONNECT BY WITH FILTERING    |                 |      1 |        |   2339 |00:00:00.13 |    3081 |
|   7 |        TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  8 |         INDEX RANGE SCAN           | IDX_TERR_CODE   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   9 |        NESTED LOOPS                |                 |      4 |        |   2338 |00:00:00.11 |    3078 |
|  10 |         CONNECT BY PUMP            |                 |      4 |        |   2339 |00:00:00.01 |       0 |
|  11 |         TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |   2339 |      3 |   2338 |00:00:00.07 |    3078 |
|* 12 |          INDEX RANGE SCAN          | IDX_TERR_PARENT |   2339 |      3 |   2338 |00:00:00.03 |    2353 |
|* 13 |     INDEX RANGE SCAN               | IDX_FACTS_TERR  |   2339 |      9 |  21051 |00:00:00.06 |    4726 |
----------------------------------------------------------------------------------------------------------------

Оценив на глазок, можно сказать, что способ соединения (шаг 3) не очень подходит для такой кардинальности, так же как и способ доступа к таблице facts (шаги 13, 2).
Учитывая тот факт, что кардинальность подзапроса оценивается оптимизатором настолько грубо, не остается ничего другого, как воспользоваться подсказкой cardinality со значением параметра равным значению колонки A-Rows для шага 4.

select /*+gather_plan_statistics cardinality(@qb1 2339)*/
       sum(human_cnt) from facts
 where territory_id in (select /*+QB_NAME(qb1)*/ territory_id
                          from dic$territory
                         start with territory_code = :code
                       connect by prior territory_id = territory_parent);

Статистика времени выполнения с уточненной кардинальностью (для того же параметра :code):

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                  |                 |      1 |      1 |      1 |00:00:03.42 |    4064 |
|*  2 |   HASH JOIN RIGHT SEMI           |                 |      1 |  21212 |  21051 |00:00:03.38 |    4064 |
|   3 |    VIEW                          | VW_NSO_1        |      1 |   2339 |   2339 |00:00:00.14 |    3081 |
|*  4 |     CONNECT BY WITH FILTERING    |                 |      1 |        |   2339 |00:00:00.13 |    3081 |
|   5 |      TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN           | IDX_TERR_CODE   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   7 |      NESTED LOOPS                |                 |      4 |        |   2338 |00:00:00.11 |    3078 |
|   8 |       CONNECT BY PUMP            |                 |      4 |        |   2339 |00:00:00.01 |       0 |
|   9 |       TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |   2339 |      3 |   2338 |00:00:00.07 |    3078 |
|* 10 |        INDEX RANGE SCAN          | IDX_TERR_PARENT |   2339 |      3 |   2338 |00:00:00.03 |    2353 |
|  11 |    TABLE ACCESS FULL             | FACTS           |      1 |    381K|    379K|00:00:00.76 |     983 |
--------------------------------------------------------------------------------------------------------------

В результате LIO уменьшился в 7 раз. Для настройки реального запроса такой метод корректирования кардинальности не претендует на универсальность, однако позволяет легко воздействовать на форму плана и оценивать его эффективность.
В моем случае знание формы более эффективного плана позволило найти решение реальной проблемы, а именно, была создана дополнительная таблица, в которой хранилась «развертка» дерева, эта новая таблица была использована в фильтрующем подзапросе вместо первоначальной древовидной. Её использование позволило оптимизатору получать корректные значения прогнозов кардинальности для большинства используемых значений параметра :code.
Кроме того, на основе проведенной работы выкристализовалась идея использовать подсказку cardinality для осуществления «что-если» анализа в рамках метода «...by cardinality feedback...». Алгоритм может быть примерно следующим:
1) выявить шаг плана, с ошибочным прогнозом кардинальности, используя для этого статистику времени исполнения;
2) подсказкой cardinality скорректировать кардинальность;
3) перезапустить запрос, если еще остаются шаги плана с некорректным прогнозом кардинальности, перейти к шагу 1), иначе перейти к следующему шагу;
4) оценить эффективность плана, при положительном ответе — найти способо помочь оптимизатору находить такой же план, но без использования подсказки(ок) cardinality.
Фактически, такая последовательность действий дает возможность понять — какой план можно получить в ситуации, когда оптимизатор имеет точную информацию о кардинальности, станет ли такой план достаточно эффективным. Ведь в результате анализа может также выяснится, что сама задача, которую решает настраиваемый запрос, требует переформулирования. Кстати, окончательным решением моего коллеги по настройке запроса стало именно переформулирование задачи.

Tuning by Cardinality Feedback в действии

В свое время, статья Tuning by Cardinality Feedback произвеля на меня неизгладимое впечатление.
И вот теперь случилось применить ее в одном интересном случае. Вот такой план имел место быть на промышленной площадке.
Сразу же бросается в глаза серъезное расхождение между прогнозом оптимизатора и статистикой времени исполнения. Первая стадия анализа привела к фрагменту плана, в котором, как мне кажется, находится ключевая ошибка оптимизатора в оценке кардинальности:

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                    | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |
-------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 17 |            HASH JOIN ANTI                        |                         |      1 |      1 |    54 |       |     7  (15)| 00:00:01 |     18 |
|  18 |             NESTED LOOPS                         |                         |      1 |      1 |    42 |       |     2   (0)| 00:00:01 |     21 |
|* 19 |              TABLE ACCESS BY USER ROWID          | IKIS_USERS_ATTR         |      1 |      1 |    25 |       |     1   (0)| 00:00:01 |      1 |
|* 20 |              INDEX FULL SCAN                     | PK_IKIS_ROLE            |      1 |      1 |    17 |       |     1   (0)| 00:00:01 |     21 |
|  21 |             VIEW                                 | VW_NSO_3                |      1 |     12 |   144 |       |     4   (0)| 00:00:01 |      3 |
|  22 |              NESTED LOOPS                        |                         |      1 |     12 |   600 |       |     4   (0)| 00:00:01 |      3 |
|* 23 |               TABLE ACCESS FULL                  | IKIS_RESOURCE           |      1 |      9 |   162 |       |     3   (0)| 00:00:01 |      4 |
|* 24 |               INDEX RANGE SCAN                   | PK_RSRC2ROLE            |      4 |      1 |    32 |       |     1   (0)| 00:00:01 |      3 |
...

все остальное лавинообразно преумножало ошибку, сделанную на 20 шаге плана.
Интересный предикат был сгенерен для этого 20 шага:

20 - filter(("IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'PUBLIC' AND "IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'_NEXT_USER'))

Собственно, данный предикат сгенерен на основе where clause представления dba_roles, которое участвует в данном запросе.
Далее простой test case, имитирующий данную ситуацию:

drop table r1;
drop table r2;
create table r1 as select rownum id,x.* from all_objects x where rownum<=1000;

update r1 set object_name='SSDR' where mod(id,276)=0;
update r1 set object_name='GSDR' where mod(id,876)=0;

create table r2 as select * from r1 where mod(object_id,2)=0;

update r1 set object_name='XXX'||substr(object_name,1,25);
update r2 set object_name=substr(object_name,1,25);


commit;

exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r1')
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r2')

create or replace view v1
as
select * from r1
where object_name<>'XXXSSDR' and object_name<>'XXXGSDR' and object_type='INDEX';

explain plan for
select count(1)
from v1,r2
where v1.object_name='XXX'||r2.object_name;

и ожидаемый, аналогичный полученному на пром-системе, результат с ошибкой оценки кардинальности для таблицы R2:

----------------------------------------------------------------------------    
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------    
|   0 | SELECT STATEMENT    |      |     1 |    40 |     9  (12)| 00:00:01 |    
|   1 |  SORT AGGREGATE     |      |     1 |    40 |            |          |    
|*  2 |   HASH JOIN         |      |     1 |    40 |     9  (12)| 00:00:01 |    
|*  3 |    TABLE ACCESS FULL| R2   |     1 |    15 |     3   (0)| 00:00:01 |    
|*  4 |    TABLE ACCESS FULL| R1   |   111 |  2775 |     5   (0)| 00:00:01 |    
----------------------------------------------------------------------------    
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")                          
   3 - filter('XXX'||"R2"."OBJECT_NAME"<>'XXXSSDR' AND                          
              'XXX'||"R2"."OBJECT_NAME"<>'XXXGSDR')                             
   4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND            
              "OBJECT_NAME"<>'XXXGSDR')  

Очевидно, что ситуация в следующем: оптимизатор генерит предикат и крайне неверно оценивает его селективность.
Собственно workaround:
1) каким-либо образом заставить оптимизатор не генерить предикат, для этого можно использовать хинт NO_MERGE или NO_QUERY_TRANSFORMATION. В таком случае план будет имеет вид:

-----------------------------------------------------------------------------   
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
-----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT     |      |     1 |    32 |     9  (12)| 00:00:01 |   
|   1 |  SORT AGGREGATE      |      |     1 |    32 |            |          |   
|*  2 |   HASH JOIN          |      |    57 |  1824 |     9  (12)| 00:00:01 |   
|   3 |    VIEW              | V1   |   111 |  1887 |     5   (0)| 00:00:01 |   
|*  4 |     TABLE ACCESS FULL| R1   |   111 |  2775 |     5   (0)| 00:00:01 |   
|   5 |    TABLE ACCESS FULL | R2   |   501 |  7515 |     3   (0)| 00:00:01 |   
-----------------------------------------------------------------------------   
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("V1"."OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")                     
   4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND            
              "OBJECT_NAME"<>'XXXGSDR')

План исходного запроса с хинтом NO_QUERY_TRANSFORMATION тяжеловат, но по времени исполнения все таки существенно лучше.

2) пока искал другие пути обнаружил, что это явление - ошибка в коде оптимизатора.
Проверял следующие версии 9.2.0.4 SE Linux, 10.2.0.1 SE/EE Linux,
10.2.0.1 SE Windows, 11.1.0.6 EE Windows.
Этот эффект проявился исключиетльно на 10.2.0.1 SE Windows (такая же версия была и в продукционной системе, где собственно проблема и была обнаружена).