stats/checks in 24X7 environment ?

Hi, Need to know when you'll run stats and onchecks in a 24X7 environment, in the organization where i am working we alternate stats and checks every sunday, during this time we also have users connecting to the database and run jobs. replication is not an option for us, need to know when you'll run stats and checks in the 24X7 environment ? Thanks ____________________________________________________________________________________ Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now.

infx dba wrote: > Hi, > > Need to know when you'll run stats and onchecks in a 24X7 environment, > in the organization where i am working we alternate stats and checks > every sunday, during this time we also have users connecting to the > database and run jobs. replication is not an option for us, need to know > when you'll run stats and checks in the 24X7 environment ? We run stats nightly using my dostats utility with options to insure that a minimum number of tables are actually updated each night: dostats -d <database> -a -A7 -b -B15 -Q 50 -P 0 this updates any tables which have not had stats updated in more than 7 days or that have had a change in the number of rows (up or down) of more than 15% using PDQPRIORITY=50 for tables and PDQPRIORITY=0 for procedures/functions. On the weekend we run the same command and monthly (1st weekend of the month) we run without the -a/-A and -b/-B options so that every table is updated at the start of the month. Typically the daily run only updates a few very active tables each day keeping the overhead low and the statistical distribution quality high. Onchecks are only run when we have a problem. I've found that if all databases use UNBUFFERED logging and we avoid RAID5 disks ;-) there are very few corruptions to deal with (and with close to 100 instances you'd think we'd have a lot!) Some comments from the folk at Home Depot, Walmart, and Cisco who have thousands of instances would be useful here. Dostats is part of the package utils2_ak available for download from the IIUG Software Repository (www.iiug.org/software). Art S. Kagel

Art S. Kagel wrote: > infx dba wrote: > > Hi, > > > > Need to know when you'll run stats and onchecks in a 24X7 environment, > > in the organization where i am working we alternate stats and checks > > every sunday, during this time we also have users connecting to the > > database and run jobs. replication is not an option for us, need to know > > when you'll run stats and checks in the 24X7 environment ? > We do not have a full 24x7 operation we have a weekly maintanence window. Why can you not have a maintence window at the weekend? onchecks can be run against a copy restored onto another machine. Normally update stats have to be run on the original machine. I doubt you can sucessfully copy the results from update stats from a copy. You will just have to take the hit,code apps to wait for locks and release locks quickly.

david@smooth1.co.uk wrote: > Art S. Kagel wrote: > >>infx dba wrote: >> >>>Hi, >>> >>>Need to know when you'll run stats and onchecks in a 24X7 environment, >>>in the organization where i am working we alternate stats and checks >>>every sunday, during this time we also have users connecting to the >>>database and run jobs. replication is not an option for us, need to know >>>when you'll run stats and checks in the 24X7 environment ? >> > > We do not have a full 24x7 operation we have a weekly maintanence > window. > > Why can you not have a maintence window at the weekend? > > onchecks can be run against a copy restored onto another machine. > > Normally update stats have to be run on the original machine. > I doubt you can sucessfully copy the results from update stats from a > copy. I related this one before: Actually, you CAN! I have it from one of the original optimizer guys that sysdistrib is the only source of distribution information and is treated as an ordinary SQL table (which it is). So you can DELETE FROM SYSDISTRIB; followed by LOAD FROM distributions.file INSERT INTO SYSDISTRIB; It's safe. Did it once from one sister server to another that was not replicated because the stats worked fine on one server but no matter what options we passed to UPDATE STATISTICS on the other machine the queries were using inefficient query paths. Deleting all the distributions from the 'bad' server and imposing the working distributions from the 'good' one solved the problem. That was the problem that lead to the discussions about sysdistrib with Menlo. It later worked out that we could update stats successfully on those tables after the 2nd of the month - had to do with data insertion patterns. But in the emergency, this kludge was a life saver. Art S. Kagel > You will just have to take the hit,code apps to wait for locks and > release locks quickly. >