ASO_ORDER_FEEDBACK_T is huge !!!

Posted on August 12, 2008. Filed under: DBA/Admin Tips |

Friends,
As part of regular house-keeping activities, we were checking for objects with huge sizes in our 11i10 apps instance and came across this object : ASO_ORDER_FEEDBACK_T

This is basically an “Order Capture” queue table that is maintained by Order Management. It works in a broadcast-subscribe concept. Order Management broadcasts evets (messages) via this queue and ANY (built-in or custom) application can subscribe to this queue to receive those messages.

In our environment, the table had millions of rows and was quite huge (12Gb). This was taking bulk of the space in the APPS_TS_QUEUES tablespace. When i was checking metalink to find ways to purge this queue, i came across this informative metalink note : 181410.1

To check, which modules have subscribed to this queue, run the SQL

SQL > select consumer_name, msg_state, count(*) from aso.aq$aso_order_feedback_t group by consumer_name, msg_state ;

In our case , we had  OZF || READY || 4555658 which means only OZF module has subscribed to this queue and has NOT processed around 4.5 million records. We realized we maynot be using this and checked with the Functional group. Since they said NO, we thought of purging it.

How to purge?

Need to do 2 things.

1) Unsubscribe OZF module subscription. To do this, login to apps as “Quoting Sales Manager” resp and navigate as Setup -> Quick Codes -> Query for “ASO_ORDER_FEEDBACK_CRM_APPS”. You will see OZF’s subscription (result of above SQL query). Just disable / delete the record there.

2) Follow step 4 of the above metalink document to drop and recreate the queue.

As always, do this on a test environment , get a good functional (Order Mgmt,Shipping etc) flow testing done before thinking to do this in PROD.

We saved close to 12G. Perhaps, you could save more instead of adding another datafile to APPS_TS_QUEUES tablespace !!

Hope this tip would help.

Cheers,

OPS Team

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

4 Responses to “ASO_ORDER_FEEDBACK_T is huge !!!”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

In one of our 11i102 environment, this table size was 3 GB with consumer OZF. Finally now as per functional guidelines disabled the same and recreated the queue.

Nice info. Thanks.

Thanks Periyasamy.

thanks for this note.
can you tell me if you have to stop any services during production queue rebuilding or this can be done online while users/jobs running normally.

excellent article………

Regards
TAI


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: