Update statistics for large databases

Hi, how can I obtain a short time for my update statistics on a large databases. It's run on a machine with 8 processors, 32 Gb Ram, RHEL4 and IDS 10 FC4. My script for update statistics begins with update statistics medium on database and continue with update statistics low and high for the tables which are most used. I use PDQPRIORITY 80 for tables and 1 for procedures. DBUPSPACE=25000:1000 The script finish in about 5 hours, the database is 70 Gb large.

hatricom said: > Hi, > > how can I obtain a short time for my update statistics on a large > databases. > It's run on a machine with 8 processors, 32 Gb Ram, RHEL4 and IDS 10 > FC4. > > My script for update statistics begins with update statistics medium on > database and continue with update statistics low and high for the > tables which are most used. > > I use PDQPRIORITY 80 for tables and 1 for procedures. > DBUPSPACE=25000:1000 > The script finish in about 5 hours, the database is 70 Gb large. That sounds like a really arbitrary and potentially bad script. Why not use Art Kagel's dostats.ec (which has a multi-instance runner as well). -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

hatricom wrote: > Hi, > > how can I obtain a short time for my update statistics on a large > databases. > It's run on a machine with 8 processors, 32 Gb Ram, RHEL4 and IDS 10 > FC4. > > My script for update statistics begins with update statistics medium on > database and continue with update statistics low and high for the > tables which are most used. > > I use PDQPRIORITY 80 for tables and 1 for procedures. > DBUPSPACE=25000:1000 > The script finish in about 5 hours, the database is 70 Gb large. A) 70GB is no longer considered "large". I doubt it should be considered "medium". B) Sequential read in a measly 10MB/s should finish reading 70GB in about 2 hours. Either your system is extremely busy, or your system is badly set. Perhaps your extents are poorly done (many small extents for the tables).

Hi, Art S. Kagel's "dostats.ec" is surley a very good solution for update statistics, but you should be able to determine, which "statistics" are neccessary for the optimizer. An "update statistics low" should be called if the number of rows changed for more than 5%-10% since the last invocation of "update statistics low". If a table's primary or unique key consists of only one column, and if the only access to that column is by a direct comparison ( pk = literal ), you can avoid an "update statistics high or medium" for that column. The same behavior is valid for all other columns where the values have a normal distribution. An "update statistics high" becomes neccessary if the optimizer cannot estimate the correct number of rows returned. To find these columns I wrote a small program which exctracted the number of distinct values as well as the absolute frequency for each indexed column. Only for columns where the values do not have a normal distribution I created an "update statistics high". For these columns I start the "Update statistics high" only if the number of rows changed by 5-10%. If you have a good monitoring system, you can also restart your "update statistics high", if the number of updated, inserted and deleted rows of the table changed for more than 5-10% ( see also: sysmaster:sysptprof.isrewrite-iswrite-isdeletes ). Having a 7TB database it takes me just 2-4hours to run the statistics. Best regards, Stefan Weideneder hatricom schrieb: > Hi, > > how can I obtain a short time for my update statistics on a large > databases. > It's run on a machine with 8 processors, 32 Gb Ram, RHEL4 and IDS 10 > FC4. > > My script for update statistics begins with update statistics medium on > database and continue with update statistics low and high for the > tables which are most used. > > I use PDQPRIORITY 80 for tables and 1 for procedures. > DBUPSPACE=25000:1000 > The script finish in about 5 hours, the database is 70 Gb large.

Hi Stefan, I think all we are intersted about your script. Coud you give us an example? Thank you! stefan@weideneder.de wrote: > Hi, > > Art S. Kagel's "dostats.ec" is surley a very good solution for update > statistics, but you should be able to determine, which "statistics" are > neccessary for the optimizer. >