Sequential Scans (David)

DATABASE sysmaster;

SELECT

stn.dbsname[1,15],

stn.tabname[1,18],

sti.ti_nptotal *

(

select pagesize

from sysdbspaces

where name = dbinfo('dbspace', sti.ti_partnum)

)/1024/1024 mb_total,

sti.ti_nrows,

spp.seqscans

FROM systabnames stn, systabinfo sti, sysptprof spp

WHERE stn.partnum = sti.ti_partnum

AND stn.partnum = spp.partnum

AND spp.seqscans > 0

ORDER BY mb_total DESC, spp.seqscans DESC;

Users locking tables

database sysmaster;
select username name, pid, dbsname[1,10] database,
tabname[1,12] table, hostname, type
from syslocks, syssessions
where
syslocks.owner = syssessions.sid
order by 3,2

Extents: Tables witha big number of extents

select dbsname, tabname table_or_index, count(*) num_of_extents,
trunc(sum( pe_size * $PAGE_SIZE ) /1024/1024,0) total_size_MB
from systabnames, sysptnext
where systabnames.partnum = pe_partnum
and dbsname != 'HASHTEMP'
and systabnames.tabname not matches 'TBL*'
group by 1,2
having count(*) > $NUM_EXTENTS
order by 1,3 desc,2
-- PAGE_SIZE in bytes, please

Checkpoints and so on

onmode -B does not flush the pages from the buffer pool. It simply
writes dirty pages to disk. The page still remains in the buffer pool.
onmode -B is probably a much better solution to the long checkpoint
issue than using very low LRU values because it uses chunk writes
rather than LRU writes.
The engine uses two basic approches to writing dirty pages to disk.
The chunk writes that are used during the checkpoint (and onmode -B)
are sorted and organized arround the physical chunk. Because of this,
they are are rather efficient -- sort of like using scatter-gather IO.
The LRU writes are not organized and thus not nearly as efficient.

LA BESTIA PARDA DE LAS BESTIAS PARDAS

select first +10 dbsname DatabaseName,
B.tabname,B.seqscans,B.bufreads,B.bufwrites,B.pagreads,B.pagwrites,
A.ti_pagesize,
(ti_nptotal * ti_pagesize ) / 1024/ 1024 Mbsizeallocated,
(ti_npused * ti_pagesize ) / 1024/ 1024 Mbusedpages,
ti_nrows nrows
from sysmaster:systabinfo A, sysmaster:sysptprof B
where A.ti_partnum = B.partnum
and B.tabname not matches "TBL*" -- util para tablespace tablespace
and B.tabname not matches "sys*"
and B.dbsname not in ('sysmaster', 'sysutils')
order by MBusedpages desc

Trukillos varios

# Find how long informix has been online
SELECT sh_curtime-sh_boottime FROM sysshmvals

# Find when the stats were last cleared
output to pipe "cat" without headings
select sh_curtime - sh_pfclrtime from sysshmvals;

Query con stats de acceso a disco y sequential scan

select first +10 sqx_sessionid,sqx_executions,sqx_bufreads,sqx_pagereads,sqx_seqscan,
sqx_sqlstatement
from syssqexplain
order by 5 desc