Re: Update statistics

En Martyn Hodgson va escriure el dia 2 Nov 99, a les 13:29: > Hi > > This is going to sound heretical, but here goes. I've recently changed > the statistics collected in our 50GByte database, from basically: > > high on first column on each index > medium on other indexed columns > low on the table I don't know the statistics optimization rules for the 7.3X.XXX engines but in my engine (7.24.UC7-1) i use (My system is OLTP): a) Update statistics medium <for_each_table> distributions only. b) update statistics high on first index column c) low on other indexed rows d) update statistics for <each_stored_procedure> To solve the problem try to do the a) step. The distributions are very important for intomization. > > We now collect the stats recommended for our version of ids (7.30 UC7 > on hp-ux). The application is a Data Warehouse, with (pretty well) a > star schema design. As seems typical of such designs, some of the > queries have to join up to 10 tables. Most of these being small fact > tables. > > Since we changed the stats collected, the optimiser has started to > make some *very* bad choices when performing large joins. There is a > clear tendancy to try to access tables via indexes and nested loop > joins, rather than table scans and hash joins. In one case this means > peforming 27,000,000 index accesses to find the 625 qualifying rows. > The index is 4 levels deep. The query now takes over 3 hours. > Previously, it started by scanning the table (7 way fragmented on fast > EMC disk) and the whole thing took less than 15 minutes. > > The queries mostly come via a client application, which won't allow > Optimiser hints. Running via dbaccess I can solve most problems with > them, using the AVOID_INDEX clause. > > The indexes we have are few, but enforce uniqueness and are used by > smaller queries, so I can't drop them. > > So, we're in the brown and smelly - unless anyone has some bright > suggestions. I'm loathed to go back to the simplistic stats collection > regeme we had before, as it meakes sense to me that some ad hoc > queries will benefit from the greater stats detail. However, doing as > the release notes/performance guide suggest is causing real problems. > > Thanks in advance: > > Martyn > martyn.hodgson@zurich.co.uk > --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes de Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------