1 2 Previous Next 20 Replies Latest reply on May 28, 2020 11:38 AM by 3510875

    Reducing the runtime of a query

    3510875

      Dear Experts,

       

      Need your favour.

      Our database version - 12.1.0.2

       

      We have a query which shows us the current number of users logged into system.It is currently running for 11 seconds.

      Can we reduce the runtime from 11 seconds to less than 5 seconds?

       

      I understand the explain plan format is not in good format,hence attached one more copy for easy reading

       

      SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'4g04c7kwhw7xt',format=>'ALLSTATS LAST'));

       

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

      SQL_ID  4g04c7kwhw7xt, child number 1

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

      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: 325715545

       

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

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

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

      |   0 | SELECT STATEMENT             |                    |      1 |        |      1 |00:00:11.70 |   38245 |  38110 |           |       |          |

      |   1 |  NESTED LOOPS                |                    |      1 |      2 |      1 |00:00:11.70 |   38245 |  38110 |           |       |          |

      |   2 |   NESTED LOOPS               |                    |      1 |      2 |      1 |00:00:11.70 |   38244 |  38110 |           |       |          |

      |*  3 |    HASH JOIN OUTER           |                    |      1 |      2 |      1 |00:00:11.70 |   38242 |  38110 |      1148K|  1148K|  384K (0)|

      |*  4 |     TABLE ACCESS FULL        | ICX_SESSIONS       |      1 |      2 |      1 |00:00:11.70 |   38165 |  38110 |           |       |          |

      |*  5 |     TABLE ACCESS FULL        | FND_RESPONSIBILITY |      1 |   2192 |   2192 |00:00:00.01 | 77 |       0 |       |       |          |

      |*  6 |    INDEX UNIQUE SCAN         | FND_USER_U1        |      1 |      1 |      1 |00:00:00.01 |  2 |       0 |       |       |          |

      |   7 |   TABLE ACCESS BY INDEX ROWID| FND_USER           |      1 |      1 |      1 |00:00:00.01 |  1 |       0 |       |       |          |

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

       

       

      Predicate Information (identified by operation id):

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

       

       

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

         4 - filter(("ICX"."DISABLED_FLAG"<>'Y' AND "ICX"."PSEUDO_FLAG"='N' AND

                    "ICX"."LAST_CONNECT">SYSDATE@!-TO_NUMBER(NVL("FND_PROFILE"."VALUE"T'),'30'))/60/24))

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

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

       

       

      Note

      -----

         - dynamic statistics used: dynamic sampling (level=2)

         - statistics feedback used for this statement

         - 1 Sql Plan Directive used for this statement

       

      40 rows selected.

       

       

      Count:

       

      SQL> select count(*) from ICX_SESSIONS;

       

        COUNT(*)

      ----------

         1066670

       

       

      Indexes:

       

        1* select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME='ICX_SESSIONS'

      SQL> /

       

       

      INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION

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

      ICX_SESSIONS_U2                XSID                                         1

      ICX_SESSIONS_U1                SESSION_ID                                   1

      ICX_SESSIONS_N1                USER_ID                                      1

       

       

      Table definition:

       

      CREATE TABLE "ICX"."ICX_SESSIONS"

         (    "SESSION_ID" NUMBER NOT NULL ENABLE,

              "WINDOW_ID" NUMBER,

              "USER_ID" NUMBER NOT NULL ENABLE,

              "MODE_CODE" VARCHAR2(30),

              "HOME_URL" VARCHAR2(240),

              "RESPONSIBILITY_APPLICATION_ID" NUMBER,

              "RESPONSIBILITY_ID" NUMBER,

              "SECURITY_GROUP_ID" NUMBER,

              "ORG_ID" NUMBER(15,0),

              "MENU_ID" NUMBER,

              "FUNCTION_ID" NUMBER,

              "FUNCTION_TYPE" VARCHAR2(30),

              "NLS_LANGUAGE" VARCHAR2(30),

              "LANGUAGE_CODE" VARCHAR2(30),

              "DATE_FORMAT_MASK" VARCHAR2(100),

              "COUNTER" NUMBER,

              "FIRST_CONNECT" DATE,

              "LAST_CONNECT" DATE,

              "NLS_DATE_LANGUAGE" VARCHAR2(30),

              "NLS_NUMERIC_CHARACTERS" VARCHAR2(30),

       

       

              "NLS_SORT" VARCHAR2(30),

              "NLS_TERRITORY" VARCHAR2(30),

              "LIMIT_TIME" NUMBER,

              "LIMIT_CONNECTS" NUMBER,

              "DISABLED_FLAG" VARCHAR2(1),

              "PSEUDO_FLAG" VARCHAR2(1),

              "PAGE_ID" NUMBER,

              "CREATED_BY" NUMBER NOT NULL ENABLE,

              "CREATION_DATE" DATE NOT NULL ENABLE,

              "LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,

              "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,

              "LAST_UPDATE_LOGIN" NUMBER,

              "LOGIN_ID" NUMBER,

              "NODE_ID" NUMBER,

              "MAC_KEY" RAW(20),

              "ENC_KEY" RAW(32),

              "XSID" VARCHAR2(32),

              "TIME_OUT" NUMBER,

              "GUEST" VARCHAR2(30),

              "DISTRIBUTED" VARCHAR2(30),

              "PROXY_USER_ID" NUMBER

         ) SEGMENT CREATION IMMEDIATE

        PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255

      NOCOMPRESS LOGGING

        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENT

      S 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_F

      LASH_CACHE DEFAULT)

        TABLESPACE "APPS_TS_TX_DATA"

       

       

       

       

       

       

      SQL

        • 1. Re: Reducing the runtime of a query
          John Thorton

          post results from SQL below

           

          select count(xid) from ICX_SESSIONS WHERE XID IS NOT NULL;

          • 2. Re: Reducing the runtime of a query
            evgenyg

            You would need to provide information about the rest of the tables involved in the query.
            As well some statistical information about predicates, uniqueness et cetera.

            Although I really don't see there something that not trivial. Trivial join of three tables and very well defined issue.

             

            Regards

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

              Here is the requested output:

               

              SQL> select count(xsid) from ICX_SESSIONS WHERE xsid is not null;

               

              COUNT(XSID)

              -----------

                  1066658

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

                The below index would likely serve you well .

                 

                create index <name> on apps.icx_sessions (pseudo_flag, disabled_flag, last_connect)

                    compress 2;

                 

                Cheers,

                • 5. Re: Reducing the runtime of a query
                  evgenyg

                  As well I would check out how many times you call: fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') function and if this is really necessary.

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

                    evgenyg wrote:

                     

                    As well I would check out how many times you call: fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') function and if this is really necessary.

                    That's an EBS package call; given that it's not using values from either table in the query it shouldn't be an issue (called once and should be pretty lightweight).

                     

                    Cheers,

                    • 7. Re: Reducing the runtime of a query
                      evgenyg

                      Thanks, never worked with EBS.

                      btw. you suggesting to create index, without knowing any statistics on the  "ICX"."DISABLED_FLAG", "ICX"."PSEUDO_FLAG", "ICX"."LAST_CONNECT"

                      don't you think that OP first need to understand what going on? Even if, most probably, the index would be right solution.

                      In general, do we want someone to learn or we want someone to use forum as free consulting services? After all the question in this topic is trivial.

                       

                      Regards

                      • 8. Re: Reducing the runtime of a query
                        Mark D Powell

                        user3510875, per your note >> 1 Sql Plan Directive used for this statement <<
                        - -
                        Why is there an SQL Plan directive for this query?  You need to look at the directive and determine if you might want to drop it.  Also dynamic statistics were used.  If this is not due to the directive you may need to update the statistics on the three tables and indexes involved.
                        - -
                        HTH -- Mark D Powell --

                         

                         

                         

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

                          evgenyg wrote:

                           

                          Thanks, never worked with EBS.

                          btw. you suggesting to create index, without knowing any statistics on the "ICX"."DISABLED_FLAG", "ICX"."PSEUDO_FLAG", "ICX"."LAST_CONNECT"

                          don't you think that OP first need to understand what going on? Even if, most probably, the index would be right solution.

                          In general, do we want someone to learn or we want someone to use forum as free consulting services? After all the question in this topic is trivial.

                           

                          Regards

                          The topic may be trivial to you, likely not to the OP. Do you think the OP doesn't know what is going on? I think they know they have a performance problem

                           

                          You originally asked the OP for quite a bit of information, though it's possible the answers would change my answer above I'm playing the odds in that it won't. So yes, we'd like the people asking questions to learn, but I disagree that poking and prodding for every tidbit of information is always necessary. If what I suggested solves the performance problem I would hope the OP would ask for an explanation. If they don't is it our jobs to force the knowledge on them?

                           

                          People who use the forums for free consulting services typically end up running out of benefactors as they are found out; in my experience at least. At the end of the day you're welcome to reply as you wish, please don't try to enforce your preferences on others.

                           

                          Cheers,

                          • 10. Re: Reducing the runtime of a query
                            evgenyg

                            Sure,

                            I had no intention to enforce my preferences on you or any one else, I am really sorry if somehow my writing make this impression.

                            My intention was to ensure that OP  would see basic information about the query and involved tables and then would get the idea what going on, not saying that Mark's point is very valid:  "Sql Plan Directive used for this statement" and not necessarily index would be picked up.  As well in my opinion index creation is big deal, sometimes it would require regression testing and for sure can affect many things and lead to even bigger performance problems (or may be I need to write challenges ?).

                             

                            Reagrds

                            Evgeni

                            • 11. Re: Reducing the runtime of a query
                              evgenyg

                              I forgot to mention that I would never download and open yours attachment (and any other file from not trusted sources) , not saying that such download  is usually blocked if someone reading that forum form work.

                               

                               

                              Regards

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

                                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

                                • 13. Re: Reducing the runtime of a query
                                  AndrewSayer

                                  evgenyg wrote:

                                   

                                  As well I would check out how many times you call: fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') function and if this is really necessary.

                                  I think this is spot on, that full tablescan is taking much longer than I would expect for the amount of buffers it's reading. Googling suggests this is a function (there's a possibility it's just a public associative array, although off the top of my head, I'm not sure it will change anything) and it most likely isn't declared as deterministic so Oracle can't safely decide to avoid executing it multiple times. This is further confirmed by the row source statistics when using the index - we can see reading even less buffers now but it's still taking a long time.

                                   

                                  Instead of just removing it though, you can use a scalar subquery so that it's just called the once:

                                  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 >

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

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

                                   

                                  Considering the amount of rows returned after applying the filters against this table, an index probably will work nicely. I would guess that the most useful filter here (the one that reduces the amount of rows the most) is the last_connect filter, so an index on just that could be appropriate. It's possible that the are lots of rows with a last_connect value in range but with pseudo_flag not being 'N' , if that's the case then adding pseudo_flag to the index (at the beginning as you use an equality predicate against it and a non-equality against the last_connect column) would be helpful. The same thinking can be applied to the disabled_flag, but here because it's an inequality filter you definitely don't want this to be the first column of your index. Have a read of the Golden Rule of Indexing https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexing/ for why it's important.

                                  • 14. Re: Reducing the runtime of a query
                                    evgenyg

                                    Thanks, good point.

                                    Let's see if OP would invest some time in his performance problem and share with us details.

                                    I would love to see 10046 though.

                                     

                                     

                                    Regards

                                    Evgeni

                                    1 2 Previous Next