Home » SQL & PL/SQL » SQL & PL/SQL » Tuning of the Procedure
Tuning of the Procedure [message #226247] Fri, 23 March 2007 01:32 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
CREATE OR REPLACE PROCEDURE Backup_Data
AS
v_inst VARCHAR2(200);
TYPE v_tab IS VARRAY(250) OF VARCHAR2(100);
v_tabnm v_tab;
BEGIN
SELECT tabnm BULK COLLECT INTO v_tabnm FROM BACKUP_TABLE;

FOR i IN 1..v_tabnm.COUNT LOOP
v_inst := 'Truncate table BACKUP.'|| SUBSTR(v_tabnm(i),5);
EXECUTE IMMEDIATE v_inst;
COMMIT;

v_inst := 'Insert into BACKUP.' || SUBSTR(v_tabnm(i),5) || ' select * from ' || v_tabnm(i);
EXECUTE IMMEDIATE v_inst;
COMMIT;
END LOOP;
COMMIT;
END Backup_Data;
/

This above mention procedure is taking table names from a table of current schema say "ROOT" and copy all the tables data into another schema "BACKUP", the table backup_table contains the table name containing around 250 tables, on an average every table have over 50k of records, while running this procedure it is taking 1 hour approx., do any one have any method to tune this procedure ..

I've tried this from BACKUP Schema but it is giving internal error or giving error in line number 10 and not compiled ..

CREATE OR REPLACE PROCEDURE Backup_Data
AS
v_inst VARCHAR2(200);
TYPE v_tab IS VARRAY(250) OF VARCHAR2(100);
v_tabnm v_tab;
BEGIN
SELECT tabnm BULK COLLECT INTO v_tabnm FROM BACKUP_TABLE;
FORALL i IN v_tabnm.FIRST..v_tabnm.LAST
EXECUTE IMMEDIATE 'Delete from :1' using SUBSTR(v_tabnm(i),5);
COMMIT;

FORALL i IN v_tabnm.FIRST..v_tabnm.LAST
EXECUTE IMMEDIATE 'Insert into :1 select * from :2' using SUBSTR(v_tabnm(i),5), v_tabnm(i);
COMMIT;
END Backup_Data;
/

[Updated on: Fri, 23 March 2007 01:41]

Report message to a moderator

Re: Tuning of the Procedure [message #226253 is a reply to message #226247] Fri, 23 March 2007 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
exp
imp
should take about 2 minutes.
Re: Tuning of the Procedure [message #226256 is a reply to message #226253] Fri, 23 March 2007 01:44 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Dear Frank,

Thanks for the quick reply.

This procedure we have to give to client and it will be an automated process calling from some other programs. I'm agreed that Export - Import will take lesser time but because we don't want to hardcoded the password in export-import script and some others issues are also thr to give that script to client.

Can we tune this procedure?

Thanks in Advance
Amit
Re: Tuning of the Procedure [message #226257 is a reply to message #226256] Fri, 23 March 2007 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Some options:
- use the append hint on insert
- remove the commits. (why commit after a truncate??)
Re: Tuning of the Procedure [message #226261 is a reply to message #226257] Fri, 23 March 2007 01:56 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Dear Frank,

Thanx for your suggestions, I'll incorporate it into my procedure.

Can you tell me why the procedure with the dynamic sql is not compiling, is thr any error ?

Thanx
Re: Tuning of the Procedure [message #226264 is a reply to message #226261] Fri, 23 March 2007 02:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
FORALL is NOT a loop construction, it is part of a command:
FORALL..INSERT or FORALL..UPDATE
You cannot use objectnames as bind variables. (would fail at runtime)
Re: Tuning of the Procedure [message #227454 is a reply to message #226247] Wed, 28 March 2007 05:21 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try using CREATE as SELECT instead of copying the data:

CREATE OR REPLACE PROCEDURE Backup_Data AS
  v_inst VARCHAR2(200);
  TYPE v_tab IS VARRAY(250) OF VARCHAR2(100);
  v_tabnm v_tab; 
BEGIN
  SELECT tabnm BULK COLLECT INTO v_tabnm FROM BACKUP_TABLE;

  FOR i IN 1..v_tabnm.COUNT LOOP
     v_inst := 'DROP table BACKUP.'|| SUBSTR(v_tabnm(i),5);
     EXECUTE IMMEDIATE v_inst;
-- COMMIT; N0 NEED TO COMMIT AFTER DDL COMMAND

     v_inst := 'CREATE TABLE BACKUP.' || SUBSTR(v_tabnm(i),5)||
       ' AS select * from ' || v_tabnm(i) || ' NOLOGGING';
     EXECUTE IMMEDIATE v_inst;
-- COMMIT; N0 NEED TO COMMIT AFTER DDL COMMAND
  END LOOP;
-- COMMIT;  N0 NEED TO COMMIT AFTER DDL COMMAND
END Backup_Data;


HTH.
Michael
Previous Topic: TYPICAL QUERY..Experts ??
Next Topic: Problem with analytical functions
Goto Forum:
  


Current Time: Sun Dec 04 10:53:17 CST 2016

Total time taken to generate the page: 0.07527 seconds