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;
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
|CRM Foundation||Contracts Core|
|Lease and Finance Management||Payments|
|Process Manufacturing Inventory||Projects|
|XML Publisher||Advanced Pricing|
|Bills of Material||Capacity|
|Legal Entity Configurator||Payroll|
|Service Contracts||Subledger Accounting|
|Enterprise Asset Management||Installed Base|
|E-Business Tax||Enterprise Command Center|
|Advanced Supply Chain Planning||Human Resources|
|Master Scheduling/MRP||Cash Management|
|Order Management||Work in Process|
|Cost Accounting||General Ledger|
|Database Administration||Application Object Library|
© 2021 Enginatics