This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 10:38 PM by user575115 RSS

Delete issue

user575115 Newbie
Currently Being Moderated
Hi,
For rollback script purpose i need to delete data from tables which are inserted.And i written like below and it's taking 30 min for 10000 rows.It's huge for me.I suppose to be delete more than 120000 rows.Why this delete taking this much time no idea.could u please advice.I have tried 3 deifferent ways like below but no use.
DELETE
FROM ibis.cw_upload_queue_item cuqi
WHERE  EXISTS
  ( SELECT 1 FROM ibis.e_coursework ec
  WHERE ec.file_name=cuqi.unique_file_name
 
  );

and 

DELETE
FROM ibis.cw_upload_queue_item cuqi
WHERE cuqi.unique_file_name IN(
  ( SELECT ec.file_name FROM ibis.e_coursework ec)
 
  );

SET serveroutput ON
DECLARE
type t2
IS
  TABLE OF cw_upload_queue_item.unique_file_name%type
INDEX BY PLS_INTEGER;
  TYPE2 T2;
  --cursor c1 is select DEPTNO,DNAME from dept;
BEGIN
  --open c1;
  --loop
  SELECT file_name BULK COLLECT INTO TYPE2 FROM e_coursework
WHERE file_name is not null;
  --EXIT WHEN type2.COUNT = 0;
  forall i IN type2.first..type2.last
  DELETE FROM ibis.cw_upload_queue_item WHERE unique_file_name=type2(i);
  
  dbms_output.put_line(sql%rowcount);
