Neoview Database Administrator's Guide (R2.3)
8 Automating Update Statistics and Reorganize Operations
You can automate both UPDATE STATISTICS and MAINTAIN (REORG) operations to run
during a maintenance window (that is, a specific time interval) every day and to operate against
a specific set of tables. The automated UPDATE STATISTICS operation uses the UPDATE
STATISTICS statement with the NECESSARY keyword to determine which histogram statistics
need to be updated for a set of tables and then updates them by using a sample size based on
the skew, the default sample size, and the sample size of the last run. Automated UPDATE
STATISTICS automatically generates histogram statistics for:
• Missing histograms needed by the Neoview SQL optimizer
• Histograms that have been read recently and are now obsolete
Histograms that are no longer needed by the optimizer are not generated.
A recently read histogram is one that was read less than 5760 minutes (or four days) since the
last time the histogram was read. To change the default limit of 5760 minutes, contact your HP
support provider.
An obsolete histogram is one where the percentage of modified rows (that is, inserted, updated,
or deleted rows) in the table, since the histogram was last generated, is greater than or equal to
15 percent. To change the default limit of 15 percent, contact your HP support provider.
Automated REORG reorganizes database tables during a percentage of time within the same
maintenance window as automated UPDATE STATISTICS. REORG compacts space needed for
rows by removing unused table space. Automated REORG operations are first performed on
the list of tables specified for automated UPDATE STATISTICS and then performed on the rest
of the database tables.
By default, no database tables are set for automated UPDATE STATISTICS and REORG. To
enable and change settings for automated UPDATE STATISTICS and REORG, see:
• “Setting Tables for Automated UPDATE STATISTICS” (page 103)
• “Listing the Automated Tables” (page 105)
• “Stopping Automated UPDATE STATISTICS” (page 107)
• “Scheduling Automated UPDATE STATISTICS and REORG” (page 107)
NOTE: To enable and change settings for automated UPDATE STATISTICS and REORG, you
must be logged in as ROLE.DBA (or super.services or the super ID if you are HP support).
Setting Tables for Automated UPDATE STATISTICS
HP_USTAT.CHG_AUTO_LIST() Stored Procedure
By default, no database tables are set for automated UPDATE STATISTICS. You must call the
stored procedure, CHG_AUTO_LIST(), to start the automation of UPDATE STATISTICS. The
CHG_AUTO_LIST() procedure also enables you to add or delete table names from the list of
automated tables.
Syntax
HP_USTAT.CHG_AUTO_LIST( IN 'operation' CHAR(10),
IN 'schema' CHAR(258),
IN 'table' CHAR(258),
OUT response CHAR(25))
'operation' is: { INSERT | DELETE }
Setting Tables for Automated UPDATE STATISTICS 103