This discussion is archived
1 2 3 4 Previous Next 53 Replies Latest reply: May 14, 2009 12:47 PM by Randolf Geist Go to original post RSS
  • 15. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    marcinp1 wrote:
    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.
    Marcin,
    Most of those latch waits are on the SELECT statement that I highilighted. It does 345M buffer gets, which is likely to be about 690M latch gets - and with 16 concurrent processes (I think that was the number the OP mentioned) doing what looks like an in-memory tablescan on a 50,000 block table, it's not surprising that there are a (relatively) few latch waits.

    Part of the 4,100 seconds of CPU will be the cost of scanning through each block looking for the one relevant row; but some of that CPU will be due to latch spnning as the 15 other processes compete with this one to get the necessary latch.

    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
  • 16. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi Jonathan,

    I have examined the plan,

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Operation | ORDER| ROWS | SIZE (KB) | Cost | Time Sec | CPU Cost | IO COST
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    SELECT STATEMENT - 3 0 0.000 2 0 0 0
    TABLE ACCESS BY INDEX ROWID 2 1 0.019 2 1 15523 2
    INDEX UNIQUE SCAN 1 1 0.000 2 1 8171 1
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    What can we conclude from this. Let me know if any more information is neede.

    Thanks in advance & Regards
    Satish

    Edited by: Satish Kumar Ballepu on May 11, 2009 9:47 PM
  • 17. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    We can conclude that you did not read and follow my instructions about how to supply the correct information in the correct format.


    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
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 18. Re: Oracle 10g performance degrades while concurrent inserts into a table
    MarcinP Oracle ACE
    Currently Being Moderated
    Jonathan Lewis wrote:
    marcinp1 wrote:
    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.
    Marcin,
    Most of those latch waits are on the SELECT statement that I highilighted. It does 345M buffer gets, which is likely to be about 690M latch gets - and with 16 concurrent processes (I think that was the number the OP mentioned) doing what looks like an in-memory tablescan on a 50,000 block table, it's not surprising that there are a (relatively) few latch waits.

    Part of the 4,100 seconds of CPU will be the cost of scanning through each block looking for the one relevant row; but some of that CPU will be due to latch spnning as the 15 other processes compete with this one to get the necessary latch.
    Jonathan,

    You are absolutely right - I mix up query and waits related to it - of course this waits are related to SELECT.
    I have similar problem when some parallel processes (about 8 to 10) had been updated a few rows (maybe about 100) in counter table
    and I was thinking about that when I wrote my answer.

    BTW
    I really appreciate your research about CBO and Oracle Performance. Amazing stuff.

    regards,
    Marcin Przepiorowski
  • 19. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    My Aplogies,

    This was not a log or AWR report it was taken from OEM gui.
  • 20. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi Team,


    As we noticed we are encountering with CPU starvation due to "SELECT STP ,BBB FROM YYYY WHERE LPTID = :B2 AND CCC = :B1" query, i thought of increasing no of CPUs', now I updated my hardware configuration from 12 CPU to 24 CPU and RAM from 48 GB to 96 GB, but still i have face the problem.

    At least now we should not get this CPU starvation problem right ? btw I have even tried partition the YYYY table using HASH algorithm on LPTID & CCC columns. I kept these partitions on a different table spaces (same HDD)

    But all these things still didn't solve my problem, Now iam running out ideas, Could you please guide me how can i solve this problem.

    Best Regards
    Satish KB
  • 21. Re: Oracle 10g performance degrades while concurrent inserts into a table
    26741 Oracle ACE
    Currently Being Moderated
    This is where your problem lies :
    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
    That is 50,142 block gets per fetch (344929100/6879). Each fetch is for 1 row but goes through 50,142. There is no physical I/O occurring -- all of this is within the Database Cache. That is why you have both
    a . High Latch Contention
    b. High CPU time (logical I/Os consume CPU)

    Adding CPUs wouldn't (and didn't) solve your problem.

    The query is doing a FullTableScan on table "YYYY" at each execution.

    Edited by: Hemant K Chitale on May 14, 2009 11:47 AM
    Deleted the reference to a Cartesian Merge. This seems to be a single Table.
  • 22. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Hi Hemant,

    I understand that , but how do i over come from this problem now, Is there any oracle configuration which i can do to solve this ?

    Regards
    satish
  • 23. Re: Oracle 10g performance degrades while concurrent inserts into a table
    26741 Oracle ACE
    Currently Being Moderated
    Is there any oracle configuration which i can do to solve this
    No there is no "oracle configuration" (anywhere in the world ) that can fix this for you.

    Ask yourself and your application development team :
    Why does Oracle have to read so many blocks at each execution ?
    Are there any Indexes on "YYYY" ?
    Should there be indexes on "YYYY" ?
    What indexes should be available ?
  • 24. Re: Oracle 10g performance degrades while concurrent inserts into a table
    700680 Newbie
    Currently Being Moderated
    Yes, i have index on this table YYYY with LPTID and CCC columns

    Infact i have even tried adding another index also on LPTID column with REVERSE KEY algorithm also.

    Any other options ?
  • 25. Re: Oracle 10g performance degrades while concurrent inserts into a table
    26741 Oracle ACE
    Currently Being Moderated
    The Explain Plan is based on either Literals or the "5%" rule for bind variables.

    What could be happening is :

    The actual execution is using an execution plan generated by peeking the bind variables on the first execution.

    You also have data skew.

    For certain predicates, the optimizer would prefer a FullTableScan (because of skew). Unfortunately, with bind variable peeking, the first execution is for such predicates and Oracle doesn't change the execution plan for subsequent executions.

    what you could do is :
    You could delete the histogram on the columns and Oracle would use the "5%" rule and an Index Range Scan, irrespective of the predicates.
  • 26. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant,

    The OP has not yet attenpted to answer my question properly, and until we see the actual run-time plan in all its detail there is little point in trying to guess why that query is doing what appears to be a full tablescan.

    We have been told that there is a unique index on the pair of columns (so any comments about bind variables, peeking, skew etc. do not apply - the query is 'select where unique key is constant' and should use the index irrespective the factors you suggest).

    We can only guess that there is a coercion problem going on that blocks the use of the index such as (a) a national language difference between client and server - requiring possibly a function-based index or (b) numeric bind variables and character columns - requiring a function-based index, or correction to the code.


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


    "The temptation to form premature theories upon insufficient data is the bane of our profession."
    Sherlock Holmes (Sir Arthur Conan Doyle) in "The Valley of Fear".
  • 27. Re: Oracle 10g performance degrades while concurrent inserts into a table
    26741 Oracle ACE
    Currently Being Moderated
    Aah ! I didn't notice that it is a Unique Scan.

    So that still does mean that RunTime behaviour is very different from what ExplainPlan supposedly shows.

    "We can only guess that there is a coercion problem going on that invalidates the index " would be right.
  • 28. Re: Oracle 10g performance degrades while concurrent inserts into a table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    Aah ! I didn't notice that it is a Unique Scan.

    So that still does mean that RunTime behaviour is very different from what ExplainPlan supposedly shows.

    "We can only guess that there is a coercion problem going on that invalidates the index " would be right.
    Although it was my original comment, it's not absolutely 100% true - as shown in [a quick demo on my blog|http://jonathanlewis.wordpress.com/2009/05/14/consistent-gets] - but it's highly likely to be true in most cases where you see this anomaly in the numbers.

    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
  • 29. Re: Oracle 10g performance degrades while concurrent inserts into a table
    26741 Oracle ACE
    Currently Being Moderated
    This particular session did only 6 "INSERT INTO YYYY VALUES (:B2 ,:B1 ,'0','0','0')" executions (only 5 columns, the last three having Zeros ?)

    Unless the OP has other (many other) sessions running INSERTs/UPDATEs on the two columns at a high rate concurrently with this session. (and could be Undo/ASSM issues with consistent gets ?)

    just speculating now ....

Legend

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