This discussion is archived
6 Replies Latest reply: Jan 24, 2013 7:37 AM by 864103 RSS

delete issue

864103 Newbie
Currently Being Moderated
Hi guys ,

oracle 11g.2 ASM with RAC under RHEL 5

i have 2 tables old and new i copt the data from DEC 2012 from new to old
i use nologging and append hint i copy 13,000,000 rows and it's take 30 min to copy the data ,
now i want to delete from the original table

delete from x
where callstarttime >= to_date ('12/01/2012','MM/DD/YYYY')      
and callstarttime < to_date ('01/01/2013','MM/DD/YYYY');
but when i run the script to show the running statement there is no rows :
SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops where totalwork !=0) 
WHERE "% Complete" != 100;
so after 10 min i canceled this delete , can you please tell me how to improve the performace of delete statement
can i use the nologging and append also as the insert (my tables there is no partitions)
or if you have idea of this issue , due to when i exec this delete i also check from OEM in TOP ACTIVITY
this delete not exists so after 10 min i canceled cuz i thought this delete will not completed.


thanks
  • 1. Re: delete issue
    sb92075 Guru
    Currently Being Moderated
    861100 wrote:
    Hi guys ,

    oracle 11g.2 ASM with RAC under RHEL 5

    i have 2 tables old and new i copt the data from DEC 2012 from new to old
    i use nologging and append hint i copy 13,000,000 rows and it's take 30 min to copy the data ,
    now i want to delete from the original table

    delete from x
    where callstarttime >= to_date ('12/01/2012','MM/DD/YYYY')      
    and callstarttime < to_date ('01/01/2013','MM/DD/YYYY');
    but when i run the script to show the running statement there is no rows :
    SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork
    ,round(sofar/totalwork*100,2) "% Complete"
    from v$session_longops where totalwork !=0) 
    WHERE "% Complete" != 100;
    so after 10 min i canceled this delete , can you please tell me how to improve the performace of delete statement
    can i use the nologging and append also as the insert (my tables there is no partitions)
    or if you have idea of this issue , due to when i exec this delete i also check from OEM in TOP ACTIVITY
    this delete not exists so after 10 min i canceled cuz i thought this delete will not completed.


    thanks
    in Oracle other sessions NEVER see uncommitted DML changes!
  • 2. Re: delete issue
    864103 Newbie
    Currently Being Moderated
    thanks for your quick answer , but i want after run this query(delete statement)
    to know current running transaction comleted in persent% but there is not (no rows) ???
  • 3. Re: delete issue
    karan Pro
    Currently Being Moderated
    Oracle PL/SQL has a bulk delete operator that often is faster than a standard SQL delete MOREOVER v$transaction has a new column in 10g USED_UBLK which tells Number of undo blocks used...
  • 4. Re: delete issue
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check this thread
    Re: Need Performance tuning in delete operation

    http://www.dba-oracle.com/t_delete_performance_speed.htm
  • 5. Re: delete issue
    881508 Journeyer
    Currently Being Moderated
    If possible drop all the indexes before the delete operation and then re-created the indexes.
    OR
    Create a new table with CTAS only with required set of rows, drop the old table and rename the new table to old.
  • 6. Re: delete issue
    864103 Newbie
    Currently Being Moderated
    thanks all for your support and time .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points