1 2 3 Previous Next 39 Replies Latest reply on Nov 28, 2019 6:18 PM by Olafur T Branched to a new discussion.

    Performance issues under constant high load

    Olafur T

      Hey,

       

      I'm running a small test server (8 cores) and running load tests. This SQL is constantly 95%+ the biggest consumer:

       

      select module_id, template_id, base_path, pattern, priority, privilege_name, upper(method) method, handler_id, source_type, items_per_page, pre_hook, source source_head
        from user_ords_services
       where status = 'PUBLISHED'
         and instr(:1, replace('/' || trim(both '/' from nvl(base_path, '')) || '/', '//', '/'), 1) = 1
       order by module_id, template_id, handler_id
      

       

      Ran statistics on the ORDS_METADATA schema, database statistics, etc..nothing helps.

       

      I restart the database and it will run fine with no problems to begin with. A half a day into load tests, the concurrency waits go through the roof and everything points to this SQL which is based on a view that is based on a view.

       

      Perhaps my setup is wrong, I installed ORDS straight to the PDB and not the CDB (user ORDS_METADATA doesn't exist in the CDB) and I see that one of the views is joining SYS.user_procedures (shouldn't be a problem).

       

      Any indexes I can make to speed up this view, it seems to be run with every single ORDS request.

       

      Regards

      Oli

        1 2 3 Previous Next