Oracle mechanics

14.05.2017

Конкатенация значений столбцов при превышении VARCHAR2 лимита 4000 bytes

Filed under: Oracle,SQL — Игорь Усольцев @ 19:26
Tags:

, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается

ORA-01489: result of string concatenation is too long

LISTAGG_CLOB

Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты:

12.1.0.2.@ SQL> SELECT shop_id, LISTAGG_CLOB(region_id || ';') regions
  2    FROM SHOPS_BY_REGIONS_VIEW
  3   GROUP BY shop_id
  4  /

140544 rows selected.

Elapsed: 00:42:20.49

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528

---------------------------------------------------------------------------------
| Id  | Operation               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |   148K|  2611K|       |  9943   (1)| 00:00:01 |
|   1 |  SORT GROUP BY          |   148K|  2611K|       |  9943   (1)| 00:00:01 |
|   2 |   VIEW                  |   438K|  7699K|       |  9943   (1)| 00:00:01 |
|   3 |    HASH UNIQUE          |   438K|    17M|    21M|  9943   (1)| 00:00:01 |
|*  4 |     FILTER              |       |       |       |            |       |
|*  5 |      HASH JOIN          |   758K|    29M|    11M|  7031   (1)| 00:00:01 |
|*  6 |       HASH JOIN         |   272K|  8527K|  4080K|  5480   (1)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL|   154K|  2259K|       |  4443   (2)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL|   267K|  4440K|       |   547   (1)| 00:00:01 |
|   9 |       TABLE ACCESS FULL |   757K|  6655K|       |   407   (2)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN   |     3 |    15 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       2283  recursive calls
    1892521  db block gets
     359890  consistent gets
          0  physical reads
        968  redo size
   53661033  bytes sent via SQL*Net to client       --*
   27687540  bytes received via SQL*Net from client --*
     421634  SQL*Net roundtrips to/from client      --*
          3  sorts (memory)
          0  sorts (disk)
     140544  rows processed

*) — пересылая по SQL*Net 50MB клиенту и 27MB обратно(!), совершая 400,000(!) roundtrip-ов, что, вероятно, является неприятной особенностью передачи CLOB данных и коственно подтверждается следующим тестом:

XMLAGG — почти в 4 раза более эффективен по времени, но так же грустно в плане сетевого обмена(**):

SQL> SELECT shop_id
  2  , xmlagg(xmlelement(s, region_id || ';').extract('//text()') order by region_id)
  3                   regions
  4                  FROM SHOPS_BY_REGIONS_VIEW
  5                  GROUP BY shop_id
  6  /

140536 rows selected.

Elapsed: 00:13:50.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528 -- тот же план

Statistics
----------------------------------------------------------
         94  recursive calls
          8  db block gets
     212764  consistent gets
          0  physical reads
       1128  redo size
   66483756  bytes sent via SQL*Net to client       --**
   52027394  bytes received via SQL*Net from client --**
     408760  SQL*Net roundtrips to/from client      --**
          3  sorts (memory)
          0  sorts (disk)
     140536  rows processed

COLLECT — несомненно лучшее по времени выполнения решение:

SQL> CREATE or REPLACE TYPE REGION_ID_TT AS TABLE OF number(10)
  2  /
 
Type created

SQL> SELECT shop_id
  2  , CAST(COLLECT(region_id ORDER BY region_id) AS REGION_ID_TT)
  3                   regions
  4                  FROM SHOPS_BY_REGIONS_VIEW
  5                  GROUP BY shop_id
  6  /

140544 rows selected.

Elapsed: 00:00:05.66

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528 -- тот же план

Statistics
----------------------------------------------------------
         96  recursive calls
          8  db block gets
     212764  consistent gets
          0  physical reads
        868  redo size
   14236251  bytes sent via SQL*Net to client       --***
        814  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     140544  rows processed

— к тому же пересылающее всего 14MB за 30+ roundtrip-ов, что для 140,000 строк при arraysize 5000 выглядит оптимальным

Однако, последующие выполнения того же запроса с CAST(COLLECT()) показывают менее эффективные рез-ты:

SQL> /

140641 rows selected.

