Re: A couple of questions

"Chuck" <chuckh_at_softhome.net> wrote

> Last I had heard, Oracle does not recommend you mess with run time
> priorities. You'd probably be better served on rethinking the job and
> seeing if you can get it to run more efficiently.

I have a typical OLTP database with many insertions per second. Whenever I run a classical DELETE command, processes on that machine (other than ORACLE.EXE) simply can not run. I run this DELETE command once daily, to maintain my data horizon from 90 days.

I tried the following approach: I defined a cursor (in a procedure) looping over the critical table, and then COMMITing on every 10,000 rows using ROWNUM. (Instead of a simple DELETE command, which deletes about 400,000 rows in one fell swoop.) But it doesn't help. The procedure still takes too much CPU, although it tries to commit chunk by chunk.

Any other idea? I have to maintain that data horizon of 90 days somehow, without taking near 100% of CPU.

> For example, if you can
> partition the table by whatever column you use to drive the delete, you
> could just drop the partition.

I'm running the Standard edition.

> Or if you aren't using partitioning, you
> might be able to create a new table with just the rows you want to keep.

Won't fly, I need that table all the time. Received on Wed Sep 03 2003 - 13:37:17 CDT