Gathering Oracle or DB2 statistics

Oracle DB2
Tivoli® Netcool® Performance Manager uses the Oracle or DB2 cost-based optimizer to determine the execution plans for the SQL queries that it uses.

Oracle or DB2 evaluates the relative cost of executing a query by using the current size of the tables involved. In order for the optimizer to determine an optimal execution plan, it needs accurate table statistics. Tivoli Netcool Performance Manager, therefore, supports a scheduled Oracle or DB2 statistics-gathering mechanism.

To activate statistics gathering, add the following command to the crontab of the DataMart owner:

0 0 * * * [ -f $PVMHOME/dataMart.env ] && . $PVMHOME/dataMart.env && dbMgr
analyzeMetaDataTables A N

Note: If you do not add this command to crontab, no statistics are gathered on the database metadata and inaccurate execution plans are generated.

The frequency of the statistics gathering is defined in the CFG_STATS table in the admin schema. Because there is cost that is associated with gathering these statistics, the database has an analysis schedule for each Tivoli Netcool Performance Manager table.

Note: After a fresh installation and your initial inventory, IBM recommends that you manually force a complete statistics gathering. You can manually force a complete statistics gathering by using dbMgr with the option analyzeMetaDataTables and the option force.

For example: dbMgr analyzeMetaDataTables A Y

For more information about dbMgr command, see dbMgr Command Reference.