"MK" <MK_at_foo.com> wrote in message
news:bj5cd6$fet07$1_at_ID-174077.news.uni-berlin.de...
>
> "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.
>
It'd be interesting to see the table definition and the delete statement.
I'm assuming that on average you are deleting 1/90th of the table ( i.e its
a 36 million row table) and that the column that drives the delete is
indexed? I'm also assuming stats are up to date and you are using the CBO?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Received on Wed Sep 03 2003 - 14:41:56 CDT
