Re: Interesting problem

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 25 May 2011 19:40:27 +0200
Message-ID: <944t8eFl06U1_at_mid.individual.net>



On 25.05.2011 15:04, Mladen Gogala wrote:
> Yesterday, on one of my databases, DBMS_SCHEDULER job started burning CPU
> like crazy. I contacted the developer and he told me that the culprit job
> was supposed to regularly purge one fairly large table. The job was
> scheduled ad 03:00 AM and should have finished in half an hour, yet it
> was still running like crazy at 08:45 AM. Not only was it burning CPU,
> the most prevalent wait event was the wait for cache buffer chain latch,
> which is the real surprise here. Here is the source code:
>
> CREATE OR REPLACE PROCEDURE REPORT_CLEANUP_PURGE
> (
> p_rowlimit NUMBER DEFAULT 1000)
> AS
> TYPE report_tab
> IS
> TABLE OF reports.report#%TYPE;
> rep# report_tab;
> CURSOR del_cur
> IS
> SELECT report#
> FROM reports
> WHERE status='RECENT'
> AND created< TRUNC(sysdate);
> BEGIN
> LOOP
> OPEN del_cur ;
> LOOP
> FETCH del_cur BULK COLLECT INTO rep# LIMIT p_rowlimit;
> EXIT
> WHEN rep#.count=0;
> FORALL i IN rep#.FIRST..rep#.LAST
> DELETE FROM reports WHERE report# = rep#(i);
>
> FORALL i IN rep#.FIRST..rep#.LAST
> DELETE FROM report_agency_Sessions WHERE report# = rep#(i);
> COMMIT;
> END LOOP;
> CLOSE del_cur;
> END LOOP;
> END;
> /

Just out of curiosity and hoping to learn something: why was this done in a stored procedure with cursors? I figure with an FK on report_agency_Sessions.report# ON DELETE CASCADE and maybe a bit of analytic SQL (for the p_rowlimit) it should be possible to do this with a single DELETE. Even if no FK was used and collecting report# to delete was expensive a trigger or a global temporary table could be used. Am I missing something?

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
Received on Wed May 25 2011 - 12:40:27 CDT

Original text of this message