Informix 14.10.FC9 reverse scans

So with some help from new features in Informix 14.10.FC7 I found that reverse scans happen by default.

Reverse scans can be bad as under contention they may have to be retried and require more cpu time.

[informix@localhost IDS.14.10.FC9W1]$ onstat -g ppf | awk ‘($13!=0) { print $0}’

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 06:57:25 — 185996 Kbytes
2023-02-05 23:17:27

Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rvscn rs_rt rhitratio name
0x100195 462 0 0 0 116 160 0 0 355 174 0 28 0 100 sysadmin:informix.idx_mon_ckpt_1

So the index sysadmin:informix.idx_mon_ckpt_1 is having reverse scans, why is this?

[informix@localhost IDS.14.10.FC9W1]$ grep idx_mon_ckpt_1 $INFORMIXDIR/etc/sysadmin/*.sql
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”create table informix.mon_checkpoint (ID integer, intvl integer, type char(12), caller char(10), clock_time int, crit_time float, flush_time float, cp_time float, n_dirty_buffs int, plogs_per_sec int, llogs_per_sec int, dskflush_per_sec int, ckpt_logid int, ckpt_logpos int, physused int, logused int, n_crit_waits int, tot_crit_wait float, longest_crit_wait float, block_time float);create unique index idx_mon_ckpt_1 on mon_checkpoint(intvl);grant select on mon_checkpoint TO ‘db_monitor’ as informix;”,

So the index is on mon_checkpoint, ok what accesses that table?

[informix@localhost IDS.14.10.FC9W1]$ grep mon_checkpoint $INFORMIXDIR/etc/sysadmin/*.sql | grep intvl | tail -1
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”insert into mon_checkpoint select $DATA_SEQ_ID, intvl, type, caller, clock_time, crit_time, flush_time, cp_time, n_dirty_buffs, plogs_per_sec, llogs_per_sec, dskflush_per_sec, ckpt_logid, ckpt_logpos, physused, logused, n_crit_waits, tot_crit_wait, longest_crit_wait, block_time FROM sysmaster:syscheckpoint WHERE intvl > (select NVL(max(intvl),0) from mon_checkpoint)”,

set explain on;
select NVL(max(intvl),0) from mon_checkpoint

gives

QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2023 23:31:32)

select NVL(max(intvl),0) from mon_checkpoint

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.mon_checkpoint: INDEX PATH

(1) Index Name: informix.idx_mon_ckpt_1
    Index Keys: intvl   (Key-Only)  (Aggregate)  (Serial, fragments: ALL)

Query statistics:

Table map :

Internal name Table name

type rows_prod est_rows rows_cons time

group 1 1 0 00:00.00

[informix@localhost IDS.14.10.FC9W1]$ onstat -g ses 47
Your evaluation license will expire on 2023-05-05 17:08:46

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:45 — 185996 Kbytes
2023-02-05 23:35:46

session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
47 informix – 1 1264330 localhost 1 90112 73664 off

Program :
/opt/IBM/IDS.14.10.FC9W1/bin/dbaccess

tid name rstcb flags curstk status
123 sqlexec 45a8eac0 Y–P— 3808 cond wait netnorm –

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
47 V 47d81040 86016 15704 77 11
47*O0 V 48b52040 4096 744 1 1

name free used name free used
overhead 0 6704 scb 0 144
opentable 0 4904 filetable 0 744
log 0 16536 temprec 0 2208
gentcb 0 1592 ostcb 0 2992
sqscb 0 26512 hashfiletab 0 552
osenv 0 2504 sqtcb 0 7792
fragman 0 336 sapi 0 144

sqscb info
scb sqscb optofc pdqpriority optcompind directives
45c421c0 48bf6028 0 0 2 1

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
47 – sysadmin CR Not Wait 0 0 9.24 On

Last parsed SQL statement :
select NVL(max(intvl),0) from mon_checkpoint

[informix@localhost IDS.14.10.FC9W1]$ onstat -g tpf 123
Your evaluation license will expire on 2023-05-05 17:08:46

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:58 — 185996 Kbytes
2023-02-05 23:35:59

Thread profiles
tid lkreqs lkw dl to lgrs totll totpl isrd iswr isrw isdl isct isrb lx drd bfr bfw lsus lsmx seq rvscn rs_rt
123 151 0 0 0 0 0 0 72 0 0 0 0 0 0 0 152 0 0 0 0 1 0

So we have a reverse scan!

I guess we would need a descending index as we to avoid the reverse scan!