6 Replies Latest reply: Jan 24, 2013 9:37 AM by 864103 RSS

    delete issue

    864103
      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
          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
            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
              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
                Check this thread
                Re: Need Performance tuning in delete operation

                http://www.dba-oracle.com/t_delete_performance_speed.htm
                • 5. Re: delete issue
                  Nadeem M
                  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
                    thanks all for your support and time .