DBA Automated Maintenance Tasks

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
There are several automated jobs and 'advisors' running by default in an Oracle database that are often not required or should not run in Oracle EBS environments. Their status and history can be queried by the following SQLs:
select dao.* from dba_autotask_operation dao
select dajh.* from dba_autotask_job_history dajh

- Automatic Optimizer Statistics Collection
For EBS, the automated DB statistics collection must be deactivated by setting DB initialization parameter _optimizer_autostats_job=false, and concurrent 'Gather Schema Statistics' scheduled instead, see note 396009.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=396009.1 ...  There are several automated jobs and 'advisors' running by default in an Oracle database that are often not required or should not run in Oracle EBS environments. Their status and history can be queried by the following SQLs:
select dao.* from dba_autotask_operation dao
select dajh.* from dba_autotask_job_history dajh

- Automatic Optimizer Statistics Collection
For EBS, the automated DB statistics collection must be deactivated by setting DB initialization parameter _optimizer_autostats_job=false, and concurrent 'Gather Schema Statistics' scheduled instead, see note 396009.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=396009.1

- Automatic Segment Advisor
Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments. This process can cause significant IO overhead and we therefore recommend disabling the automated scheduling. It can be run on demand instead, in case DBAs would want to take action on optimizing the storage.

- Automatic SQL Tuning Advisor
Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. In EBS environents, such automated tuning recommendations are typically useless, as the majority of performance issues is caused by incorrect custom SQL coding, e.g. lack of where-clause restrictions to allow index access or coding that prevents the optimizer from executing queries efficiently. The automatic tuning advisor can not optimize such SQLs, as it can not modify the SQL logic, which is required for most scenarios. We recommend deactivating the advisor to reduce processing overhead.

- Automatic Database Diagnostic Monitor (ADDM)
This job runs after each AWR snapshot and creates performance tuning recommendations. In environments which are not actively monitored by DBAs such as development or test systems, this should be deactivated by setting alter system set "_addm_auto_enable"=false.
Blitz Report's DBA AWR reports give better and more detailed insights into performance bottlenecks, such as wait times or problematic SQLs.

To deactivate all automated tasks completely, execute the following commands:
exec dbms_auto_task_admin.disable;
alter system set "_addm_auto_enable"=false;   more

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
dawc.*
from
dba_autotask_window_clients dawc
Download
 
Related Blitz Reports
INV Item Upload INV Item definition detail CAC Intercompany SO Price List vs. Item Cost Comparison FND Key Flexfields INV Item Templates FND Descriptive Flexfields DBA AWR Active Session History DBA SGA+PGA Memory Configuration DBA AWR SQL Performance Summary
Blitz Report™

Blitz Report™ provides multiple benefits: