Surveillance des activités Ingres

Marcel Berthoud

Centre d'Informatique Hospitalière

Hôpital Cantonal Universitaire de Genève

Surveillance des activités Ingres

Traçage des connexions procedure connect



create procedure connect (
host char(8), tty char(10), pid integer2, user char(10), com char(20) )
as begin
insert into sessions
select
dbmsinfo('session_id'),
host, c(pid), tty ,
user, date('now'), com,
int4(dbmsinfo('dbms_cpu'));
commit
end

Traçage des connexions (2) setenv's

dans .login ou équivalent:

tty = `tty`
pid = `pid`

setenv ING_SET "execute procedure connect(
host='$HOSTNAME',tty='$tty',pid=$pid,
user=$LOGNAME,com='whatever'); "

(ou setenv ING_SET_<db_name> "..." pour une banque particulière)

Traçage des queries

dans .login avec setenv ING_SET

setenv II_EMBED_SET "printqry; qryfile /tmp/$pid.log"

Traçage des blocages

Traçage des blocages (2)

02 10:12:15 000031583F5EC3B0/40DC10C0/prdimp1 bloque 5 transactions

02 10:12:15 IS KEY(TABLE,DB=2EC38097,TABLE=[216,0])

02 10:12:15 S KEY(PAGE,DB=2EC38097,TABLE=[216,0],PAGE=132894)

02 10:12:15 S KEY(PAGE,DB=2EC38097,TABLE=[216,0],PAGE=132808)

02 10:12:15 IX KEY(TABLE,DB=2EC38097,TABLE=[218,0])

02 10:12:15 S KEY(CKP_TXN,NAME=prdimp1 -impact1 )

02 10:12:15 X KEY(PAGE,DB=2EC38097,TABLE=[218,0],PAGE=354349)

02 10:12:15 X KEY(PAGE,DB=2EC38097,TABLE=[218,0],PAGE=354493)

02 10:12:15 X KEY(PAGE,DB=2EC38097,TABLE=[218,357],PAGE=63389)

02 10:12:15 IS KEY(TABLE,DB=2EC38097,TABLE=[205,0])

02 10:12:15 S KEY(PAGE,DB=2EC38097,TABLE=[205,0],PAGE=1950)

02 10:12:15 S KEY(PAGE,DB=2EC38097,TABLE=[205,0],PAGE=1951)

02 10:12:15 IX KEY(TABLE,DB=2EC38097,TABLE=[239,0])

02 10:12:15 X KEY(PAGE,DB=2EC38097,TABLE=[239,0],PAGE=15149)

02 10:12:15 X KEY(PAGE,DB=2EC38097,TABLE=[239,0],PAGE=15154)

02 10:12:15 S KEY(CONTROL,DB=2EC38097,TABLE=[218,0])

02 10:12:15 4642

02 10:12:15 40DC10C0 prdimp1 impact1

02 10:12:15 ----

02 10:12:24 ----

Traçage du log-file

02 05:32 log file: 0%

02 06:00 log file: 1%

02 06:00 0 0 24 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:00 1 1 2780 writes for 000031583F5B81CF/4044DB80 <impact1>

02 06:00 0 1 3484 writes for 000031583F5B80D0/4154A880 <requete2>

02 06:01 log file: 3%

02 06:01 0 2 51 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:01 1 3 5645 writes for 000031583F5B8211/4044DB80 <impact1>

02 06:02 log file: 4%

02 06:02 0 4 2653 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:02 4 4 38 writes for 000031583F5B865C/4154A880 <requete2>

02 06:03 log file: 6%

02 06:03 0 4 2653 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:03 6 6 21 writes for 000031583F5B8A28/4154A880 <requete2>

02 06:04 log file: 6%

02 06:04 0 4 2653 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:04 6 6 46 writes for 000031583F5B8B70/4154A880 <requete2>

02 06:05 log file: 8%

02 06:05 0 7 5302 writes for 000031583F5B8081/40CE9DA0 <archimed>

02 06:05 8 8 36 writes for 000031583F5B914F/4154A880 <requete2>

02 06:06 log file: 8%

02 06:06 8 8 47 writes for 000031583F5B938A/40CE9DA0 <archimed>

02 06:07 log file: 0%

Dimensionnement du log-file

force abort à 50%, pour réduire les risques de log-file full.