Home » SQL & PL/SQL » SQL & PL/SQL » Pl/SQL Procedure Performance Issue (Oracle 10g)
Pl/SQL Procedure Performance Issue [message #417373] Fri, 07 August 2009 12:55 Go to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

I am working on archive and purge process where I need to move data from active database to Archive Database and once,the archive process is completed successfully then start purge process.

The Below procedure is for the Archive Process and the process is taking almost 40 min to complete and getting " Unable to Extend table Error "

Pls,suggest me where I am doing wrong....


CREATE OR REPLACE PROCEDURE Wf_archive
(num_days IN NUMBER,
commit_num IN NUMBER)
AS
--THE COMMIT COUNTER
v_commit NUMBER := 0;
CURSOR s1 IS
SELECT auditrecordkey
FROM bphadmin.auditrecord
WHERE Trunc(eventtimestamp) < = (SYSDATE - num_days);
iauditrecordkeylist auditrecord.auditrecordkey%TYPE;
BEGIN
OPEN s1;


FETCH s1 INTO iauditrecordkeylist;
LOOP
INSERT INTO auditrecord
SELECT *
FROM bphadmin.auditrecord
WHERE auditrecordkey = iauditrecordkeylist;

v_commit := v_commit + 1;

--CHECK IF COUNTER MATCHES THE COMMIT FREQUENCY
IF v_commit >= commit_num THEN
COMMIT;

-- RESET THE COUNTER
v_commit := 0;
END IF;
END LOOP;

--ARCHIVED sUCCESSFULLY
-- RETURN_VAL := 1;
CLOSE s1;
END wf_archive;
/
Re: Pl/SQL Procedure Performance Issue [message #417375 is a reply to message #417373] Fri, 07 August 2009 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your 3 previous topics:
BlackSwan wrote on Mon, 16 March 2009 02:16
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

BlackSwan wrote on Thu, 14 May 2009 00:01
use LOG ERRORS clause on DML statements

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

BlackSwan wrote on Wed, 24 June 2009 05:44
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Michel Cadot wrote on Wed, 24 June 2009 06:33
The only urgent thing for you to get a quick answer is to read OraFAQ Forum Guide and follow it.

Regards
Michel


Now maybe you know what you have to do.

Regards
Michel
Re: Pl/SQL Procedure Performance Issue [message #417379 is a reply to message #417373] Fri, 07 August 2009 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>suggest me where I am doing wrong....
Unwilling or incapable of reading & following Posting Guidelines

CREATE OR REPLACE PROCEDURE Wf_archive
     (num_days    IN NUMBER,
      commit_num  IN NUMBER)
AS
  --THE COMMIT COUNTER
  v_commit  NUMBER := 0;
  CURSOR s1 IS
    SELECT auditrecordkey
    FROM   bphadmin.auditrecord
    WHERE  Trunc(eventtimestamp) < = (SYSDATE - num_days);
  iauditrecordkeylist  auditrecord.auditrecordkey%TYPE;
BEGIN
  OPEN s1;
  
  FETCH s1 INTO iauditrecordkeylist;
  
  LOOP
    INSERT INTO auditrecord
    SELECT *
    FROM   bphadmin.auditrecord
    WHERE  auditrecordkey = iauditrecordkeylist;
    
    v_commit := v_commit + 1;
    
    --CHECK IF COUNTER MATCHES THE COMMIT FREQUENCY
    IF v_commit >= commit_num THEN
      COMMIT;
      
      -- RESET THE COUNTER
      v_commit := 0;
    END IF;
  END LOOP;
  
  --ARCHIVED sUCCESSFULLY
  -- RETURN_VAL := 1;
  CLOSE s1;
END wf_archive;
/ 

[Updated on: Fri, 07 August 2009 13:20]

Report message to a moderator

Re: Pl/SQL Procedure Performance Issue [message #417554 is a reply to message #417375] Mon, 10 August 2009 03:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You haven't told us the actual error that you're getting, so I can't give an accurate answer, but I'd guess that you've run out of extents in the table Auditrecord, or run out of space in the tablespace that it is in.

Additionally, you can replace your entire proedure with:
CREATE OR REPLACE PROCEDURE Wf_archive
  (num_days    IN NUMBER) AS
BEGIN
  INSERT INTO auditrecord
  SELECT *
  FROM   bphadmin.auditrecord
  WHERE  Trunc(eventtimestamp) < = (SYSDATE - num_days);

  COMMIT;
END wf_archive;
/ 
and it will work faster, and not give you the problem I suspect that you're having at the moment, where half of your data has transfered across and been committed.
Previous Topic: Cursor- no data found exception handaling
Next Topic: To capture the Number of rows in each partition of a table
Goto Forum:
  


Current Time: Sat Dec 10 20:37:52 CST 2016

Total time taken to generate the page: 0.03913 seconds