1 Reply Latest reply on Mar 30, 2020 9:35 PM by John Thorton

    Performance issue in a relatively small table

    franklin.jr

      I have an annoying issue regarding a query in Oracle APEX. My table is relatively small (50k rows) but its related Oracle APEX page is taking longer than desired to refresh and navigate through pages. I found the following warning while debugging my Oracle APEX page: 'The database has "max_string_size" set to "EXTENDED": Disabling bulk fetches.', but due to current Oracle online cloud database version, documentation says I cannot "downgrade" database settings from EXTENDED back to STANDARD, and somehow I think this would not be a good idea even if possible. Therefore I ask you some help on how this performance issue can be solved; I think it's not related to Oracle APEX but rather a SQL performance issue. I think of creating a PL/SQL pipelined function with bulk collect, but other ideas are welcome. Regards, Franklin

        • 1. Re: Performance issue in a relatively small table
          John Thorton

          franklin.jr wrote:

           

          I have an annoying issue regarding a query in Oracle APEX. My table is relatively small (50k rows) but its related Oracle APEX page is taking longer than desired to refresh and navigate through pages. I found the following warning while debugging my Oracle APEX page: 'The database has "max_string_size" set to "EXTENDED": Disabling bulk fetches.', but due to current Oracle online cloud database version, documentation says I cannot "downgrade" database settings from EXTENDED back to STANDARD, and somehow I think this would not be a good idea even if possible. Therefore I ask you some help on how this performance issue can be solved; I think it's not related to Oracle APEX but rather a SQL performance issue. I think of creating a PL/SQL pipelined function with bulk collect, but other ideas are welcome. Regards, Franklin

          If you have NO idea why things are slow today, how did you decide or conclude that a pipeline function with bulk collect might improve this mystery?

          As a general Pl/SQL will be slower than plain SQL, because only SQL can directly interact with database content; while PL/SQL only  adds overhead.

           

          Please click on URL & provide requested details

           

          How to  improve the performance of my query? / My query is running slow.

          1 person found this helpful