ASO_ORDER_FEEDBACK_T runs amok, devours database!

There, got that out of my system. I really shouldn't write blog entries after reading The Onion.

A post today on Oracle Mix prompted me to dust off some notes about a problem I ran into a while ago with a table growing out of control in E-Business Suite. Here it is, rescued from the "Hey, that could be a blog post someday" bin:

Observations

  1. The APPS_TS_QUEUES tablespace in our production E-Business Suite instance had grown to the point where it was a significant percentage of the database's overall storage footprint. Not huge, but big enough to be noticeable.
  2. Most of the space seemed to be consumed by the table ASO_ORDER_FEEDBACK_T and its related LOB segments:
    select segment_name "Segment",
    tablespace_name,
    segment_type "Type",
    bytes/1024/1024 "MB"
    from dba_segments
    where segment_name like 'ASO_%FEEDBACK%';
    
    Segment              TABLESPACE_NAME Type  MB
    -------------------- --------------- ----- -----
    ASO_ORDER_FEEDBACK_T APPS_TS_TX_DATA TABLE 3001
    
    select sum(s.bytes)/1024/1024 "MB", count(1) "NumLobs"
    from dba_lobs l, dba_segments s
    where s.segment_name = l.segment_name
    and l.table_name = 'ASO_ORDER_FEEDBACK_T';
    
    MB    NumLobs
    ----- ----------------------
    520   26
    
  3. It appears that all space in ASO_ORDER_FEEDBACK_T is taken up with unprocessed records for the Trade Management (OZF) consumer:
    select consumer_name, msg_state, count(*)
    from aso.aq$aso_order_feedback_t
    group by consumer_name, msg_state;
    
    CONSUMER_NAME MSG_STATE COUNT(*)
    ------------- --------- ----------------------
    OZF           READY     468562
    
  4. On our system, Trade Management (OZF) wasn't even licensed, which probably accounts for the records not being consumed from the queue:
    select app.product_code,
    tl.application_name,
    decode(inst.status, 'I', 'Installed',
    'S', 'Shared',
    'N', 'Not Installed',
    'Wha-huh?') LicStatus,
    inst.patch_level
    from applsys.fnd_product_installations inst,
    applsys.fnd_application app,
    applsys.fnd_application_tl tl
    where app.application_id= inst.application_id
    and tl.application_id= app.application_id
    and app.product_code = 'OZF';
    
    PRODUCT_CODE APPLICATION_NAME LICSTATUS      PATCH_LEVEL
    ------------ ---------------- ---------      -----------
    OZF          Trade Management Not Installed  R12.OZF.A.4
    

Remediation

The following Metalink My Oracle Support Notes address this situation. Although the notes reference to Apps 11.5.x, the conditions and fixes are also applicable to R12, as far as I can tell.

In our case, the fix was easy, since we only had one registered consumer for the queue, and we definitely weren't using that module/application in E-Business Suite:

  1. Delete the QuickCode for OZF as described in Question 1 of Note 181410.1
  2. Purge and rebuild the ASO_ORDER_FEEDBACK_T table as described in Note 740305.1. The process is referenced in Note 181410.1 as well, but it's spelled out much more clearly in Note 740305.1.

Caveats

  • The solution is only this easy if you're not actually using the Order Feedback queue. If you are using the queue, then truncating and rebuilding it is not such a great idea. You might still be able to reclaim some space the slow way, though.
  • While neither document lists this as a requirement, you're probably better off performing this work during a maintenance window, when users aren't doing anything that might be trying to modify the queue.
  • You are going to run through this process on your test system first, right? Good. ;-)

3 Comments

  1. Javed
    Posted 5 December 2009 at 3:32 | Permalink

    Great info ...

    Cheers

  2. Posted 19 July 2013 at 19:02 | Permalink

    Thanks for sharing John. Very useful and educational.
    I wonder what functionality the messages are coming from. it could be that there is a Functional Level setup that wasn't performed in the right way and therefore messages are ending up in the queue.

    Yury

  3. Posted 19 July 2013 at 19:11 | Permalink

    Hi Yury,

    It's been a few years, but as I recall from the Notes I read, this usually comes from a setting (the Quickcode) that is enabled by default accidentally.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*