--/*
set autoddl on;
show version;
commit;
set term ^;create or alter procedure sp_huge_ext as begin end^set term ;^
commit;
recreate table tdetl(id int); commit;
recreate table tmain(id int); commit;
recreate table tlog(id int); commit;

recreate sequence gmain;
recreate sequence gdetl;
recreate table tdetl(id bigint primary key using index tdetl_pk_idx, pid bigint, s01 varchar(36), s02 varchar(36));
recreate table tmain(id bigint primary key using index tmain_pk_idx, s01 varchar(36), s02 varchar(36));
recreate table tlog( gmain bigint, gdetl bigint, dts_before timestamp, dts_after timestamp, elapsed_ms computed by(datediff(millisecond from dts_before to dts_after)) );

alter table tdetl add constraint tdetl_fk foreign key(pid) references tmain using index tdetl_fk_idx;
create index tmain_s01 on tmain(s01);
create index tmain_s02 on tmain(s02);
create index tdetl_s01 on tdetl(s01);
create index tdetl_s02 on tdetl(s02);
commit;
set stat on;
set list on;
select * from mon$database;
commit;
set list off;
set stat off;
--*/

set term ^;
create or alter procedure sp_huge_ext(a_n_main int = -1) returns(msg varchar(100)) as
  declare n_main int = 2000000;
  declare n_detl int = 100; -- child records in tdetl per each parent row in tmain
  declare v_id bigint;
  declare i int;
  declare j int;
  declare verbose_step int=100;
  declare tx timestamp;
  declare tc timestamp;
  declare elapsed_ms_now int;
  declare elapsed_ms_min int = 999999999;
  declare elapsed_ms_max int = -1;
  declare msg4min varchar(25);
  declare msg4max varchar(25);
begin
  n_main=iif(a_n_main > 0, a_n_main, n_main);
  tx=cast('now' as timestamp);
  while(n_main>0) do begin
    j=verbose_step;
    while(j>0) do begin

        n_main = n_main-1; -- main counter

        insert into tmain(id, s01, s02)
        values( gen_id(gmain,1), uuid_to_char(gen_uuid()), uuid_to_char(gen_uuid()) )
        returning :j-1, id into j, v_id;

        i=n_detl;
        while (i>0) do
          insert into tdetl(id, pid, s01,s02)
          values( gen_id(gdetl,1), :v_id, uuid_to_char(gen_uuid()), uuid_to_char(gen_uuid()) )
          returning :i-1 into i;
    end -- j=verbose_step downto 1

    tc=cast('now' as timestamp);
    insert into tlog(gmain, gdetl, dts_before, dts_after)
    values( gen_id(gmain,0), gen_id(gdetl,0), :tx, :tc )
    returning elapsed_ms into elapsed_ms_now;

    if ( elapsed_ms_min > elapsed_ms_now ) then begin
      elapsed_ms_min=elapsed_ms_now;
      msg4min='min_ms='||elapsed_ms_min||' at '||left(right(tc,13),8);
    end

    if ( elapsed_ms_max < elapsed_ms_now ) then begin
      elapsed_ms_max=elapsed_ms_now;
      msg4max='max_ms='||elapsed_ms_max||' at '||left(right(tc,13),8);
    end

    tx=cast('now' as timestamp);

    msg=left(right(tx,13),8)||': gdetl='||right(cast(1000000000+gen_id(gdetl,0) as varchar(10)),9)||'  '||msg4min||'  curr_ms='||elapsed_ms_now||'  '||msg4max;
    suspend;

  end -- while (n_main>0)
end
^set term ;^
commit;

set transaction read write read committed record_version no wait;
select * from sp_huge_ext for update;
commit;

delay=10
fdb=huge_noext
fbport=3333
fbhome=/opt/fb30trnk
log=./logs/mon30_$(date +'%y%m%d_%H%M%S').log
err=./logs/mon30_$(date +'%y%m%d_%H%M%S').err
rm -f $log
while :
do
  echo -----------------------------------------------------------------------------------
  echo $(echo -n $(date +'%Y-%m-%d %H:%M:%S.%N')|cut -c1-24) - starting quering mon tables
  echo check result in $log, errors in $err
  echo $fbhome/bin/isql localhost/$fbport:$fdb -user sysdba -pas masterke -pag 999 -n i ...
  $fbhome/bin/isql localhost/$fbport:$fdb -user sysdba -pas masterke -pag 999 -n \
                    -i askmon30.sql  2>>$err | sed -e "s/ \{1,\}$//" >>$log
  ls -la $log $err
  p=$delay # temply, 30.10.2013 1658, was: $(( $delay + RANDOM % 30))
  echo $(echo -n $(date +'%Y-%m-%d %H:%M:%S.%N')|cut -c1-24) - done. Now take pause $p seconds. . .
  sleep $p
done

