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
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 ....