Re: table usage profile in 6.x 7.x

Saeed Talebbeik (saeed@sap-ag.de) wrote: : Folks, Is there any statistics kept within the online 6.x or 7.x : system that shows how many I/O operations have been performed : on a particular table? The question that I want to answer is to : run a benchmark on my application and find out those tables that : cause the highest I/O operations during the benchmark. : I do not want to put the table in a separate chunk and then use : onstat -g iof option. : Regards, <<<Saeed>>> I don't think there is a good answer to this. The best I can come up with is the SMI table sysptprof which contains columns for pagreads and pagwrites (in 7.1 anyway, not sure about 6.0). There are likely to be several problems with using this in the way you desire: 1) This table is like onstat -t, in that it only contains data for tables which are currently held open. Unless your benchmark is heavy enough that, at any given instant, there is always at least one thread accessing each table of interest, then the totals will keep getting cleared down. It may be possible to fudge this by writing a special transaction which idles in the background whilst holding an open cursor on each of the tables of interest. If you *need* to collect data on *every* table then this is unlikely to be practical. 2) pagreads and pagwrites will not correspond directly to the number of physical I/Os caused by that table, as OnLine will try to merge multiple pages into a single I/O where it can. If you're doing OLTP type activity then you might be happy to assume a 1:1 ratio. If you're doing PDQ type activity then you might be happy to apply the system-wide ratios available from onstat -p or onstat -g iog. If you're doing OLCP, then I can't think of a good answer, although you might be able to deduce something from the seqscans column (also in sysptprof) combined with a good understanding of the new read-ahead algorithms (I'm not sure my understanding is good enough). 3) I'm sure there must be others i haven't thought of. If anybody else has better ideas on this, please post! -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.