Friday May 29, 2015

Increase throughput of your Workflow Mailers

The Workflow Notification Mailer is a component running on the Workflow Mailer GSM Service (also known as container). This service can host multiple instances of the Workflow Notification Mailer so that the load of sending and receiving e-mails can be shared, improving the overall performance. This is specially true as more and more applications moved from PLSQL-based notifications, that pretty much only required access to the database, to HTML/Framework-based notifications that also require access to the application server to retrieve the content of these good-looking notifications. 

Note: to learn more about Generic Service Management click here.

Creating a new component for the mailer allows the following:

  • Increase the number of processes for inbound and outbound processors - although this can also achieved simply by increasing the number of processors in the existing Notification Mailer 
  • Use a different SMTP account to process outbound messages and/or a different IMAP account for inbound processing
  • Specify a different correlation ID to dedicate message processing to a specific item type
  • Specify a user, responsibility and application different from the default one (SYSADMIN). This is typically done when specific profile values need to be applied for the applications user that the mailer will use to retrieve its content.

Creating another Notification Mailer component

 By creating a new Container a whole new set of components will be added to exclusively manage the components (mailers) and this way provide more scalability. The diagram below depicts what a Component Container is composed by.


 To create a new component follow this simple steps:

  • Connect to Oracle Apps with the responsibility Workflow Administrator Web Applications
  • Navigate to Oracle Applications Manager -> Workflow Manager
  • Click on Notification Mailer. Then click on the Create button on the top right part of the page
  • Select the Workflow Mailer service and hit Continue
  • Key in the corresponding parameter values for this new Notification Mailer.
  • Use the Advanced option to specify the Correlation Id and other parameters. The Inbound Agent is always WF_NOTIFICATION_IN while the Outbound Agent is WF_NOTIFICATION_OUT.

Creating a new Workflow Mailer service

A new Workflow Mailer component still has a limitation: the connection to retrieve HTML/Framework content is synchronized and this means that only one Java thread can retrieve the content at a time. Of course, this retrieval happens very fast, but still there is the chance of a bottle neck when the number of this kind of notifications is very high. 

To increase the performance in this scenario a new Workflow Mailer service is the solution, and it can be done as follows:

  • Connect to Oracle Apps with the responsibility Workflow Administrator Web Applications
  • Navigate to Oracle Applications Manager -> Workflow Manager
  • Under column Container, click on Workflow Mailer Service and hit the Create New button
  • Enter the names desired for the new service. The image below provides an example
  • Use the button Add from Available Shits to indicate the availability of this new service. Use the one called Active 24 hours every day.

  • Save the changes

Now new Workflow Notification component can be created with the steps given earlier, but instead of using service Workflow Mailer, use New Workflow Mailer with the desired parameter values.

Tuesday Aug 03, 2010

Gathering Stats for Workflow's Queue Tables

This blog post contributed by Ajay Verma.

Statistics gathering is an important activity in order to enable the cost based optimizer to take a decision on the execution plan to choose from the various possible ones with which a particular SQL statement can be run. It is recommended to gather the statistics for an object when it is in steady state, i.e. when the corresponding tables have a fair amount of data giving out statistics which would lead the best execution plan to be picked by CBO.

From 10g onwards , Oracle database introduced Automatic Optimizer Statistics Collection feature in which a scheduled job GATHER_STATS_JOB runs in the nights and gathers stats for tables with either empty or stale statistics using DBMS_STATS  package APIs. This indeed is a great feature but for highly volatile tables such as AQ/Streams tables  it is quite possible that when the stats collection job runs, these tables may not have the data that is representative of their full load period. One scenario can be stats collection on queue tables when they were empty, and the optimizer, as a result, choosing poor execution plans when they have a lot of data in them during normal workload hours.

As a direct fix to tackle the above issue, from 10gR2 database onwards, AQ table stats are getting locked immediately after its creation so that the auto stats collector doesn't change it in the future. This can be confirmed as below:

SQL> CREATE TYPE event_msg_type AS OBJECT   
                  (  name            VARCHAR2(10), 
                     current_status  NUMBER(5),  
                     next_status     NUMBER(5)  
Type created.
--Create Queue Table
SQL> EXEC DBMS_AQADM.create_queue_table(
PL/SQL procedure successfully completed. 
--Create Queue
SQL> EXEC DBMS_AQADM.create_queue(queue_name =>'test_queue',
PL/SQL procedure successfully completed.
--Check for locks on stats
SQL> select owner, table_name, stattype_locked
     from dba_tab_statistics
     where stattype_locked is not null
     and owner='APPS' and table_name='TEST_QUEUE_TAB';
---------- ---------------- ----- 
--Gather stats
SQL> EXEC dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB')
BEGIN dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'); END; 
ERROR at line 1: 
ORA-20005: object statistics are locked (stattype = ALL) 
ORA-06512: at "SYS.DBMS_STATS", line 18408 
ORA-06512: at "SYS.DBMS_STATS", line 18429 
ORA-06512: at line 1

As we can see above , queue table stats are locked and hence further stats gathering fails. We can still go ahead and use force parameter provided in DBMS_STATS package to override any lock on statistics:

SQL>exec dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'
PL/SQL procedure successfully completed.

The recommended use is to gather statistics with a representative queue message load and keep the stats locked to avoid getting picked up by auto stats collector.

What happens in Apps Instance?

From Oracle Apps 11i  (on top of Oracle Database 10gR2) instance onwards, the automatic statistics gathering job GATHER_STATS_JOB is disabled using $APPL_TOP/admin/adstats.sql , as can be confirmed below:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS 
JOB_NAME                       ENABLE
------------------------------ ------

Oracle Apps provides separate concurrent programs which make use of procedures in FND_STATS package to gather statistics for apps database objects. FND_STATS is basically a wrapper around DBMS_STATS and is recommended by Oracle for stats collection in Oracle Apps environment because of the flexibility it provides in identifying the objects with empty and stale stats. The modification threshold ( % of rows used to estimate) is fixed to 10% in DBMS_STATS while it can vary from 0 to 100% incase of FND_STATS.

But FND_STATS doesn't provide the force parameter to override any lock on statistics and hence fails while gathering statistics for a queue table whose stats are locked :

SQL> select owner, table_name, stattype_locked ,last_analyzed
  2   from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
-------------- --------------- ----- ---------
APPLSYS        WF_DEFERRED     ALL   23-FEB-09
SQL> exec fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
BEGIN fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); END;
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 1505
ORA-06512: at line 1	

The same error is observed on running the Gather Table Statistics concurrent program for such queue tables. Gather Schema Statistics concurrent program in general skips the table incase any error is encountered while gathering its stats and hence the queue tables with locked stats are also skipped.

Hence currently ,  in order to carry out stat collection for Workflow queue tables whose stats are locked,the approach should be to unlock the queue table temporarily using DBMS_STAT's Unlock APIs when the table have a representative load for the correct stats to be gathered, gather the statistics using FND_STATS and lock it again.

SQL> begin
  2    dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
  3    fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
  4    dbms_stats.lock_table_stats('APPLSYS','WF_DEFERRED');
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> select owner, table_name, stattype_locked ,last_analyzed
  2  from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
---------------- ---------------- ----- -------------
APPLSYS          WF_DEFERRED      ALL   12-APR-09	

Another approach can be to unlock the WF queue table stats during the adpatch run itself as Oracle's automatic stats collection feature is disabled in Apps environments and the stats collection in general occurs through the Apps provided concurrent programs. What say?



« July 2016