Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17

    Unanswered: Delete query hangs

    I'm trying to use a delete query to delete a few thousand rows from an Oracle 9i database, but the query hangs. I can delete a few hundred rows, but more than that and the query just sits there working. How can I fix this? Could it be a logging/redo/rollback issue? If so, please give me some guidance...and speak slowly, because I'm new to Oracle.
    -Keith

  2. #2
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    Update...I was just able to delete 2000 records from the table, and it took more than 20 minutes to do so. Here's the query:

    delete from flights where flight_id > 188000

    "flight_id" is the primary key. There are no delete triggers, only an insert trigger.
    -Keith

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    what database version?

    Couple solitions/possibilities:

    1. add a hint
    delete /*+ index (flights flight_pk)*/ flights where flight_id > 188000

    2. Alter the table to nologging beforehand
    alter table flights nologging
    delete ...
    commit
    alter table flights logging


    3. Disable constraints and indexes (all except PK) before you do the delete.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    It's version 9.2.0.1.0.

    I don't know much about hints, but here's the EXPLAIN PLAN for the delete query:

    Code:
    OBJECT_NAME                    OPERATION                      OPTIONS
    ------------------------------ ------------------------------ ----------------------------
                                   DELETE STATEMENT
    FLIGHTS                        DELETE
    FLIGHTS                        TABLE ACCESS                   BY INDEX ROWID
    FLIGHTS_PK                     INDEX                          RANGE SCAN
    I altered the table (and the index) to nologging, but this didn't help. It took 4 minutes to delete 1000 records (each record containing 21 fields, none of them large). There are now about 180,000 records total.

    I disabled all constraints except PK, still with no benefit.

    I just realized I did manage do delete an equivalent amount of data from a different table a few days ago. It was not a problem like this table is. But I can't figure out what the difference might be.

    Let me comment, though, that even though none of this has helped with this problem, these are great tips that I will add to my list for future troubleshooting. I didn't even know you could disable constraints until you mentioned it.
    -Keith

  5. #5
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    I just noticed my SYSTEM tablespace is 99% full. Is that normal?
    -Keith

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I would look at what objects are in the SYSTEM tablespace.
    Only SYS/SYSTEM objects should be in there.

    I also suggest doing a sql_trace on your delete and then tkprof it.
    then post that output.

    Explain plan is not telling you what it REALLY doing, just what it thinks it SHOULD do.

    is flight_id numeric?
    I suggest using quotes:
    flight_id > '188000'

    use the hint like this:

    PHP Code:
    delete /*+ index_desc (flights FLIGHTS_PK) */ flights where flight_id '188000'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    I hope this is what you're asking for...these are the owners in dba_segments with tablespace_name of 'system':

    OWNER
    ------------------------------
    MDSYS
    ORDSYS
    OUTLN
    SCOTT
    SYS
    SYSTEM
    WMSYS

    Here's the trace on the smaller delete query (the one that deletes the last 1000 rows):

    Code:
    Execution Plan
    ----------------------------------------------------------
       0      DELETE STATEMENT Optimizer=CHOOSE
              (Cost=187 Card=10772 Bytes=140036)
       1    0   DELETE OF 'FLIGHTS'
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'FLIGHTS'
                  (Cost=187 Card=10772 Bytes=140036)
       3    2       INDEX (RANGE SCAN) OF 'FLIGHTS_PK' (UNIQUE)
                    (Cost=22 Card=10772)
    
    Statistics
    ----------------------------------------------------------
           3003  recursive calls
          30881  db block gets
        1402040  consistent gets
        1394022  physical reads
         596276  redo size
            629  bytes sent via SQL*Net to client
            540  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
           1000  rows processed
    flight_id is numeric; I tried the query with and without quotes with the same result. I also used the hint, with the same result.

    Thanks for the clarification on explain plan vs sql trace...that's useful to know.
    -Keith

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    other possibilities:

    insmaller terms, all we want to do is find the rows greater than 188000

    this might be possible using exists clause

    PHP Code:
    delete flights f
    where exists 
    (select null from flights 
    where f
    .flight_id flight_id and flight_id 188000); 
    try that with sql_trace and show me the output please
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    That one took the same amount of time as the other queries. Here's the result:

    Code:
    Execution Plan
    ----------------------------------------------------------
       0      DELETE STATEMENT Optimizer=CHOOSE
              (Cost=204 Card=11743 Bytes=211374)
       1    0   DELETE OF 'FLIGHTS'
       2    1     NESTED LOOPS (SEMI) (Cost=204 Card=11743 Bytes=211374)
       3    2       TABLE ACCESS (BY INDEX ROWID) OF 'FLIGHTS'
                    (Cost=204 Card=11743 Bytes=152659)
       4    3         INDEX (RANGE SCAN) OF 'FLIGHTS_PK' (UNIQUE)
                      (Cost=24 Card=11743)
       5    2       INDEX (UNIQUE SCAN) OF 'FLIGHTS_PK' (UNIQUE)
    
    Statistics
    ----------------------------------------------------------
           3003  recursive calls
          33099  db block gets
        1403042  consistent gets
        1394021  physical reads
         596736  redo size
            629  bytes sent via SQL*Net to client
            605  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
           1000  rows processed
    -Keith

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Delete query hangs

    Have you considered increasing the size of your rollback segment?

  11. #11
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17

    Re: Delete query hangs

    Originally posted by carloa
    Have you considered increasing the size of your rollback segment?
    My undo_management is set to AUTO...doesn't that mean Oracle handles rollback segments automatically? If so, should I set it to manual and manage it myself?
    -Keith

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    9i UNDO takes care of itself.


    did you disable all FK constraints from other tables pointing to
    the flights table?

    SELECT * FROM user_cons_columns
    WHERE
    column_name = 'FLIGHT_ID'

    most of the time the overhead has to do with constraints and indexes
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    Success!

    You called it, Duck...it was a foreign key in another table pointing to flight_id causing the problem. Disabling that allowed me to delete the remaining rows (66,094) in 7 seconds.

    Now I have to figure out how to fix that foreign key. But I don't know how to give you enough details on the key to isolate the problem. It looks pretty straightforward to me...there's not even a cascade on it.
    -Keith

  14. #14
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    Could the problem be that I don't have an index on the foreign key in the table linking to FLIGHTS? That is, the OFFLOADS table has a field called FLIGHT_ID which is a foreign key to the field of the same name in FLIGHTS.
    -Keith

  15. #15
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    that is absolutely a problem. When u don't have an index on a foreign key column, everytime something happens with one or more fk values in the master table, oracle issues a full table scan on the referencing table, resulting in a lot of fulle table scans (one per each master record).
    Lesson to be learned:
    Alway index foreign key columns ! Not indexing them can result i really strange behaviour regarding performance, not only in delete statement, but in update statements as well.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •