1 2 3 4 Previous Next 57 Replies Latest reply on Sep 14, 2020 8:56 AM by Srini.S Go to original post
      • 45. Re: Performance issues with APEX page while running
        fac586

        Are all of the objects referenced in the query standard eBS components or are there custom tables/views involved?

        • 46. Re: Performance issues with APEX page while running
          Srini.S

          Are all of the objects referenced in the query standard eBS components or are there custom tables/views involved?

           

          All are eBS.objects.

          • 47. Re: Performance issues with APEX page while running
            Srini.S

            Conclusion:
            There is a lot of tuning potential for this query. However it is not clear if the speed of this query is directly connected to the APEX issue that you experience.

            There seems to be some additional problem with it (maybe when the results are loaded into an APEX collection, something breaks).

            Thanks a lot. This is a quite a good homework for me.. Let me see what can be tried out.

             

            So far as dates are concerned, there are records that don't have start date populated and are considered active and so NVL() has to be used...No shortcut there.

            • 48. Re: Performance issues with APEX page while running
              Srini.S

              When that happens again, could you test to shut down and restart ORDS and see if anything changed?

               

              In general locks on session level are to be expected if something is running. It only becomes a problem when other sessions want to get the same locks (blocking lock situation).
              WAIT events are what you should watch out for. However it might be difficult to find the session that is causing the waits in the first place.

              If you are on an enterprise edition of the database, ask your DBA to run an ASH report while the problem appears.

               

              Will try this too...thank you.

              • 49. Re: Performance issues with APEX page while running
                fac586

                Srini.S wrote:

                 

                With the region disabled (that has the above query), the debug from a calling page looks like below. Page opened and the displayed the region#1 without any problem. I have two debug files created.

                 

                There are still things going on here that I don't understand.

                 

                ElapsedExecutionMessageLevel
                0.002260.00105Reset NLS settings4
                0.003310.00043alter session set NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'4
                0.003740.00004...NLS: Set Decimal separator="."4
                0.003770.00014...NLS: Set NLS Group separator=","4
                0.003910.00004...NLS: Set g_nls_date_format="DD-MON-RR"4
                0.003950.00006...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"4
                0.004010.00069...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"4
                0.00470.00008...Setting session time_zone to -04:004
                0.004790.00159R E Q U E S T show4
                0.006380.00045Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: en4
                0.006820.00005alter session set nls_language='AMERICAN' nls_territory='AMERICA'4
                0.006870.00011NLS: CSV charset=WE8MSWIN12524
                0.006980.00003...NLS: Set Decimal separator="."4
                0.007010.00012...NLS: Set NLS Group separator=","4
                0.007130.00004...NLS: Set g_nls_date_format="DD-MON-RR"4
                0.007170.00004...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"4
                0.007210.00007...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"4
                0.007280.00004NLS: Language=en4
                0.007320.00058Application 100, Page Template: 149938090300443584

                 

                Why are we seeing a debug log for application 100 when a post above indicates that the application ID is 196?

                 

                0.017880.00055Show page template body4
                0.018430.00029Load JavaScript file=/i/libraries/apex/widget.treeView.js?v=18.1.0.00.454
                0.018720.00055SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.019270.00009......Result = true4
                0.019360.00037SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.019740.00009......Result = true4
                0.019820.0003SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.020130.00008......Result = true4
                0.020210.0003SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.020510.0001......Result = true4
                0.020610.00029SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.02090.00008......Result = true4
                0.020980.00031SQL Statement prepared: line count=1, first index=1, last index=1, characters=61, found binds=14
                0.02130.00008......Result = true4
                0.021380.00032SQL Statement prepared: line count=1, first index=1, last index=1, characters=61, found binds=14
                0.02170.00009......Result = true4
                0.021780.0003SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14
                0.022080.00008......Result = true4
                0.022170.00029SQL Statement prepared: line count=1, first index=1, last index=1, characters=68, found binds=14

                 

                Any idea what the SQL statements referred to are?

                 

                Are there 9 instances of something in the application?

                 

                0.025340.00011Evaluate which regions should be rendered for display point BODY4
                0.025450.00001Render regions4
                0.025460.00012...Region: Duplicate CMPN4
                0.025580.00022...region is nocache - do not cache4
                0.02580.00238......No page items/buttons to render4
                0.028180.00002Generated SQL query is:4
                0.02820.00002select i.* from (4
                0.028220.00001select "ITEM1","DESCRIPTION1","MPN1","MANUFACTURER1","CMPN1","ITEM2","DESCRIPTION2","MPN2","MANUFACTURER2","CMPN2","ITEMID1","ITEMID2"from ((4
                0.028230.00004select /*+ qb_name(apex$inner) */d."ITEM1",d."DESCRIPTION1",d."MPN1",d."MANUFACTURER1",d."CMPN1",d."ITEM2",d."DESCRIPTION2",d."MPN2",d."MANUFACTURER2",d."CMPN2",d."ITEMID1",d."ITEMID2" from (4
                0.028270.00001WITH Q1 AS ( SELECT MSI1.INVENTORY_ITEM_ID ITEMID1, MSI1.SEGMENT1 ITEM1, DESCRIPTION DESCRIPTION1, --MSI1.EAM_ITEM_TYPE EAM1, ( SELECT EFP.GEEGO_FIMS_MFG_PART_NUMBER FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND MSI1.ORGANIZATION_ID = 83 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID AND ROWNUM = 1 ) MPN1, ( SELECT EFP.GEEGO_MANUFACTURER FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND ROWNUM = 1 AND MSI1.ORGANIZATION_ID = 83 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID ) MANUFACTURER1, ( SELECT EFP.GEEGO_CONVERTED_MPN FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND ROWNUM = 1 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID AND MSI1.ORGANIZATION_ID = 83 ) CMPN1FROM APPS.MTL_SYSTEM_ITEMS_B MSI1, APPS.MTL_PARAMETERS MP1WHERE MP1.ORGANIZATI~4
                0.028280.00001) d4
                0.028290.00001)) i4
                0.02830.00001) i where 1=14
                0.028310.00009order by "ITEM1" asc nulls last4
                0.02840.00001Rewrite SQL query to:4
                0.028420.00001select a.*,row_number() over (order by null) apx$rownum from(4
                0.028430.00001select i.* from (4
                0.028440.00001select "ITEM1","DESCRIPTION1","MPN1","MANUFACTURER1","CMPN1","ITEM2","DESCRIPTION2","MPN2","MANUFACTURER2","CMPN2","ITEMID1","ITEMID2"from ((4
                0.028450.00004select /*+ qb_name(apex$inner) */d."ITEM1",d."DESCRIPTION1",d."MPN1",d."MANUFACTURER1",d."CMPN1",d."ITEM2",d."DESCRIPTION2",d."MPN2",d."MANUFACTURER2",d."CMPN2",d."ITEMID1",d."ITEMID2" from (4
                0.028490.00001WITH Q1 AS ( SELECT MSI1.INVENTORY_ITEM_ID ITEMID1, MSI1.SEGMENT1 ITEM1, DESCRIPTION DESCRIPTION1, --MSI1.EAM_ITEM_TYPE EAM1, ( SELECT EFP.GEEGO_FIMS_MFG_PART_NUMBER FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND MSI1.ORGANIZATION_ID = 83 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID AND ROWNUM = 1 ) MPN1, ( SELECT EFP.GEEGO_MANUFACTURER FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND ROWNUM = 1 AND MSI1.ORGANIZATION_ID = 83 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID ) MANUFACTURER1, ( SELECT EFP.GEEGO_CONVERTED_MPN FROM APPS.EGO_FIMS_PART_AGV EFP WHERE MSI1.INVENTORY_ITEM_ID = EFP.INVENTORY_ITEM_ID AND ROWNUM = 1 AND MSI1.ORGANIZATION_ID = EFP.ORGANIZATION_ID AND MSI1.ORGANIZATION_ID = 83 ) CMPN1FROM APPS.MTL_SYSTEM_ITEMS_B MSI1, APPS.MTL_PARAMETERS MP1WHERE MP1.ORGANIZATI~4
                0.028510.00003) d4
                0.028530.00001)) i4
                0.028540.00001) i where 1=14
                0.028550.00001order by "ITEM1" asc nulls last4
                0.028573.44577)a4
                3.474340.01868rows loop: 15 row(s)4

                 

                This looks a lot like rendering of the problem report region. Was it successfully rendered on this occasion?

                 

                If not, what exactly is this region?

                 

                3.493020.00049Load JavaScript file=/i/libraries/apex/widget.report.js?v=18.1.0.00.454
                3.49350.00003Evaluate which sub regions should be rendered4
                3.493540.00015...No sub regions to render4
                3.493690.00002Evaluate which regions should be rendered for display point BODY_14
                3.493710.00002...No regions to render4
                3.493720.00001Evaluate which regions should be rendered for display point BODY_24
                3.493740.00006...No regions to render4
                3.493790.00002......No page items/buttons to render4
                3.493810.00001Evaluate which regions should be rendered for display point BODY_34
                3.493820.00004...No regions to render4
                3.493870.00064Evaluate which regions should be rendered for display point REGION_POSITION_054
                3.49450.00004Render regions4
                3.494540.00011...Region: User Information4
                3.494650.00055...region is nocache - do not cache4

                 

                This appears to be the second (static content?) region...

                3.495210.00029......Render page items and buttons4
                3.49550.00018Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_1" because the first region positioned at a grid column defines the span.2
                3.495670.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_1" because the first region positioned at a grid column defines the span.2
                3.495830.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_2" because the first region positioned at a grid column defines the span.2
                3.495990.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_2" because the first region positioned at a grid column defines the span.2
                3.496150.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_3" because the first region positioned at a grid column defines the span.2
                3.49630.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_3" because the first region positioned at a grid column defines the span.2
                3.496450.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_4" because the first region positioned at a grid column defines the span.2
                3.496620.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_4" because the first region positioned at a grid column defines the span.2
                3.496770.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_5" because the first region positioned at a grid column defines the span.2
                3.496920.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_5" because the first region positioned at a grid column defines the span.2
                3.497070.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_6" because the first region positioned at a grid column defines the span.2
                3.497230.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_6" because the first region positioned at a grid column defines the span.2
                3.497380.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_7" because the first region positioned at a grid column defines the span.2
                3.497540.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_7" because the first region positioned at a grid column defines the span.2
                3.497690.00016Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_8" because the first region positioned at a grid column defines the span.2
                3.497850.00015Grid Column Span 4 ignored for component "P0_PAGE_ACCESS_LEVEL_DESC_8" because the first region positioned at a grid column defines the span.2
                3.498010.00015Grid Column Span 4 ignored for component "P0_DELEGATED_LEVEL_1" because the first region positioned at a grid column defines the span.2
                3.498160.00612Grid Column Span 4 ignored for component "P0_DELEGATED_LEVEL_DESC_1" because the first region positioned at a grid column defines the span.2

                 

                I have never seen messages like these before (and search results indicate that no one else has either).

                 

                What are the layout settings for these items, their container region, and any other region that might be considered the "first region positioned at a grid column" that would affect this, whether on the global page or the page under investigation?

                • 50. Re: Performance issues with APEX page while running
                  Sven W.

                  Richard Legge wrote:

                   

                  Quite possible. But does a refresh create a parallel connection or refreshes the same connection? I am not basically knowledgeable more on these aspects, so interested to know. If multiple connections are created and they run forever, is it possible to put a restriction on that? Like, unless previous connection is complete or results are out, you don't allow to send another request that will go create another unending request?

                   

                  Assuming you are using ORDS, each request would use one of a pool of connections. so each subsequent request may use the same, or a different connection.

                   

                  is it possible to put a restriction on that? Like, unless previous connection is complete or results are out, you don't

                   

                  Not that I'm aware of. Unless you have a refresh button that you disable when you click it. But ideally you want to be solving the hanging problem, not trying to put a plaster on it... Can you reproduce it in a test environment? Can you change the query so that it returns quicker. What does the explain plan look like (shre on here) As mentioned, unless you put the query on here, we can't get a sense of why it would perform badly in an interactive report..

                  Fully agree.

                   

                  Assuming that the Query is very slow, a user that will reload the page will be almost guaranteed to get a new session from the ORDS connection pool, since the old session is still actively running the statement.
                  Assuming also that multiple versions of running the same statement will compete for some DB ressources (maybe IO, CPU or temp space), the available ORDS sessions will quickly all be running the same statement and the whole APEX environment might become very unresponsive.

                   

                  Solution: use ressource manager (if the license is available) to stop longrunning sql and limit ressource AND tune the query.

                  1 person found this helpful
                  • 51. Re: Performance issues with APEX page while running
                    Srini.S

                    I have never seen messages like these before (and search results indicate that no one else has either).

                     

                    What are the layout settings for these items, their container region, and any other region that might be considered the "first region positioned at a grid column" that would affect this, whether on the global page or the page under investigation?

                     

                     

                    Thanks again for taking pain to go through all the information provided. Probably I need to give some more background for better clarity.

                    1. Application #100 page#27 is the actual page that we have problem with where we had a region originally with one report and that worked well for sometime. We added a second region recently and that is when all this problem started.

                     

                    2. In order to ensure that the application is not the problem, this page was copied to application #196 as page #39 and was tried there as well.

                     

                    3. P0 is a global page where we load user access levels and all page items in that page (up to  9 levels a user may have) appear in all the other pages (as a standard functionality of global page).

                     

                    4. When the problematic page was loaded in stages as suggested by you by first making the second region as NEVER and only first region made to work and debug taken, it worked and gave debug messages as well and I uploaded those here.

                     

                    5. When the same page is called again with second (problematic) region enabled back and debug initiated, page never got loaded as usual and debug messages may not have captured anything about this region since after sometime when page did not load, I closed the page and pulled debug messages.

                     

                     

                    Additionally, whenever the page is left for long waiting to be loaded, environment seems to be going down with ORDS error and we are approaching sysadmins to restart the APEX (or may be ORDS restart , I am not sure). This has happened for at least 5 or 6 six times now. So, in order not to see an ORDS error issue and spending time coordinating with sysadmins, after page does not load for sometime, we are closing the page. We are also running the query from sql developer to check for locks and we find one or two records there and we kill those to start re-testing all over again.

                     

                     

                    Hope this gives some more clarity.

                    • 52. Re: Performance issues with APEX page while running
                      Srini.S

                      Fully agree.

                       

                      Assuming that the Query is very slow, a user that will reload the page will be almost guaranteed to get a new session from the ORDS connection pool, since the old session is still actively running the statement.
                      Assuming also that multiple versions of running the same statement will compete for some DB ressources (maybe IO, CPU or temp space), the available ORDS sessions will quickly all be running the same statement and the whole APEX environment might become very unresponsive.

                       

                      Solution: use ressource manager (if the license is available) to stop longrunning sql and limit ressource AND tune the query.

                       

                      The unresponsive environment seems to be happening even if we don't reload, rather just leaving the page to be loaded for some time. But the query apparently is the cause from the many inputs that I can see from previous posts.

                       

                      All that I can't understand is : is it possible that a query yields results in the SQL workshop but fails with a page? I am guessing that SQL workshop and page run as the same user (APEX_PUBLIC_USER)?

                      • 53. Re: Performance issues with APEX page while running
                        Srini.S

                        Any idea what the SQL statements referred to are?

                         

                        Are there 9 instances of something in the application?

                        Not sure, but may be the global page trying to get the user's access levels using the custom repetitive query that we have as the user logs in.( I made a mention of this in one of previous posts just now)

                         

                         

                        This looks a lot like rendering of the problem report region. Was it successfully rendered on this occasion?

                         

                        If not, what exactly is this region?

                        It was rendered and that is the first region that we used for sometime successfully, having almost the same query but for a different check with slightly different information being queried. The moment we have second region also added, we have the problem started.

                        • 54. Re: Performance issues with APEX page while running
                          AndyH

                           

                          Additionally, whenever the page is left for long waiting to be loaded, environment seems to be going down with ORDS error and we are approaching sysadmins to restart the APEX (or may be ORDS restart , I am not sure). This has happened for at least 5 or 6 six times now. So, in order not to see an ORDS error issue and spending time coordinating with sysadmins, after page does not load for sometime, we are closing the page. We are also running the query from sql developer to check for locks and we find one or two records there and we kill those to start re-testing all over again.

                           

                           

                          Hope this gives some more clarity.

                          Closing the page will have no impact on the queries - once the page starts rendering the query will continue.

                           

                          What is creating locks? A report/select statement should not create locks.

                          1 person found this helpful
                          • 55. Re: Performance issues with APEX page while running
                            Richard Legge

                            What happens if you have a 'clean page', with just his one query on it? Maybe even create a test application with nothing on page zero. Does it run then?  You need to eliminate whether it is just the query that is causing the issue, or a combination of things.  Do you / sysadmins have access to htop on linux?  One of the things to look for when something performing badly is whether a APEX (ords) process is sitting at 100% and for how long..

                            1 person found this helpful
                            • 56. Re: Performance issues with APEX page while running
                              Sven W.

                              Srini.S wrote:

                               

                              ...All that I can't understand is : is it possible that a query yields results in the SQL workshop but fails with a page? I am guessing that SQL workshop and page run as the same user (APEX_PUBLIC_USER)?

                              Yes this is possible. Mostly because APEX adds additional code around your select statement. So the two queries can be slightly different. The additional code might change the execution plan of the query. When you monitor sessions an DB level you should be able to see which query the DB is currently running.

                               

                              Try to deactivate pagination for example. Adding the logic for pagination is a common performance issue for queries that return many rows and are slow.
                              I still think the main issue is the slow query.

                              1 person found this helpful
                              • 57. Re: Performance issues with APEX page while running
                                Srini.S

                                Richard Legge    fac586     jariola     Sylvain Martel    Sven W.    AndyH

                                 

                                Thanks for all your responses so far. Quite a few learning for me , yet, again. I have requested my sysadmin team to look into this as well to further help me. Once I get to some solution, will come back and update here.

                                 

                                Regards

                                Srini

                                1 2 3 4 Previous Next