Database maintenance tasks (DB Cleaner, DB Space Manager, DB Table Exporter) manage the data warehouse. In particular:
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.
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 Editto 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:
DB Table Exporter task - general 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.
The following Analyze modes are available:
It is not recommended to leave the Analyze mode turned ON for all daily runs.
The recommended setting is to leave it running in Auto mode.
This section addresses the following FAQs related to database maintenance:
rowcount) in the Oracle Explain Plan updated by the Auto or Analyze mode, or by neither?
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.
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.
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.