alter session set workarea_size_policy=manual
alter session set sort_area_size=<очень мало>

set serveroutput on

declare
  l_create_sql varchar2(1000) := 'create table t as
select rownum study_site, trunc(dbms_random.value(1,10+1))*5000 cnt
from
(select * from dual connect by level <= 1e2) t1,
(select * from dual connect by level <= 1e#) t2';
  s            timestamp;
begin

  for i in 0 .. 4 loop

    dbms_output.put_line('Iteration ' || i || ' Records ' || 1e3 * 10 ** i);

    execute immediate 'drop table t';
    execute immediate replace(l_create_sql, '#', i);

    execute immediate 'truncate table z';

    s := systimestamp;
    insert --+ append
    --+ plsql zzz
    into z
      select min(study_site) mi, max(study_site) ma, sum(cnt) su
        from (select *
                from table(foo(cursor (select study_site, cnt
                                  from t
                                 order by study_site),
                               65000)))
       group by the_group
       order by the_group;
    dbms_output.put_line(rpad('PL/SQL', 10, ' ') ||
                         to_char(systimestamp - s));

    execute immediate 'truncate table z';

    s := systimestamp;
    -- NoFormat Start
    insert --+ append
    --+ model zzz
    into z
    SELECT s, MAX(e), MAX(sm) FROM (
        SELECT s, e, cnt, sm FROM t
         MODEL DIMENSION BY(row_number()
                                over(order by study_site) rn)
               MEASURES(study_site s, study_site e, cnt, cnt sm)
               RULES(sm[rn > 1] =
                       CASE WHEN (sm[cv() - 1] + cnt[cv()]) > 65000
                                  OR cnt[cv()] > 65000
                            THEN cnt[cv()]
                            ELSE sm[cv() - 1] + cnt[cv()]
                        END,
                     s[rn > 1] =
                       CASE WHEN(sm[cv() - 1] + cnt[cv()]) > 65000
                                 OR cnt[cv()] > 65000
                            THEN s[cv()]
                            ELSE s[cv() - 1]
                        END))
      GROUP BY s ORDER BY s;
    -- NoFormat End
    dbms_output.put_line(rpad('MODEL', 10, ' ') ||
                         to_char(systimestamp - s));

  end loop;

end;
/
Iteration 0 Records 1000
PL/SQL    +000000000 00:00:00.036000000
MODEL     +000000000 00:00:00.014000000
Iteration 1 Records 10000
PL/SQL    +000000000 00:00:00.031000000
MODEL     +000000000 00:00:00.028000000
Iteration 2 Records 100000
PL/SQL    +000000000 00:00:00.072000000
MODEL     +000000000 00:00:00.228000000
Iteration 3 Records 1000000
PL/SQL    +000000000 00:00:00.543000000
MODEL     +000000000 00:00:02.217000000
Iteration 4 Records 10000000
PL/SQL    +000000000 00:00:04.362000000
MODEL     +000000000 00:00:22.500000000

PL/SQL procedure successfully completed.


set lines 100 pages 100
column sign format a15
select ash.*,
       regexp_substr(sql_text, '--\+ [^ ]+', 1, 2) sign
  from (select sql_id,
               count(*) cnt,
               max(ash.pga_allocated) / 1024 pga_kb,
               nullif(max(ash.temp_space_allocated) / 1024, 1024) temp_kb,
               sql_exec_id
          from v$active_session_history ash
         where ash.sample_time > sysdate - 5 / (24 * 60)
         group by ash.sql_id, sql_exec_id) ash
  join v$sql v
    on ash.sql_id = v.sql_id
   and sql_text like '%--+ append%zzz%'
   and sql_text not like '%v$sql%'
 order by sql_text, sql_exec_id;

SQL_ID               CNT     PGA_KB    TEMP_KB SQL_EXEC_ID SIGN
------------- ---------- ---------- ---------- ----------- ---------------
6buf0spwk6tzn          3      11955               16777219 --+ model
6buf0spwk6tzn         22      96947               16777220 --+ model
d7ysg6gr2mt5r          4      54771               16777220 --+ plsql

select ash.*, regexp_substr(sql_text, '--\+ [^ ]+', 1, 2) sign
  from (select sql_id,
               count(*) cnt,
               max(ash.pga_allocated) / 1024 pga_kb,
               nullif(max(ash.temp_space_allocated) / 1024, 1024) temp_kb,
               sql_exec_id
          from v$active_session_history ash
         where ash.sample_time > sysdate - 1 / (24)
         group by ash.sql_id, sql_exec_id) ash
  join v$sql v
    on ash.sql_id = v.sql_id
   and sql_text like '%--+ append%yyyyy%'
   and sql_text not like '%v$sql%'
 order by 6, 2;

SQL_ID               CNT     PGA_KB    TEMP_KB SQL_EXEC_ID SIGN
------------- ---------- ---------- ---------- ----------- ---------------
abnq6xymjag8v          3      12356               16777219 --+ model
abnq6xymjag8v         24     102084               16777220 --+ model
abnq6xymjag8v       1793     191155    1025024    16777221 --+ model
0muc2rqf46yp6          1       5875                        --+ plsql
0muc2rqf46yp6          4      51908               16777220 --+ plsql
0muc2rqf46yp6         58      95795     361472    16777221 --+ plsql

SQL> select product, time_id, quant, oper, decode(c,1,q) fv, bug_case, bug_decode
  2  from (select it.*, trunc(time_id) x from invent_table it)
  3  match_recognize
  4  (
  5    partition by product, x
  6    order by time_id desc
  7    measures
  8      case when count(*) = 1 then 2 else 3 end bug_case,
  9      decode(count(*), 1, 2, 3) bug_decode,
 10      count(*) c,
 11      final last(d.quant) q
 12    all rows per match with unmatched rows
 13    pattern (D+ dummy*)
 14    define
 15      d as d.oper = 'D'
 16  )
 17  order by product, trunc(time_id), time_id;

PRODUC TIME_ID   QU O FV   BUG_CASE BUG_DECODE
------ --------- -- - -- ---------- ----------
bottle 01-APR-01 10 I
bottle 06-APR-01 8  D
bottle 06-APR-01 9  D 8
can    01-APR-01 15 I
can    04-APR-01 5  U
can    04-APR-01 11 D
can    04-APR-01 7  I
can    04-APR-01 77 I
can    04-APR-01 99 D
can    04-APR-01 10 D
can    04-APR-01 11 D 99

11 rows selected.