9 Replies Latest reply on Sep 15, 2017 5:00 PM by Dear DBA Frank

    How to understand execution plan and improve query performance

    user12251389

      I have below query which returns 90,000 records and its taking almost 10 minutes to run. I dont have indexes on any of the tables and i cannot provide index as i am running this query in PROD.

       

      I am viewing the execution plan in sql developer but not understanding exactly which condition is taking too much time and how can i improve it.

       

      Select * from ORDER_MART FOL where FOL.PARENT_PROD_SRCID 
      IN 
      (
      select e.PARENT_PROD_SRCID
      from SRC_GRP a 
      JOIN MAR_GRP b ON a.h_lpgrp_id = b.h_lpgrp_id
      JOIN DATA_GRP e ON e.parent_prod_srcid = b.H_LOCPR_ID
      WHERE a.CHILD_LOCPR_ID != 0 
      AND dt_id BETWEEN 20170101 AND 20170731 
      AND valid_order = 1 
      AND a.PROD_TP_CODE like 'C%'
      )
      AND FOL.PROD_SRCID = 0 and IS_CAPS = 1;
      

       

      Below is my query execution plan:

      Untitled.png

        • 1. Re: How to optimize the query adding more joins in order to force the partition pruning
          AndrewSayer

          user12251389 wrote:

           

          I have below query which returns 90,000 records and its taking almost 10 minutes to run. I dont have indexes on the any of the tables and i cannot provide index as i am running this query in PROD.

           

          Is there any way where i can add more joins in order to force the partition pruning or any other way where i can optimize the query performance.

           

          1. Select*fromORDER_MARTFOLwhere
          2. EXISTS
          3. (
          4. selecte.PARENT_PROD_SRCID
          5. fromSRC_GRPa
          6. JOINMAR_GRPbONa.h_lpgrp_id=b.h_lpgrp_id
          7. JOINDATA_GRPeONe.parent_prod_srcid=b.H_LOCPR_ID
          8. WHEREa.CHILD_LOCPR_ID!=0--removethelinesfromINT_CDW_DV.S_LOCAL_PROD_GRP_MAINwithchildprodsrcidequalto0
          9. ANDdt_idBETWEEN20170101AND20170731--gettheordersforspecificperiodwhichareaffected
          10. ANDvalid_order=1--andis_caps=1
          11. ANDa.PROD_TP_CODElike'C%'
          12. )
          13. ANDFOL.PROD_SRCID=0andIS_CAPS=1;

          Do you think that's enough information for us to help you?

           

          What is the plan?

          Where is the time going?

          Partitions?What partitions?
          Indexes? What indexes?

           

          What are your driving filters and what do they mean?

           

          Why are you running this new query on production? Surely it should be written in development and tuned there, then tested etc..?

          • 2. Re: How to optimize the query adding more joins in order to force the partition pruning
            Sven W.

            Not sure why you think adding more joins will help with partition pruning. If you want to make sure to access data only from a specific partition, then you can name the partition in the from clause.


            An example from the docs (https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702 )

             

             

            SELECT * FROM sales PARTITION (sales_q2_2000) s

              WHERE s.amount_sold > 1500

              ORDER BY cust_id, time_id, channel_id;

            • 3. Re: How to optimize the query adding more joins in order to force the partition pruning
              user12251389

              I have added the query execution plan and i cannot add indexes because of permission restrictions in PROD.

               

              What i mean partition means either it is possible to user here some ranking function or row_number() over (partition by) clause which can optimize my query performance

              • 4. Re: How to optimize the query adding more joins in order to force the partition pruning
                BrunoVroman

                Hello,

                 

                a few remarks

                 

                - please put aliases where I have written "xxx", "yyy", "zzz"

                SELECT *
                  FROM order_mart fol
                  WHERE EXISTS( SELECT NULL
                                  FROM src_grp a
                                  JOIN mar_grp b
                                    ON a.h_lpgrp_id = b.h_lpgrp_id
                                  JOIN data_grp e
                                    ON e.parent_prod_srcid = b.h_locpr_id
                                  WHERE a.child_locpr_id != 0
                                    AND xxx.dt_id BETWEEN 20170101 AND 20170731
                                    AND yyy.valid_order = 1 /* and zzz.is_caps = 1 */
                                    AND a.prod_tp_code LIKE 'C%'
                              )
                  AND fol.prod_srcid = 0
                  AND fol.is_caps = 1
                ;

                 

                - avoid to comment with -- in statement, if "crlf" are not kept we can't know how far the comment spans; rather use /* ... */

                 

                - no link between "fol" and "a", "b" or "e"? I guess that yes, see first question with xxx yyy zzz

                 

                - dt_id is a NUMBER representing a DATE? Hmmm looks like a bad idea. (and if it is a VARCHAR it is even worse)

                 

                - EXISTS( SELECT NULL ... ) is equivalent to EXISTS( SELECT e.parent_prod_srcid ... )

                 

                Best regards,

                 

                Bruno Vroman.

                • 5. Re: How to optimize the query adding more joins in order to force the partition pruning
                  user12251389

                  actually i have updated my query. I used EXISTS just to improve performance and i have used IN now which i was using previsouly.

                  • 6. Re: How to optimize the query adding more joins in order to force the partition pruning
                    2980262

                    Use Filer Row Percentage Method if you can't make use of indexes.

                    • 7. Re: How to optimize the query adding more joins in order to force the partition pruning
                      user12251389

                      can you plese tell me how to do that in my query ?

                      • 8. Re: How to optimize the query adding more joins in order to force the partition pruning
                        user12251389

                        But how can i use partition in my query instead of joins and i am really not sure if it will improve the performance ?

                        • 9. Re: How to understand execution plan and improve query performance
                          Dear DBA Frank

                          It seems your question has little to do with SQLdeveloper so should have been logged in the Database General Discussions forum. 

                           

                          • The screenshot of the execution plan shows it seems to have been generated with the F10 feature of SQLdeveloper (Explain Plan), so it shows the plan that the optimizer intends to use.  It could be different from the one actually used, which you can see, after running your SELECT /*+ gather_plan_statistics */ , with
                            select * from table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS'));

                           

                          • that screenshot is truncated, so judging from what we can see on it, it seems that the bulk of the time spent will be the full table scan of table ORDER_MART (it accounts for most of the total cost of 1798406).  That would mean to speed up that query, you must get rid of that FTS or greatly improve its performance.
                          • if you really cannot add indexes, then to speed up that FTS, you could increase its degree of parallelism (maybe also increase the db_file_multiblock_read_count parameter, but that would have repercussions instance-wide)
                          • if I were you, I would use my test environnment to demonstrate that an index on columns PROD_SRCID and IS_CAPS would probably improve performance by orders of magnitudes (if you don't have an environment with enough records in the ORDER_MART table, you could export that table from prod and import it into your test database; it should be enough to prove your point that an index on it will improve performance dramatically).  In your test environment, time the SELECT before creating the index then create that 2-column index and time the SELECT again.  Each time, use the SELECT from ...DISPLAY_CURSOR to get the execution plan that was actually used, and check whether your index was used!