| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
At 01:34 PM 14-10-03 -0800, you wrote:
>Hemant,
John,
My apologies for the delay. I hadn't logged on to my email last night.
Here's the output from my site
11.0.3
"Purge Obsolete Workflow Runtime Data" set to AGE=90 days [ITEM_TYPE and
ITEM_KEY null in parameters]
SQL> set time on
14:57:42 SQL> 14:57:42 SQL> select activity_status, count(*) 14:57:46 2 from applsys.wf_item_activity_statuses 14:57:46 3 group by activity_status;
Hemant
>This applies on 11i only. I would rebuild all indexes supporting the
>WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been
>working on some AOL table(space) problems in the background and noticed that
>in 11i by default, we are not be purging _all_ the WF data that we should be
>purging. I believe the current Purge routine purges activity rows whose
>persistence has expired and are marked 'TEMPORARY' and ignores those that
>are COMPLETE (see below). My contention is that it should be deleting old
>rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes
>141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.
>
>You could check this using the following SQLs
>
>select activity_status, count(*)
>from applsys.wf_item_activity_statuses
>group by activity_status;
>
>select item_type,activity_status,count(*)
>from
>applsys.wf_item_activity_statuses where activity_status='COMPLETE'
>group by item_type,activity_status;
>
>Once the 'correct' purge is complete, the 'holey' indexes will need to be
>rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to
>reasonable levels.
>
>Let me know what your install shows up.
>John Kanagaraj
>DB Soft Inc
>Phone: 408-970-7002 (W)
>
>Grace - Getting something we do NOT deserve
>Mercy - NOT getting something we DO deserve
>Click on 'http://www.needhim.org' for Grace and Mercy that is freely
>available!
>
>** The opinions and facts contained in this message are entirely mine and do
>not reflect those of my employer or customers **
>
>-----Original Message-----
>Sent: Tuesday, October 14, 2003 8:39 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>John,
>
>I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the
>table itself, occassionally].
>This Saturday I will also be rebuilding some ALR indexes.
>Which WorkFlow Indexes do you rebuild ?
>
>Hemant
>
>At 11:44 AM 13-10-03 -0800, you wrote:
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: John Kanagaraj
> INET: john.kanagaraj_at_hds.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital_at_singnet.com.sg Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Oct 16 2003 - 10:19:29 CDT
 |
 |