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.
Posted by at
Labels: ,

27 comments:

  1. UsmanNovember 7, 2012 at 6:21 AM

    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. AlbinNovember 7, 2012 at 6:25 AM

    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. ShwetaDecember 7, 2012 at 4:04 AM

    Hi Albin

    Where can i set RowsPerPollingInterval in 10g?

    ReplyDelete
    Replies
    1. AlbinDecember 11, 2012 at 6:19 AM

      This property is not supported in 10g.

      In 10g the polling is controlled by MaxTransactionSize and PollingInterval

      Regards
      Albin I

      Delete
  4. AnonymousJanuary 15, 2013 at 6:50 PM

    Hi Albin,

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


    Thanks
    SNM

    ReplyDelete
    Replies
    1. AlbinJanuary 16, 2013 at 11:57 PM

      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. AnonymousAugust 21, 2013 at 11:42 AM

      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. SuryaApril 16, 2013 at 7:52 AM

    Thanks a lot Albin, very userful post

    ReplyDelete
  6. AlbinAugust 22, 2013 at 12:48 AM

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

    Regards
    Albin I

    ReplyDelete
    Replies
    1. AnonymousAugust 22, 2013 at 8:35 AM

      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. AlbinSeptember 2, 2013 at 2:50 AM

      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. LakeshNovember 4, 2013 at 9:56 PM

    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. AlbinNovember 6, 2013 at 12:51 AM

      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. UnknownNovember 21, 2013 at 10:44 AM

    Hi Albin

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

    ReplyDelete
  9. UnknownMarch 6, 2014 at 5:04 AM

    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. AlbinMarch 6, 2014 at 5:46 AM

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

      Regards
      Albin I

      Delete
    2. UnknownMarch 7, 2014 at 6:21 AM

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

      Delete
  10. UnknownMarch 7, 2014 at 6:21 AM

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

    ReplyDelete
  11. AnonymousMarch 20, 2014 at 12:42 PM

    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. AlbinMarch 21, 2014 at 1:24 AM

    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. AnonymousMarch 24, 2014 at 11:40 PM

      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. ShanthiJune 29, 2016 at 8:01 AM

      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. IoanJuly 10, 2015 at 4:16 AM

    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
  14. UnknownMarch 11, 2016 at 5:24 AM

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

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

    ReplyDelete
  15. noorJune 30, 2016 at 3:56 AM

    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. AlbinJune 30, 2016 at 4:58 PM

      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
  16. PrasannaOctober 30, 2020 at 6:54 AM

    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

Subscribe to: Post Comments (Atom)