Configuring the database maintenance tasks

The database maintenance tasks enable you to manage the data warehouse. The three database maintenance tasks are as follows: 

  • Database Cleaner task: Enables you to configure simple queries for the deletion of content of tables.
  • Database Space Manager task: Enables you to manage tables and partitions configured in the Aging section, and to evaluate statistics.
  • Table Exporter task: Enables you to export the content of tables to files. You need to manually configure this task.


To configure the database maintenance tasks

  1. To access the database maintenance tasks, in the TrueSight Capacity Optimization console, navigate to Administration > ETL & SYSTEM TASKS > System tasks, and in the System tasks table, click the appropriate task name.
    The task details page is displayed.
  2. In the Run configurations table, click Edit .
  3. In the General configuration section, specify the task name, deploy status or environment, and a description. 
  4. Perform the task-specific configuration:
    • Database Cleaner task: You can configure automated purging of older results of your report instances to free up space in the file system. By default, 100 most recent results per report instance are retained, and the older report results are purged.
      If required, modify the number of report results to be retained from the Report aging field. 

    • Database Space Manager task:

      Click Advanced and in the DB Manager tab, select the Analyze mode option as per your requirements:

      • Auto mode (Default): Performs an Oracle Table Statistic Update for the tables or partitions that are under aging configuration. 
      • Analyze schema: Recomputes statistics for all schemas. You should use this mode only in case of performance issues to force statistic updates for tables.
      • Analyze all partitions: Recomputes statistics for all partitions. You should use this mode only in case of performance issues to force statistic updates for tables.

      Note

      It is not recommended to select the Analyze schema or Analyze all partitions option for all daily runs.
      The recommended setting is to leave it running in Auto mode.

    • Table Exporter task: You can configure the data backup properties.
      In the General configuration section, specify the details of the tables that you want to back up and the output directory where the backed up file will be stored:
        • In the Table list field, enter a semicolon (;) separated list of database table names to be backed up to a file.
        • In the Output directory field, enter the full path to the backup directory (for example, /opt/cpit/dbalignment/backup/source) and provide the Output file name.

  5. To configure the scheduling of the task, click Advanced, and then click the Scheduling options tab.
    • To restrict the running of the task at a specific hour of the day (for example, 06), day of the week (For example, Mon), and day of the month (For example, 5), specify the appropriate value in the corresponding fields and ensure that the Apply mask validation option shows true
      Hour maskDay of week maskDay of month mask
    • To run the task after a specific time, specify the time in hh:mm:ss format in the Execute after time field. For example, 23:30:00.
    • To mark the task as enqueable, in the Enqueable list, select true.

Database maintenance FAQs

This section addresses the following frequently asked questions related to database maintenance:

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.

Was this page helpful? Yes No Submitting... Thank you

Comments