#################
2.5; buff = 65535
#################

#############  gtt on commit DELETE rows  ##################
SQL> set stat on; insert into gttdel select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows 1000000; commit;  quit ;
Current memory = 283260972
Delta memory = 106148
Max memory = 283878040
Elapsed time= 5.17 sec   5.16   5.14   5.01   5.17
Buffers = 65535
Reads = 35
Writes 26
Fetches = 5237790
Current memory = 283253164
Delta memory = -7808
Max memory = 283878040
Elapsed time= 0.27 sec   0.27   0.27   0.26   0.26
Buffers = 65535
Reads = 1
Writes 20434
Fetches = 20456

trace:
------
insert into gttdel select 'abcdefghijklmnopqrstuvwxyz0123456789' from r
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (RDB$FIELDS NATURAL, RDB$FIELDS NATURAL, RDB$FIELDS NATURAL)
0 records fetched
   5204 ms, 1 read(s), 26 write(s), 5237112 fetch(es), 1122525 mark(s)

Table                             Natural     Index    Update    Insert
***********************************************************************
RDB$FIELDS                        1008198
GTTDEL                                                          1000000
<...>
2013-08-01T23:30:14.5780 (1264:01F1BEB8) COMMIT_TRANSACTION
        perf25 (ATT_38, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
        C:\MIX\firebird\fb25\bin\isql.exe:2084
                (TRA_82, CONCURRENCY | WAIT | READ_WRITE)
   2186 ms, 1 read(s), 20434 write(s), 20456 fetch(es), 20433 mark(s)


#############  gtt on commit PRESERVE rows  ##################
SQL> set stat on; insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows 1000000; commit;  quit; 
Database:  192.168.0.201/3252:perf25, User: sysdba
SQL> set stat on; insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows
 1000000; commit; quit;
Current memory = 283260980
Delta memory = 106156
Max memory = 283878048
Elapsed time= 5.27 sec   5.06   5.17   5.53   5.30
Buffers = 65535
Reads = 35
Writes 26
Fetches = 5237795
Current memory = 283253324
Delta memory = -7656
Max memory = 283878048
Elapsed time= 0.27 sec   0.27   0.27   0.25   0.26
Buffers = 65535
Reads = 1
Writes 20434
Fetches = 1

trace:
------
insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from r
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (RDB$FIELDS NATURAL, RDB$FIELDS NATURAL, RDB$FIELDS NATURAL)
0 records fetched
   5346 ms, 1 read(s), 26 write(s), 5237112 fetch(es), 1122525 mark(s)

Table                             Natural     Index    Update    Insert
***********************************************************************
RDB$FIELDS                        1008198
GTTSAV                                                          1000000
<...>
2013-08-01T23:30:28.4060 (1264:01F1BEB8) COMMIT_TRANSACTION
        perf25 (ATT_39, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
        C:\MIX\firebird\fb25\bin\isql.exe:2016
                (TRA_83, CONCURRENCY | WAIT | READ_WRITE)
    259 ms, 1 read(s), 20434 write(s), 1 fetch(es), 1 mark(s)

#################
3.0; buff = 65535
#################

#############  gtt on commit DELETE rows  ##################
SQL> set stat on; insert into gttdel select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows 1000000; commit;  quit; 
Current memory = 810656
Delta memory = 380696
Max memory = 1448536
Elapsed time= 6.41 sec   6.47   6.16   6.31   6.36
Buffers = 65535
Reads = 42
Writes 28
Fetches = 5242686
Current memory = 789408
Delta memory = -21248
Max memory = 1448536
Elapsed time= 0.29 sec   0.30   0.30   0.28   0.30
Buffers = 65535
Reads = 0
Writes 20435
Fetches = 20458

trace:
------
insert into gttdel select 'abcdefghijklmnopqrstuvwxyz0123456789' from rd
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (RDB$FIELDS NATURAL, RDB$FIELDS NATURAL, RDB$FIELDS NATURAL)
0 records fetched
   6301 ms, 4 read(s), 28 write(s), 5242479 fetch(es), 1122529 mark(s)

Table                             Natural     Index    Update    Insert
************************************************************************
RDB$FIELDS                        1007195
GTTDEL                                                          1000000

2013-08-01T23:16:53.5620 (3240:00F12A08) COMMIT_TRANSACTION
        perf30 (ATT_101, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
        C:\MIX\firebird\fb25\bin\isql.exe:1604
                (TRA_80, CONCURRENCY | WAIT | READ_WRITE)
    288 ms, 20435 write(s), 20458 fetch(es), 20434 mark(s)



#############  gtt on commit PRESERVE rows  ##################
set stat on; insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows 1000000; commit;  quit; 
Database:  192.168.0.201/3330:perf30, User: sysdba
SQL> set stat on; insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from rdb$fields,rdb$fields,rdb$fields rows
 1000000; commit; quit;
Current memory = 810664
Delta memory = 380704
Max memory = 1448544
Elapsed time= 6.36 sec    6.26   6.26   6.22  6.41
Buffers = 65535
Reads = 42
Writes 28
Fetches = 5242686
Current memory = 789592
Delta memory = -21072
Max memory = 1448544
Elapsed time= 0.29 sec    0.28   0.26   0.28  0.26
Buffers = 65535
Reads = 0
Writes 20435
Fetches = 1


trace:
------
insert into gttsav select 'abcdefghijklmnopqrstuvwxyz0123456789' from r
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (RDB$FIELDS NATURAL, RDB$FIELDS NATURAL, RDB$FIELDS NATURAL)
0 records fetched
   6207 ms, 4 read(s), 28 write(s), 5242479 fetch(es), 1122529 mark(s)

Table                             Natural     Index    Update    Insert
***********************************************************************
RDB$FIELDS                        1007195
GTTSAV                                                          1000000
<...>
2013-08-01T23:17:08.6090 (3240:00F11900) COMMIT_TRANSACTION
        perf30 (ATT_104, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
        C:\MIX\firebird\fb25\bin\isql.exe:608
                (TRA_81, CONCURRENCY | WAIT | READ_WRITE)
    279 ms, 20435 write(s), 1 fetch(es), 1 mark(s)

#################
 2.5 ; buff = 65535
#################

#############  gtt on commit DELETE rows  ##################
isql 192.168.0.201/3252:perf25 -n -user sysdba -pas masterke -i insgtt del .sql

Current memory = 283254328
Delta memory = 99492
Max memory = 283871644
Elapsed time=  5.11 sec    5.11    4.63   5.08   4.88   4.76   5.03   4.92   5.16   5.06 
Buffers = 65535
Reads = 34
Writes 26
Fetches = 3163667
Current memory = 283245252
Delta memory = -9076
Max memory = 283871644
Elapsed time= 0.26 sec  
Buffers = 65535
Reads = 1
Writes 20434
Fetches = 20456


trace:
------
execute block as
declare n int=1000000;
begin
  while(n>0) do insert into gttdel values('abcdefghijklmnopqrstuvwxyz012
end
0 records fetched
   5103 ms, 1 read(s), 26 write(s), 3163342 fetch(es), 1122525 mark(s)

Table                             Natural     Index    Update    Insert
xpunge
************************************************************************
******
GTTDEL                                                          1000000


#############  gtt on commit PRESERVE rows  ##################
isql 192.168.0.201/3252:perf25 -n -user sysdba -pas masterke -i insgtt sav .sql

Current memory = 283254340
Delta memory = 99500
Max memory = 283871656
Elapsed time=  4.78 sec    4.91   4.81   4.97   4.85   4.83   4.91   5.07   4.96   5.14 
Buffers = 65535
Reads = 34
Writes 26
Fetches = 3163672
Current memory = 283245404
Delta memory = -8936
Max memory = 283871656
Elapsed time= 0.26 sec
Buffers = 65535
Reads = 1
Writes 20434
Fetches = 1

trace:
------
execute block as
declare n int=1000000;
begin
  while(n>0) do insert into gttsav values('abcdefghijklmnopqrstuvwxyz01
end
0 records fetched
   5127 ms, 1 read(s), 26 write(s), 3163342 fetch(es), 1122525 mark(s)

Table                             Natural     Index    Update    Insert
xpunge
***********************************************************************
******
GTTSAV                                                          1000000

///////////////////////////////////////////////////////////////////////////////////////////////
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

#################
 3.0 ; buff = 65535
#################

#############  gtt on commit DELETE rows  ##################
C:\MIX\firebird\fb25>isql 192.168.0.201/3330:test30 -user sysdba -pas masterke -n -i insgtt del .sql
Current memory = 812232
Delta memory = 383224
Max memory = 1450312
Elapsed time=  5.75 sec   5.73   5.67   5.82   5.64   5.74   5.66   5.75   6.14   5.76 
Buffers = 65535
Reads = 39
Writes 28
Fetches = 3163552
Current memory = 788232
Delta memory = -24000
Max memory = 1450312
Elapsed time= 0.28 sec
Buffers = 65535
Reads = 0
Writes 20435
Fetches = 20458
trace:
   5759 ms, 1 read(s), 28 write(s), 3163346 fetch(es), 1122529 mark(s)

Table                             Natural     Index    Update    Insert
***********************************************************************
GTTDEL                                                          1000000

#############  gtt on commit PRESERVE rows  ##################
C:\MIX\firebird\fb25>isql 192.168.0.201/3330:test30 -user sysdba -pas masterke -n -i insgtt sav .sql
Current memory = 812232
Delta memory = 383224
Max memory = 1450312
Elapsed time=  6.22 sec   5.72   5.87   6.00   5.80   5.67   5.59   5.69   5.91   5.82 
Buffers = 65535
Reads = 39
Writes 28
Fetches = 3163552
Current memory = 788416
Delta memory = -23816
Max memory = 1450312
Elapsed time= 0.27 sec
Buffers = 65535
Reads = 0
Writes 20435
Fetches = 1

trace:
   5824 ms, 1 read(s), 28 write(s), 3163346 fetch(es), 1122529 mark(s)

Table                             Natural     Index    Update    Insert
xpunge
*************************************************************************
GTTSAV                                                          1000000

 -- create sequence g; commit;

alter sequence g restart with 0;
commit;
recreate table tbig (
    id  integer not null,
    n01 integer not null,
    c01 varchar(20),
    c02 varchar(20)
);
commit;

insert into tbig
select
    gen_id(g,1),
    rand()*1000,
    rpad('', 4, uuid_to_char( gen_uuid() ) ),
    rpad('', 8, uuid_to_char( gen_uuid() ) )
from  rdb$types,rdb$types,rdb$types
rows 10000000;
commit;
---------
create index tbig_n01 on tbig(n01);
create index tbig_c01 on tbig(c01);
create index tbig_c02 on tbig(c02);
commit;

SQL> set stat on; set plan on; select count(*) from (select * from tbig where n01= :p1  rows 10) t1 join tbig t2 on t2.c02 starting with t1.c01; 
// вместо :p1 подставляю разные числа от 0 до 999.

PLAN JOIN (T1 TBIG INDEX (TBIG_N01), T2 INDEX (TBIG_C02))
// план одинаковый и на 2.5 и на 3.0

Current memory = 283445928
Delta memory = 179608
Max memory = 283455816
Elapsed time=  8.41 sec 
Buffers = 65535
Reads = 1445
Writes 0
Fetches = 3596

Current memory = 1020968
Delta memory = 1320
Max memory = 1985560
Elapsed time=  5.55 sec 
Buffers = 65535
Reads = 990
Writes 0
Fetches = 2540

-- это результат снятия статистики непосредственно перед выполнением запроса  (первого и единственного):
 MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0                     0                     0                     0                        0 
           2              1             283236656             285421568             283239700                285421568 
           3              2                  5488                     0                  5552                        0 
           4              3                  4896                     0                  6472                        0 

-- это результат снятия статистики сразу по окончании запроса:
 MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0                     0                     0                     0                        0 
           2              1             283236652             285421568             283254492                285421568 
           3              2                  5116                     0                  5180                        0 
           4              3                  4752                     0                  6472                        0 
           5              1             283431216             285683712             283436888                285683712 
           6              2                  1660                     0                  1660                        0 
           7              3                184324                     0                185516                    69632 

-- это результат снятия статистики непосредственно перед выполнением запроса (первого и единственного):
MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0                626432                393216                638552                   458752 
           2              1                389280                 65536                401720                4294901760 
            3              2                  6704                     0                  6784                        0 
           4              3                 10304                 65536                 12160                    65536 
           5              1                124656                     0                124656                        0 
           6              1                  7344                     0                  7344                        0 

-- это результат снятия статистики сразу по окончании запроса:
 MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0               1517784                995208               1548440                  1126280 
           2              1                692256                401328                726168               4294901760 
           3              2                  2336                     0                  2336                        0 
           4              3                200008                 73648                200632               4294901760 
           5              1                389184            4294901760                407864               4294901760 
           6              2                  6704                     0                  6784                        0 
           7              3                 10112            4294901760                 12160               4294901760 
           8              1                135864                     0                155944                        0 
           9              2                   736                     0                   736                        0 
          10              1                  7344                     0                  7344                        0

 MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0             277742208             279064576             277747780                279064576 
           2              1                 16248                     0                 16320                        0 
           3              2                  5488                     0                  5552                        0 
           4              3                  4896                     0                  6472                        0 

-----


 MON$STAT_ID MON$STAT_GROUP       MON$MEMORY_USED  MON$MEMORY_ALLOCATED   MON$MAX_MEMORY_USED MON$MAX_MEMORY_ALLOCATED 
============ ============== ===================== ===================== ===================== ======================== 
           1              0             277761268             279261184             277952620                279326720 
           2              1                 16776                     0                199160                    69632 
           3              2                  5100                     0                  5164                        0 
           4              3                  4744                     0                  6460                        0