Update statistics from "generation program" question

Posted in 2000

Doug McAllister — Thu, 30 Mar 2000 09:59:12 -0500 — source: Informix-list mailing list archive (1991-1998)

Hi all.. Given the following table definition: create table acct_nbr_srch_keys ( t_acct_num char(10), fund_num char(3), acct_num char(13), doc_id integer not null , fldr_id integer not null ); create unique index acctnbr_s_keys_pk on acct_nbr_srch_keys (acct_num,fund_num,t_acct_num,doc_id); create index acctnbr_s_keys_ix1 on acct_nbr_srch_keys (t_acct_num,acct_num,fund_num,fldr_id,doc_id); create index acctnbr_sk_doc_fk on acct_nbr_srch_keys (doc_id); create index acctnbr_s_keys_ix2 on acct_nbr_srch_keys (fldr_id); I am curious to understand why the update statistics statements are generated as follows: UPDATE STATISTICS MEDIUM FOR TABLE acct_nbr_srch_keys DISTRIBUTIONSONLY; UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (t_acct_num)DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, acct_num, fund_num, fldr_id, doc_id); UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (acct_num)DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, t_acct_num, doc_id); UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (doc_id); UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (fldr_id); Specifically, why are there multiple updates for the same columns (look at the two LOW statements). I understand the HIGH's. This table contains 31,000,000 rows and this update statistics job takes 15 hours to complete on a six-way Sun machine. I would like to shave any time off the execution that I can. thanks as always.....

↪ replying to Doug McAllister

mars1972@my-deja.com — Thu, 30 Mar 2000 17:59:45 GMT — source: Informix-list mailing list archive (1991-1998)

What is generating the update statistics lines? And what version of Informix are you using? In article <38E36BC0.3AE05BFD@nospam.fmr.com>, Doug McAllister <doug.mcallister@nospam.fmr.com> wrote: > This is a multi-part message in MIME format. > --------------AD06AC18049333F604EE0DF3 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Hi all.. > > Given the following table definition: > > create table acct_nbr_srch_keys > ( > t_acct_num char(10), > fund_num char(3), > acct_num char(13), > doc_id integer not null ,> fldr_id integer not null > ); > > create unique index acctnbr_s_keys_pk on acct_nbr_srch_keys > (acct_num,fund_num,t_acct_num,doc_id);> > create index acctnbr_s_keys_ix1 on acct_nbr_srch_keys > (t_acct_num,acct_num,fund_num,fldr_id,doc_id); > create index acctnbr_sk_doc_fk on acct_nbr_srch_keys (doc_id); > create index acctnbr_s_keys_ix2 on acct_nbr_srch_keys (fldr_id);> > I am curious to understand why the update statistics statements are > generated as follows: > > UPDATE STATISTICS MEDIUM FOR TABLE acct_nbr_srch_keys DISTRIBUTIONS> ONLY; > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (t_acct_num)> DISTRIBUTIONS ONLY; > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, > acct_num, fund_num, fldr_id, doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (acct_num)> DISTRIBUTIONS ONLY; > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, > t_acct_num, doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (fldr_id);> > Specifically, why are there multiple updates for the same columns (look > at the two LOW statements). > I understand the HIGH's. > > This table contains 31,000,000 rows and this update statistics job takes > 15 hours to complete on a six-way Sun machine. I would like to shave any > time off the execution that I can. > > thanks as always..... > > --------------AD06AC18049333F604EE0DF3 > Content-Type: text/x-vcard; charset=us-ascii; > name="doug.mcallister.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for Doug McAllister > Content-Disposition: attachment; > filename="doug.mcallister.vcf" > > begin:vcard > n:McAllister;Doug > tel;work:(603) 791-5688 > x-mozilla-html:TRUE > url:www.fidelity.com > org:Fidelity Investments > version:2.1 > email;internet:doug.mcallister@fmr.com > title:Consulting DBA > adr;quoted-printable:;;2 Contra Way=0D=0AT1Q;Merrimack;NH;03054;USA > fn:Doug McAllister > end:vcard > > --------------AD06AC18049333F604EE0DF3-- > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Doug McAllister

Stefan Weideneder — Thu, 30 Mar 2000 23:50:44 +0100 — source: Informix-list mailing list archive (1991-1998)

Hi all, isn't this schema of update statistics the recommandation for SAP/R3 tables, where we have to perform an UPDATE STATISTICS HIGH on the first indexed column and an UPDATE STATISTICS MEDIUM for every other indexed column. I have another question. What system tables will be updated if I run the following part of your output ? UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, t_acct_num, doc_id); UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, acct_num, fund_num, fldr_id, doc_id); Best regards, -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

↪ replying to mars1972@my-deja.com

Doug McAllister — Fri, 31 Mar 2000 11:57:33 -0500 — source: Informix-list mailing list archive (1991-1998)

