Monday, October 8, 2012

DB Adapter Message Throttling in Oracle SOA Suite 11g

DB Adapter Message Throttling in Oracle SOA Suite 11g:


DB throttling is the mechanism to control the number of database records processed by the SOA engine in a particular interval through DB Adapter. 

Throttling also can be used to control the number of records send to the end systems. If the throttling is not defined, the end systems may flood with number of messages that will affect the functioning of the end systems. Throttling parameters should be configured based on the end systems capacity to process the incoming messages.

Until Oracle SOA Suite 11.1.1.6.0 the message throttling will not work as we expect, all the messages matching the where condition of the polling sql are processed in the same polling interval.

As of Oracle Adapters release 11.1.1.6.0 we can set the inbound DBAdapter property RowsPerPollingInterval to control the throttling. It acts as a limit on the number of records which can be processed in one polling interval. The default value is unlimited.

The Patch 12881289 should be applied to enable this for SOA 11.1.1.5.0 and earlier versions.

The maximum rows processed per second

The maximum rows processed per second are:
Number of active nodes in SOA cluster x NumberOfThreads x RowsPerPollingInterval / PollingInterval

MaxTransactionSize can be thought of as RowsPerDatabaseTransaction or DatabaseFetchSize that is how many records will be fetched to DB Adapter engine from the database for each transaction. It does not affect how many rows can be processed in one polling interval period.

The one exception is the following configuration:
-distributed polling checked, usesSkipLocking="false"




In this one case RowsPerPollingInterval will default to MaxTransactionSize instead of unlimited in case of RowsPerPollingInterval property is not specified in the jca file.

If RowsPerPollingInterval is set to lower than MaxTransactionSize or MaxRaiseSize, they will be effectively lowered to RowsPerPollingInterval that means the value RowsPerPollingInterval will considered for MaxTransactionSize and MaxRaiseSize.

There is no UI support to add the RowsPerPollingInterval property. We have to add the property manually to the db.jca file for the inbound polling service. Add it to the same section as the properties MaxRaiseSize, MaxTransactionSize, and PollingInterval, in any order.


29 comments:

  1. I am having a problem with BPEL process (Database adapter) on clustered environment (2 nodes). The adapter is supposed to poll a database table for changes after 8 minutes interval (PollingInterval=480) but it is working in a different manner.

    1) BPEL instance is created and it polls at table (time 05:13:21)
    2) BPEL instance is created and it polls at table (time 05:14:54) Another instance is created that only fetches single records from table even if there are many records.
    3) BPEL instance is created and it polls at table (time 05:14:53)
    4) BPEL instance is created and it polls at table (time 05:21:21)
    5) BPEL instance is created and it polls at table (time 05:22:54)

    Just wanted to know if you faced similar situation or have an idea what I am doing wrong?

    ReplyDelete
  2. Can you check how many records are processed in a particular polling interval?

    Number of active nodes in SOA cluster x NumberOfThreads x RowsPerPollingInterval / PollingInterval


    Regards
    Albin I

    ReplyDelete
  3. Hi Albin

    Where can i set RowsPerPollingInterval in 10g?

    ReplyDelete
    Replies
    1. This property is not supported in 10g.

      In 10g the polling is controlled by MaxTransactionSize and PollingInterval

      Regards
      Albin I

      Delete
  4. Hi Albin,

    After i did this change data is not getting polled, any thaughts.


    Thanks
    SNM

    ReplyDelete
    Replies
    1. Which version of SOA Suite you are using, if 11.1.1.5.0 then the patch should be applied before using this variable.

      In 11.1.1.6.0 the variable can be directly used.

      Regards
      Albin I

      Delete
    2. where can we get the patch albin?? it will be very helpfull if you post the URL from where we can download the patch

      Delete
  5. Thanks a lot Albin, very userful post

    ReplyDelete
  6. You have to login to oracle metalink to download the patch.

    Regards
    Albin I

    ReplyDelete
    Replies
    1. hi albin
      usesSkipLocked Condition is there in console
      But can we overwrite that property from our business logic
      In my case i don't have permissions to access console.. so can i make that usesSkipLocked property to false through my code(anywhere) without going to console

      Delete
    2. The usesSkipLocking property cant be updated from your business logic.You can write a WLST script/JAVA code to update the same,the weblogic admin username/password is required to execute either the WLST script or JAVA code.

      Regards
      Albin I

      Delete
  7. Hi Albin, very nice article. I am new to Oracle SOA 11g. which version you are using to select statement with multiple where conditions (eg: where (state <> 'P' or state is NULL)? I have not seen this option in my current version 11.1.1.5

    ReplyDelete
    Replies
    1. This where condition added based on the logical delete - STATE =P.

      We could not able to add multiple conditions in DB Adapter.You can implement such a scenario by polling on the Database view instead on the base table,create the view based on the required condition.

      Post if you have any further questions.

      Regards
      Albin I

      Delete
  8. Hi Albin

    can you please tell mewhich throttling is better either SOA throttling or OSB throttling?

    ReplyDelete
  9. Hi Albin,
    I created a project using file adapter to fetch records, but only one record is fetched rest of dem got skipped...!!!can u help me

    ReplyDelete
    Replies
    1. Verify whether the SQL generated in DB Adapter returns multiple records by executing the SQL from SQL clients.

      Regards
      Albin I

      Delete
    2. Yes I checked It has no errors...Its fetching all records,even though its not coming...!!!

      Delete
  10. Yes I checked It has no errors...Its fetching all records,even though its not coming...!!!

    ReplyDelete
  11. Hello Albin,
    Is there a way we can incorporate time-based polling in DB Adapter? What I mean is to fire the db adapter when a specific time has reached, say at 9 PM on weekdays. If you did any custom solution for this in the past, it would be really helpful for me.

    Thanks in Advance
    Sammy

    ReplyDelete
  12. We will not be able to implement the time-based polling in DB Adapter.
    You can achieve the same scenario by implementing scheduling.

    Create a Job class in java and schedule the same based on your requirement.
    Fetch the data from database table and Invoke the bpel service from the Job class

    Refer the below post for some the details.

    http://www.albinsblog.com/2011/12/oracle-soa-suite-11g-scheduling.html#.UyvZ9fmSySo

    Regards
    Albin I
    http://www.albinsblog.com/

    ReplyDelete
    Replies
    1. We thought about it. But the issue is we won't be able to achieve batching using scheduler, since the the database query returns huge number of records. A sync read of close to 200,000 records would not be possible if we use scheduler. My assumption is that batching of messages is only possible in Polling Mode. Correct me if I am wrong.

      Thanks
      Sammy

      Delete
    2. Sammy, did you find the solution to your problem?
      Also, wll the changes be applicable to OSB environment as well?

      Delete
    3. I am having a similar requirement where I need to do batch read using DB Adapter (not polling) and need to control the batch size. How is this done? I am on OSB 11.1.1.6

      Delete
  13. Hello Albin,
    Hi,

    We have been trying to move a large amount of records form Table1 (in DB1) to Table2 (in DB2) using OSB.
    We are using Db adapters to read data from Databases.

    DB1 (Table1) - Datasource - DBAdapter - ProxyService - BusinessService - DBAdapter - DataSource - DB2 (Table2).

    The Data looks like this:

    CHANGE_TYPE SI_CREATED_BY SI_CREATION_DATE GLOBAL_NAME HIERARCHY_TYPE LEVEL_NO NODE_DESC NODE_ID NODE_TYPE NODE_VALUE PARENT_DESC PARENT_NODE_ID PUBLISHED_DATE PUBLISHED_STATUS REFERENCE_ID SI_UPDATE_DATE SI_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY CREATED_BY CREATION_DATE LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10
    XXXXXXXX 12-22-2004 00:00:00 XX X/X XX XX XXX XXX - XX XX - XXXXX XXX (XXXXXXXXX) XXX,XXX X XXX_XXXXXX XX XXX XXX - XXXX XXX XXXXXXXX - XX XXX,XXX 04-05-2013 00:00:00 XXXXXXX 04-05-2013 00:00:00 XXXXXX

    The second row is just one record.

    When we do it for a less amount of records say 1000, we can read the data. But when we increase the number of records, we get the following error:
    weblogic.socket.MaxMessageSizeExceededException: Incoming message of size: '10000080' bytes exceeds the configured maximum of: '10000000' bytes for protocol: 't3'.

    We tried increasing the message size to 20000000, but still unable to read the data.Is there a way to do this simple operation in OSB?

    And after this is achieved, we need to configure the service in such a way that it will read the records from Table1 only once in 24 hours, i.e. if today 3 PM, then the next day 3 PM and so on.

    ReplyDelete
  14. Hi Albin,

    My requirement is very unique in the sense that I want only 32 Orders to be processed at any given time which is independent of the DB adapter threads. DB adapter can start 32 threads and stop but after a fixed interval(pooling interval) it will again spawn 32 records, time for completing the first set is variable and is cause of concern.

    Thanks,
    Ioan

    ReplyDelete
  15. For SOA Parking Lot Pattern (Throttling in BPEL) please refer:

    https://oraclesoabasicsforfreshers.wordpress.com/2016/03/10/soa-parking-lot-pattern/

    ReplyDelete
  16. Hi Albin,

    Is there a way to check how many records are processed in a particular polling interval in 11g?
    Can i find out throught wlst script?

    Regards,
    Noorki

    ReplyDelete
    Replies
    1. I don't think so that is possible anyhow the maximum records will be processed in a particular polling interval is - Number of active nodes in SOA cluster x NumberOfThreads x RowsPerPollingInterval / PollingInterval

      If you really want to identify the number of records polled in a particular polling interval, you can capture the polled time stamp of every record so that executing a SQL query in the table will help you to identify the counts(even you can identify the count through em console by counting the number of instance created in a particular interval X number of records processed by every instances).

      Regards
      Albin I

      Delete
  17. Does distributed polling with MaxRaiseSize work if I use custom sql for poll operation in dbadapter?
    I am polling db with a custom sql query. It works fine. But when I increase maxRaiseSize to 5, I am still getting one records in the poll.

    ReplyDelete