I had to take over WF management from my colleague and I s**k.
- current WF sizes ~80G, and eats ~1.4G daily.. and I assume its mostly text
- got millions of lines in wf_error, wf_deferred (and all other wf tables)
- i read tons of blogs and forums, and they all end up with a bunch of meta-links
- its a stand-alone wf installation, got no app server tools with it..
- wf_purge is running nightly for 6 hours and I cant see its procedures hitting any of these tables (no size or stat changes)
can anyone give a real life example? what would happen if I simply truncate wf_error and wf_deferred? im not planning to truncate other tables, but it would be nice to loose some weight there as well.
i made a db clone and truncated these tables, its running for 2 days already and I cant see anything terribly wrong (like warned in a couple of forums).
another thing i tried - executed wf_event.listen on all 'Ready' messages, that didnt help either.
I'm in same position as you. Millions of records in WF_ERROR and WF_DEFERRED. And no apparent way to easily remove rows.
Did you end up truncating these tables in Production? Any issues when you did so?
WF_ERROR queue is used for error reporting purposes. You can rebuilt the queue table and get away with it, but any failures that occurred and have not been notified about could be missed.
WF_DEFERRED is a functional queue and it contains messages for applications all around, of any type. Messing with this queue is very advised against. It cannot be recovered but from a backup. You want this queue to be successfully dequeued and processed by the Deferred Agent Listener.
Now, as with any queue in Advance Queuging (Streams) you can check the property RETENTION_TIME of the queue table. When a message is processed and no longer needed, the message will be there for as many seconds as this column reads. Check DBA_QUEUES.RETENTION_TIME. Usually this value should be zero but sometimes can be increased so as to let messages sit there for a while and run debug activities. If no issues are present then the retention time should be zero and messages should be removed as soon as they are processed.
Please check dbms_aqadm package to see how to change the retention_time.
Lastly, check what the status of those messages are. Ideally you should only have status=0 (ready to be processed by the next loop of the agent listener) or 2 (processed). Large amounts of messages in other states are to be investigated.