These are generated by Art K's dostats program on a 7.30.uc3 system mars1972@my-deja.com wrote: > What is generating the update statistics lines? And what version of > Informix are you using? > > In article <38E36BC0.3AE05BFD@nospam.fmr.com>, > Doug McAllister <doug.mcallister@nospam.fmr.com> wrote: > > This is a multi-part message in MIME format. > > --------------AD06AC18049333F604EE0DF3 > > Content-Type: text/plain; charset=us-ascii > > Content-Transfer-Encoding: 7bit > > > > Hi all.. > > > > Given the following table definition: > > > > create table acct_nbr_srch_keys > > ( > > t_acct_num char(10), > > fund_num char(3), > > acct_num char(13), > > doc_id integer not null ,> > fldr_id integer not null > > ); > > > > create unique index acctnbr_s_keys_pk on acct_nbr_srch_keys > > (acct_num,fund_num,t_acct_num,doc_id);> > > > create index acctnbr_s_keys_ix1 on acct_nbr_srch_keys > > (t_acct_num,acct_num,fund_num,fldr_id,doc_id); > > create index acctnbr_sk_doc_fk on acct_nbr_srch_keys (doc_id); > > create index acctnbr_s_keys_ix2 on acct_nbr_srch_keys (fldr_id);> > > > I am curious to understand why the update statistics statements are > > generated as follows: > > > > UPDATE STATISTICS MEDIUM FOR TABLE acct_nbr_srch_keys DISTRIBUTIONS> > ONLY; > > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (t_acct_num)> > DISTRIBUTIONS ONLY; > > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, > > acct_num, fund_num, fldr_id, doc_id); > > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (acct_num)> > DISTRIBUTIONS ONLY; > > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, > fund_num, > > t_acct_num, doc_id); > > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (doc_id); > > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (fldr_id);> > > > Specifically, why are there multiple updates for the same columns > (look > > at the two LOW statements). > > I understand the HIGH's. > > > > This table contains 31,000,000 rows and this update statistics job > takes > > 15 hours to complete on a six-way Sun machine. I would like to shave > any > > time off the execution that I can. > > > > thanks as always..... > > > > --------------AD06AC18049333F604EE0DF3 > > Content-Type: text/x-vcard; charset=us-ascii; > > name="doug.mcallister.vcf" > > Content-Transfer-Encoding: 7bit > > Content-Description: Card for Doug McAllister > > Content-Disposition: attachment; > > filename="doug.mcallister.vcf" > > > > begin:vcard > > n:McAllister;Doug > > tel;work:(603) 791-5688 > > x-mozilla-html:TRUE > > url:www.fidelity.com > > org:Fidelity Investments > > version:2.1 > > email;internet:doug.mcallister@fmr.com > > title:Consulting DBA > > adr;quoted-printable:;;2 Contra Way=0D=0AT1Q;Merrimack;NH;03054;USA > > fn:Doug McAllister > > end:vcard > > > > --------------AD06AC18049333F604EE0DF3-- > > > > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.

↪ replying to Stefan Weideneder

PC User — Thu, 06 Apr 2000 16:01:00 -0400 — source: Informix-list mailing list archive (1991-1998)

Stefan Weideneder wrote: > Hi all, > > isn't this schema of update statistics the recommandation > for SAP/R3 tables, where we have to perform an UPDATE > STATISTICS HIGH on the first indexed column and an > UPDATE STATISTICS MEDIUM for every other indexed column.> > I have another question. What system tables will be > updated if I run the following part of your output ? > > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, > t_acct_num, doc_id); > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, > acct_num, fund_num, fldr_id, doc_id); UPDATE STATISTICS LOW updates the row and page counts in systables, the levels, leaves, and nunique columns in sysindexes, and the colmin andcolmax figures for the named columns in syscolumns. Art S. Kagel

↪ replying to Doug McAllister

PC User — Thu, 06 Apr 2000 16:11:02 -0400 — source: Informix-list mailing list archive (1991-1998)

Doug McAllister wrote: > Hi all.. > > Given the following table definition: > > create table acct_nbr_srch_keys > ( > t_acct_num char(10), > fund_num char(3), > acct_num char(13), > doc_id integer not null , > fldr_id integer not null > );> > create unique index acctnbr_s_keys_pk on acct_nbr_srch_keys > (acct_num,fund_num,t_acct_num,doc_id);> > create index acctnbr_s_keys_ix1 on acct_nbr_srch_keys > (t_acct_num,acct_num,fund_num,fldr_id,doc_id); > create index acctnbr_sk_doc_fk on acct_nbr_srch_keys (doc_id); > create index acctnbr_s_keys_ix2 on acct_nbr_srch_keys (fldr_id);> > I am curious to understand why the update statistics statements are > generated as follows: > > UPDATE STATISTICS MEDIUM FOR TABLE acct_nbr_srch_keys DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (t_acct_num)> DISTRIBUTIONS ONLY; > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, > acct_num, fund_num, fldr_id, doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (acct_num)> DISTRIBUTIONS ONLY; > UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, > t_acct_num, doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (doc_id); > UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (fldr_id);> Because dostats implements the recommendations for best efficient stats generation as described in the 7.21 release notes which are also now documented in the Performance Guide manual and these are the recommended statements. > > Specifically, why are there multiple updates for the same columns (look > at the two LOW statements). > The recommendation requires that a LOW be executed for the ENTIRE KEY for each index. This is in order to update the levels, leaves, and nunique columns in sysindexes. The only redundancy is the actual update of the syscolumns records for the columns repeated in multiple indexes. The sorting and counting are the same regardless of whether the column were named but by leaving it out the sysindexes record could not be updated. > I understand the HIGH's. > > This table contains 31,000,000 rows and this update statistics job takes > 15 hours to complete on a six-way Sun machine. I would like to shave any > time off the execution that I can. Recommendations to speed dostats operation: 1) PDQPRIORITY=100 (at least 40 anyway of there is much other activity). 2) PSORT_NPROCS=40 (Yes I know the manual says 10 is the maximum effective value, try it, I did.) 3) PSORT_DBTEMP=[list of AT LEAST 3 and prefereably 4 different OS filesystems preferable on different disk farms and controllers than the database) UPDATE STATISTICS is a real dog without the PSORT parameters set properly. Run multiple copies of dostats one for each table. You should be able to productively run many copies. I have run as many as 100 on a 28 CPU VP server running on a 32-way M88100 (50MHZ) system with no noticeable slowdown in server performance. Art S. Kagel

Glossary

UPDATE STATISTICS