Elapsed: 00:00:22.48

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528 -- тот же план

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1056909  consistent gets -- ****
          0  physical reads
          0  redo size
  121571108  bytes sent via SQL*Net to client       --*****
   42756314  bytes received via SQL*Net from client --*****
     281316  SQL*Net roundtrips to/from client      --*****
          1  sorts (memory)
          0  sorts (disk)
     140641  rows processed

— 5-кратное увеличения consistent gets с 200,000 до 1,000,000 — к ожидаемым 200,000 consistent gets pin (fastpath) добавляются 800,000 consistent gets examination (fastpath), что, возможно(???), связано с периодическим созданием/использованием долгоживущих Transient Objects by COLLECT Function (Doc ID 1603706.1)
— плюс сетевой обмен (*****) вырастает многократно — до 120MB к клиенту и 42MB обратно на сервер за 280,000 roundtrip-ов — что указывает на своеобразно эволюционирующую совместную работу SQL*Net с COLLECTION, с другой стороны становится видно, что сетевой трафик для такого типа запросов имеет неопределяющее значение

В любом случае, CAST(COLLECT()) — самый быстрый способ, однако требующий изменений на принимающей стороне — How to Use the New Collect Sql Function With JDBC 10.2.0.x (Doc ID 605158.1)

Но если есть возможность модифицировать код [приложения] — можно попробовать, предварительно оценив максимальную длину получаемого CLOB:

SQL> 
select max(dbms_lob.getlength(regions))
  from (
SELECT shop_id,
       xmlagg(xmlelement(s, region_id || ';')).extract('//text()').getclobval() regions
  FROM SHOPS_BY_REGIONS_VIEW
 GROUP BY shop_id
)
  8  /

MAX(DBMS_LOB.GETLENGTH(REGIONS))
--------------------------------
                           18044

, попробовать передать по сети рез-т XMLAGG пачкой из 10 (2-х кратный запас:) VARCHAR2 полей:

SQL>
    with q as 
          (SELECT/*+ MATERIALIZE */ shop_id,
                  xmlagg(xmlelement(s, region_id || ';')).extract('//text()')
                  .getclobval() as regions
             FROM SHOPS_BY_REGIONS_VIEW
            GROUP BY shop_id)
    SELECT shop_id,
           dbms_lob.substr(regions, 4000, 1)     as regions01,
           dbms_lob.substr(regions, 4000, 4001)  as regions02,
           dbms_lob.substr(regions, 4000, 8001)  as regions03,
           dbms_lob.substr(regions, 4000, 12001) as regions04,
           dbms_lob.substr(regions, 4000, 16001) as regions05,
           dbms_lob.substr(regions, 4000, 20001) as regions06,
           dbms_lob.substr(regions, 4000, 24001) as regions07,
           dbms_lob.substr(regions, 4000, 28001) as regions08,
           dbms_lob.substr(regions, 4000, 32001) as regions09,
           dbms_lob.substr(regions, 4000, 36001) as regions10
      from q
    /

140642 rows selected.

Elapsed: 00:00:36.47

Execution Plan
----------------------------------------------------------
Plan hash value: 3024530331 -- план естественно непринципиально изменился

------------------------------------------------------------------------------------
| Id  | Operation                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |   148K|   285M|       | 10018   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |       |       |       |            |          |
|   2 |   LOAD AS SELECT           |       |       |       |            |          |
|   3 |    SORT GROUP BY           |   148K|  2611K|       |  9947   (1)| 00:00:01 |
|   4 |     VIEW                   |   438K|  7709K|       |  9947   (1)| 00:00:01 |
|   5 |      HASH UNIQUE           |   438K|    17M|    21M|  9947   (1)| 00:00:01 |
|*  6 |       FILTER               |       |       |       |            |          |
|*  7 |        HASH JOIN           |   759K|    29M|    11M|  7031   (1)| 00:00:01 |
|*  8 |         HASH JOIN          |   273K|  8538K|  4080K|  5480   (1)| 00:00:01 |
|*  9 |          TABLE ACCESS FULL |   154K|  2265K|       |  4443   (2)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL |   267K|  4440K|       |   547   (1)| 00:00:01 |
|  11 |         TABLE ACCESS FULL  |   757K|  6655K|       |   407   (2)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN    |     3 |    15 |       |     1   (0)| 00:00:01 |
|  13 |   VIEW                     |   148K|   285M|       |    71   (2)| 00:00:01 |
|  14 |    TABLE ACCESS FULL       |   148K|  2611K|       |    71   (2)| 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          3  recursive calls
        115  db block gets
     213191  consistent gets
        106  physical reads
        616  redo size
    1549662  bytes sent via SQL*Net to client       --*
        860  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     140642  rows processed

— что по времени сравнимо с CAST(COLLECT()), но стабильно и, видимо, оптимально передаёт по сети (*) всего 1,5MB за 30 roundtrip-ов

/*+ MATERIALIZE */ в последнем случае оказывается абсолютно необходим, поскольку нематериализованный Subquery Factoring (похоже, используя DBMS_LOB.SUBSTR в «жёсткой» связке с XMLAGG ?):

SQL>
with q as 
      (SELECT shop_id,
              xmlagg(xmlelement(s, region_id || ';')).extract('//text()')
              .getclobval() as regions
         FROM SHOPS_BY_REGIONS_VIEW
        GROUP BY shop_id)
SELECT shop_id,
       dbms_lob.substr(regions, 4000, 1)     as regions01,
       dbms_lob.substr(regions, 4000, 4001)  as regions02,
       dbms_lob.substr(regions, 4000, 8001)  as regions03,
       dbms_lob.substr(regions, 4000, 12001) as regions04,
       dbms_lob.substr(regions, 4000, 16001) as regions05,
       dbms_lob.substr(regions, 4000, 20001) as regions06,
       dbms_lob.substr(regions, 4000, 24001) as regions07,
       dbms_lob.substr(regions, 4000, 28001) as regions08,
       dbms_lob.substr(regions, 4000, 32001) as regions09,
       dbms_lob.substr(regions, 4000, 36001) as regions10
  from q
/

140642 rows selected.

Elapsed: 00:05:17.46        -- выполняется так же неэффективно как и простой XMLAGG (время выполнения прямо пропорционально кол-ву выбираемых VARCHAR2)

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528 -- с прежним планом

Statistics                  -- , но лучшей статистикой
----------------------------------------------------------
         87  recursive calls
          8  db block gets
     214451  consistent gets
          0  physical reads
        868  redo size
    5353344  bytes sent via SQL*Net to client
        860  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     140642  rows processed

— в частности, более эффективным (но не оптимальным) сетевым обменом за счёт передачи VARCHAR2 полей

P.S.
xmlagg.extract.getclobval() творит чудеса:

SQL>
SELECT shop_id,
       xmlagg(xmlelement(s, region_id || ';')).extract('//text()').getclobval() as regions
  FROM SHOPS_BY_REGIONS_VIEW
 GROUP BY shop_id
/

140647 rows selected.

Elapsed: 00:01:23.92

Execution Plan
----------------------------------------------------------
Plan hash value: 2533425528

Statistics
----------------------------------------------------------
         87  recursive calls
          8  db block gets
     213713  consistent gets
          1  physical reads
       1424  redo size
   97718069  bytes sent via SQL*Net to client
   93616340  bytes received via SQL*Net from client
     441679  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     140647  rows processed

и ещё бОльшие — с /*+ MATERIALIZE*/ — неожиданно:

SQL>
with q as
 (SELECT /*+ materialize*/
   shop_id,
   xmlagg(xmlelement(s, region_id || ';')).extract('//text()').getclobval() as regions
    FROM SHOPS_BY_REGIONS_VIEW
   GROUP BY shop_id)
SELECT shop_id, regions from q
/
140646 rows selected.

Elapsed: 00:00:31.80

Execution Plan
----------------------------------------------------------
Plan hash value: 3024530331

Statistics
----------------------------------------------------------
          3  recursive calls
        117  db block gets
     353698  consistent gets
        106  physical reads
        676  redo size
   28688286  bytes sent via SQL*Net to client
    1547658  bytes received via SQL*Net from client
     140648  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     140646  rows processed

— плюс во втором случае заметна оптимизация трафика

Реклама

Добавить комментарий »

Комментариев нет.

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