Re: A couple of questions

MK wrote:

>"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.
>
>
>

First ... stop with the incremental commits. It does nothing but slow things down.

Next ... on what basis are you performing the deletes? Presumably a WHERE clause.
Are the columns in the WHERE clause part of an index? Is the optimizer set to CHOOSE?
Are statistics current for both the table and index using DBMS_STATS?

If optimizing what you have doesn't work ... I'd be thinking partitioning. And if you can't upgrade from Standard Edition to Enterprise Edition ... build your own partitioning. It isn't difficult.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)