1 2 3 Previous Next 41 Replies Latest reply on Jan 27, 2020 1:03 PM by Olafur T Branched to a new discussion.

    Performance issues under constant high load

    Olafur T



      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.




        1 2 3 Previous Next