Home » SQL & PL/SQL » SQL & PL/SQL » How To Log a PL/SQL Transaction...
icon9.gif  How To Log a PL/SQL Transaction... [message #249528] Wed, 04 July 2007 20:37 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hello all..

I have a simple procedure like this..

spool /home/oracle9/MIE/CDR-25JUN2007.lst


CREATE OR REPLACE PROCEDURE P_DEL28SEP2006 as

cursor s1 is SELECT rowid, t.* /*+ parallel(tel_calldtl partition(bl_period_0625),4) */ 
from tel_calldtl partition(bl_period_0625) t
where year_month_date= '200706'
and bill_period='25' and source in ('SMJ', 'SBH', 'SWK');
  
i number := 0;

begin
  for c1 in s1 loop
      delete from TEL_CALLDTL partition(bl_period_0625)
             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every 1000 records
      if i > 1000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/

exec P_DEL28SEP2006;

spool off


So, my question is how do I capture how many rows that have been deleted inside the spool file?

Thank you very much.
Re: How To Log a PL/SQL Transaction... [message #249530 is a reply to message #249528] Wed, 04 July 2007 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>So, my question is how do I capture how many rows that have been deleted inside the spool file?
Use another counter variable.

BTW - COMMITing inside a LOOP is very poor progamming practice & can (easily?) result in ORA-01555 error.
icon6.gif  Re: How To Log a PL/SQL Transaction... [message #249533 is a reply to message #249530] Wed, 04 July 2007 22:26 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Thu, 05 July 2007 10:19
>So, my question is how do I capture how many rows that have been deleted inside the spool file?
Use another counter variable.

BTW - COMMITing inside a LOOP is very poor progamming practice & can (easily?) result in ORA-01555 error.

I'm not sure about that... But all this while the script is running well deleting records from a very very huge table. I never get any error so far.

I get the script from http://rjh.keybit.net/oracle/plsloop.sql

It really made a vast improvement of deletion compared to my traditional delete.

Back to the topic, so how do I make another counter? I'm not really familiar with PL/SQL i.e programming. I'm only good in SQL.
Re: How To Log a PL/SQL Transaction... [message #249535 is a reply to message #249528] Wed, 04 July 2007 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> I never get any error so far.
The key words are "so far".
You don't have to believe me.
visit http://asktom.oracle.com
& do a keyword search on ORA-01555

>I get the script from http://rjh.keybit.net/oracle/plsloop.sql
Not everything on the 'Net is worth the electrons to store them

>It really made a vast improvement of deletion compared to my traditional delete.

It is not the COMMIT that make it go faster.
In faster it will go even faster without the COMMIT.
Comment out the COMMIT & do your own benchmarks.

>Back to the topic, so how do I make another counter?

j number :=0;


j := j +1;
icon6.gif  Re: How To Log a PL/SQL Transaction... [message #249537 is a reply to message #249535] Wed, 04 July 2007 22:53 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Thu, 05 July 2007 11:33
> I never get any error so far.
The key words are "so far".
You don't have to believe me.
visit http://asktom.oracle.com
& do a keyword search on ORA-01555

>I get the script from http://rjh.keybit.net/oracle/plsloop.sql
Not everything on the 'Net is worth the electrons to store them

>It really made a vast improvement of deletion compared to my traditional delete.

It is not the COMMIT that make it go faster.
In faster it will go even faster without the COMMIT.
Comment out the COMMIT & do your own benchmarks.

>Back to the topic, so how do I make another counter?

j number :=0;


j := j +1;

Thanks for your quick response.

Well, I did get the ORA-01555 Snapshot Too Old once or twice before.. ehehe. But at least since there is a commit inside that loop, some of the records have been successfully deleted. So, I just need to run the procedure once again to complete the rest of the deletion.

Thanks for your concern anyway. I'll take a look into that later.

But now, how do I print out the results of j??
icon10.gif  Re: How To Log a PL/SQL Transaction... [message #249540 is a reply to message #249528] Wed, 04 July 2007 23:21 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
OK OK I got it!!!


------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE P_DELOLD as

cursor s1 is SELECT rowid, t.* from loading_log_del t where bill_period=25;

i number := 0;
j number :=0;

begin
for c1 in s1 loop
delete from loading_log_del
where rowid = c1.rowid;

j := j +1;

end loop;

DBMS_OUTPUT.PUT_LINE (j ||' rows have been deleted');


end;



------------------------------------------------------------------------------------------------------------


Thanks anacedent!! Cool

[Updated on: Wed, 04 July 2007 23:44]

Report message to a moderator

Re: How To Log a PL/SQL Transaction... [message #249541 is a reply to message #249528] Wed, 04 July 2007 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Well, I did get the ORA-01555 Snapshot Too Old once or twice before
If the COMMIT is removed, the likelihood of the ORA-01555 will be reduced or eliminated.
If you don't want to take advice offered here, then do NOT ask for it!
Re: How To Log a PL/SQL Transaction... [message #249581 is a reply to message #249540] Thu, 05 July 2007 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sql> delete .....

will return how many rows were deleted.
And it will do so in less time.
Re: How To Log a PL/SQL Transaction... [message #249586 is a reply to message #249581] Thu, 05 July 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Surely but PL/SQL seems to be the latest fad: don't do in one SQL what you can do with a cursor loop in PL/SQL.

Regards
Michel

[Updated on: Thu, 05 July 2007 02:11]

Report message to a moderator

Re: How To Log a PL/SQL Transaction... [message #249592 is a reply to message #249586] Thu, 05 July 2007 02:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
[sarcasm]
actually, there is a very good reason for that.
It saves $$ on disk space (no rollback needed!!)
and with prices of disks nowadays...
[/sarcasm]
Re: How To Log a PL/SQL Transaction... [message #249597 is a reply to message #249581] Thu, 05 July 2007 02:31 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Frank wrote on Thu, 05 July 2007 15:00
sql> delete .....

will return how many rows were deleted.
And it will do so in less time.

Well of course!!

But you must be joking by saying that a normal SQL delete will do it in less time?? Mad

As far as I know, that's the least efficient way and I might not be able to delete at all since need to commit the deletion of a very huge sets of data.
Re: How To Log a PL/SQL Transaction... [message #249599 is a reply to message #249597] Thu, 05 July 2007 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As far as I know, that's the least efficient way and I might not be able to delete at all since need to commit the deletion of a very huge sets of data.

I think you have to read again the Concepts manual.
The "commit" execution duration is INDEPENDENT of how many data you have to commit.
So committing 1000 times instead of 1 will take 1000 times longer without speaking about the increase in load on the server and wait on other sessions you will imply.

1 SQL is (almost) ALWAYS the faster way to execute a task.

Regards
Michel
Re: How To Log a PL/SQL Transaction... [message #249604 is a reply to message #249599] Thu, 05 July 2007 02:50 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Michel Cadot wrote on Thu, 05 July 2007 15:37
Quote:
As far as I know, that's the least efficient way and I might not be able to delete at all since need to commit the deletion of a very huge sets of data.

I think you have to read again the Concepts manual.
The "commit" execution duration is INDEPENDENT of how many data you have to commit.
So committing 1000 times instead of 1 will take 1000 times longer without speaking about the increase in load on the server and wait on other sessions you will imply.

1 SQL is (almost) ALWAYS the faster way to execute a task.

Regards
Michel


You all had make me getting befuddled now.. Confused

In fact, I came accross that PL/SQL after having a terrible problem by deleting the conventional way. After googling the error, I was informed that a frequent commit will solve the problem.

And now?? Sad

I have some proof here..

1. http://www.juliemkalman.com/sql.html - point 46 & 47
2. http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_3.shtml:

How to perform long deletes - (commiting every n number of records)

by Jeff Hunter, Sr. Database Administrator

When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough. One way to solve this issue is to use the TRUNCATE command. TRUNCATE does not generate rollback and has the added benefit of rebuilding any indexes on the table. Another solution would be to use the anonymous PL/SQL block below.

DECLARE

       count  NUMBER  := 0;
       total  NUMBER  := 0;

       CURSOR del_record_cur IS
         SELECT rowid
         FROM   <OWNER>.<TABLE_NAME>
         WHERE  <YOUR_CRITERIA>

     BEGIN
       FOR rec IN del_record_cur LOOP
         DELETE FROM <OWNER>.<TABLE_NAME>
           WHERE rowid = rec.rowid;

         total := total + 1;
         count := count + 1;

         IF (count >= 1000) THEN
           COMMIT;
           count := 0;
         END IF;

       END LOOP;
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.');
     END;
     /
Re: How To Log a PL/SQL Transaction... [message #249606 is a reply to message #249604] Thu, 05 July 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
http://www.juliemkalman.com/sql.html - point 46 & 47

Is wrong.

Quote:
http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_3.shtml

I can't access the page.

Quote:
it is possible for the transaction to fail because of the rollback segment not being large enough.

Right but you have to size your rollback segment to the work you have to do.
Will you saw your piano because your car boot can't contain it or will you rent a truck to move?

Regards
Michel
Re: How To Log a PL/SQL Transaction... [message #249608 is a reply to message #249528] Thu, 05 July 2007 03:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Too bad you did not read the second link to its end.
Quote:
Soon after publishing this months DBA Tip, Geurts Maarten wrote in to indicate that the above PL/SQL block may not work where you may have either many processes running on the system or if the table is too large. If you do not have a rollback segment large enough, you may encounter the "snapshot too old" error. This is because the code block is modifing the snapshot the first cursor uses to get its rowids from.

You need to reopen the snapshot.

...

Again hitting the rollback segment size.
Moreover it does not say anything about efficiency.

How many rows do you suppose to delete?
How large is your UNDO tablespace?
icon9.gif  Re: How To Log a PL/SQL Transaction... [message #249611 is a reply to message #249528] Thu, 05 July 2007 03:35 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
OK guys...

I Surrender ~ Celine Dion

Anyway, I would like to inform that the script is running fine for me to delete about 40,000,000 records on average.

I really had a difficulty deleting them with the rollback segment error before I found that script.

Thanks to you all for your opinion. Smile
Re: How To Log a PL/SQL Transaction... [message #249612 is a reply to message #249604] Thu, 05 July 2007 03:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is a valid solution to the problem of not having enough Rollback/Undo space.
However, you've not mentioned that this has been a problem that you have encountered.

Let's have a nice simple example. We'll create a table, put a hundred thousand rows in it, and try deleting them in one go, and then in 1000 groups with one commit, and delete them in 1000 groups with a commit after each delete.

As the results below show, the single delete took 4.09 seconds, the 1000 deletes with one commit took 17.06 seconds, and the 1000 deletes with individual commits took 20.08 seconds.
SQL> drop table delete_Test;

Table dropped.

SQL> create table delete_test (id_col  number,grp number, col_1 varchar2(20));

Table created.

SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> delete delete_test;

100000 rows deleted.

Elapsed: 00:00:04.09
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> set timing off

SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.06
SQL> set timing off
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.08
SQL> set timing off
Re: How To Log a PL/SQL Transaction... [message #249615 is a reply to message #249612] Thu, 05 July 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good test but I think you are alone on the database, doesn't it?
Try the same thing with concurrent OLTP transactions and you will see moreover others waiting on "redo" latches and their process time increases (and yours too).

Regards
Michel
Re: How To Log a PL/SQL Transaction... [message #249617 is a reply to message #249611] Thu, 05 July 2007 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
aimy wrote on Thu, 05 July 2007 10:35
OK guys...

I Surrender ~ Celine Dion

Anyway, I would like to inform that the script is running fine for me to delete about 40,000,000 records on average.

I really had a difficulty deleting them with the rollback segment error before I found that script.

Thanks to you all for your opinion. Smile


How many rows are left in your table?
A better solution might be to recreate your table with only the remaining rows.
Re: How To Log a PL/SQL Transaction... [message #249648 is a reply to message #249604] Thu, 05 July 2007 07:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Do note that the one link that does work is from 9 years ago.
Re: How To Log a PL/SQL Transaction... [message #249662 is a reply to message #249648] Thu, 05 July 2007 08:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Will you saw your piano because your car boot can't contain it or will you rent a truck to move?

No, but If I have to move 40 tons of sand to the other side of a river, and only have a bridge that can support 10 tons, I might make 4 trips instead of building a bigger bridge. Wink

Happy Car Analogy Day to you all! Grin
Re: How To Log a PL/SQL Transaction... [message #249672 is a reply to message #249615] Thu, 05 July 2007 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's the results from a much more active database - I was still the only one using this table though.
This single delete performs the same, and the multiple deletes are much worse.

SQL> delete delete_test;

100000 rows deleted.

Elapsed: 00:00:04.02
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> 
SQL> set timing off
SQL> 
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> 
SQL> set timing on
SQL> 
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:51.02
SQL> 
SQL> set timing off
SQL> 
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> 
SQL> set timing on
SQL> 
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.03
SQL> 
SQL> set timing off
SQL> 
SQL> drop table delete_test;

Table dropped.
Re: How To Log a PL/SQL Transaction... [message #249742 is a reply to message #249528] Thu, 05 July 2007 13:49 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
No, but If I have to move 40 tons of sand to the other side of a river, and only have a bridge that can support 10 tons, I might make 4 trips instead of building a bigger bridge.

Nice analogy, however I would describe the posted approach as moving the sand in a hand-barrow Wink.

If aimy would read the second article to its very end (I did not posted it here), he would find a solution combining multi-row delete (not row-by-row as processed now) with intermediate commits. Definitely better than the posted one, not causing ORA-01555. I would just increase the commit size, 5000 is still too low.

Quote:
One way to solve this issue is to use the TRUNCATE command.

I would think over sub-partitioning on source column (if possible).
Previous Topic: to check user logged in to apps
Next Topic: Performance issue with an update statement
Goto Forum:
  


Current Time: Sat Dec 03 10:18:55 CST 2016

Total time taken to generate the page: 0.04379 seconds