onstat g opn - lockmode
Posted in 2012
Hi ,
Ifx 11.50 FC9X6 , AIX 6.1
(with 1 RSS read-only synchronized)
Monitoring the instance , I'm trying figure out why one stock historical
table persists keep 60% of the table size into buffer pool a few days
(checking with onstat -P) (60% = 10GB).
So, I'm looking some historical "snapshots" what I create each 15
minutes (onstat commands...) for users what is reading constantly this
table or executing sequential scans.
for my surprise with "onstat -g ppf" I detect the bufwrites growing for
this table.
This table should not have any update at this epoch of month.... is a
historical stock, they was populate only between months.
At the same time, the "onstat -g opn" show me just one user accessing it.
Tracing the session , this user was running a report application, so ,
no update/insert/delete into the code (4GL).
Looking the last 15 minutes (the "snapshot" what I capture) , only the
same user was accessing this table.
onstat -g ppflog.2012-07-21_1400.gz : partnum lkrqs lkwts dlks touts isrd iswrt
isrwt isdel bfrd bfwrt seqsc rhitratio
log.2012-07-21_1400.gz : 0x2700004 52183079 0 0 0 439858377
9530760 0 10665012 497228308 27129013 4 97
log.2012-07-21_1415.gz : 0x2700004 52183079 0 0 0 >> 442757465
9530760 0 10665012 500191255 27129533 4 97
log.2012-07-21_1430.gz : 0x2700004 52183079 0 0 0 446671162
9530760 0 10665012 504198331 27130293 4 97
onstat -g opn
log.2012-07-21_1400.gz : tid rstcb isfd op_modeop_flags partnum ucount ocount lockmode
log.2012-07-21_1400.gz : 293488390 0x0700001d1527a108 17 0x00000400
0x00000007 0x02700004 1 1 7
log.2012-07-21_1415.gz : 293488390 0x0700001d1527a108 17 0x00000400
0x00000007 0x02700004 1 1 9
log.2012-07-21_1430.gz : 293488390 0x0700001d1527a108 17 0x00000400
0x00000007 0x02700004 1 1 9
I'm trying figure out :
1) Why the lockmode show the status 7 and 9 ( 7 Intent exclusive lock ,
9 Exclusive lock) if have only selects over the table.
2) Why op_mode have flag 0x400? (0x000400 Manual record lock).
3) who is updating this table!??? (
could be the btree scanner ? And we can't able to detect here they
accessing the table and updating the rowids?
The application/report (4gl) what the user is running :
- There is no update statistics into the code
- no lock command / transactions
- the session use only dirty read isolation
- any "set environment" defined (like batched read)
any tips?
My primary objective is discovery why this table don't leave the buffer
pool and for that , discovery who is "updating" this table...
Regards
Cesar