hi all
WI-V2.5.3.26727 Firebird 2.5, работает как SuperClassic
(строка запуска: C:\1Install\FIREBIRD_2_5\bin\fb_inet_server.exe -s fb25sc -m)
Запущен трейс.
Имеется скрипт, отличающийся только значениями диапазона обрабатываемых строк:
recreate table tmp(x int); commit;
create index tmp_x on tmp(x); commit;
insert into tmp select rand()*10 from rdb$types, rdb$types rows 50000; commit;
select count(*) from tmp where x between 2 and 5; commit;
delete from tmp where x between 2 and 5; commit;
select count(*) from tmp where x between 2 and 5; commit;
quit;
var-2: low=2, high=7.
recreate table tmp(x int); commit;
create index tmp_x on tmp(x); commit;
insert into tmp select rand()*10 from rdb$types, rdb$types rows 50000; commit;
select count(*) from tmp where x between 2 and 7; commit;
delete from tmp where x between 2 and 7; commit;
select count(*) from tmp where x between 2 and 7; commit;
quit;
Каждый из них запускался по нескольку раз новым коннектом (других аттачей к базе нет):
1.
2.
3.
4.
5.
6.
C:\MIX\firebird\jaybird>isql 192.168.0.201/3252:employee -n
Database: 192.168.0.201/3252:employee
SQL> recreate table tmp(x int); commit;
SQL> create index tmp_x on tmp(x); commit;
SQL> ...
SQL> quit;
Статистика по трейсу:
var-1. 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
2014-01-16T22:54:21.4480 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_719, CONCURRENCY | WAIT | READ_WRITE)
Statement 128:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 5
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
21 ms, 39845 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 19904
2014-01-16T22:54:21.4480 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_719, CONCURRENCY | WAIT | READ_WRITE)
1 ms, 1 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:54:21.4480 (3616:014AA274) START_TRANSACTION
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_720, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:54:21.5110 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_720, CONCURRENCY | WAIT | READ_WRITE)
Statement 130:
-------------------------------------------------------------------------------
delete from tmp where x between 2 and 5
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
0 records fetched
65 ms, 99557 fetch(es), 19904 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 19904 19904
2014-01-16T22:54:21.7290 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_720, CONCURRENCY | WAIT | READ_WRITE)
214 ms, 619 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:54:21.7290 (3616:014AA274) START_TRANSACTION
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_721, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:54:21.9010 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_168, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_721, CONCURRENCY | WAIT | READ_WRITE)
Statement 131:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 5
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
0 ms, 3 fetch(es)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
Statement 128:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 7
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
32 ms, 60271 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 30109
2014-01-16T22:58:52.6670 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_754, CONCURRENCY | WAIT | READ_WRITE)
0 ms, 1 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:58:52.6670 (3616:014AA274) START_TRANSACTION
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_755, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:58:52.7610 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_755, CONCURRENCY | WAIT | READ_WRITE)
Statement 130:
-------------------------------------------------------------------------------
delete from tmp where x between 2 and 7
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
0 records fetched
98 ms, 150598 fetch(es), 30109 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 30109 30109
2014-01-16T22:58:52.9790 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_755, CONCURRENCY | WAIT | READ_WRITE)
215 ms, 619 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:58:52.9790 (3616:014AA274) START_TRANSACTION
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_756, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:58:53.2140 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_173, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1440
(TRA_756, CONCURRENCY | WAIT | READ_WRITE)
Statement 131:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 7
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
114 ms, 145305 fetch(es), 47587 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 15710
Чем объяснить, что в var-1 заключительный count(*) НЕ привел к expunges > 0 ?
PS. Для low=2 high=6 всё еще загадочнее: expunges то есть, а то и нет!
Вот они *есть*... 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
Statement 130:
-------------------------------------------------------------------------------
delete from tmp where x between 2 and 6
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
0 records fetched
81 ms, 125214 fetch(es), 25034 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 25034 25034
2014-01-16T22:49:14.3540 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_163, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1492
(TRA_685, CONCURRENCY | WAIT | READ_WRITE)
179 ms, 619 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:49:14.3540 (3616:014AA274) START_TRANSACTION
employee (ATT_163, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1492
(TRA_686, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:49:14.5580 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_163, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:1492
(TRA_686, CONCURRENCY | WAIT | READ_WRITE)
Statement 131:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 6
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
16 ms, 21567 fetch(es), 7012 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 2310
... а вот их уже и *нет*: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
2014-01-16T22:57:06.3540 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_171, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2212
(TRA_741, CONCURRENCY | WAIT | READ_WRITE)
Statement 130:
-------------------------------------------------------------------------------
delete from tmp where x between 2 and 6
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
0 records fetched
82 ms, 125120 fetch(es), 25015 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 25015 25015
2014-01-16T22:57:06.5730 (3616:014AA274) COMMIT_TRANSACTION
employee (ATT_171, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2212
(TRA_741, CONCURRENCY | WAIT | READ_WRITE)
212 ms, 619 write(s), 1 fetch(es), 1 mark(s)
2014-01-16T22:57:06.5730 (3616:014AA274) START_TRANSACTION
employee (ATT_171, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2212
(TRA_742, CONCURRENCY | WAIT | READ_WRITE)
2014-01-16T22:57:06.7760 (3616:014AA274) EXECUTE_STATEMENT_FINISH
employee (ATT_171, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2212
(TRA_742, CONCURRENCY | WAIT | READ_WRITE)
Statement 131:
-------------------------------------------------------------------------------
select count(*) from tmp where x between 2 and 6
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMP INDEX (TMP_X))
1 records fetched
0 ms, 3 fetch(es)
2014-01-16T22:57:06.8860 (3616:014AA274) COMMIT_TRANSACTION
|