END;
table structure

  CREATE TABLE "IBIS"."CW_UPLOAD_QUEUE_ITEM" 
   (     "UNIQUE_FILE_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
     "PORTFOLIO_TEMPLATE_ELEMENT_ID" NUMBER, 
     "YEAR" NUMBER(4,0), 
     "MONTH" VARCHAR2(9 BYTE), 
     "SCHOOL_CODE" VARCHAR2(6 CHAR), 
     "PERSON_CODE_ADDED" VARCHAR2(6 BYTE), 
     "PERSON_CODE_SUBMITTED" VARCHAR2(6 BYTE), 
     "STATUS_ID" VARCHAR2(4 BYTE), 
     "ADDED_TO_QUEUE_TIMESTAMP" DATE, 
     "SUBMITTED_FOR_PROCESSING_TS" DATE, 
     "UPLOAD_STARTED_TIMESTAMP" DATE, 
     "UPLOAD_COMPLETE_TIMESTAMP" DATE, 
     "FAILURE_STATUS_ID" VARCHAR2(4 BYTE), 
     "FAILURE_TIMESTAMP" DATE, 
     "FILE_NAME" VARCHAR2(300 CHAR), 
     "FILE_EXTENSION" VARCHAR2(4 BYTE), 
     "FILE_SIZE" NUMBER, 
     "PAPER_CODE" VARCHAR2(25 BYTE), 
     "SELF_TAUGHT" VARCHAR2(1 BYTE), 
     "SHARED" CHAR(1 BYTE) DEFAULT 'N', 
      CONSTRAINT "PK_CW_UPLOAD_QUEUE_ITEM" PRIMARY KEY ("UNIQUE_FILE_NAME")
  USING INDEX PCTFREE 0 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IB_INDEXTWO"  ENABLE, 
      CONSTRAINT "FK_CW_UPLOAD_QUEUE_STATUS" FOREIGN KEY ("STATUS_ID")
       REFERENCES "IBIS"."CW_STATUS_LOOKUP" ("STATUS_ID") ENABLE, 
      CONSTRAINT "FK_UPLOAD_QUEUE_FAIL_STAT" FOREIGN KEY ("FAILURE_STATUS_ID")
       REFERENCES "IBIS"."CW_STATUS_LOOKUP" ("STATUS_ID") ENABLE, 
      CONSTRAINT "FK_QUEUE_ITEM_PERSON_ADD" FOREIGN KEY ("PERSON_CODE_ADDED")
       REFERENCES "IBIS"."PERSON" ("PERSON_CODE") ENABLE, 
      CONSTRAINT "FK_QUEUE_ITEM_PERSON_SUB" FOREIGN KEY ("PERSON_CODE_SUBMITTED")
       REFERENCES "IBIS"."PERSON" ("PERSON_CODE") ENABLE, 
      CONSTRAINT "FK_UPLOAD_QUEUE_TEMP_ELEMENT" FOREIGN KEY ("PORTFOLIO_TEMPLATE_ELEMENT_ID")
       REFERENCES "IBIS"."CW_PORTFOLIO_TEMPLATE_ELEMENT" ("PORTFOLIO_TEMPLATE_ELEMENT_ID") ENABLE, 
      CONSTRAINT "FK_UPLOAD_QUEUE_ITEM_FILE_TYPE" FOREIGN KEY ("FILE_EXTENSION")
       REFERENCES "IBIS"."CW_FILE_TYPE" ("FILE_EXTENSION") ENABLE
   ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IB_DATATWO" ;
 

  CREATE INDEX "IBIS"."CW_UPLOAD_Q_ITEM_IDX" ON "IBIS"."CW_UPLOAD_QUEUE_ITEM" ("YEAR", "MONTH", "SCHOOL_CODE", "PAPER_CODE") 
  PCTFREE 0 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 2 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IB_INDEXTWO" ;
 
  CREATE UNIQUE INDEX "IBIS"."PK_CW_UPLOAD_QUEUE_ITEM" ON "IBIS"."CW_UPLOAD_QUEUE_ITEM" ("UNIQUE_FILE_NAME") 
  PCTFREE 0 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IB_INDEXTWO" ;
 
  • 1. Re: Delete issue
    983451 Newbie
    Currently Being Moderated
    First question even before checking your content..

    Are there any triggers on your table : ibis.cw_upload_queue_item ??

    Cheers,
    Manik.
  • 2. Re: Delete issue
    user503635 Explorer
    Currently Being Moderated
    Hi,

    Is your table referenced by other tables, especially those of very big size.
  • 3. Re: Delete issue
    Purvesh K Guru
    Currently Being Moderated
    user575115 wrote:
    Hi,
    For rollback script purpose i need to delete data from tables which are inserted.And i written like below and it's taking 30 min for 10000 rows.It's huge for me.I suppose to be delete more than 120000 rows.Why this delete taking this much time no idea.could u please advice.I have tried 3 deifferent ways like below but no use.
    >
    DELETE
    FROM ibis.cw_upload_queue_item cuqi
    WHERE EXISTS
    ( SELECT 1 FROM ibis.e_coursework ec
    WHERE ec.file_name=cuqi.unique_file_name

    );
    This seems to be the fastest of all the approaches. But I still cannot believe that all the three take same time, 30 mins for 10K rows. Also, I would recommend you to scrap the PL/SQL approach as it is the slowest amongst these.

    To answer other question of why Delete is taking so much of time, you will have to provide us with details in greater lengths.

    1. Table and Index Structure of E_COURSEWORK.
    2. Record Counts of Both tables.
    3.
    select num_rows, blocks, avg_row_len, last_analyzed
      from user_table
    where table_name in ('e_coursework', 'cw_upload_queue_item')
    4. Explain Plan of Both Delete the statements.

    Please read {message:id=3292438} and post the details.

    Please do not forget to post your Oracle version
    select * from v$version.
  • 4. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi ,
    There is no triggers on this table and all reference tables are very small tables and person tables is not big table it is also mediam size table.

    Edited by: user575115 on Jan 10, 2013 12:51 AM
  • 5. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    E_COURSEWORK structure
    
    
    CREATE TABLE E_COURSEWORK
    (
      ID NUMBER(*, 0) NOT NULL,
      E_ASSESSMENT_ID NUMBER(*, 0) NOT NULL,
      TITLE VARCHAR2(500 CHAR) NOT NULL,
      NUM_WORDS NUMBER(*, 0) DEFAULT 0 NOT NULL,
      WORK_AUTHENTICATED CHAR(1 CHAR) DEFAULT 'N' NOT NULL,
      FILE_PATH VARCHAR2(200 CHAR),
      FILE_NAME VARCHAR2(90 CHAR),
      FILE_EXTENSION VARCHAR2(5 CHAR),
      UPLOAD_DATE DATE NOT NULL,
      MD5_CHECKSUM VARCHAR2(90 CHAR)
    , CONSTRAINT PK_COURSEWORK PRIMARY KEY
      (
        ID
      )
      ENABLE
    )
      TABLESPACE "IB_DATATWO"
      LOGGING 
      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE
      (
        INITIAL 64K
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        BUFFER_POOL DEFAULT
      )
    ;
    
    ALTER TABLE E_COURSEWORK
    ADD CONSTRAINT UQ_E_COURSEWORK_FILE_NAME UNIQUE
    (
      FILE_NAME
    )
     ENABLE
    ;
    
    ALTER TABLE E_COURSEWORK
    ADD CONSTRAINT FK_COURSEWORK_E_ASSESSMENT_CA FOREIGN KEY
    (
      E_ASSESSMENT_ID
    )
    REFERENCES E_ASSESSMENT_CANDS
    (
    E_ASSESSMENT_ID
    )
    ON DELETE CASCADE ENABLE
    ;
    explain plan for sql exists
    line 1: SQLPLUS Command Skipped: set linesize 130
    line 2: SQLPLUS Command Skipped: set pagesize 0
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
    Plan hash value: 1872412360                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                 
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
    | Id  | Operation              | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                 
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
    |   0 | DELETE STATEMENT       |                           |  7665 |   838K|       |  2480   (2)| 00:00:22 |                                                                                                                                                                                                 
    |   1 |  DELETE                | CW_UPLOAD_QUEUE_ITEM      |       |       |       |            |          |                                                                                                                                                                                                 
    |*  2 |   HASH JOIN RIGHT SEMI |                           |  7665 |   838K|  4312K|  2480   (2)| 00:00:22 |                                                                                                                                                                                                 
    |*  3 |    INDEX FAST FULL SCAN| UQ_E_COURSEWORK_FILE_NAME |   126K|  2832K|       |   142   (3)| 00:00:02 |                                                                                                                                                                                                 
    |   4 |    TABLE ACCESS FULL   | CW_UPLOAD_QUEUE_ITEM      |   229K|    19M|       |  1405   (2)| 00:00:13 |                                                                                                                                                                                                 
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                 
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                 
       2 - access("EC"."FILE_NAME"="CUQI"."UNIQUE_FILE_NAME")                                                                                                                                                                                                                                                    
       3 - filter("EC"."FILE_NAME" IS NOT NULL)                                                                                                                                                                                                                                                                  
    
    17 rows selected
    select count(*) from cw_upload_queue_item;
    229984
    
    select count(*) from e_coursework;
    
    140724
    
    and below sql is rerunning results  with in sec's.these rows only i'm trying to delete.
    
    select count(*)
    FROM ibis.cw_upload_queue_item cuqi
    WHERE  EXISTS
      ( SELECT 1 FROM ibis.e_coursework ec
      WHERE ec.file_name=cuqi.unique_file_name
     
      );
    125702
     block info:
    
    NUM_ROWS               BLOCKS                 AVG_ROW_LEN            LAST_ANALYZED             
    ---------------------- ---------------------- ---------------------- ------------------------- 
    229984                 7300                   197                    09-JAN-13                 
    140528                 4442                   217                    14-SEP-12           
    
    
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
    PL/SQL Release 10.2.0.2.0 - Production
    "CORE     10.2.0.2.0     Production"
    TNS for Solaris: Version 10.2.0.2.0 - Production
    NLSRTL Version 10.2.0.2.0 - Production      
    
    2 rows selected
    Edited by: user575115 on Jan 10, 2013 1:04 AM

    Edited by: user575115 on Jan 10, 2013 1:07 AM
  • 6. Re: Delete issue
    user503635 Explorer
    Currently Being Moderated
    Your cw_upload_queue_items has a pk on unique_file_name, any other tables reference this table on unique_file_name?
  • 7. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi ,
    Yes..3 tables are having FK with references of this pk column and these tables are deleted before this table.like below
    CW_UPLOAD_QUEUE_ITEM_CAND
    CW_UPLOAD_QUEUE_ITEM_META
    CW_UPLOAD_LOG
    all are executed success except last one
    DELETE
    FROM ibis.cw_pfolio_tplate_meta cptm
    WHERE EXISTS
      (SELECT 1
      FROM ibis.cw_candidate_portfolio ccp,
        ibis.e_assessment_cands eac
      WHERE cptm.candidate_portfolio_id =ccp.candidate_portfolio_id
      AND ccp.year                      =eac.year
      AND ccp.month                     =eac.month
      AND ccp.paper_code                =eac.paper_code
      AND ccp.candidate                 =eac.candidate
      AND ccp.e_coursework              =eac.e_coursework
      );
    DELETE
    FROM ibis.cw_cand_portfolio_file ccpf
    WHERE EXISTS
      (SELECT 1
      FROM ibis.cw_file cf,
        ibis.e_coursework ec
      WHERE ccpf.file_id = cf.file_id
      AND ec.file_name   =cf.unique_file_name
      );
    DELETE
    FROM ibis.cw_upload_queue_item_meta cuim
    WHERE EXISTS 
      ( SELECT 1 FROM ibis.e_coursework ec
      WHERE cuim.unique_file_name =ec.file_name);
      
    DELETE
    FROM ibis.cw_upload_queue_item_cand cuqc
    WHERE  EXISTS
      ( SELECT 1 FROM ibis.e_coursework ec
      WHERE ec.file_name=cuqc.unique_file_name
      );
        
    DELETE
    FROM ibis.cw_file cf
    WHERE  EXISTS
      ( SELECT 1 FROM ibis.e_coursework ec
      WHERE ec.file_name=cf.unique_file_name
      );
    DELETE
    FROM ibis.cw_upload_queue_item cuqi
    WHERE  EXISTS
      ( SELECT 1 FROM ibis.e_coursework ec
      WHERE ec.file_name=cuqi.unique_file_name
      );
    COMMIT
    /
  • 8. Re: Delete issue
    Purvesh K Guru
    Currently Being Moderated
    user575115 wrote:
    Hi,
    explain plan for sql exists
    line 1: SQLPLUS Command Skipped: set linesize 130
    line 2: SQLPLUS Command Skipped: set pagesize 0
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
    Plan hash value: 1872412360                                                                                                                                                                                                                                                                                  
    
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
    | Id  | Operation              | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                 
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
    |   0 | DELETE STATEMENT       |                           |  7665 |   838K|       |  2480   (2)| 00:00:22 |                                                                                                                                                                                                 
    |   1 |  DELETE                | CW_UPLOAD_QUEUE_ITEM      |       |       |       |            |          |                                                                                                                                                                                                 
    |*  2 |   HASH JOIN RIGHT SEMI |                           |  7665 |   838K|  4312K|  2480   (2)| 00:00:22 |                                                                                                                                                                                                 
    |*  3 |    INDEX FAST FULL SCAN| UQ_E_COURSEWORK_FILE_NAME |   126K|  2832K|       |   142   (3)| 00:00:02 |                                                                                                                                                                                                 
    |   4 |    TABLE ACCESS FULL   | CW_UPLOAD_QUEUE_ITEM      |   229K|    19M|       |  1405   (2)| 00:00:13 |                                                                                                                                                                                                 
    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
    
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
    
    2 - access("EC"."FILE_NAME"="CUQI"."UNIQUE_FILE_NAME")                                                                                                                                                                                                                                                    
    3 - filter("EC"."FILE_NAME" IS NOT NULL)                                                                                                                                                                                                                                                                  
    
    17 rows selected
    The Optimizer calculates to complete the Delete statement in 22 seconds, also with number of rows to be deleted differs significantly (7665 as estimated to 125K), I am suspecting you are taking 30 minutes because of some other session, acquiring locks on few or many rows to be deleted. And thus delaying the process.

    Can you check if you are requiring approx. 30 mins everytime you run the delete statement?
    Also, you can check if there is any blocking session, preventing you to delete the rows, using this:
    SELECT 
       l1.sid || ' is blocking ' || l2.sid blocking_sessions
    FROM 
       v$lock l1, v$lock l2
    WHERE
       l1.block = 1 AND
       l2.request > 0 AND
       l1.id1 = l2.id1 AND
       l1.id2 = l2.id2;
    >
    select count(*) from cw_upload_queue_item;
    229984

    select count(*) from e_coursework;

    140724

    and below sql is rerunning results with in sec's.these rows only i'm trying to delete.

    select count(*)
    FROM ibis.cw_upload_queue_item cuqi
    WHERE EXISTS
    ( SELECT 1 FROM ibis.e_coursework ec
    WHERE ec.file_name=cuqi.unique_file_name

    );
    125702
    block info:

    NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
    ---------------------- ---------------------- ---------------------- -------------------------
    229984 7300 197 09-JAN-13
    140528 4442 217 14-SEP-12


    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
    PL/SQL Release 10.2.0.2.0 - Production
    "CORE     10.2.0.2.0     Production"
    TNS for Solaris: Version 10.2.0.2.0 - Production
    NLSRTL Version 10.2.0.2.0 - Production

    2 rows selected

    Statistics seems to be fairly close to actual estimates. Hence, I would first investigate the Blocking session situation. Can you try and post the results please?
  • 9. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    Sorry to saying i 'm not able to run this query due to grants issue on these tables(err:table or view does exists).
    I had thought some time many users are using hence it's getting delayed,But last from 5 days i'm trying to figure out the issue(i was ran couple of times for different number of rows).And i had doubt on blocks then i was disabled all constraints then truncated the table and reloaded the data then gathered statistics (on 4th jan) even no use....finally i'm posted thread.
  • 10. Re: Delete issue
    Purvesh K Guru
    Currently Being Moderated
    user575115 wrote:
    Hi,
    Sorry to saying i 'm not able to run this query due to grants issue on these tables(err:table or view does exists).
    I had thought some time many users are using hence it's getting delayed,But last from 5 days i'm trying to figure out the issue(i was ran couple of times for different number of rows).
    I find the statements, contradictory. You do not have grants to DML on tables, but you still ran it a couple of times. Strange?!
    And i had doubt on blocks then i was disabled all constraints then truncated the table and reloaded the data then gathered statistics (on 4th jan) even no use....finally i'm posted thread.
    How does a Blocking session situation be negated by disabling the constraints, truncating and re-populating the data?

    Did you mean Oracle Blocks (where rows are stored)? If yes, then your other tables might also face similar situation. I do not eliminate this situation unless everything else is done, because Row Chaining and Row migration might induce some delays in statements, but rarely are found to be the culprits.

    Hence, My take will still remain on the blocking session, unless you are confident enough to eliminate it.
  • 11. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    I meant privileges are not having on V$lock tables not forDML statements.I ran now also the delete statement and it's completed after 211 minuses(3 hr 40 mins) for 120000 rows.
  • 12. Re: Delete issue
    Purvesh K Guru
    Currently Being Moderated
    Alright. Can you check this?
    SELECT
       s.blocking_session, 
       s.sid, 
       s.serial#, 
       s.seconds_in_wait
    FROM
       v$session s
    WHERE
       blocking_session IS NOT NULL;
    You can even consult with your DBA with the problem and they might help you with the Blocking sessions, in case you are not able to query the v$sessions view.
  • 13. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    I able to run this sql statement and currently it's returning 0 rows.
    Is this select statement need to be execute during delete statement?
  • 14. Re: Delete issue
    AlbertoFaenza Expert
    Currently Being Moderated
    user575115 wrote:
    Hi ,
    Yes..3 tables are having FK with references of this pk column and these tables are deleted before this table.like below
    CW_UPLOAD_QUEUE_ITEM_CAND
    CW_UPLOAD_QUEUE_ITEM_META
    CW_UPLOAD_LOG
    Just run the folllowing query to see if you are missing any referential integrity constraint:
    SELECT l.owner, l.table_name, l.constraint_name, r.owner remote_owner
         , r.table_name remote_table
      FROM all_constraints l, user_constraints r
     WHERE     l.constraint_type = 'R'
           AND l.r_constraint_name = r.constraint_name
           AND l.owner = r.owner
           AND r.table_name = 'CW_UPLOAD_QUEUE_ITEM'
           AND r.owner = 'IBIS';
    Regards.
    Al
1 2 Previous Next

Legend

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