set autoddl off;
set list off;
set blob on;
set width mon_user 20;
set width remote_process 30;
set width sql_txt 50;
set width remote_ip 15;
set width rn 3;
commit;
    select
      current_time dts
      -- mon$attachments:
      ,cast(row_number()over(order by mon$attachment_id) as char(3)) rn
      ,a.mon$remote_address remote_IP
      ,a.mon$attachment_id attach_id
      ,a.mon$user mon_user
      ,a.mon$stat_id       stat_id
      ,a.mon$server_pid    server_PID
      ,a.mon$remote_pid    remote_PID
      -- mon$memory_usage:
      ,u.mon$memory_used used_memory
      ,u.mon$memory_allocated alloc_by_OS
      -- mon$io_stats:
      ,i.mon$page_reads reads
      ,i.mon$page_writes writes
      ,i.mon$page_fetches fetches
      ,i.mon$page_marks marks
      -- mon$record_stats:
      ,r.mon$record_seq_reads seq_reads
      ,r.mon$record_idx_reads idx_reads
      ,r.mon$record_inserts ins_cnt
      ,r.mon$record_updates upd_cnt
      ,r.mon$record_deletes del_cnt
      ,r.mon$record_backouts bk_outs
      ,r.mon$record_purges purges
      ,r.mon$record_expunges expunges
      -- aux info:
      ,right(a.mon$remote_process,30) remote_process
    from mon$attachments a
        left join mon$memory_usage u on a.mon$stat_id=u.mon$stat_id
        left join mon$io_stats i on a.mon$stat_id=i.mon$stat_id
        left join mon$record_stats r on a.mon$stat_id=r.mon$stat_id
    where
      a.mon$attachment_id<>current_connection
;

          DTS RN  REMOTE_IP          ATTACH_ID MON_USER                  STAT_ID   SERVER_PID   REMOTE_PID           USED_MEMORY           ALLOC_BY_OS                 READS                WRITES               FETCHES                 MARKS             SEQ_READS             IDX_READS               INS_CNT               UPD_CNT               DEL_CNT               BK_OUTS                PURGES              EXPUNGES REMOTE_PROCESS
============= === =============== ============ ==================== ============ ============ ============ ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ==============================
12:10:59.0000 1   <null>                    13 Cache Writer                   12        28998       <null>                  9672                131072                     0              32097624                     2                     1                     0                     0                     0                     0                     0                     0                     0                     0 <null>
12:10:59.0000 2   127.0.0.1                 14 SYSDBA                          5        28998        29279               1810928               3427992              30686726               4024446            2487716139             517814324                   319                   947              84215045                    57                    29                     9                     2                     0 /opt/fb30trnk/bin/isql
12:10:59.0000 3   <null>                    15 Garbage Collector              10        28998       <null>                188928                327680                     2                    91                  1474                   159                   502                     0                     0                     0                     0                     0                     3                    29 <null>


          DTS RN  REMOTE_IP          ATTACH_ID MON_USER                  STAT_ID   SERVER_PID   REMOTE_PID           USED_MEMORY           ALLOC_BY_OS                 READS                WRITES               FETCHES                 MARKS             SEQ_READS             IDX_READS               INS_CNT               UPD_CNT               DEL_CNT               BK_OUTS                PURGES              EXPUNGES REMOTE_PROCESS
============= === =============== ============ ==================== ============ ============ ============ ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ===================== ==============================
12:11:09.0000 1   <null>                    13 Cache Writer                   12        28998       <null>                  9672                131072                     0              32226473                     2                     1                     0                     0                     0                     0                     0                     0                     0                     0 <null>
12:11:09.0000 2   127.0.0.1                 14 SYSDBA                          5        28998        29279               1814120               3427992              30814107               4030330            2491296812             518546973                   319                   947              84334190                    57                    29                     9                     2                     0 /opt/fb30trnk/bin/isql
12:11:09.0000 3   <null>                    15 Garbage Collector              10        28998       <null>                188928                327680                     2                    91                  1474                   159                   502                     0                     0                     0                     0                     0                     3                    29 <null>

-- created auto, do NOT edit!
-- run: $fbhome/bin/isql localhost/$fbport:$fdb -pag 999 -n -i askmon.tmp 2>>$err | sed -e "s/ \{1,\}$//" >>$log 
set autoddl off;
set list off;
set blob on;
set width dts 12;
set width mon_user 20;
set width remote_process 30;
set width sql_txt 50;
set width remote_ip 15;
set width rn 3;
set heading off;
select substring(cast(current_timestamp as varchar(25)) from 12 for 12) dts,'iter #'||1 msg from rdb$database;
set heading on;
 commit; 
select
     substring(cast(current_timestamp as varchar(25)) from 12 for 12) dts
     -- mon$attachments:
     ,cast(row_number()over(order by mon$attachment_id) as char(3)) rn
     . . . 
;
where
      a.mon$attachment_id<>current_connection
;
shell sleep 0.5;


set heading off;
select substring(cast(current_timestamp as varchar(25)) from 12 for 12) dts,'iter #'||2 msg from rdb$database;
 commit; 
select
     substring(cast(current_timestamp as varchar(25)) from 12 for 12) dts
     -- mon$attachments:
     ,cast(row_number()over(order by mon$attachment_id) as char(3)) rn
   . . . 
