1 2 Previous Next 20 Replies Latest reply on May 28, 2020 11:38 AM by 3510875 Go to original post
      • 15. Re: Reducing the runtime of a query
        Mark D Powell

        user3510875, If you are going to create an index on icx_sessions since it is the target of an outer join I believe it needs to look like (disabled_flag, pseudo_flag, responsibility_id, first_connect, last_connect) as the first two columns allow you to find the rows of interest in icx_sessions, responsibility_id provides indexed access to fnd_responsibility and the last two columns provide all the information required from icx_sessions so no table access should be required as a FFS of the index should be an option.  If this is better or not should be easy enough to check.

        - -

        HTH -- Mark D Powell --

        • 16. Re: Reducing the runtime of a query
          Tubby

          3510875 wrote:

           

          Hi,

           

          Below is the plan after creating index.It didnt make a big difference.

           

          SELECT /*+ GATHER_PLAN_STATISTICS */ icx.first_connect,last_connect,

          usr.user_name,usr.DESCRIPTION, resp.responsibility_key, function_type

          FROM apps.icx_sessions icx JOIN apps.fnd_user usr ON usr.user_id =

          icx.user_id LEFT JOIN apps.fnd_responsibility resp ON

          resp.responsibility_id = icx.responsibility_id WHERE last_connect >

          SYSDATE - NVL (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), 30) / 60 / 24

          AND disabled_flag != 'Y' AND pseudo_flag = 'N'

           

          Plan hash value: 1485575382

           

          • -------------------------------------------------------------------------------------------------------------------------------------------------
          • | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
          • -------------------------------------------------------------------------------------------------------------------------------------------------
          • | 0 | SELECT STATEMENT | | 1 | | 5 |00:00:10.77 | 3064 | | | |
          • |* 1 | HASH JOIN RIGHT OUTER | | 1 | 26654 | 5 |00:00:10.77 | 3064 | 1048K| 1048K| 1387K (0)|
          • |* 2 | TABLE ACCESS FULL | FND_RESPONSIBILITY | 1 | 1 | 2192 |00:00:00.01 | 76 | | | |
          • |* 3 | HASH JOIN | | 1 | 26654 | 5 |00:00:10.77 | 2988 | 1126K| 1126K| 1360K (0)|
          • | 4 | TABLE ACCESS FULL | FND_USER | 1 | 2627 | 2627 |00:00:00.01 | 121 | | | |
          • | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| ICX_SESSIONS | 1 | 26654 | 5 |00:00:10.76 | 2867 | | | |
          • |* 6 | INDEX RANGE SCAN | XXABR_PER | 1 | 4800 | 5 |00:00:10.77 | 2839 | | | |
          • -------------------------------------------------------------------------------------------------------------------------------------------------

           

          Predicate Information (identified by operation id):

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

           

          1 - access("RESPONSIBILITY_ID"="ICX"."RESPONSIBILITY_ID")

          2 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20200519_1939')

          3 - access("USR"."USER_ID"="ICX"."USER_ID")

          6 - access("ICX"."PSEUDO_FLAG"='N' AND "ICX"."LAST_CONNECT">SYSDATE@!-TO_NUMBER(NVL("FND_PROFILE"."VALUE"('ICX_SESSION_TIMEOUT'),'30')

          )/60/24)

          filter(("ICX"."DISABLED_FLAG"<>'Y' AND "ICX"."LAST_CONNECT">SYSDATE@!-TO_NUMBER(NVL("FND_PROFILE"."VALUE"('ICX_SESSION_TIMEOUT'),'

          30'))/60/24))

           

          35 rows selected.

           

          Thank You

          Based on that it looks like the addition of the function is skewing the cardinality estimates; we see that the e-rows is 4800 and the a-rows is 5 for the index range scan. I believe the reason for that is the application of the function to the predicate sysdate. You can confirm that easily enough by just running the query with SYSDATE and not "mucking" with it at all (leave out the subtraction of the value from the function call). What happens when you do that?

           

          Cheers,

          • 17. Re: Reducing the runtime of a query
            Jonathan Lewis

            Are you allowed to edit the query ?

            If so then change

             

            last_connect > SYSDATE - NVL (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), 30) / 60 / 24

             

            to

             

            last_connect > (select SYSDATE - NVL (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), 30) / 60 / 24 from dual)

             

             

            The estimates that the optimizer is using for the predicate are pure guesswork (based on 0.9% for an open range scan on an index and 5% for an open range scan on a table (which is why the table estimate if 5.55 time the size of the index estimate in the last case).  Unfortunately it looks as if Oracle has to call the function (possibly twice) for every row it examines - and this may be reduced to a single call thanks to scalar subquery caching if you wrap the predicate calculation in a select from dual.

             

             

             

            Regards

            Jonathan Lewis

            • 18. Re: Reducing the runtime of a query
              3510875

              Thank you all for your updates.

               

              Dear Jonathan,

               

              Perfect!.We have changed the code and the response time of this query is now less than a second.

              I am trying to understand how this was fixed.can you pls expand a little bit on this statement

              "The estimates that the optimizer is using for the predicate are pure guesswork (based on 0.9% for an open range scan on an index and 5% for an open range scan on a table (which is why the table estimate if 5.55 time the size of the index estimate in the last case).  Unfortunately it looks as if Oracle has to call the function (possibly twice) for every row it examines - and this may be reduced to a single call thanks to scalar subquery caching if you wrap the predicate calculation in a select from dual."

               

              Changed plan:

              Plan hash value: 192117661

               

               

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

              | Id  | Operation             | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |      OMem |  1Mem | Used-Mem |

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

              |   0 | SELECT STATEMENT      |                    |      1 |        |     14 |00:00:00.35 |   38584 |           |       |          |

              |*  1 |  HASH JOIN RIGHT OUTER|                    |      1 |  53307 |     14 |00:00:00.35 |   38584 |      1048K|  1048K| 1366K (0)|

              |*  2 |   TABLE ACCESS FULL   | FND_RESPONSIBILITY |      1 |      1 |   2192 |00:00:00.01 |      76 |           |       |          |

              |*  3 |   HASH JOIN           |                    |      1 |  53307 |     14 |00:00:00.34 |   38508 |      1126K|  1126K| 1324K (0)|

              |   4 |    TABLE ACCESS FULL  | FND_USER           |      1 |   2627 |   2627 |00:00:00.01 |     121 |           |       |          |

              |*  5 |    TABLE ACCESS FULL  | ICX_SESSIONS       |      1 |  53307 |     14 |00:00:00.34 |   38387 |           |       |          |

              |   6 |     FAST DUAL         |                    |      1 |      1 |      1 |00:00:00.01 |       0 |           |       |          |

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

               

               

              Predicate Information (identified by operation id):

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

               

               

                 1 - access("RESPONSIBILITY_ID"="ICX"."RESPONSIBILITY_ID")

                 2 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20200519_1939')

                 3 - access("USR"."USER_ID"="ICX"."USER_ID")

                 5 - filter("ICX"."LAST_CONNECT">)

               

              Thank You

              • 19. Re: Reducing the runtime of a query
                Jonathan Lewis

                The main body of the plan has changed shape because the statistics have worked out differently on this run, but the key feature is that the full tablescan of ICX_SESSION now takes 0.34 seconds rather than 11.70 seconds because it is comparing last_connect with a "derived constant" rather than calling a pl/sql function for every single row.  The FAST DUAL at operation 6 is notionally running as a filter subquery, but the inputs never change so Oracle uses scalar subquery caching to say "I've already run this subquery for this value and know the answer so I can just re-use the answer" - hence starts = 1 and my invention of the term "derived constant".

                 

                If you're puzzled by my comment about 0.55

                 

                When the optimizer sees the expression "column > {unknown value}" as a filter predicate in a tablescan it uses a selectivity of 5% as a guesstimate; when the expression is an access predicate for an index the optimizer uses a guesstimate of 0.9%. So, assume you have 1,000,000 rows in the table and a suitable index. The index "Rows" figure would report an estimate of 9,000 *0.9% of 1,000,000) and the table "Rows" figure would report an estimate 50,000 rows (5%).

                 

                50,000 / 9.000 = 5.555.... which is the 5.55 that I mentioned.

                 

                 

                Regards

                Jonathan Lewis

                 

                 

                 

                 

                P.S.  I am a little puzzled - your original plan showed another two more filter predicates on icx_session:

                 

                "ICX"."DISABLED_FLAG"<>'Y' AND "ICX"."PSEUDO_FLAG"='N'

                 

                Do you know why they have disappeared ?

                • 20. Re: Reducing the runtime of a query
                  3510875

                  Dear Jonathan,

                   

                  Thanks for the update.We have queried again and we are able to see the filter predicates.

                  How can we interpret operation 6 in this plan.Not able to get the order of operations in this plan.

                  We have a small script which gave the order as 7,6,1,5,2,4,3

                   

                  Plan hash value: 192117661

                   

                   

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

                  | Id  | Operation             | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |      OMem |  1Mem | Used-Mem |

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

                  |   0 | SELECT STATEMENT      |                    |      1 |        |     13 |00:00:00.34 |   38570 |           |       |          |

                  |*  1 |  HASH JOIN RIGHT OUTER|                    |      1 |  26654 |     13 |00:00:00.34 |   38570 |      1048K|  1048K| 1354K (0)|

                  |*  2 |   TABLE ACCESS FULL   | FND_RESPONSIBILITY |      1 |      1 |   2192 |00:00:00.01 |      76 |           |       |          |

                  |*  3 |   HASH JOIN           |                    |      1 |  26654 |     13 |00:00:00.34 |   38494 |      1126K|  1126K| 1340K (0)|

                  |   4 |    TABLE ACCESS FULL  | FND_USER           |      1 |   2627 |   2627 |00:00:00.01 |     121 |           |       |          |

                  |*  5 |    TABLE ACCESS FULL  | ICX_SESSIONS       |      1 |  26654 |     13 |00:00:00.34 |   38373 |           |       |          |

                  |   6 |     FAST DUAL         |                    |      1 |      1 |      1 |00:00:00.01 |       0 |           |       |          |

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

                   

                   

                  Predicate Information (identified by operation id):

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

                   

                   

                     1 - access("RESPONSIBILITY_ID"="ICX"."RESPONSIBILITY_ID")

                     2 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20200519_1939')

                     3 - access("USR"."USER_ID"="ICX"."USER_ID")

                     5 - filter(("ICX"."DISABLED_FLAG"<>'Y' AND "ICX"."PSEUDO_FLAG"='N' AND "ICX"."LAST_CONNECT">)

                   

                  32 rows selected.

                   

                  Thank You

                  1 2 Previous Next