Re: Update Statistics

In article <1pkq8cINNbh5@emory.mathcs.emory.edu> jgordon@ssf-sys.DHL.COM (Jim Gordon) writes: >As far as I can tell Informix is locking the >system catalog rather than the table. BUT, and this is what causes us >problems, it appears to lock the table entry in the system catalog >from the time you start the update stats on a table to when it >completes its calculation. > >I can't off hand remember the complete set of problems I have faced in >the past but basically during this time the table is effectively >locked for many operations as other programs can't get to the system >catalog info. But this doesnt seem necessary to me. Why does it have >to hold the lock for the entire time? Why not read the table, >calculate the new info and then take the lock and do an update >transaction on the catalog? This would only hold the lock for a >second rather than minutes and yes, DHL does have some BIG tables. There were two bugs reported against UPDATE STATISTICS holding locks too long -- 7981 and 10409. According to 7981, UPDATE STATISTICS put a lock on the system catalog entry for the table at the beginning of the UPDATE STATISTICS, and didn't release it until the table was read, the statistics collected, and all relevent info updated. This was fixed in 4.10.UC1; it now waits until it's ready to do the update before acquiring locks. 10409 reported that when the database had logging, UPDATE STATISTICS would hold all the locks it acquired until the entire UPDATE STATISTICS completed. Thus, if it took 1 hour to update the whole database, the first table updated would be locked for (almost) an hour, while the last table would be locked only for as long as it took to update its statistics. This has been fixed in 4.10.UD1 and 5.00 so that each table is treated as a single transaction, and the locks on that table are released when it is completed, PROVIDED that the UPDATE STATISTICS was not executed in an explicit transaction. If yourdatabase is ANSI, you are ALWAYS in an explicit transaction, and will still have this problem. The only workaround is to execute UPDATE STATISTICS on each of your tables individually. June ----------------------------------------------------------------- June Tong Informix Software, Inc. Regency Support 4100 Bohannon Drive (415) 926-6433 Menlo Park, CA 94025 e-mail: junet@informix.com or uunet!infmx!junet -----------------------------------------------------------------