Informix 7.30UC7XH Update Statistics

Posted in 1999

M. Jiwani — Mon, 26 Jul 1999 20:56:05 GMT — source: Informix-list mailing list archive (1991-1998)

I've got a 100GB DB (with 500 tables and 700 indexes). We need to refresh the whole database and do an update statistics on the last step. Please help me out to determine which one is the best to choose if I just have 24 hours to finish this update statistics: 1) update statistics; 2) update statistics for table "table name"; 3) set pdq priority to 100 and update statistics; 4) set pdq priority to 100 and update statistics for each table; or 5) what else you can think of. -- DK/MJ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

↪ replying to M. Jiwani

Richard Auslander — Mon, 26 Jul 1999 15:29:29 -0700 — source: Informix-list mailing list archive (1991-1998)

I've had some good luck updating statistics in parallel; i.e., running around 3 to 5 simultaneous batch processes, each running at pdqpriority high. Rich "M. Jiwani" wrote: > I've got a 100GB DB (with 500 tables and 700 indexes). > We need to refresh the whole database and do an update > statistics on the last step. > > Please help me out to determine which one is the best > to choose if I just have 24 hours to finish this update > statistics: > 1) update statistics; > 2) update statistics for table "table name"; > 3) set pdq priority to 100 and update statistics; > 4) set pdq priority to 100 and update statistics for each > table; or > 5) what else you can think of. > > -- > DK/MJ > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

↪ replying to M. Jiwani

Paul Wilson — Tue, 27 Jul 1999 00:11:09 +0100 — source: Informix-list mailing list archive (1991-1998)

I would do seperate updates for each table .. however you need a better strategy than the one mentioned.. e.g. for a table with a primary key index something like update statistics medium for table <tablename> distributions only; update statistics high for table <tablename>(<primary key>) M. Jiwani <mjiwani@my-deja.com> wrote in message news:7nii11$vkj$1@nnrp1.deja.com... > I've got a 100GB DB (with 500 tables and 700 indexes). > We need to refresh the whole database and do an update > statistics on the last step. > > Please help me out to determine which one is the best > to choose if I just have 24 hours to finish this update > statistics: > 1) update statistics; > 2) update statistics for table "table name"; > 3) set pdq priority to 100 and update statistics; > 4) set pdq priority to 100 and update statistics for each > table; or > 5) what else you can think of. > > > > -- > DK/MJ > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.

↪ replying to Paul Wilson

