Re: The sysmaster tables

dmaxwell@netcomuk.co.uk wrote: > > We have found some tables in the sysmaster database but can not find > documentation for them. > > For example, how could we trace from a record in sysuserthreads back > to the database being accessed by the user? Is this the best way to > find the databases being used by different users? > > Other selects on sysmaster tables appear to perform poorly. For > example 'select * from syslocks' takes 10 seconds to retrieve one row > on a very lightly loaded system . Can anything be done to improve > this or are we 'misusing' the tables? Remember that the 'tables' in SYSMASTER are not really database tables at all, they are pseudo tables looking into shared memory structures. Several of these look at highly dynamic tables, for example syslocks, syssqexplain, syspprofs, systwaits, sysshmem, sysseswts, sysptprof, sysmutexes, etc. Querying these can take time because the engine must resolve the constantly changing links and pointers in memory. Indeed our systems are so busy that a query against syssqexplain without a specific session id NEVER RETURNS and in earlier versions (we run 7.21 now but in 7.12 & 7.13) eventually (after 2 days) grabbed all available resources so that no other queries could run and the server had to be shutdown. These queries could not even be aborted! AArrgghh. I honestly have not even tried in 7.21 because I am not wont to bring a production machine to its knees (I like my job). Anyway this is the delay that you are seeing. It is a combination of a fairly large 'table' and which is highly dynamic (was that even English?). Art S. Kagel