Re: Slow inserts

Ben Thompson wrote: Ben, do you perform large numbers of deletes on this table? The periodic large numbers of IOs on the table's chunk may be BTREE cleaner/scanner activity. BTW Version and platform info would be helpful, especially since the BTREE cleaning functionality has changed 3 or 4 times over recent releases to improve things. Art S. Kagel > We have a problem with inserts into a particularly large table being > slow for no obvious reason (500ms per row instead of under 10ms) and the > whole system being slow in general when this occurs. Analysis of the > sessions running and the SQL statements they're running never reveals > anything untoward. This table is in its own chunk and "onstat -z" and > various "onstat -g iof" commands show that this chunk is being accessed > heavily (400io/sec). Typically this slow running starts at random times, > lasts about 20 minutes and then stops again. The frequency of this is > maybe 3 to 4 times a week. > > Recently I looked at the size of the primary key index on this table > which is a segmented key covering 5 columns (we aim to redesign the > database to sort this out). It is approaching 1Gb (I got this using > oncheck -pe and looked at the allocated extent for the index). Our> machine has approximately 1.5Gb BUFFERs and other large tables to deal > with as well. Could it be that the index is too large to be buffered and > that the engine is having to go to disc to work out whether the insert > violates the primary key and thus hitting system performance hard? > Thoughts please. > > Ben.