Re: 7.3 Performance with PeopleSoft application

The recommendations for running update statistics have changed somewhat from 7.2 to 7.3; If you can use sqexplain to see what the optimizer is doing, that often helps determine if there are bad decisions being made. Optimizer directives can help (assuming you have access to the code) in these cases. Regarding how UPDATE STATS should be run, from the 7.3 documentation: For each table that your query accesses, build data distributions according to the following guidelines: 1. Run UPDATE STATISTICS MEDIUM for all columns in a table that do not head an index. This step is a single UPDATE STATISTICS statement. The default parameters are sufficient unless the table is very large, in which case you should use a resolution of 1.0, 0.99. With the DISTRIBUTIONS ONLY option, you can execute UPDATE STATISTICS MEDIUM at the table level or for the entire system because the overhead of the extra columns is not large. 2. Run UPDATE STATISTICS HIGH for all columns that head an index. For the fastest execution time of the UPDATE STATISTICS statement, you must execute one UPDATE STATISTICS HIGH statement for each column. In addition, when you have indexes that begin with the same subset of columns, run UPDATE STATISTICS HIGH for the first column in each index that differs. For example, if index ix_1 is defined on columns a, b, c, and d, and index ix_2 is defined on columns a, b, e, and f, run UPDATE STATISTICS HIGH on column a by itself. Then run UPDATE STATISTICS HIGH on columns c and e. In addition, you can run UPDATE STATISTICS HIGH on column b, but this step is usually notnecessary. 3. For each multicolumn index, execute UPDATE STATISTICS LOW for all of its columns. For the single-column indexes in the preceding step, UPDATE STATISTICS LOW is implicitly executed when you execute UPDATE STATISTICS HIGH. 4. For small tables, run UPDATE STATISTICS HIGH. Because the statement constructs the index information statistics only once for each index, these steps ensure that UPDATE STATISTICS executes rapidly. Tim Jones wrote in message <364AF58F.377A@gate.net>... >For the PeopleSoft databases, we currently UPDATE STATISTICS MEDIUM on >the entire database, then UPDATE STATISTICS HIGH DISTRIBUTIONS ONLY on >each column which leads an index, then UPDATE STATISTICS LOW for each >additional column referenced in other parts of an index which are not a >lead column on another index. > >In our internal custom database which we run Informix 4GL apps against, >we pursue a similar but different strategy. We update statistics medium >on each individual table, then update statistics high on columns which >lead an index OR we update statistics high on multiple columns in the >same order in which they appear in important indexes. > >I want to know if these strategies are appropriate or are there better >strategies to improve performance, particularly on the PeopleSoft >databases. > >Since going to 7.3 the performance of the PeopleSoft databases have >improved in some cases but severely degraded in other cases. We have >also seen poor performance in our internal custom app which has been >fine for years in regards to some joins on unindexed columns. > >This CASE # is 794304 Opened 11-12-98 > > >Thanks in advance.