Re: onstat -u, B-flag

June Tong wrote: > > Alexander Selg (alexander.selg@siemens.at) wrote: > : The command "onstat -u" gives some information about the userthreads. In > : the 2.column you can see the flags to each thread. The 1.position > : indicates the resource a thread is waiting for. > : My question is: if there is a "B" on the 1.position, which means the > : thread is waiting for a buffer, which buffer is meant - physical log > : buffer, logical log buffer, regular buffers ? > > Regular buffers. > > : Background: > : I have threads, which make deletes of about 3000 rows in a tables of > : 3.000.000 rows. Therefor they need 3 - 10 minutes. There are 2 indexes > : on each of these tables and no constraints. With onstat -u i see, that > : they are sometimes waiting on buffers; so my idea is that waiting for > : buffers makes the threads using so much time - any other ideas ? > > What do you mean by "you have threads"? How do you delete the 3000 rows -- > all in one transaction, or different "threads"? What is the WHERE clause > on your DELETE? What fields are indexed? Have you turned on SET EXPLAIN? > > June > > ---- June Tong Informix Software ---- > ---- Senior Consultant (415) 926-6140 ---- > ---- International Support junet@informix.com ---- > ---- Location-du-jour: Menlo Park ---- > * > * Standard disclaimers apply > * > - Please do not send me requests/questions by mail. When I have the knowledge > - and time permits, I try to answer questions on comp.databases.informix, but > - travel schedule, time, and volume make responding to personal requests > - difficult and often slow. Please call your local Informix Technical Support > - organization for assistance with technical issues. The Problem was, that our UPDATE STATISTICS HIGH, which runs every day for 29 tables, didn't come to all tables because it needs to much time (after 24h the process control killed the update statistics process and restarted him) I found out that an UPDATE STATISTICS HIGH on the index-attributes and an UPDATE STATISTICS MEDIUM on the other attributes is enough for the optimizer to make an index scan (instead of sequential) - and it needs only half the time of an UPDATE STATISTICS HIGH on the whole table. Maybe it's also enogh to use LOW or DITRIBUTIONS ONLY - I don't know. On the other side, maybe it's not necessary to run UPDATE STATISTICS every day. The tables have about 3.000.000 rows; 200.000 of them are deleted every day by one process (with "delete from atable where adatetime <= "xx.xx.xxxx xx:xx:xx"" on adatetime there is an index(dulps) and on adatetime and another attribute there is an unique index) and 200.000 rows are inserted every day (with "insert into atable values(.....)"). How many % of a table must change, so that it's necessary to run UPDATE STATISTICS ? Thanks for your responds Alexander -- ------------------------------------------------------------------- Alexander Selg | e-mail: alexander.selg@siemens.at Siemens AG Austria | Tel.: +43 1 1707-45960 A-1101 Wien, Gudrunstr. 11 | Fax.: +43 1 1707-55832 -------------------------------------------------------------------