Re: Update Statistics

Colin Dawson wrote: > IDS 9.40.FC8X3 > Solaris 10 > SUN M5000 16*4 core CPU 128Gb Mem > > dostats version on this server:- Features Version 5.10, Source Revision: > 1.125 > > > > Sorry to raise this topic again. > > We have a table with 654,484,092 rows, fragmentation is round robin in 8 > fragments. Indexes are in their own dbspaces. There are approximately > 400,000 rows added daily (no deletes, no updates). Using dostats to > manage update statistics it took 29hours overall, output below > > Working on tjrnl: > jrnl_id, line_id(LOW)...SUCCESSFUL (3 hrs 25 mins 57.326 Secs) > acct_id, j_op_type(LOW)...SUCCESSFUL (2 hrs 16 mins 25.218 Secs) > acct_id, cr_date(LOW)...SUCCESSFUL (14 hrs 29 mins 20.725 Secs) > j_op_ref_key, j_op_ref_id(LOW)...SUCCESSFUL (2 hrs 11 mins > 2.913 Secs) > cr_date(LOW)...SUCCESSFUL (2 hrs 1 mins 40.404 Secs) > jrnl_id, cr_date, j_op_type, j_op_ref_key, > acct_id(HIGH)...SUCCESSFUL (5 hrs 18 mins 3.348 Secs) > line_id, j_op_ref_id, user_id, amount, desc, > balance(MEDIUM)...SUCCESSFUL (0 hrs 0 mins 43.592 Secs) > Table tjrnl completed (29 hrs 43 mins 10.539 Secs) > > The script is run with the following > DBUPSPACE=0:2048 > PSORT_NPROCS=47 > > Command used to execute dostats was > dostats -d <dbname> -t none -Q 30 -p -E -v -i > @/informix/dostats/dostats_include_Thu > > >From previous conversations with Informix Tech Support I was told that > a 'LOW' does not use PDQ so only has 1 thread scanning the whole table > > Is there anything else I can do to make this run quicker/faster/better? > > > BTW, We'll be moving to 10.00.FC9 before the end of the month > > > > > > ------------------------------------------------------------------------ > " Upgrade to Internet Explorer 8 Optimised for MSN. " Download Now > <http://extras.uk.msn.com/internet-explorer-8/?ocid=T010MSN07A0716U>; I believe Art will pick this if you post directly on IIUG mailing list... I'm not familiar with the output from dostats. You should confirm that the high clause is using the "DISTRIBUTION ONLY" clause. Otherwise it is redoing what was already done by the LOW mode. I *don't* believe dostats does that... But check. The majority of time is spent in this: acct_id, cr_date(LOW)...SUCCESSFUL (14 hrs 29 mins 20.725 Secs) Question: cr_date is a date or datetime? I would put my money on datetime...? Now some comments: - I've always found funny that usually, the LOW is what takes more time... There is a good reason for it. It goes through the indexes... - I think low mode can activate the btree scanner. There were several bugs in it. I'm not sure they're fixed in your version. But they should be fixed in 10.00.FC9 (even on FC8). Please check if there is high activity on your indexes/partitions in the btree scanner threads. I'm not sure about 9.4x but in 10 you have a lot of control over how these threads work - One thought for further discussion: Would it be possible to run several low modes (different columns) at the same time? I never felt this need and never investigate it... If possible you could reduce your time to about half... - Run the update statistics with the set explain option. It may be interesting to look at the output - Your PSORT_NPROCS... How many cores do you have? Has anyone suggested that number? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...