Find high INSERT/UPDATE/DELETE tables
Indika Jinadasa asked how to find tables with the most INSERT/UPDATE/DELETE activity. Gabor Fekete suggested sysmaster sysptprof. Andreas Legner said that gives only indirect clues and recommended the sysptnhdr counters ninserts/nupdates/ndeletes, which are cumulative; the default mon_table_profile task in sysadmin captures them over time and can be joined to ph_task and ph_run.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear All, Is there a way to find out the tables which has more INSERTs/UPDATEs/DELETs transactions ? Please help ! Thanks ! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------
Hi You can look after in sysmaster: sysptprof table. https://www.ibm.com/docs/en/informix-servers/14.10?topic=tables-sysptprof-table ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------
Hi Gabor, Thanks for the reply. I assume SYSPTPROF in SYSMASTER DB is having relevant information for SELECT,INSERT,DELETE? But what about the UPDATE transaction information for TABLE ? ( isreads + bufreads+ pagreads) -> SELECT (iswrites + bufwrites +pagwrites) -> INSERT (isdeletes) -> DELETE Thanks ! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------
Partition profiles (sysptprof) would give very indirect clues only, and e.g. a select could be causing one or a multitude of buffer reads on a given partition (an update probably would be reads and writes). There are explicit insert/update/delete counters, again per partition (so for a fragmented table multiple such partitions' counters had to be combined): sysptnhdr:ninserts/nupdates/ndeletes. These counters are cumulative over the partition's life time, so to see activity over a certain period of time, you'd have to capture them at start and end of such period. There's a mon_table_profile task, in sysadmin:ph_task and enabled by default, that does precisely this, into a table in sysadmin db also called mon_table_profile. Join this table to ph_task and ph_run (ph_task.tk_id = run_task_id and mon_table_profile.id = run_task_seq) to get enties' times. (This, roughly, also is what Auto Update Statistics would do to determine the tables it actually has to refresh.) HTH, Andreas ------------------------------ Andreas Legner ------------------------------