Home » SQL & PL/SQL » SQL & PL/SQL » Tune PLSQL block
Tune PLSQL block [message #393567] Mon, 23 March 2009 07:11 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Table bk_logmnr_contents consists of the 150000 rows.
And below block takes 90 minutes to execute the block.
What changes i can do so that below block will tune?
DECLARE
CURSOR c_logmnr
IS 
SELECT row_id,sql_redo
FROM bk_logmnr_contents;
text VARCHAR2(2000);
BEGIN 
FOR i IN c_logmnr
LOOP
BEGIN
IF UPPER(i.SQL_REDO) LIKE 'INSERT INTO "TEST".%' THEN
IF UPPER(i.SQL_REDO) LIKE 'INSERT INTO "TEST"."LEAD"%' THEN
SELECT REPLACE(REPLACE( sql_redo, ',NULL);', ');' ),'"BOUNCE_MSG_T","")','"BOUNCE_MSG_T")')
INTO text
FROM bk_logmnr_contents 
WHERE row_id =i.row_id
AND UPPER(SQL_REDO) LIKE 'INSERT INTO "TEST"."LEAD"%';
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
ELSE

SELECT sql_redo
INTO text
FROM bk_logmnr_contents 
WHERE row_id =i.row_id
AND UPPER(SQL_REDO) LIKE 'INSERT INTO "TEST".%';
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
END IF;
END IF;

IF UPPER(i.SQL_REDO) LIKE 'DELETE FROM "TEST".%' THEN
IF UPPER(i.SQL_REDO) LIKE 'DELETE FROM "TEST"."FORMSVAL"%' THEN
SELECT REPLACE( SUBSTR(sql_redo,1,INSTR(sql_REDO,'ROWID')-6)||';', 'and "SYSCREATED" =', 'AND TO_DATE("SYSCREATED", ''DD-MON-RR'') =' ) sql_redo 
INTO text
FROM bk_logmnr_contents 
WHERE row_id =i.row_id
AND UPPER(i.SQL_REDO) LIKE 'DELETE FROM "TEST".%';
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
ELSE 


SELECT SUBSTR(sql_redo,1,INSTR(sql_REDO,'ROWID')-6)||';' sql_redo 
INTO text
FROM bk_logmnr_contents 
WHERE row_id =i.row_id
AND UPPER(SQL_REDO) LIKE 'DELETE FROM "TEST".%';
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
END IF;
END IF;

--DATABASE
IF UPPER(i.sql_REDO) LIKE 'UPDATE "TEST"."DATABASE"%' THEN
SELECT a.sql_redo|| b.ID||';'
INTO text
FROM DATABASE b,(SELECT SUBSTR(sql_redo,1,INSTR(sql_REDO,'ROWID')-1) || ' id =' sql_redo , row_id
FROM bk_logmnr_contents WHERE row_id =i.row_id) a
WHERE UPPER(sql_REDO) LIKE 'UPDATE "TEST"."DATABASE"%'
AND a.row_id=b.ROWID AND b.ROWID = i.row_id;
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
END IF;

--LEAD 
IF UPPER(i.sql_REDO) LIKE 'UPDATE "TEST"."LEAD"%' THEN
SELECT a.sql_redo|| b.ID||';'
INTO text
FROM lead b,(SELECT SUBSTR(sql_redo,1,INSTR(sql_REDO,'ROWID')-1) || ' id =' sql_redo , row_id
FROM bk_logmnr_contents WHERE row_id =i.row_id) a
WHERE UPPER(sql_REDO) LIKE 'UPDATE "TEST"."LEAD"%'
AND a.row_id=b.ROWID AND b.ROWID = i.row_id;
INSERT INTO bk_logmnr_contents_ins(sql_redo) VALUES(text);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END; 

Thanks,
Re: Tune PLSQL block [message #393571 is a reply to message #393567] Mon, 23 March 2009 07:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I wonder if there is a slower way to do this..
Select everything multiple times row by row, whereas this whole beast can be rewritten as an INSERT .. SELECT..
Re: Tune PLSQL block [message #393583 is a reply to message #393571] Mon, 23 March 2009 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I wonder if there is a slower way to do this..

./fa/449/0/

Re: Tune PLSQL block [message #393587 is a reply to message #393567] Mon, 23 March 2009 08:05 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks all for replies.

I created index on the base table and user insert...select clause and it runs within a minute.
Thanks,
Previous Topic: show row level records in single column in hirarchy
Next Topic: Help needed for SQL Query (merged)
Goto Forum:
  


Current Time: Tue Dec 06 10:32:59 CST 2016

Total time taken to generate the page: 0.14068 seconds