Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18

    Unanswered: delete taking long time

    I have to delete records from tables based on ids, i.e.,

    DELETE FROM vew_receivable_payable
    WHERE rcp_portfolio_id = p_portfolio_id;

    receivable_payable table has a non unique index on portfolio_id .
    There are around 5050 records for one portfolio in receivable_payable
    but the delete continues forever.
    Also there is a trigger on receivable_payable table , which fires for delets and updates.

    Any suggetions to improve the performance would be greatly appreciated
    lucy

  2. #2
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18
    DELETE STATEMENT, GOAL = ALL_ROWS Cost=2874 Cardinality=6901 Bytes=959239
    DELETE Object owner=ROR Object name=RECEIVABLE_PAYABLE
    TABLE ACCESS BY INDEX ROWID Object owner=ROR Object name=RECEIVABLE_PAYABLE Cost=2874 Cardinality=6901 Bytes=959239
    INDEX RANGE SCAN Object owner=ROR Object name=IDX_RCP_PORTFOLIO_ID Cost=28 Cardinality=6901
    lucy

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Enable SQL_TRACE & run results thru TKPROF and CUT & PASTE results back here.

    I suspect it is not really doing any deletes but waiting on a resource.

    Do a CUT& PASTE for the following SQL & results:

    Code:
    SELECT DECODE(REQUEST,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type
    from v$lock
    where (id1, id2, type) in (select id1, id2, type from v$lock where request>0)
    order by id1, request
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In your code
    DELETE FROM vew_receivable_payable
    WHERE rcp_portfolio_id = p_portfolio_id;

    Are you saying to delete every row where the column rcp_portfolio_id equals the column p_portfolio_id or is p_portfolio_id a program variable?

    Also does your table contain any LOBs? What is you on delete trigger doing? As anacedent indicated, a simple delete of 5050 rows on an indexed column should not take very long. Either a lock, very large rows, or your trigger is causing the problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18
    Yes. p_portfolio_id is a variable.
    The trigger is an audit trigger, which just records the deleted or updated rows into another audit_receivable_payable table.
    The table does not contain any LOB.

    The problem seems to be specific to one environment(DEV)

    The same query in another evironment (DEV2) took few secs.

    Need to investigate more.
    Thanks for help everyone!!
    lucy

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do you have up to date statistics on DEV?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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