Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Рекурсивный with  [new]
inFik
Member

Откуда:
Сообщений: 140
Интересно почему в случае с рекурсивным with такое чрезмерно больше количество current, да и вообще почему его так таращит, у меня разобраться не получается, просьба помочь или ткните в RTFM.


SQL ID: 8pk2tzup1f9cv
Plan Hash: 968211682
SELECT COUNT(1) 
FROM
 DUAL CONNECT BY LEVEL <= 3000000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.65       1.65          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.65       1.66          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
3000000   CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=1886679 us)
      1    FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

SQL ID: 1fztvu41xga6u
Plan Hash: 3653425330
WITH TMP(L) AS ( SELECT 1 L FROM DUAL UNION ALL SELECT L+1 FROM TMP WHERE L+1 
  <= 3000000 ) SELECT COUNT(1) FROM TMP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     53.06      54.44        306          0   22986364           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     53.06      54.44        306          0   22986364           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=306 pw=8029 time=0 us)
3000000   VIEW  (cr=0 pr=306 pw=8029 time=41695480 us cost=4 size=0 card=2)
3000000    UNION ALL (RECURSIVE WITH) BREADTH FIRST (cr=0 pr=306 pw=8029 time=40177816 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
2999999     RECURSIVE WITH PUMP  (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.01          0.01
  db file sequential read                       306        0.30          1.28
  asynch descriptor resize                        1        0.00          0.00


P.S.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
18 окт 12, 18:22    [13342648]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ORASQL/XTENDER> WITH TMP(L) AS ( SELECT 1 L FROM DUAL UNION ALL SELECT L+1 FROM TMP WHERE L+1
  2    <= 3000000 ) SELECT COUNT(1) FROM TMP;

1 row selected.

Elapsed: 00:01:40.88

Execution Plan
----------------------------------------------------------
Plan hash value: 3653425330

-------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |      |     1 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                            |      |     1 |            |          |
|   2 |   VIEW                                     |      |     2 |     4   (0)| 00:00:01 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |            |          |
|   4 |     FAST DUAL                              |      |     1 |     2   (0)| 00:00:01 |
|*  5 |     RECURSIVE WITH PUMP                    |      |       |            |          |
-------------------------------------------------------------------------------------------

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

   5 - filter("L"+1<=3000000)


Statistics
----------------------------------------------------------
        230  recursive calls
   31137916  db block gets
          0  consistent gets
      15804  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
    3000001  sorts (memory)
          0  sorts (disk)
          1  rows processed
то ли у меня никогда таких объемов не было, что я не замечал, то ли сортировок таких не было...
18 окт 12, 21:09    [13343249]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
inFik,

Два принципиально различных механизма. В сессии 1 выполни:

select  saddr
  from  v$session
  where sid in (select sid from v$mystat)
/


Затем в сессии 1 запусти:

BEGIN
    FOR v_rec IN (
WITH TMP(L) AS ( SELECT 1 L FROM DUAL UNION ALL SELECT L+1 FROM TMP WHERE L+1
<= 3000000 ) SELECT COUNT(1) FROM TMP) LOOP
      NULL;
    END LOOP;
END;
/


и пока это фурычит, в сессии 2 выполняй последовательно:

select  sum(blocks)
  from  v$tempseg_usage
  where session_addr = 'XXX' -- где XXX это saddr сессии 1
/


и наблюдай как растет размер временного сегмента используемого сессией 1. Затем повтори то же самое с CONNECT BY.

SY.
18 окт 12, 21:35    [13343324]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
xtender
  3000001  sorts (memory)
я сглупил, у него же всегда есть сортировка, а в данном случае все время "сортирует" саму текущую строку...
18 окт 12, 21:43    [13343348]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
xtender,

Тебе доводилось использовать recursive subquery factoring clause в продакшене?
Можно пример использования?
18 окт 12, 21:51    [13343378]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
inFik,

И еще один намек:

SQL> BEGIN
  2      FOR v_rec IN (
  3  SELECT * FROM DUAL CONNECT BY LEVEL <= 30000000
  4  ) LOOP
  5        NULL;
  6      END LOOP;
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-30009: Not enough memory for  operation
ORA-06512: at line 2


SQL> 


SY.
18 окт 12, 21:57    [13343393]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
dbms_photoshop,

Для дистинкта по индексу он хорошо подходит я здесь в какой -то теме писал и блоге у себя
18 окт 12, 22:08    [13343431]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
xtender,

А можно ссылочку?
А то множество академических применений и я могу привести, а так чтоб было обосновано в продакшене - такого пока не доводилось.
18 окт 12, 22:20    [13343488]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
dbms_photoshop,

Вот: http://www.xt-r.com/2012/09/distinct-values-topn.html
Там желательно еще пройти по комментарию к Игорю Усольцеву, там в комментариях я еще немного пояснил. А вообще минусов у rsqf достаточно - новый механизм, оптимизатор с ним вообще ничего не умеет делать.
18 окт 12, 22:29    [13343530]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
inFik
Member

Откуда:
Сообщений: 140
SY
inFik,

И еще один намек:

SQL> BEGIN
  2      FOR v_rec IN (
  3  SELECT * FROM DUAL CONNECT BY LEVEL <= 30000000
  4  ) LOOP
  5        NULL;
  6      END LOOP;
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-30009: Not enough memory for  operation
ORA-06512: at line 2


SQL> 


SY.


Вот как раз по этому и заинтересовал меня рекурсивный with, т.к. часто сталкивался с проблемой, SORT_AREA_SIZE не резиновый :( В продакшене конечно использовать я его не буду, но для генерации больших объемов with удобнее был бы, если бы столько не работал ... задумывался в сторону temp, завтра поэксперементирую еще, спасибо!

xtender
xtender
  3000001  sorts (memory)
я сглупил, у него же всегда есть сортировка, а в данном случае все время "сортирует" саму текущую строку...



с сортировкой понятно, меня больше волнует вопрос ... почему количество чтений-то на порядок выше?
18 окт 12, 22:29    [13343531]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
inFik,

Проще select count (*) from xmltable('1 to 3000000')
18 окт 12, 22:36    [13343566]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
inFik,

Потому что многократно обращаешься к тем же блокам для сортировки - оракл не знает сколько у тебя сиблингов
18 окт 12, 22:39    [13343577]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
inFik
Вот как раз по этому и заинтересовал меня рекурсивный with, т.к. часто сталкивался с проблемой, SORT_AREA_SIZE не резиновый


SQL> WITH TMP(L) AS ( SELECT 1 L FROM DUAL UNION ALL SELECT L+1 FROM TMP WHERE L+1 
  2    <= 3000000 ) SELECT COUNT(1) FROM TMP
  3  /

  COUNT(1)
----------
   3000000

Elapsed: 00:01:56.96
SQL> SELECT COUNT(*) FROM DUAL CONNECT BY LEVEL <= 3000000
  2  /
SELECT COUNT(*) FROM DUAL CONNECT BY LEVEL <= 3000000
*
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation


Elapsed: 00:00:01.23
SQL> CREATE OR REPLACE
  2    TYPE NumList
  3      AS
  4        TABLE OF number
  5  /

Type created.

Elapsed: 00:00:00.99
SQL> create or replace
  2    function row_generator(
  3                           p_how_many number
  4                          )
  5      return NumList
  6      pipelined
  7      is
  8      begin
  9          for i in 1..p_how_many loop
 10            pipe row(i);
 11          end loop;
 12  end;
 13  /

Function created.

Elapsed: 00:00:00.05
SQL> SELECT COUNT(*) FROM table(row_generator(3000000))
  2  /

  COUNT(*)
----------
   3000000

Elapsed: 00:00:01.20
SQL> 


SY.

Сообщение было отредактировано: 18 окт 12, 23:06
18 окт 12, 23:05    [13343672]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
SY,

обойти по Льюисовски быстрее:
with gen as (
     select level
     from dual 
     connect by level<=1e3
     )
select count(*)
from gen t1,gen t2
where rownum<3000000
18 окт 12, 23:24    [13343745]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
с порядком я чуток ошибся
18 окт 12, 23:26    [13343750]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
+
ORASQL/XTENDER> with gen as (
  2       select level
  3       from dual
  4       connect by level<=1e3
  5       )
  6  select count(*)
  7  from gen t1,gen t2,gen
  8  where rownum<3000003
  9  /

  COUNT(*)
----------
   3000002

1 row selected.

Elapsed: 00:00:00.61
ORASQL/XTENDER> SELECT COUNT(*) FROM table(row_generator(3000002));

  COUNT(*)
----------
   3000002

1 row selected.

Elapsed: 00:00:01.24
18 окт 12, 23:29    [13343764]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
xtender
обойти по Льюисовски быстрее


Соглaсен, но при этом надо каждый раз думать насчет "c порядком я чуток ошибся".

SY.

Сообщение было отредактировано: 18 окт 12, 23:48
18 окт 12, 23:47    [13343820]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
xtender
Потому что многократно обращаешься к тем же блокам для сортировки - оракл не знает сколько у тебя сиблингов
Дело не в этом.
wurdu показывал и для нерекурсивного with наличие чтений в current mode: 13127551.
Ясно только, что Оракл как-то по другому учитывает чтения именно для subquery factoring clause.
19 окт 12, 01:51    [13344019]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
dbms_photoshop,

так там материализация же... а про рекурсивный вообще выглядит это как изменения и обращения к какому-то индексу дерева рекурсии = пока рекурсия меньше 4040 - current нет, как только больше - появляются
19 окт 12, 09:55    [13344594]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Забыл сказать что это зависит именно от глубины рекурсии, а не от размера строки
19 окт 12, 10:02    [13344640]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный with  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
xtender
dbms_photoshop,

так там материализация же... а про рекурсивный вообще выглядит это как изменения и обращения к какому-то индексу дерева рекурсии = пока рекурсия меньше 4040 - current нет, как только больше - появляются
Ну у меня появляются и для глубины 3000.
SQL> with tmp(id) as (select id + 1 from tmp where tmp.id + 1 <= 3000 union all select 1 id from dual)
  2  select count(*) from tmp;


Statistics
----------------------------------------------------------
          1  recursive calls
         79  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       3001  sorts (memory)
          0  sorts (disk)
          1  rows processed
Ивент для того, чтоб сдампить блоки в current mode мне найти не удалось. :)
23 окт 12, 02:50    [13359852]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить