Abnormally Large Trace Table
I am seeing unusual growth in one of the raw tables in the SYSADMIN database. The table is called mon_syssqltrace_iter. I see over 56M rows in the table and over 5m pages. The next biggest version of this table that I have seen in our environment contains 5M rows in 463K pages. Still large but not in proportional to the workload. The former averages 12 users the latter over 200 users. Same OLTP application. The chunk is using 22GB of a 40GB expandable chunk. On the higher volume database, the whole chunk is 7GB. I normally shy away from doing any maintenance on system tables, but I need to stop the growth on this one and reduce it back to a normal size. Can this table be truncated or dropped/recreated. What impact would that have on the sysadmin functions / OAT? Any insight would be appreciated Thanks, Howard
You can just delete rows you no longer have an interest in and use the "REPACK SHRINK" SQL API function to release the space. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Feb 18, 2014 at 5:45 PM, HOWARD HANSEN <hhansen@follett.com> wrote: > I am seeing unusual growth in one of the raw tables in the SYSADMIN > database. > The table is called mon_syssqltrace_iter. I see over 56M rows in the table > and > over 5m pages. The next biggest version of this table that I have seen in > our > environment contains 5M rows in 463K pages. Still large but not in > proportional to the workload. The former averages 12 users the latter over > 200 > users. Same OLTP application. > > The chunk is using 22GB of a 40GB expandable chunk. On the higher volume > database, the whole chunk is 7GB. > > I normally shy away from doing any maintenance on system tables, but I > need to > stop the growth on this one and reduce it back to a normal size. Can this > table be truncated or dropped/recreated. What impact would that have on the > sysadmin functions / OAT? > > Any insight would be appreciated > > Thanks, > > Howard > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c2623095d77a04f2b6274b
First you should deactivate SQLTRACE... and/or the mon_* task that copies the SQLTRACE circular buffer into a regular sysadmin table (actually I think there are three tables) I believe (but would have to verify) that the *iter table stores execution data of prepared statements... so the good news if that's the one becoming to large is that your application follows the best practice of preparing the statements :) Regards On Tue, Feb 18, 2014 at 10:45 PM, HOWARD HANSEN <hhansen@follett.com> wrote: > I am seeing unusual growth in one of the raw tables in the SYSADMIN > database. > The table is called mon_syssqltrace_iter. I see over 56M rows in the table > and > over 5m pages. The next biggest version of this table that I have seen in > our > environment contains 5M rows in 463K pages. Still large but not in > proportional to the workload. The former averages 12 users the latter over > 200 > users. Same OLTP application. > > The chunk is using 22GB of a 40GB expandable chunk. On the higher volume > database, the whole chunk is 7GB. > > I normally shy away from doing any maintenance on system tables, but I > need to > stop the growth on this one and reduce it back to a normal size. Can this > table be truncated or dropped/recreated. What impact would that have on the > sysadmin functions / OAT? > > Any insight would be appreciated > > Thanks, > > Howard > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c1def28eb4f404f2b6a38b