14 Replies Latest reply: Sep 4, 2013 12:32 PM by jihuyao RSS

    Query Is taking almost 100% cpu

    PVM

      Hi All,

       

      The below query is taking almost 100% cpu.

       

      SELECT TO_CHAR(max(distinct(LAST_UPDATE_TIME)), :"SYS_B_0") as COMPLETED_DATE FROM pc_order WHERE line_of_business = :1 AND ( order_status = :2 OR order_status = :3 )AND ( order_number = :4 OR order_type||order_number = :5 )ORDER BY last_update_time DESC
      


      Database Version is 11.2.0.3

      4 node RAC

       

      LINE_OF_BUSINESS having index

      ORDER_NUMBER, ORDER_TYPE, ORDER_SUPP_VERSION   --- Composite index

      ORDER_NUMBER --- having Index

      LAST_UPDATE_TIME --- having index

       

      Statistics are up to date

       

      Total records --- 4798887

       

      Please let me know your suggestion on this.

       

      Thanks


        • 1. Re: Query Is taking almost 100% cpu
          sb92075

          SELECT To_char(Max(DISTINCT( last_update_time )), :"SYS_B_0") AS COMPLETED_DATE

          FROM   pc_order

          WHERE  line_of_business = :1

                 AND ( order_status = :2

                        OR order_status = :3 )

                 AND ( order_number = :4

                        OR order_type

                           ||order_number = :5 )

          ORDER  BY last_update_time DESC;



          post EXPLAIN PLAN for SQL above

          • 2. Re: Query Is taking almost 100% cpu
            PVM

            Apologize not able to format properly

             

            Plan hash value: 3323521584
            
            --------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
            --------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT REMOTE| | 1 | 29 | 11618 (2)| 00:02:20 | |
            | 1 | SORT AGGREGATE | | 1 | 29 | | | |
            |* 2 | TABLE ACCESS FULL | PC_ORDER | 240 | 6960 | 11618 (2)| 00:02:20 | PVCRA~ |
            --------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            Note
            -----
              - fully remote statement
            
            
            
             2 - filter(("ORDER_STATUS"=:2 OR "ORDER_STATUS"=:3) AND 
              "LINE_OF_BUSINESS"=TO_NUMBER(:1) AND ("ORDER_TYPE"||"ORDER_NUMBER"=:5 OR 
              "ORDER_NUMBER"=:4))
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            • 3. Re: Query Is taking almost 100% cpu
              sb92075

              Manikandan wrote:

               

              Apologize not able to format properly

               

              1. Plan hash value: 3323521584 
              2. -------------------------------------------------------------------------------------------- 
              3. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst | 
              4. -------------------------------------------------------------------------------------------- 
              5. | 0 | SELECT STATEMENT REMOTE| | 1 | 29 | 11618 (2)| 00:02:20 | | 
              6. | 1 | SORT AGGREGATE | | 1 | 29 | | | | 
              7. |* 2 | TABLE ACCESS FULL | PC_ORDER | 240 | 6960 | 11618 (2)| 00:02:20 | PVCRA~ | 
              8. -------------------------------------------------------------------------------------------- 
              9. Predicate Information (identified by operation id): 
              10. --------------------------------------------------- 
              11. Note 
              12. ----- 
              13.   - fully remote statement 
              14.  
              15.  
              16.  
              17. 2 - filter(("ORDER_STATUS"=:2 OR "ORDER_STATUS"=:3) AND  
              18.   "LINE_OF_BUSINESS"=TO_NUMBER(:1) AND ("ORDER_TYPE"||"ORDER_NUMBER"=:5 OR  
              19.   "ORDER_NUMBER"=:4)) 

               

              Please explain why the contradiction between row count above & below exists

               

              >Statistics are up to date

              >Total records --- 4798887

              • 4. Re: Query Is taking almost 100% cpu
                PVM
                SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical'));
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------
                Plan hash value: 3323521584
                
                -------------------------------------------------------------------------------
                | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                -------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |          |     1 |    29 | 11618   (2)| 00:02:20 |
                |   1 |  SORT AGGREGATE    |          |     1 |    29 |            |          |
                |*  2 |   TABLE ACCESS FULL| PC_ORDER |   240 |  6960 | 11618   (2)| 00:02:20 |
                -------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------
                ---------------------------------------------------
                
                   2 - filter(("ORDER_STATUS"=:2 OR "ORDER_STATUS"=:3) AND
                              "LINE_OF_BUSINESS"=TO_NUMBER(:1) AND ("ORDER_TYPE"||"ORDER_NUMBER"
                =:5
                
                              OR "ORDER_NUMBER"=:4))
                
                16 rows selected.
                
                SQL> select count(*) from pc_order;
                
                  COUNT(*)
                ----------
                   4798957
                

                 

                Its the same.

                 

                Thanks

                • 5. Re: Query Is taking almost 100% cpu
                  sb92075

                  Manikandan wrote:

                   

                  1. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical')); 
                  2. PLAN_TABLE_OUTPUT 
                  3. -------------------------------------------------------------------------------- 
                  4. Plan hash value: 3323521584 
                  5. ------------------------------------------------------------------------------- 
                  6. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
                  7. ------------------------------------------------------------------------------- 
                  8. |   0 | SELECT STATEMENT   |          |     1 |    29 | 11618   (2)| 00:02:20 | 
                  9. |   1 |  SORT AGGREGATE    |          |     1 |    29 |            |          | 
                  10. |*  2 |   TABLE ACCESS FULL| PC_ORDER |   240 |  6960 | 11618   (2)| 00:02:20 | 
                  11. ------------------------------------------------------------------------------- 
                  12. Predicate Information (identified by operation id): 
                  13. PLAN_TABLE_OUTPUT 
                  14. -------------------------------------------------------------------------------- 
                  15. --------------------------------------------------- 
                  16.    2 - filter(("ORDER_STATUS"=:2 OR "ORDER_STATUS"=:3) AND 
                  17.               "LINE_OF_BUSINESS"=TO_NUMBER(:1) AND ("ORDER_TYPE"||"ORDER_NUMBER" 
                  18. =:5 
                  19.               OR "ORDER_NUMBER"=:4)) 
                  20. 16 rows selected. 
                  21. SQL> select count(*) from pc_order; 
                  22.   COUNT(*) 
                  23. ---------- 
                  24.    4798957 

                   

                  Its the same.

                   

                  Thanks

                  same? same as what?

                  line #2 in EXPLAIN PLAN show rows=240

                  so why such a large difference between 240 & 4,798,957 values?

                   

                  how long does the query take to complete?

                  how many rows are returned by the query?

                  • 6. Re: Query Is taking almost 100% cpu
                    JohnWatson

                    sb92075 wrote:

                     

                    Manikandan wrote:

                     

                    1. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical')); 
                    2. PLAN_TABLE_OUTPUT 
                    3. -------------------------------------------------------------------------------- 
                    4. Plan hash value: 3323521584 
                    5. ------------------------------------------------------------------------------- 
                    6. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
                    7. ------------------------------------------------------------------------------- 
                    8. |   0 | SELECT STATEMENT   |          |     1 |    29 | 11618   (2)| 00:02:20 | 
                    9. |   1 |  SORT AGGREGATE    |          |     1 |    29 |            |          | 
                    10. |*  2 |   TABLE ACCESS FULL| PC_ORDER |   240 |  6960 | 11618   (2)| 00:02:20 | 
                    11. ------------------------------------------------------------------------------- 
                    12. Predicate Information (identified by operation id): 
                    13. PLAN_TABLE_OUTPUT 
                    14. -------------------------------------------------------------------------------- 
                    15. --------------------------------------------------- 
                    16.    2 - filter(("ORDER_STATUS"=:2 OR "ORDER_STATUS"=:3) AND 
                    17.               "LINE_OF_BUSINESS"=TO_NUMBER(:1) AND ("ORDER_TYPE"||"ORDER_NUMBER" 
                    18. =:5 
                    19.               OR "ORDER_NUMBER"=:4)) 
                    20. 16 rows selected. 
                    21. SQL> select count(*) from pc_order; 
                    22.   COUNT(*) 
                    23. ---------- 
                    24.    4798957 

                     

                    Its the same.

                     

                    Thanks

                    same? same as what?

                    line #2 in EXPLAIN PLAN show rows=240

                    so why such a large difference between 240 & 4,798,957 values?

                     

                    how long does the query take to complete?

                    how many rows are returned by the query?

                    The 240 rows is the estimate of rows returned after applying the filter.

                    But since the query being explain uses binds, the estimates may bear no relation to reality. I find it better to replace the binds with literal values and then explain.

                    • 7. Re: Query Is taking almost 100% cpu
                      Nikolay Savvinov

                      Hi,

                       

                      the query is written in a way that prevents the optimizer from using indexes. The application design raises serious questions if it requires you to concatenate columns before comparing them to a predicate. The overuse of OR is also raising questions: this is another thing which makes it difficult (or impossible) for the optimizer to use indexes.

                       

                      BTW what is the number of rows returned by the query (not the total number of rows in the table)?

                       

                      Finally -- how do you know that this query is responsible for 100% CPU usage? It doesn't seem very likely, given that the query is executed serially on a 4-node RAC cluster (although it's possible if you only have 1 CPU per node). Are you talking about a single session executing the query once, or many sessions running it at once concurrently? What diagnostic information did you use to arrive to your conclusions? Can you post it?

                       

                      Best regards,

                      Nikolay

                      • 8. Re: Query Is taking almost 100% cpu
                        Hemant K Chitale

                        line #2 in EXPLAIN PLAN show rows=240

                        so why such a large difference between 240 & 4,798,957 values?

                         

                        After all these years, you DON'T KNOW HOW TO READ AN EXPLAIN PLAN ?

                         

                         

                        Hemant K Chitale

                        • 9. Re: Query Is taking almost 100% cpu
                          JohnWatson

                          I would think that the index on line_of_business cannot can be used for access, then filter the other predicates. However, the name line_of_business implies a column with few distinct values so the CBO may well be correct not to use it. That having been said, the query is not thought through. For example, the DISTINCT and the ORDER BY are pointless.

                           

                          Message was edited by: JohnWatson   Corrected typo. Sorry about that.

                          • 10. Re: Query Is taking almost 100% cpu
                            Jonathan Lewis

                            JohnWatson wrote:

                             

                            I would think that the index on line_of_business cannot can be used for access, then filter the other predicates. However, the name line_of_business implies a column with few distinct values so the CBO may well be correct not to use it. That having been said, the query is not thought through. For example, the DISTINCT and the ORDER BY are pointless.

                             

                            Message was edited by: JohnWatson   Corrected typo. Sorry about that.

                            Fortunately the optimizer seems to have spotted that and reduced the query to "select max() from ... where ..."

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Query Is taking almost 100% cpu
                              Jonathan Lewis

                              The order_number index is technically redundant since it is a prefix of the composite index.  You need to do some testing, though before you can drop it in case it's being used in cases where the optimizer won't use the composite index unless you adjust the clustering_factor.

                               

                              As another poster has suggested, "line_of_business" looks as if it should be usable, but we might guess that the cardinality estimates on that predicate make it unsuitable (and if that is the case then perhaps we should ask if the index has been created with compression).

                               

                              Order_number looks like a possible efficient access path, but (a) you have a compound predicate with OR and (b) the second part of that predicate includes a concatenation that is not indexed.  POSSIBLY if you could rewrite that predicate as (order_number = :b4 or (order_type = :b5a and order_number = :b5b) then the optimizer could use either a concatenation or an in-list iterator path on the index starting with order_number.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Query Is taking almost 100% cpu
                                PVM

                                Thanks A lot for your suggestions....

                                • 13. Re: Query Is taking almost 100% cpu
                                  PVM

                                  Actually i dont know how much rows it gives...It was running for an hour in PROD. So i killed that session. I got the info from AWR report and it was taking 99.85% cpu. The query came from application (WLS+OSB).

                                   

                                  Thanks

                                  • 14. Re: Query Is taking almost 100% cpu
                                    jihuyao

                                    Since no group by only one row is returned.  If you separate the 2 'OR' into 4 condition sets in union all, 4 rows will be returned and in turn the maximum LAST_UPDATE_TIME in the 4 rows will be returned.  Each set may need different index.