too many open cursors in one table

Dear All.... I've a IDS 7.31.UD8 in RedHat Linux , Every morning AM 7:30 or so , the data in table A would be deleted to empty, and then in AM 8:30 , hundreds of esql/c aps would select table A with or without using index ....only 4 or 5 sessions would insert/update/delete table A !!! Usually table A would only have 3500 ~ 4000 Rows in it , don't know why these days the select in this table is very very slow , even just "select * from table A" in dbaccess.... onstat -g opn | grep "200208" showes 170 open cursors in it ....all aps which select Table A would be very slow at this time , and then I run "update statistics high " for all index fields in Table A , after about 5 minutes,without any aps modified or disconnected, onstat -g opn | grep "200208" showes about 15 open cursors in Table A,and all the select * from Table A sessions run smoothly .... What bother me is , in AM 7:30 , table A is empty , AM 8:30 , hundreds of sessions suddenly select Table A , and thousands of data (by 4 or 5 sessions) insert Table A at the same time , for months it is not a problem , but for these 2 days,all aps select Table A very slow , after update statistics high for index fields , wait for 5 minutes, and then every thing goes well again , if I run update statistics before AM 8:30 , the table is empty , If I run after AM 8:30 , it is too busy then , what is the right time to run update statisitcs ?! and why it effect so much ?! Thanks and forget my english .....

For a queue table like that, assuming the number of rows is about the same from one day to the next, I would run an UPDATE STATISTICS LOW... DROP DISTRIBUTIONS after the load at 8:30 and NOT anytime between the delete and the reload. Without distributions, IDS will use the older optimizer logic that just uses the #rows and the depth of each index to make decisions which will not be affected by the changing values in the table from day to day. Artt\\\\ Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Oct 22, 2009 at 4:18 AM, MARS CHEN <mars@jsun.com> wrote: > Dear All.... > > I've a IDS 7.31.UD8 in RedHat Linux , Every morning AM 7:30 or so , > the data in table A would be deleted to empty, and then > in AM 8:30 , hundreds of esql/c aps would select table A with or without > using index ....only 4 or 5 sessions would insert/update/delete table A !!! > > Usually table A would only have 3500 ~ 4000 Rows in it , don't know why > these days the select in this table is very very slow , even just > "select * from table A" in dbaccess.... > onstat -g opn | grep "200208" showes 170 open cursors in it ....> all aps which select Table A would be very slow at this time , > and then I run "update statistics high " for all index fields in > Table A , after about 5 minutes,without any aps modified or disconnected, > onstat -g opn | grep "200208" showes about 15 open cursors in Table A,> and all the select * from Table A sessions run smoothly .... > > What bother me is , in AM 7:30 , table A is empty , AM 8:30 , > hundreds of sessions suddenly select Table A , and thousands of data > (by 4 or 5 sessions) insert Table A at the same time , for months > it is not a problem , but for these 2 days,all aps select Table A very slow > , > after update statistics high for index fields , wait for 5 minutes, > and then every thing goes well again , if I run update statistics before > AM 8:30 , the table is empty , If I run after AM 8:30 , > it is too busy then , what is the right time to run update statisitcs ?! > and why it effect so much ?! > > Thanks and forget my english ..... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bda28e388fc047683de28