Neoview Database Administrator's Guide (R2.5)

5 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. You can also specify tables that you do not want UPDATE STATISTICS
to automatically operate on. 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 used recently by the optimizer and are now obsolete
Histograms that are no longer needed by the optimizer are not generated.
A recently used histogram is one that was used in the last 5760 minutes (or four days). 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 25)
• “Listing the Automated Tables” (page 28)
• “Stopping Automated UPDATE STATISTICS” (page 29)
• “Scheduling Automated UPDATE STATISTICS and REORG Using NCI” (page 30)
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, or to add tables to a list that prevents UPDATE STATISTICS from being
performed.
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 | EXCLUDE }
Setting Tables for Automated UPDATE STATISTICS 25