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

    ORDS SLOW

    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

      "OS_USERNAME",

      "USERNAME",

      "USERHOST",

      "TERMINAL",

      "TIMESTAMP",

      "OWNER",

      "OBJ_NAME",

      "ACTION",

      "ACTION_NAME",

      "NEW_OWNER",

      "NEW_NAME",

      "OBJ_PRIVILEGE",

      "SYS_PRIVILEGE",

      "ADMIN_OPTION",

      "GRANTEE",

      "AUDIT_OPTION",

      "SES_ACTIONS",

      "LOGOFF_TIME",

      "LOGOFF_LREAD",

      "LOGOFF_PREAD",

      "LOGOFF_LWRITE",

      "LOGOFF_DLOCK",

      "COMMENT_TEXT",

      "SESSIONID",

      "ENTRYID",

      "STATEMENTID",

      "RETURNCODE",

      "PRIV_USED",

      "CLIENT_ID",

      "ECONTEXT_ID",

      "SESSION_CPU",

      "EXTENDED_TIMESTAMP",

      "PROXY_SESSIONID",

      "GLOBAL_UID",

      "INSTANCE_NUMBER",

      "OS_PROCESS",

      "TRANSACTIONID",

      "SCN",

      "SQL_BIND",

      "SQL_TEXT",

      "OBJ_EDITION_NAME",

      "DBID",

      "RLS_INFO",

      "CURRENT_USER"

      from

      "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
          EJ-Egyed

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

           

          Or

           

          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
            960754

            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
              960754

              Anybody ?

              Taking ideas