Adam Richards — Mon, 26 Jul 1999 23:33:15 GMT — source: Informix-list mailing list archive (1991-1998)

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> <body text="#000000" bgcolor="#FFFFFF" link="#0000FF" vlink="#800080" alink="#FF0080"> <tt>here is my perl program for update statistics....</tt> <tt>use it all if you like... god knows this news group has helped me enough..</tt> <tt>it does a low, then a medium distibutions only, then a high on lead colums then on procedures.</tt> <tt>you need to change occurances of dbaccess tepr to your database name....</tt> <tt>feedback is welcome.</tt> <hr WIDTH="100%"> <tt>#!/users/arichard/bin/perl/perl</tt> <tt>######################################################</tt> <tt># written by Adam B. Richards 4/1999</tt> <tt>######################################################</tt> <tt>$|=1;</tt> <tt>######################################################</tt> <tt># set the ifmx environmental settings</tt> <tt>$ENV{PDQPRIORITY} =10;</tt> <tt>$ENV{PSORT_NPROCS}=5;</tt> <tt>$colmode='high';</tt> <tt>######################################################</tt> <tt>print `date`."\\n";</tt> <tt>######################################################</tt> <tt>$up_low_cmd='dbaccess tepr << EOT</tt> <tt>-- first run a low</tt> <tt>update statistics low;</tt> <tt>EOT</tt> <tt>';</tt> <tt>print $up_low_cmd;print `$up_low_cmd`;</tt> <tt>######################################################</tt> <tt>$up_med_cmd='dbaccess tepr << EOT</tt> <tt>-- first run a medium on table data only (no indexes)</tt> <tt>update statistics medium for table distributions only;</tt> <tt>EOT</tt> <tt>';</tt> <tt>print $up_med_cmd;print `$up_med_cmd`;</tt> <tt>######################################################</tt> <tt>$idxcmd='dbaccess tepr << EOT</tt> <tt>-- select a.tabname, b.tabid, b.idxname, b.part1,  c.colname  from</tt> <tt>select distinct a.tabname table, "|" d, c.colname pidxcol  from</tt> <tt>systables a, sysindexes b, syscolumns c</tt> <tt>where</tt><tt></tt> <tt>b.tabid = a.tabid</tt> <tt>and</tt> <tt>b.tabid = c.tabid</tt> <tt>and</tt> <tt>b.part1 = c.colno</tt> <tt>and</tt> <tt>a.tabid > 99;</tt> <tt>EOT</tt> <tt>';</tt> <tt>@idxlist= `$idxcmd`;</tt> <tt>j:foreach $i (@idxlist)</tt> <tt>        {</tt> <tt>        $t = &trim($i);</tt> <tt>        if (length($t)<=0) { next j;}# ignore blank lines</tt> <tt>        if ($t=~m/d pidxcol/) {next j;} #ignore header</tt> <tt>        chomp($t);</tt> <tt>        ($table,$col)=split('\\|',$i);</tt> <tt>        $table=&trim($table); $col=&trim($col);</tt> <tt>        $ccmd='dbaccess tepr << EOT</tt> <tt>update statistics '.$colmode.' for table '.$table.' ('.$col.');</tt> <tt>EOT';</tt> <tt>print "---- START: ".`date`."\\n";</tt> <tt>print $ccmd;print `$ccmd`;</tt> <tt>        }</tt> <tt>######################################################</tt> <tt>$arcmd='dbaccess tepr << EOT</tt> <tt>-- update stats on the stored procedures last</tt> <tt>update statistics for procedure;</tt> <tt>EOT</tt><tt></tt> <tt>';</tt> <tt>print $arcmd;print `$arcmd`;</tt> <tt>######################################################</tt> <tt>print `date`."\\n";</tt> <tt>######################################################</tt> <tt>sub trim</tt> <tt>{</tt> <tt> my @out = @_;</tt> <tt> for (@out){</tt> <tt>        s/^\\s+//;</tt> <tt>        s/\\s+$//;</tt> <tt>        }</tt> <tt>return wantarray ? @out : $out[0];</tt> <tt>}</tt> <tt>###########################################</tt> <hr WIDTH="100%"><tt></tt> <tt>Paul Wilson wrote:</tt> <blockquote TYPE=CITE><tt>I would do seperate updates for each table .. however you need a better</tt> <tt>strategy than the one mentioned..</tt><tt></tt> <tt>e.g. for a table with a primary key index something like</tt><tt></tt> <tt>update statistics medium for table <tablename> distributions only;</tt> <tt>update statistics high for table <tablename>(<primary key>)</tt><tt></tt> <tt>M. Jiwani <mjiwani@my-deja.com> wrote in message</tt> <tt><a href="news:7nii11$vkj$1@nnrp1.deja.com">news:7nii11$vkj$1@nnrp1.deja.com</a>...</tt> <tt>> I've got a 100GB DB (with 500 tables and 700 indexes).</tt> <tt>> We need to refresh the whole database and do an update</tt> <tt>> statistics on the last step.</tt> <tt>></tt> <tt>> Please help me out to determine which one is the best</tt> <tt>> to choose if I just have 24 hours to finish this update</tt> <tt>> statistics:</tt> <tt>> 1) update statistics;</tt> <tt>> 2) update statistics for table "table name";</tt> <tt>> 3) set pdq priority to 100 and update statistics;</tt> <tt>> 4) set pdq priority to 100 and update statistics for each</tt> <tt>>    table; or</tt> <tt>> 5) what else you can think of.</tt> <tt>></tt> <tt>></tt> <tt>></tt> <tt>> --</tt> <tt>> DK/MJ</tt> <tt>></tt> <tt>></tt> <tt>> Sent via Deja.com <a href="http://www.deja.com/">http://www.deja.com/</a></tt>; <tt>> Share what you know. Learn what you don't.</tt></blockquote> <tt></tt><tt></tt> <tt>--</tt> <tt>Adam Richards, MCI WorldCom  - V622-1328 - 719-535-1328</tt> <tt>WWW:   <A HREF="http://166.37.1.157/">http://166.37.1.157/</A></tt>; <tt>EMAIL: adam.richards@wcom.com</tt> <tt></tt>  </body> </html>

↪ replying to M. Jiwani

Art S. Kagel — Wed, 28 Jul 1999 12:37:19 -0400 — source: Informix-list mailing list archive (1991-1998)

"M. Jiwani" wrote: > > I've got a 100GB DB (with 500 tables and 700 indexes). > We need to refresh the whole database and do an update > statistics on the last step. > > Please help me out to determine which one is the best > to choose if I just have 24 hours to finish this update > statistics: > 1) update statistics; > 2) update statistics for table "table name"; > 3) set pdq priority to 100 and update statistics; > 4) set pdq priority to 100 and update statistics for each > table; or > 5) what else you can think of. Get my dostats.ec utility from the IIUG Software Repository and run 5-10 copies, on different tables, at once with PDQPRIORITY=100/(#copies). Here is an ksh/awk script to create the script to run all the needed dostats in parallel groups: #! /usr/bin/ksh if [[ $# -lt 2 ]]; then echo Usage: $0 database #copies <table_template> exit 1 fi dbase=$1 ncopies=$2 if [[ $# -eq 3 ]]; then templ=$3 else templ='*' fi dbaccess $dbase - <<EOF 2>/dev/null output to temp$$ without headings select tabname from systables where tabid > 99 and tabname matches "$templ";EOF awk -v ncopies=$ncopies -v dbase=$dbase ' BEGIN { cnt=0; pdq=100/ncopies; printf "PDQPRIORITY=%d; export PDQPRIORITY \\n", pdq; } { if (length( $1 ) == 0){ next; } } { # Every N copies of dostats insert a wait if ((cnt % ncopies) == 0 && cnt > 0) { print "wait"; } # output a dostats command for each table in the background printf "dostats -d %s -t %s & \\n", dbase, $1, pdq; cnt++; } END { # Now update stats on all stored procedures. print "dostats -d %s -p \\n", dbase; } ' temp$$ ##### End script ##### Then to generate a multiple dostats script, assume you named the above genstats: genstats mydatabase 5 >updstats.sh Art S. Kagel

Glossary

UPDATE STATISTICS