1 2 Previous Next 27 Replies Latest reply: May 5, 2014 7:21 PM by Jonathan Lewis RSS

    optimizing a query

    969952

      Hi All,

       

      I have a query .. It's taking very long time for fetching the data.. Here is the explain plan of  a query. Please find the below explain plan and suggest me to improve the performance.

      {code}

       

      --------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                              | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |

      --------------------------------------------------------------------------------------------------------------------

      |  0 | SELECT STATEMENT                      |                          |    2 |  574 |  1211  (51)| 00:00:12 |

      |  1 |  SORT UNIQUE                          |                          |    2 |  574 |  1211  (51)| 00:00:12 |

      |  2 |  UNION-ALL                            |                          |      |      |            |          |

      |*  3 |    TABLE ACCESS BY INDEX ROWID        | SCHOOL_FEE                |    1 |    41 |    18  (0)| 00:00:01 |

      |  4 |    NESTED LOOPS                      |                          |    1 |  287 |  604  (1)| 00:00:06 |

      |  5 |      NESTED LOOPS                      |                          |    1 |  246 |  587  (1)| 00:00:06 |

      |  6 |      NESTED LOOPS                    |                          |    6 |  1218 |  584  (1)| 00:00:06 |

      |  7 |        NESTED LOOPS                    |                          |    1 |  116 |  174  (0)| 00:00:02 |

      |  8 |        MERGE JOIN CARTESIAN          |                          |    1 |    91 |    4  (0)| 00:00:01 |

      |  9 |          MERGE JOIN CARTESIAN          |                          |    1 |    68 |    3  (0)| 00:00:01 |

      |  10 |          MERGE JOIN CARTESIAN        |                          |    1 |    48 |    2  (0)| 00:00:01 |

      |  11 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |    1 |    28 |    1  (0)| 00:00:01 |

      |* 12 |            INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  13 |            BUFFER SORT                |                          |    1 |    20 |    1  (0)| 00:00:01 |

      |  14 |            TABLE ACCESS BY INDEX ROWID| SCHOOL_EXAM_CAL          |    1 |    20 |    1  (0)| 00:00:01 |

      |* 15 |              INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  16 |          BUFFER SORT                  |                          |    1 |    20 |    2  (0)| 00:00:01 |

      |  17 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |    1 |    20 |    1  (0)| 00:00:01 |

      |* 18 |            INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  19 |          BUFFER SORT                  |                          |    1 |    23 |    3  (0)| 00:00:01 |

      |  20 |          TABLE ACCESS BY INDEX ROWID  | SCHOOL_EXAM_CAL          |    1 |    23 |    1  (0)| 00:00:01 |

      |* 21 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |* 22 |        TABLE ACCESS BY INDEX ROWID    | SCHOOL_FEE_INR            |    1 |    25 |  170  (0)| 00:00:02 |

      |* 23 |          INDEX RANGE SCAN              | SCHOOL_FEE_INR_CONV_IDX  |  849 |      |    1  (0)| 00:00:01 |

      |* 24 |        TABLE ACCESS BY INDEX ROWID    | SCHOOL_FACILITIES        |  881 | 76647 |  410  (1)| 00:00:04 |

      |* 25 |        INDEX RANGE SCAN              | SCHOOL_FACILITIES_IDX    | 29089 |      |  105  (2)| 00:00:01 |

      |  26 |      TABLE ACCESS BY INDEX ROWID      | SCHOOL_PARENS_CS          |    1 |    43 |    1  (0)| 00:00:01 |

      |* 27 |        INDEX RANGE SCAN                | SCHOOL_PARENS_IND        |    1 |      |    1  (0)| 00:00:01 |

      |* 28 |      INDEX RANGE SCAN                  | SCHOOL_FACILITY_FEE_IND  |    88 |      |    1  (0)| 00:00:01 |

      |* 29 |    TABLE ACCESS BY INDEX ROWID        | SCHOOL_FACILITY_FEE      |    1 |    41 |    18  (0)| 00:00:01 |

      |  30 |    NESTED LOOPS                      |                          |    1 |  287 |  605  (1)| 00:00:06 |

      |  31 |      NESTED LOOPS                      |                          |    1 |  246 |  587  (1)| 00:00:06 |

      |  32 |      NESTED LOOPS                    |                          |    6 |  1218 |  584  (1)| 00:00:06 |

      |  33 |        NESTED LOOPS                    |                          |    1 |  116 |  174  (0)| 00:00:02 |

      |  34 |        MERGE JOIN CARTESIAN          |                          |    1 |    91 |    4  (0)| 00:00:01 |

      |  35 |          MERGE JOIN CARTESIAN          |                          |    1 |    68 |    3  (0)| 00:00:01 |

      |  36 |          MERGE JOIN CARTESIAN        |                          |    1 |    48 |    2  (0)| 00:00:01 |

      |  37 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |    1 |    28 |    1  (0)| 00:00:01 |

      |* 38 |            INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  39 |            BUFFER SORT                |                          |    1 |    20 |    1  (0)| 00:00:01 |

      |  40 |            TABLE ACCESS BY INDEX ROWID| SCHOOL_EXAM_CAL          |    1 |    20 |    1  (0)| 00:00:01 |

      |* 41 |              INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  42 |          BUFFER SORT                  |                          |    1 |    20 |    2  (0)| 00:00:01 |

      |  43 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |    1 |    20 |    1  (0)| 00:00:01 |

      |* 44 |            INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |  45 |          BUFFER SORT                  |                          |    1 |    23 |    3  (0)| 00:00:01 |

      |  46 |          TABLE ACCESS BY INDEX ROWID  | SCHOOL_EXAM_CAL          |    1 |    23 |    1  (0)| 00:00:01 |

      |* 47 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01        |    1 |      |    1  (0)| 00:00:01 |

      |* 48 |        TABLE ACCESS BY INDEX ROWID    | SCHOOL_FEE_INR            |    1 |    25 |  170  (0)| 00:00:02 |

      |* 49 |          INDEX RANGE SCAN              | SCHOOL_FEE_INR_CONV_IDX  |  849 |      |    1  (0)| 00:00:01 |

      |* 50 |        TABLE ACCESS BY INDEX ROWID    | SCHOOL_FACILITIES        |  881 | 76647 |  410  (1)| 00:00:04 |

      |* 51 |        INDEX RANGE SCAN              | SCHOOL_FACILITIES_IDX    | 29089 |      |  105  (2)| 00:00:01 |

      |  52 |      TABLE ACCESS BY INDEX ROWID      | SCHOOL_PARENS_CS          |    1 |    43 |    1  (0)| 00:00:01 |

      |* 53 |        INDEX RANGE SCAN                | SCHOOL_PARENS_IND        |    1 |      |    1  (0)| 00:00:01 |

      |* 54 |      INDEX RANGE SCAN                  | SCHOOL_FACILITY_FEE_IDX  |    1 |      |    18  (0)| 00:00:01 |

      --------------------------------------------------------------------------------------------------------------------

      {code}

       

      Thanks

        • 1. Re: optimizing a query
          Hoek

          Posting just a plan without a query, no database version and no context at all won't get you far.

          I suggest your read and get familiar with these instructions:

          Oracle related stuff: Basic SQL statement performance diagnosis - HOW TO, step by step instructions

          • 2. Re: optimizing a query
            969952

            Hi ,

             

            I am working on Oracle 10.2.0.5.0

             

            Thanks.

            • 3. Re: optimizing a query
              Etbin

              --------------------------------------------------------------------------------------------------------------------

              | Id  | Operation                              | Name                     | Rows  | Bytes | Cost(%CPU)|  Time    |

              --------------------------------------------------------------------------------------------------------------------

              |  0  | SELECT STATEMENT                       |                          |     2 |   574 |  1211 (51)| 00:00:12 |

              |  1  |  SORT UNIQUE                           |                          |     2 |   574 |  1211 (51)| 00:00:12 |

              |  2  |  UNION-ALL                             |                          |       |       |           |          |

              |* 3  |    TABLE ACCESS BY INDEX ROWID         | SCHOOL_FEE               |     1 |    41 |    18  (0)| 00:00:01 |

              |  4  |    NESTED LOOPS                        |                          |     1 |   287 |   604  (1)| 00:00:06 |

              |  5  |      NESTED LOOPS                      |                          |     1 |   246 |   587  (1)| 00:00:06 |

              |  6  |      NESTED LOOPS                      |                          |     6 |  1218 |   584  (1)| 00:00:06 |

              |  7  |        NESTED LOOPS                    |                          |     1 |   116 |   174  (0)| 00:00:02 |

              |  8  |        MERGE JOIN CARTESIAN            |                          |     1 |    91 |     4  (0)| 00:00:01 |

              |  9  |          MERGE JOIN CARTESIAN          |                          |     1 |    68 |     3  (0)| 00:00:01 |

              |  10 |          MERGE JOIN CARTESIAN          |                          |     1 |    48 |     2  (0)| 00:00:01 |

              |  11 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    28 |     1  (0)| 00:00:01 |

              |* 12 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  13 |            BUFFER SORT                 |                          |     1 |    20 |     1  (0)| 00:00:01 |

              |  14 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    20 |     1  (0)| 00:00:01 |

              |* 15 |              INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  16 |          BUFFER SORT                   |                          |     1 |    20 |     2  (0)| 00:00:01 |

              |  17 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    20 |     1  (0)| 00:00:01 |

              |* 18 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  19 |          BUFFER SORT                   |                          |     1 |    23 |     3  (0)| 00:00:01 |

              |  20 |          TABLE ACCESS BY INDEX ROWID   | SCHOOL_EXAM_CAL          |     1 |    23 |     1  (0)| 00:00:01 |

              |* 21 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |* 22 |        TABLE ACCESS BY INDEX ROWID     | SCHOOL_FEE_INR           |     1 |    25 |   170  (0)| 00:00:02 |

              |* 23 |          INDEX RANGE SCAN              | SCHOOL_FEE_INR_CONV_IDX  |   849 |       |     1  (0)| 00:00:01 |

              |* 24 |        TABLE ACCESS BY INDEX ROWID     | SCHOOL_FACILITIES        |   881 | 76647 |   410  (1)| 00:00:04 |

              |* 25 |        INDEX RANGE SCAN                | SCHOOL_FACILITIES_IDX    | 29089 |       |   105  (2)| 00:00:01 |

              |  26 |      TABLE ACCESS BY INDEX ROWID       | SCHOOL_PARENS_CS         |     1 |    43 |     1  (0)| 00:00:01 |

              |* 27 |        INDEX RANGE SCAN                | SCHOOL_PARENS_IND        |     1 |       |     1  (0)| 00:00:01 |

              |* 28 |      INDEX RANGE SCAN                  | SCHOOL_FACILITY_FEE_IND  |    88 |       |     1  (0)| 00:00:01 |

              |* 29 |    TABLE ACCESS BY INDEX ROWID         | SCHOOL_FACILITY_FEE      |     1 |    41 |    18  (0)| 00:00:01 |

              |  30 |    NESTED LOOPS                        |                          |     1 |   287 |   605  (1)| 00:00:06 |

              |  31 |      NESTED LOOPS                      |                          |     1 |   246 |   587  (1)| 00:00:06 |

              |  32 |      NESTED LOOPS                      |                          |     6 |  1218 |   584  (1)| 00:00:06 |

              |  33 |        NESTED LOOPS                    |                          |     1 |   116 |   174  (0)| 00:00:02 |

              |  34 |        MERGE JOIN CARTESIAN            |                          |     1 |    91 |     4  (0)| 00:00:01 |

              |  35 |          MERGE JOIN CARTESIAN          |                          |     1 |    68 |     3  (0)| 00:00:01 |

              |  36 |          MERGE JOIN CARTESIAN          |                          |     1 |    48 |     2  (0)| 00:00:01 |

              |  37 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    28 |     1  (0)| 00:00:01 |

              |* 38 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  39 |            BUFFER SORT                 |                          |     1 |    20 |     1  (0)| 00:00:01 |

              |  40 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    20 |     1  (0)| 00:00:01 |

              |* 41 |              INDEX RANGE SCAN          | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  42 |          BUFFER SORT                   |                          |     1 |    20 |     2  (0)| 00:00:01 |

              |  43 |            TABLE ACCESS BY INDEX ROWID | SCHOOL_EXAM_CAL          |     1 |    20 |     1  (0)| 00:00:01 |

              |* 44 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |  45 |          BUFFER SORT                   |                          |     1 |    23 |     3  (0)| 00:00:01 |

              |  46 |          TABLE ACCESS BY INDEX ROWID   | SCHOOL_EXAM_CAL          |     1 |    23 |     1  (0)| 00:00:01 |

              |* 47 |            INDEX RANGE SCAN            | SCHOOL_EXAM_CAL_01       |     1 |       |     1  (0)| 00:00:01 |

              |* 48 |        TABLE ACCESS BY INDEX ROWID     | SCHOOL_FEE_INR           |     1 |    25 |   170  (0)| 00:00:02 |

              |* 49 |          INDEX RANGE SCAN              | SCHOOL_FEE_INR_CONV_IDX  |   849 |       |     1  (0)| 00:00:01 |

              |* 50 |        TABLE ACCESS BY INDEX ROWID     | SCHOOL_FACILITIES        |   881 | 76647 |   410  (1)| 00:00:04 |

              |* 51 |        INDEX RANGE SCAN                | SCHOOL_FACILITIES_IDX    | 29089 |       |   105  (2)| 00:00:01 |

              |  52 |      TABLE ACCESS BY INDEX ROWID       | SCHOOL_PARENS_CS         |     1 |    43 |     1  (0)| 00:00:01 |

              |* 53 |        INDEX RANGE SCAN                | SCHOOL_PARENS_IND        |     1 |       |     1  (0)| 00:00:01 |

              |* 54 |      INDEX RANGE SCAN                  | SCHOOL_FACILITY_FEE_IDX  |     1 |       |    18  (0)| 00:00:01 |

              --------------------------------------------------------------------------------------------------------------------

               

              I formatted a plan few days ago. Now I'm afraid someone could say why I'm not willing to repeat the exercise.

               

              Regards

               

              Etbin

              • 4. Re: optimizing a query
                969952

                Hi All,

                 

                Can any one share me your thoughts on this.

                 

                Thanks

                • 5. Re: optimizing a query
                  SomeoneElse

                  You have a history of posting elaborate explain plans with no queries and no other information.

                   

                  https://community.oracle.com/thread/3551924

                  https://community.oracle.com/thread/3552276

                  https://community.oracle.com/thread/3551236

                  https://community.oracle.com/thread/3552314

                   

                  How well has that been working?

                   

                  If you were to post all the information requested in the Tuning threads you might get better help.

                  • 6. Re: optimizing a query
                    969952

                    Hi.. yes.. as i think for different issues need to raised different  threads so have raised different ticktes..you please have a look into the Explain plan and give your ideas to tune the query?

                     

                    Thanks

                    • 7. Re: optimizing a query
                      JustinCave

                      A plan with no information about the actual execution statistics, no information about your tables, no information about your query, no predicates, etc. provides no information that we can reasonably use to help you.

                       

                      The optimizer estimates that your query returns 2 rows in 12 seconds.  The optimizer has far more information about your data than I do, so I have no reason to believe that it is incorrect.  If you have reason to believe that the optimizer isn't finding the best plan, you'll need to explain to us why you believe that.  If you want help rewriting a query, we'd need to see what the current query is.  If you don't want to give us any information, it's unlikely that we can give you any help.

                       

                      Justin

                      • 8. Re: optimizing a query
                        969952

                        Justin,

                         

                        as it's official data I can't post you the query oredicates and other information.. so can you please have a look into the X-Plan and give me some ideas to tune the query.

                         

                        Thanks

                        • 9. Re: optimizing a query
                          JustinCave

                          We don't need your actual data.  We do need information to give you any help.

                           

                          If you are saying that your table definitions are confidential, come up with a different data model in a different domain and show us that.  If you are saying that your data is confidential, come up with sample data that isn't (we don't care whether the data is real). 

                           

                          If you are saying that telling us how many rows the query actually returns, how long it really takes, where Oracle is filtering data, etc. then we can't help you.  You need to contact a consulting company that can sign a non-disclosure agreement and help you tune your queries.

                           

                          Justin

                          • 10. Re: optimizing a query
                            969952

                            Hi Justin,

                             

                            the query is returning very less number of rows around 200. but for retrieving those rows taking long run. As I gone through the Explain plan I could not be able to find the issue...I felt the quer is having concatenation issue so tried with Parallel hint,No_expand ,first_rows and some basic what am aware.. but didn'e see any improvement in fetching the datd... so have a look and let me know are there any possibilities to figure out the problem is?

                             

                            Thanks

                            • 11. Re: optimizing a query
                              JustinCave

                              As I said, the optimizer estimates that your query returns 2 rows and runs in 12 seconds.  You're saying that the query actually returns 200 rows.  That's useful information.  How long does it actually take?

                               

                              Can you post a plan that includes the actual execution statistics to show where the optimizer's estimates diverge from reality?  You've already said that you can't post a plan that includes predicate information, that's going to substantially complicate the problem and limit what can possibly suggest.

                               

                              Justin

                              • 12. Re: optimizing a query
                                969952

                                It is taking  21:37 Mins. have posted the same explain plan .. just canges the names? remaining everything is same as real query.

                                 

                                Thanks.


                                • 13. Re: optimizing a query
                                  969952

                                  just curious ho did you figure out the otpimizer estimated 2 rows in 12 secs? can you share me the steps to figure out..

                                  • 14. Re: optimizing a query
                                    rp0428
                                    just curious ho did you figure out the otpimizer estimated 2 rows in 12 secs? can you share me the steps to figure out..

                                    You're joking, right?

                                    | Id  | Operation                              | Name                     | Rows  | Bytes | Cost(%CPU)|  Time    |

                                    --------------------------------------------------------------------------------------------------------------------

                                    |  0  | SELECT STATEMENT                       |                          |     2 |   574 |  1211 (51)| 00:00:12 |

                                    Take a good look at that line above and see if you can figure it out!

                                    1 2 Previous Next