Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002

    Unanswered: DELETE statement performance problem


    We have a table in PROD
    CREATE TABLE address
    address_id NUMBER(12) NOT NULL,
    street_line1 VARCHAR2(40),
    street_line2 VARCHAR2(40),
    city VARCHAR2(40),
    state VARCHAR2(2),
    phone_area VARCHAR2(3),
    phone_num VARCHAR2(7),
    phone_ext VARCHAR2(6),
    zip_code VARCHAR2(9)

    with Primary Key on address_id.
    There are no any other Keys (unique or foreign) on this table.
    No any triggers also.

    table has about 2 500 000 records.
    we use sequence to generate address_id for this table. Now the next sequense value is about 4 500 000.

    there are a lot of DELETE and INSERT statements running (online txns) on this table - about 10 000 of each per day.

    We got a DELETE performance problem for this table.

    DELETE *
    FROM address
    WHERE address_id = 12345;

    it takes about 5.5 sec to complete it (before we rebuilt index it was 10-12 sec per DELETE statement)
    it's too much for us.
    we can afford only 0.5 sec per statement.

    What I cannot understand is that the same INSERT/UPDATE/SELECT statements complete during 0.3 - 0.5 sec.

    The next we going to do is to partition this table (range partition on address_id).
    But first I'd like to find out why it is only DELETE course us such problems and INSERT/UPDATE/SELECY work just fine?
    Does anybody have any ideas/suggestions?

    yes, almost forgot, we have Oracle 8.1.7

    Thanks a lot,

  2. #2
    Join Date
    Nov 2001
    Provided Answers: 1
    Write a procedure which collects the
    session statistics (redo generated ,roll back ghenerated) before
    the start of delete and then run delete and then collect the statistics
    after that .
    Also run a trace to find if there are any waits.

    Post the results.

Posting Permissions

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