3 Replies Latest reply on Feb 12, 2020 9:07 PM by 960754



      Trying to use ORDS to pull audit information across all DB into a central repo via a define web module in Apex (using ORDS).

      It works - but its REALLY slow due to the pagination. As it executes it many many times on a data dictionary that results in a FTS.

      Its only 5000 rows - but takes (729 *.465082 sec ~  340 seconds)




      The APEX/ORDS  query becomes


      select * from (

      select q_.* , row_number() over (order by 1) RN___ from (

      SELECT MAIN_QUERY.* FROM (select














































      "AUDITVIEW") MAIN_QUERY WHERE  ( ( (timestamp > :1 ) ) )

      ) q_


      where RN___ between :2  and :3      <==== PROBLEM here - results in  700+ exec



      Enter value for _sql_id: 4sma1jjur0wy1

      SQL_ID                        : 4sma1jjur0wy1

      FIRST_LOAD_TIME               : 2020-02-04/10:57:04

      PLAN_HASH_VALUE               : 3516423120

      EXECUTIONS                    : 729

      PX_SERVERS_EXECUTIONS         : 0

      BUFFER_GETS                   : 61554771

      BGPE                          : 84437.27

      ROWSPROC                      : 27.01

      MODULE                        : Oracle REST Data Services

      SEC                           : .465082

      LAST_LOAD_TIME                : 2020-02-04/10:57:04

      PROGRAM_ID                    : 0

      SQL_PROFILE                   :

      SQL_PLAN_BASELINE             :

      SQL_PATCH                     :

      PROGRAM_ID                    : 0

      LAST_ACTIVE_TIME              : 04-feb-2020 11:09:57

      SQL_PLAN_BASELINE             :

      CHILD_NUMBER                  : 0

      IS_BIND_SENSITIVE             : N

      IS_BIND_AWARE                 : N

      SQL_TEXT                      : select * from ( select q_.* , row_number() over (order by 1) RN___ from ( SELECT MAIN_QUERY.* FROM (select  "OS_USERNAME",  "USERNAME",



      How do I alter the pagination to just say GO FETCH all rows - or a larger set each time.

        • 1. Re: ORDS SLOW

          When submitting the GET request, you can add ?limit=10000 to the end of your URL to allow 10000 items to be returned.




          When setting up the service using ORDS.DEFINE_HANDLER or ORDS.DEFINE_SERVICE you can set the parameter p_items_per_page to 0 for an unlimited number of records to be returned at a time or specify a number that you would like to be returned per page.

          • 2. Re: ORDS SLOW

            Im not sure about the first part in terms of passing that on each and every call .....


            The only control on the IG is here - but doesnt seem to really help (below screenshot).

            Actually did some more digging and I think the pagination is obviously an issue - but also is the fact that the predicate (filter by) in the report is not passed through to runtime SQL



            So need to figure out how to parameterize that and pass it to the runtime webcall rest module.


            • 3. Re: ORDS SLOW

              Anybody ?

              Taking ideas