5 Replies Latest reply on Nov 22, 2019 8:50 AM by user8977605

    e:SQLGL:frm:GLXJEENT hang when using table fnd_descr_flex_contexts_vl

    user8977605

      we are seeing CPU utilization for below SQL query and while running the SQL query check to see its using the form as "e:SQLGL:frm:GLXJEENT"

       

      the query was hanging with CPU usage of 200% while running below query:

       

      SELECT /* $Header: fdfdfu.lc 120.26.12010000.25 2017/10/27 14:26:50 tebarnes ship $ */ descriptive_flex_context_code FROM fnd_descr_flex_contexts_vl WHERE application_id = :applid AND descriptive_flexfield_name = :dflex_name AND descriptive_flex_context_name = :refctx_name AND enabled_flag = 'Y'

       

      please help us to fix the issue asap.

        • 1. Re: e:SQLGL:frm:GLXJEENT hang when using table fnd_descr_flex_contexts_vl
          Pennycong-Oracle

          what's the sql_id of this query? please get SQLT XTRACT (Ref. Doc ID 215187.1) for the SQL_ID, and in the output report, it will tell how to tune the performance.

          • 2. Re: e:SQLGL:frm:GLXJEENT hang when using table fnd_descr_flex_contexts_vl
            user8977605

            please find the below details.

             

            sql_id='4nsa99cznr3fx'

            • 3. Re: e:SQLGL:frm:GLXJEENT hang when using table fnd_descr_flex_contexts_vl
              user8977605

              here is the sqlplain for the query :

               

                
              OPERATION OBJECT_NAME OPTIONS CARDINALITY COST
              SELECT STATEMENT
                    1 5
                 
              NESTED LOOPS
                    1 5
                     
              TABLE ACCESS
              APPLSYS.FND_DESCR_FLEX_CONTEXTS_TL BY INDEX ROWID 1 4
                         
              Filter Predicates
                             
              AND
                                 
              T.APPLICATION_ID=TO_NUMBER(:APPLID)
                                 
              T.DESCRIPTIVE_FLEXFIELD_NAME=:DFLEX_NAME
                         
              INDEX
              APPLSYS.FND_DESCR_FLEX_CONTEXTS_TL_N1 RANGE SCAN 2 3
                             
              Access Predicates
                                 
              AND
                                     
              T.DESCRIPTIVE_FLEX_CONTEXT_NAME=:REFCTX_NAME
                                     
              T.LANGUAGE=USERENV('LANG')
                     
              TABLE ACCESS
              APPLSYS.FND_DESCR_FLEX_CONTEXTS BY INDEX ROWID 1 1
                         
              Filter Predicates
                             
              B.ENABLED_FLAG='Y'
                         
              INDEX
              APPLSYS.FND_DESCR_FLEX_CONTEXTS_U1 UNIQUE SCAN 1   
                             
              Access Predicates
                                 
              AND
                                     
              B.APPLICATION_ID=TO_NUMBER(:APPLID)
                                     
              B.DESCRIPTIVE_FLEXFIELD_NAME=:DFLEX_NAME
                                     
              B.DESCRIPTIVE_FLEX_CONTEXT_CODE=T.DESCRIPTIVE_FLEX_CONTEXT_CODE
                 
              Other XML
                     
              {info}
                         
              info type="db_version"
                             
              11.1.0.7
                         
              info type="parse_schema"
                         
              "APPS"
                         
              info type="plan_hash"
                             
              3031669581
                         
              info type="plan_hash_2"
                             
              2716916254
                         
              info type="sql_profile"
                         
              "SYS_SQLPROF_016e16c0b5ac0000"
                         
              {hint}
                             
              USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2")
                             
              LEADING(@"SEL$F5BB74E1" "T"@"SEL$2" "B"@"SEL$2")
                             
              INDEX_RS_ASC(@"SEL$F5BB74E1" "B"@"SEL$2" ("FND_DESCR_FLEX_CONTEXTS"."APPLICATION_ID" "FND_DESCR_FLEX_CONTEXTS"."DESCRIPTIVE_FLEXFIELD_NAME" "FND_DESCR_FLEX_CONTEXTS"."DESCRIPTIVE_FLEX_CONTEXT_CODE"))
                             
              INDEX_RS_ASC(@"SEL$F5BB74E1" "T"@"SEL$2" ("FND_DESCR_FLEX_CONTEXTS_TL"."DESCRIPTIVE_FLEX_CONTEXT_NAME" "FND_DESCR_FLEX_CONTEXTS_TL"."LANGUAGE"))
                             
              OUTLINE(@"SEL$2")
                             
              OUTLINE(@"SEL$1")
                             
              MERGE(@"SEL$2")
                             
              OUTLINE_LEAF(@"SEL$F5BB74E1")
                             
              ALL_ROWS
                             
              DB_VERSION('11.1.0.7')
                             
              OPTIMIZER_FEATURES_ENABLE('9.2.0')
                             
              IGNORE_OPTIM_EMBEDDED_HINTS
              • 5. Re: e:SQLGL:frm:GLXJEENT hang when using table fnd_descr_flex_contexts_vl
                user8977605

                do you have any solution for fixing the issue ?