Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Absurd CURRENT MODE count?

    Hello everybody,

    I'm having a hard time in interpreting a TRACE file.
    I get very slow times on a delete query from a ProC procedure; every 1000 deletes a commit is issued, and it takes some 3 minutes to process those 1000 rows!! That's some 50 times slower than it should.

    Examining the trace file, I noticed this:

    delete from event
    where
    ev_cod_event=:b0


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 15883 1610.89 2674.97 52057 64118 690769896 15883
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 15884 1610.89 2674.97 52057 64118 690769896 15883


    Notice the "CURRENT" column value. That's beyond any expected measure. How in the world can I have 700M CURRENT MODE value, while processing 1000 rows? I examined the rest of the code but it seems correct.
    I guess the ridiculous current mode value might be a clue that to expert eyes, might point to an obvious answer, which I just can't see for myself.

    Thank to all the community for the support, ask away if you need more info about the environment, the program etc.

    Carl

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALWAYS
    Post Operating System (OS) name & version for DB server system.(uname -a)
    Post results of
    SELECT * from v$version

    post EXPLAIN PLAN

    More often than not Oracle accurately reports reality whether you believe that or not.
    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.

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Sorry, my fault for not reading the stickies.
    uname:
    AIX sx0bzn5a 3 5 00C3A2BB4C00

    Oracle Version:

    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE 9.2.0.8.0 Production
    TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production


    Plan of the DELETE statement:


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 DELETE STATEMENT GOAL: CHOOSE
    0 DELETE OF 'EVENTO'


    And, here's the plan of the main cursor that drives the DELETE.


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    15885 SORT (ORDER BY)
    2252613 FILTER
    2252613 HASH JOIN
    36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TPEVENTO'
    3388599 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EVENTO'
    0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IX_MOVIMENTI_EVENTO'
    (NON-UNIQUE)

    ************************************************** ******************************

    0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_EVENTO' (UNIQUE)



    I might add that the DELETE started to take long times after working fine for a year. No major changes in the indexes, that are quite looked after; no change in partitioning, everything else runs fine, etc. The only clue I've got is that CURRENT MODE value skyrocketing.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ANALYZE both table & indexes.
    It can't hurt & might help.

    It would be nice if you re-read STICKY post to learn how to use <code tags> to nicely format column data
    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.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I see we don't have the whole picture.
    Looks like you are looping one row at a time and then deleting said row.
    My advice:
    1. Give us the whole picture. ie: here is my procedure which does X and ...
    2. You are deleting 1 row at a time 15,000 times. That is the slow way.
    3. You complain about your delete taking 3 minutes to delete 10,000 rows when in actuality you are deleting 1 row in less than 1 second (.16 seconds): 2674 secs / 15883 deletes
    4. Either delete all in one statement instead of 15,000 individual deletes or put an index on ev_cod_event and you should see better results.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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