where
      a.mon$attachment_id<>current_connection
;
shell sleep 0.5;
. . . и так далее 10 раз . . .

@echo off
cls
set sql=askmon.tmp
set n=%1
set p=%2
if .%1.==.. set n=5
if .%2.==.. set p=10

echo -- created auto, do NOT edit!>%sql%
echo -- run: $fbhome/bin/isql localhost/$fbport:$fdb -pag 999 -n -i %sql% 2^>^>$err ^| sed -e "s/ \{1,\}$//" ^>^>$log >>%sql%
echo set autoddl off;>>%sql%
echo set list off;>>%sql%
echo set blob on;>>%sql%
echo set width dts 12;>>%sql%
echo set width mon_user 20;>>%sql%
echo set width remote_process 30;>>%sql%
echo set width sql_txt 50;>>%sql%
echo set width remote_ip 15;>>%sql%
echo set width rn 3;>>%sql%
for /l %%x in (1, 1, %n%) do (
  echo set heading off;>>%sql%
  echo select substring(cast(current_timestamp as varchar(25^)^) from 12 for 12^) dts,'iter #'^|^|%%x msg from rdb$database;>>%sql%
  if .%%x.==.1. echo set heading on;>>%sql%
  echo commit;>>%sql%
  echo select>>%sql%
  echo      substring(cast(current_timestamp as varchar(25^)^) from 12 for 12^) dts>>%sql%
  echo      -- mon$attachments:>>%sql%
  echo      ,cast^(row_number^(^)over^(order by mon$attachment_id^) as char^(3^)^) rn>>%sql%
  echo      ,a.mon$remote_address remote_IP>>%sql%
  echo      ,a.mon$attachment_id attach_id>>%sql%
  echo      ,a.mon$user mon_user>>%sql%
  echo      ,a.mon$stat_id       stat_id>>%sql%
  echo      ,a.mon$server_pid    server_PID>>%sql%
  echo      ,a.mon$remote_pid    remote_PID>>%sql%
  echo      -- mon$memory_usage:>>%sql%
  echo      ,u.mon$memory_used used_memory>>%sql%
  echo      ,u.mon$memory_allocated alloc_by_OS>>%sql%
  echo      -- mon$io_stats:>>%sql%
  echo      ,i.mon$page_reads reads>>%sql%
  echo      ,i.mon$page_writes writes>>%sql%
  echo      ,i.mon$page_fetches fetches>>%sql%
  echo      ,i.mon$page_marks marks>>%sql%
  echo      -- mon$record_stats:>>%sql%
  echo      ,r.mon$record_seq_reads seq_reads>>%sql%
  echo      ,r.mon$record_idx_reads idx_reads>>%sql%
  echo      ,r.mon$record_inserts ins_cnt>>%sql%
  echo      ,r.mon$record_updates upd_cnt>>%sql%
  echo      ,r.mon$record_deletes del_cnt>>%sql%
  echo      ,r.mon$record_backouts bk_outs>>%sql%
  echo      ,r.mon$record_purges purges>>%sql%
  echo      ,r.mon$record_expunges expunges>>%sql%
  echo      -- aux info:>>%sql%
  echo      ,right(a.mon$remote_process,30^) remote_process>>%sql%
  echo      -- mon$statements:>>%sql%
  echo      --,left(cast(s.mon$sql_text as varchar(32760^)^),50^) sql_txt>>%sql%
  echo from mon$attachments a>>%sql%
  echo       left join mon$memory_usage u on a.mon$stat_id=u.mon$stat_id>>%sql%
  echo       left join mon$io_stats i on a.mon$stat_id=i.mon$stat_id>>%sql%
  echo       left join mon$record_stats r on a.mon$stat_id=r.mon$stat_id>>%sql%
  echo where>>%sql%
  echo       a.mon$attachment_id^<^>current_connection>>%sql%
  echo ;>>%sql%
  if .%%x.==.%n%. exit
  echo shell sleep %p%;>>%sql%
  echo.>>%sql%
  echo.>>%sql%
)

2014-01-19T01:26:06.1380 (19458:0x7f1cd9294d08) EXECUTE_STATEMENT_FINISH
        /var/db/fb30/askmon_n.fdb (ATT_84, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30/bin/isql:23275
                (TRA_2028, CONCURRENCY | WAIT | READ_WRITE)

Statement 172:
-------------------------------------------------------------------------------
select * from sp_huge_ext(20000) for update
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (SP_HUGE_EXT NATURAL)
200 records fetched
 422175 ms, 7850564 read(s), 99833 write(s), 58629037 fetch(es), 12358863 mark(s)

Table                             Natural     Index    Update    Insert    Delete
unge
*********************************************************************************
****
RDB$PAGES                                                            20          

RDB$INDICES                                       9                              

RDB$RELATION_CONSTRAINTS                          2                              

TDETL                                                           2000000          

TMAIN                                                             20000          

TLOG                                                                200