Hi all,

We're experiencing some severe performance issues on the absence (PERSWEAD) form within HR. All my traces point at the fact that the queries relating to the following two tables are taking the most time:

pay_element_entries_f
pay_element_entry_values_f

As a result of a 3 year plus payoll live period, and an OTL system which makes more element entries than it is possible to shake the proverbial stick at, I've got 11.6
million rows and 75.6 million rows in each of these tables respectively.

/* Cowboy hat on */

Hacking out the vast majority of these rows in a test environment reduces the query time for the absence form substantially, to a matter of seconds, rather than a couple of minutes

/* cowboy hat off */

So- my question is: has anyone had any experience of purging these tables of obselete data? There is no core Purge process for these tables that I can find, and Oracle are being less than co-operative...

Any helps/ hints/ ideas/ cowboy solutions appreciated.

Thanks

Simon,

We've got 34 million rows in pay_element_entries_f and have toyed with the idea of purging these tables. We used py_element_entry_api.delete_element_entry with p_datetrack_delete_mode => 'ZAP' on a test instance as part of a 'Clean up suite' but have yet to encounter performance problems that merit taking it further.

Have you checked the parameters on pay_action_parameters? I believe that LOW_VOLUME = 'N' will put a hint on the sql that may speed things up a bit (I'm sure on of our techie gurus will confirm/elaborate). We also use CHUNK_SHUFFLE = 'Y' so that the payroll run slaves spread the load.

Pat

I know this is an old post, but thought i'd chip in with new / recent info.

There have been several performance enhancements in recent patches (during the course of 2007) - i will not mention names, but many of these have been driven by technical work carried out by a well-known propeller-head techie working here at the nations health provider...

Also at same said project, there have been numerous trials of 3rd party archiving tools/solutions...this is still on going as we have yet to find a best fit tool (and may end up producing our own). The best sofar has been a dramatic re-working of the killer script <img src="/ubbthreads/images/graemlins/notworthy.gif" alt="" /> (commissions greatfully accepted).
I'll keep you all posted on any news.

Oh, and 34million rows is not huge <img src="/ubbthreads/images/graemlins/smile.gif" alt="" /> We're currently topping the billion-row mark for run result values, and the element entry table is past the 100 million row mark.