This discussion is archived
1 2 3 4 Previous Next 53 Replies Latest reply: May 14, 2009 12:47 PM by Randolf Geist RSS

Oracle 10g performance degrades while concurrent inserts into a table

700680 Newbie
Currently Being Moderated
Hello Team,

I am trying to insert into single table via multiple threads, Some of these threads perform reasonably well but some will take really longer time, As the time goes on performance drastically degrades (even down by 500 to 600 times). With AWR report i see that there quite huge number of buffer gets there. I am not sure how can i reduce those. If i ran on a single thread this operation is consistent.

I tried quite a few options like
1. Increasing SGA Memory
2. Moving redo logs to another disk drive.
3. Trying it on a empty table
4. Trying it on a table which has huge data (4 Million rows)
5. I have even tried partitioning the table with HASH algoritm

Note: Each thread i am pupming equal amount of data (let say 25K rows).

Can any body suggest me a clue what could be the culprit here.


Thanks in Advance
Satish Kumar Ballepu
  • 1. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Centinul Guru
    Currently Being Moderated
    It would be helpful if you posted the wait events you are seeing in the AWR report, as well as the table structure, index structure, Oracle version, etc.

    It's really hard to troubleshoot without a lot of information.

    It sounds like you are trying to implement a 'custom' parallel insert mechanism, and depending on the structure of your data you could be getting wait events like 'read by other session' or something like that.
  • 2. Re: Oracle 10g performance degrades while concurrent inserts into a table
    MarcinP Oracle ACE
    Currently Being Moderated
    Hi,

    What are a storage parameters ?
    Free lists ?
    Free lists groups ?
    To you have any triggers executed during inserts ?
    What is a tablespace definition ?
    Oracle version ?
    number of CPU, RAM ?
    SGA ?

    We need more data to help you.


    regards,
    Marcin Przepiorowski
    http://oracleprof.blogspot.com/
  • 3. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi ,

    1. Free Lists & Free List groups are set to default ( ie., 1)
    2. Yes I do have trigger on this insertion, Which internall calls a proc which inturn inserts a row one tableand updates a row in one another table.
    3. Oracle Version is 10.2.0
    4. 12 CPU, RAM - 48 GB (System OS: Solaris 5.10 version)
    5. SGA 800 M ( i have even tried increasing upto 10 Gig)
    6. Table space is occupied by only 55%

    Please let me know if any more details needed.

    Regards
    Satish Kumar Ballepu
  • 4. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Justin Cave Oracle ACE
    Currently Being Moderated
    I don't see where you have responded to Centinul's questions
    It would be helpful if you posted the wait events you are seeing in the AWR report, as well as the table structure, index structure, Oracle version, etc.
    Justin
  • 5. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi,

    Please find the complete details ... of a session, using TKPROF, My query is to insert into A1, it internally calls a trigger which calls an updates on XXX & BBB table in this process it also as couple of select queries from YYYY & ZZZZ tabless Pls find them in detail below

    UPDATE XXX SET A= A+1, STPT = :B3 WHERE LPID = :B2 AND B= :B1

    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6880 12.10 11.47 0 239071 7404 6880
    Fetch 0 0.00 0.00 0 0 0 0
    ---------------------------------------------------------------------------------------
    total 6880 12.10 11.47 0 239071 7404 6880

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    buffer busy waits 9 0.00 0.00
    latch: cache buffers chains 25 0.00 0.00
    latch: undo global data 2 0.00 0.00
    ********************************************************************************

    SELECT COUNT(0) FROM CHANGE_ORDER WHERE OBJECTID = :B1


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6879 2.35 2.36 0 0 0 0
    Fetch 6879 0.47 0.44 0 6879 0 6879
    ---------------------------------------------------------------------------------------
    total 13758 2.82 2.80 0 6879 0 6879

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)
    ********************************************************************************

    SELECT STP ,BBB FROM YYYY WHERE LPTID = :B2 AND CCC = :B1


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6879 2.59 2.57 0 0 0 0
    Fetch 6879 4194.02 4104.82 0 344929100 0 6879
    ---------------------------------------------------------------------------------------
    total 13758 4196.61 4107.40 0 344929100 0 6879

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    latch: cache buffers chains 36558 0.01 3.52
    buffer busy waits 22 0.00 0.00
    latch: undo global data 15 0.01 0.02
    ********************************************************************************

    SELECT TO_CHAR(CURRENT_DATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6880 0.72 0.77 0 0 0 0
    Fetch 6880 0.35 0.30 0 0 0 6880
    ---------------------------------------------------------------------------------------
    total 13760 1.07 1.07 0 0 0 6880

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)
    ********************************************************************************

    SELECT COUNT(0) FROM ZZZZ WHERE OBJECTID = :B3 AND CHANGEORDERID = :B2 AND :B1 != 8 AND :B1 != 7 AND :B1 != 9


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6880 5.34 5.26 0 0 0 0
    Fetch 6880 0.20 0.14 0 0 0 6880
    ---------------------------------------------------------------------------------------
    total 13760 5.54 5.41 0 0 0 6880

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)
    ********************************************************************************

    UPDATE BBB SET BD_A = BD_T -
    BD_U - BD_R + :B7 - :B6 , DB_A =
    DB_T - DB_U - DB_R + :B5 - :B4 ,
    BD_U = BD_U - :B7 + :B6 , DB_U =
    DB_U - :B5 + :B4 , CONS_A = ROUND(CONS_A +
    :B3 , 0), CONS_U = ROUND(CONS_U - :B3 , 0),
    MODIFIEDDATE = :B2
    WHERE
    (OBJECTID=:B1 AND OBJECTID != ' ')


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6880 12.59 12.06 0 20640 7398 6880
    Fetch 0 0.00 0.00 0 0 0 0
    ---------------------------------------------------------------------------------------
    total 6880 12.59 12.06 0 20640 7398 6880

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)
    ********************************************************************************

    INSERT INTO A1
    VALUES
    (:B1,:B2,:B3,:B4,:B5,:B6,:B7,:B8,:B9,' ',' ',:B10,:B11,:B12,:B13,:B14,:B15,
    :B16,' ',' ',:B17,' ',:B18,:B19,:B20,:B21,:B22,:B23,:B24,:B25,:B26,:B27,
    :B28,:B29,:B30,:B31,:B32,:B33,:B34,:B35,:B36,:B37,:B38,:B39,:B40,:B41,:B42,
    :B43,:B44,:B45,:B46,:B47,:B48,:B49,:B50,:B51,:B52,:B53,:B54,:B55,:B56,:B57,
    :B58,:B59,:B60,:B61,:B62,:B63,:B64,:B65,:B66,:B67,:B68,:B69,:B70,:B71,:B72,
    :B73,:B74,:B75,:B76,:B77,' ',:B78,:B79,' ')


    call count cpu elapsed disk query current rows
    -----------------------------------------------------------------------------------------------------------
    Parse 3439 0.25 0.17 0 0 0 0
    Execute 3440 57.56 56.67 0 208525 129932 3440
    Fetch 0 0.00 0.00 0 0 0 0
    -----------------------------------------------------------------------------------------------------------
    total 6879 57.81 56.84 0 208525 129932 3440

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 58

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 6879 0.00 0.03
    SQL*Net message from client 6879 0.01 33.51
    SQL*Net more data from client 3439 0.00 0.27
    buffer busy waits 2 0.00 0.00
    ********************************************************************************

    INSERT INTO A2
    VALUES
    (:B1,:B2,:B3,:B4,:B5,:B6,:B7,:B8,:B9,:B10,:B11,:B12,:B13,:B14,:B15,:B16,:B17,
    :B18,:B19,:B20,:B21,:B22,:B23,:B24,:B25,:B26,:B27,' ',:B28,:B29,:B30,:B31,
    :B32,:B33,:B34,:B35,:B36,:B37,:B38,:B39,:B40,:B41,:B42,:B43,:B44,:B45,:B46,
    :B47,:B48,:B49,:B50,:B51,:B52,:B53,:B54,:B55,:B56,:B57,:B58,:B59,:B60,:B61,
    :B62,:B63,:B64,:B65,:B66,:B67,:B68,:B69,:B70)


    call count cpu elapsed disk query current rows
    ---------------------------------------------------------------------------------------
    Parse 3440 0.33 0.33 0 0 0 0
    Execute 3440 31.98 31.59 0 3887 43880 3440
    Fetch 0 0.00 0.00 0 0 0 0
    ---------------------------------------------------------------------------------------
    total 6880 32.31 31.92 0 3887 43880 3440

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 58

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 6880 0.00 0.01
    SQL*Net message from client 6880 11.43 44.20
    SQL*Net more data from client 3440 0.01 0.17
    log file sync 2 0.00 0.00
    ********************************************************************************

    COMMIT


    call count cpu elapsed disk query current rows
    ------------------------------------------------------------------------------------
    Parse 5 0.00 0.00 0 0 0 0
    Execute 5 0.00 0.00 0 0 0 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------------------------------------------------------------------------------------
    total 10 0.00 0.00 0 0 0 0

    Misses in library cache during parse: 0
    Parsing user id: 58 (recursive depth: 1)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    log file sync 8 0.00 0.00
    ********************************************************************************

    SELECT COUNT(0) FROM ABXY WHERE LPTID = :B2 AND A= :B1


    call count cpu elapsed disk query current rows
    ------------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 13758 5.59 5.43 0 0 0 0
    Fetch 13758 6.97 6.79 0 477922 0 13758
    ------------------------------------------------------------------------------------
    total 27516 12.56 12.22 0 477922 0 13758

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 58 (recursive depth: 1)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    latch: cache buffers chains 13 0.00 0.00
    buffer busy waits 1 0.00 0.00
    ********************************************************************************

    select file#
    from
    file$ where ts#=:1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2507 0.04 0.03 0 0 0 0
    Execute 2507 0.80 3.93 0 0 0 0
    Fetch 116 0.01 0.00 0 144 0 100
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 5130 0.85 3.97 0 144 0 100

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Parsing user id: SYS (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS FULL FILE$ (cr=4 pr=0 pw=0 time=139 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    log file sync 3076 0.01 3.30
    ********************************************************************************

    INSERT INTO YYYY VALUES (:B2 ,:B1 ,'0','0','0')


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6 0.00 0.00 0 0 24 6
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6 0.00 0.00 0 0 24 6

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

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 6883 0.60 0.51 0 0 0 0
    Execute 6885 89.59 88.31 0 212414 173814 6881
    Fetch 11 0.00 0.00 0 99 0 13
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 13779 90.19 88.83 0 212513 173814 6894

    Misses in library cache during parse: 5
    Misses in library cache during execute: 6

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 13778 0.00 0.05
    SQL*Net message from client 13778 11.43 79.65
    SQL*Net more data from client 6879 0.01 0.44
    log file sync 3 0.00 0.00
    buffer busy waits 2 0.00 0.00


    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 3467 0.05 0.06 0 0 0 0
    Execute 58509 42.52 45.45 0 259743 14834 13774
    Fetch 41396 4202.02 4112.51 0 345414054 0 41379
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 103372 4244.59 4158.02 0 345673797 14834 55153

    Misses in library cache during parse: 5
    Misses in library cache during execute: 5

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    latch: cache buffers chains 36596 0.01 3.52
    log file sync 4354 0.01 4.58
    buffer busy waits 32 0.00 0.00
    latch: undo global data 17 0.01 0.02

    6897 user SQL statements in session.
    28 internal SQL statements in session.
    6925 SQL statements in session.
    ********************************************************************************
    Trace file compatibility: 10.01.00

    Thanks in advance
    Satish Kumar Ballepu

    Edited by: user11150696 on May 11, 2009 4:46 AM
  • 6. Re: Oracle 10g performance degrades while concurrent inserts into a table
    mbobak Oracle ACE
    Currently Being Moderated
    Are you using ASSM tablespaces?
    How many concurrent insert processes are running?

    -Mark
  • 7. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi,

    I am not using ASSM, I am running 16 threads in parallel. In fact for 8 or 10 threads also this problem exists.

    Regards
    Satish
  • 8. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Almost all your time in this trace seems to be spent on:

    SELECT STP ,BBB FROM YYYY WHERE LPTID = :B2 AND CCC = :B1 
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute   6879      2.59       2.57          0          0          0           0
    Fetch     6879   4194.02    4104.82          0  344929100          0        6879
    And it's all CPU time - so your 'slowing down with concurrent usage' may simply be CPU starvation.
    At first sight this looks like a tablescan for a query that should be using indexed access.

    Check for (a) a suitable index and (b) suitable use of that index.
    After you've fixed this, you may need to worry about the usual concurrency issues (ASSM/Freelists/Buffer busy waits).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 9. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi ,

    I already have an unique index on YYYY table for LPTID and CCC columns.

    Regards
    Satish
  • 10. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Centinul Guru
    Currently Being Moderated
    You satisfied the first question in Jonathan's request. How about the second? Have you generated an explain plan for this query to see if it is actually using the index?
  • 11. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Can you please guide me how do i do that, I am not aware of how to generate explain plan for that query.
  • 12. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user11150696 wrote:
    Hi ,

    I already have an unique index on YYYY table for LPTID and CCC columns.
    Fetch 6879 4194.02 4104.82 0 344929100 0 6879
    So now you need to look at (b)
    +"Check for (a) a suitable index and (b) suitable use of that index."+

    You're not using it if it takes 344,929,100 buffer visits to visit a single row by unique key 6,8979 times.

    Since it's unique, this suggests a type-mismatch issue: perhaps number/varchar, perhaps varchar/Nvarchar.
    Search v$sql for the text, and then query v$sql_plan on the same sql_id and child number - check especially the filter_predicates to see what's really happening on that query. (See http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ for more information about using dbms_xplan to see what's in v$sql_plan).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "For every expert there is an equal and opposite expert"
    Arthur C. Clarke
  • 13. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user11150696 wrote:
    Can you please guide me how do i do that, I am not aware of how to generate explain plan for that query.
    Since you have the trace file already (and I don't mean the tkprof output), you could do the following:

    Read the trace file to find the statement you're interested id - the line above it will be a +"PARSING"+ line, and will include a reference to the statement hash_value look like +'hv=3838377475845'+.

    Use the hash_value to query v$sql to get the sql_id and child_number;

    Use the sql_id and child number in a call to dbms_xplan.display_cursor:
    PARSING IN CURSOR #7 len=68 dep=0 uid=55 oct=3 lid=55 tim=448839952404 *hv=3413100263* ad='2f6ede48'
    select ... etc.  (the statement I want the plan for)
    
    
    SQL> select sql_id , child_number from v$sql where hash_value = *3413100263*;
    
    SQL_ID        CHILD_NUMBER
    ------------- ------------
    053tyaz5qzjr7            0
    
    
    SQL> select * from table(dbms_xplan.display_cursor(*'053tyaz5qzjr7'*,*0*));
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------
    ----------------------------------------
    SQL_ID  053tyaz5qzjr7, child number 0
    -------------------------------------
    select  /*+ use_concat */  small_vc from  t1 where  n1 = 1 or n2 = 2
    
    Plan hash value: 82564388
    
    ----------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
    ----------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |       |       |     4 |
    |   1 |  CONCATENATION               |       |       |       |       |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    10 |   190 |     2 |
    |*  3 |    INDEX RANGE SCAN          | T1_N2 |    10 |       |     1 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |    10 |   190 |     2 |
    |*  5 |    INDEX RANGE SCAN          | T1_N1 |    10 |       |     1 |
    ----------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("N2"=2)
       4 - filter(LNNVL("N2"=2))
       5 - access("N1"=1)
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "For every expert there is an equal and opposite expert."
    Arthur C. Clarke

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 14. Re: Oracle 10g performance degrades while concurrent inserts into a table
    MarcinP Oracle ACE
    Currently Being Moderated
    Jonathan Lewis wrote:
    Almost all your time in this trace seems to be spent on:

    SELECT STP ,BBB FROM YYYY WHERE LPTID = :B2 AND CCC = :B1 
    
    call count cpu elapsed disk query current rows
    --------------------------------------------------------------------------------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 6879 2.59 2.57 0 0 0 0
    Fetch 6879 4194.02 4104.82 0 344929100 0 6879
    Hi,

    Be aware that this query

    UPDATE XXX SET A= A+1, STPT = :B3 WHERE LPID = :B2 AND B= :B1

    can be a bottleneck too if you have more then one session loaded rows with this same LPID and B.
    or rows with different LPID and B are in that same block.
    See that wait:

    latch: cache buffers chains 36558 0.01 3.52

    It's mean that there is a contention to access a buffer chain with DB blocks.
    This wait appear when you start a parallel load.

    But before that you should solve problem described by Jonathan.


    regards,
    Marcin Przepiorowski
    http://oracleprof.blogspot.com/

    Edited by: marcinp1 on May 11, 2009 10:00 PM
1 2 3 4 Previous Next

Legend

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