Configuring the database maintenance tasks

Database maintenance tasks (DB Cleaner, DB Space Manager, DB Table Exporter) manage the data warehouse

• The DB Cleaner task allows you to configure simple queries for the deletion of table.
• The DBSpaceManager task allows you to manage tables and partitions configured in the Aging section, and to evaluate statistics.
• The DB Table Exporter task allows you to export the content of tables to files.

Examples of database maintenance tasks

The only task which needs to be manually configured is the DB Table Exporter task, that performs a back-up of database tables on the filesystem.

General configuration

Navigate to Administration > ETL & SYSTEM TASKS > System tasks and in the working area on the right, click the appropriate name available in the System tasks table. In the Run configurations table, click Edit  to access the advanced configuration page. Click the General configuration tab, in addition to general settings such as name, environment and description, you can edit the following properties:

• Table list: A semicolon (;) separated list of database table names that will be backed up to a file.
• Output directory: Full path to the backup directory, e.g. /opt/cpit/dbalignment/backup/source.
• Output file name: The backup file name.

DB Table Exporter task - general configuration

Advanced configuration

The DB Manager tab enables you to set a variety of advanced options concerning the DBSpaceManager task. The following sections discuss these options and properties in more detail.

Setting Analyze modes

The following Analyze modes are available:

• Auto: Perform an Oracle Table Statistic Update for the table or partitions that are under aging configuration. This is the default mode.
• Analyze schema: Recomputes statistics for all schemas. Use this mode only in case of performance issues to force statistic updates for tables.
• Analyze all partitions: Recomputes statistics for all partitions. Use this mode only in case of performance issues to force statistic updates for tables.
• Disabled: Indicates that the Analyze mode is disabled.

Database maintenance FAQs

This section addresses the following FAQs related to database maintenance:

• 861124707
• 861124707
• 861124707

Is the Estimated Rows Count (rowcount) in the Oracle Explain Plan updated by the Auto or Analyze mode, or by neither?

The rowcount that is updated is visible using the following queries:

• select table_name, num_rows, sample_size, last_analyzed, avg_row_len from user_tables
• select table_name, partition_name, num_rows, sample_size, last_analyzed, avg_row_len from user_tab_partitions

Though the Estimated Rows Count in a execution plan is based on statistics, it also depends on rows that are involved in the query, that is, it specifies the number of rows selected by specific queries, and not the number of rows present in the table.

What is the value of manually executing the Database Statistics gathering in TrueSight Capacity Optimization?

The process of manually gathering schema statistics as described in the Oracle Statistics and Performances Knowledge Base Article helps only if you integrate several TrueSight Capacity Optimization sources during first week of utilization. This floods millions of rows per day, while Oracle assumes that the tables are completely empty, that is, it remains under the impression that the Oracle statistics jobs not yet executed.

About the Space Used metric and the Database Space Manager

FAQ: How does the Database Space Manager process the series?

Answer: The Database Space Manager targets to compact a number of series daily, with a timeout of three hours. Due to this scheduled timeout of three hours, the targeted series may not get entirely processed in one day, depending on the number of series. In such a scenario, the Database Space Manager resumes processing the next day, starting from the last processed series that it could not completely compact, and targets to compact more series. If the Database Space Manager is timed out before 100 percent execution, it is logged in the Database Space Manager task logs. This process is repeated on a daily basis.

FAQ: Does the Space Used (MB) metric under Business Driver Data and System Data tables in Administration > Data Warehouse > Status have anything to do with the Database Space Manager capturing the Database Statistics or if this information is obtained differently in TrueSight Capacity Optimization.

Answer: The Database Space Manager task is responsible for filling a special table (TBS_SPACE) with the current space utilized by each database object (tables, indexes, partitions). This table is then used as input for the Status page.

The following question derives from the original question above:

FAQ: In the Business Driver Data and System Data tables, does the Space Used (MB) metric represent the actual amount of database space in-use within the tables, or does it represent the space consumed by the tables on disk (because Oracle doesn't shrink tables after data has been deleted from them)?

Answer: The Space Used (MB) metric represents the Allocated Space on the tablespace. And so, even if you delete all records through a Delete operation, the allocated space does not change. The only way to reclaim space in this case is to reorganize them, or to use a truncate drop storage operation, loosing all content.