3 Replies Latest reply on Apr 8, 2020 1:40 PM by user4472267

    Very Slow REST response

    user4472267

      Hello,

       

      I need help and I would appreciate if you take a consider my problem and give me some advice about how to solve it.

       

      I installed standalone ORDS using SQL Developer and successfully run the service and get metadata catalog.  Metadata Catalog is retrieved within 1-2 sec in browser and in curl app. and that's fine. My problem is in retrieving data from custom tables (on same db). For example table items in user schema dme with 4 rows needs about 2-3 minutes to give response and displayed data on browser, or curl.

      Either the first, second or third run, calling service via browser or curl, each takes 2-3 minutes to complete the activity and give back the response.

       

       

      Environment details:

       

      SQL Developer (Version 19.4.0.354)

      ORDS (ords-19.4.0.352.1226)

      Oracle Database 11g Express Edition Release 11.2.0.2.0

       

       

      Thanks

       

      KZ

        • 1. Re: Very Slow REST response
          thatJeffSmith-Oracle

          your sql is bad, or your db needs tuned

           

          i'd start with a session trace/real time sql monitoring investigation...assuming the time being spent is in the database - always check your SQL first. slow sql will always kill the response time

           

          we'd need to know more about your scenario to know for sure what's going on

          • 2. Re: Very Slow REST response
            user4472267

            Hi,

             

            Thank you for you time. Please can you look images in attachment.

             

            File with sql from session  (There is one SQL which was active during REST execution)

             

            And file XE_ora_1410.trc. (My gaze stopped at text which begins with: minimum sleep is 146 usecs - and I don't know what does this mean).

             

            After that I run the the service several times (http://localhost:8085/ords/dme/items/) and always takes about 1min & 30s to complete.

             

            During the creation of REST services for table items I used RESTful Services wizard. I didn't write any SQL

            by my self.

             

            Yesterday I finished with statistics by running:

             

            exec dbms_stats.gather_database_stats;

            -- PL/SQL procedure successfully completed.

             

            exec dbms_stats.gather_dictionary_stats;

            -- PL/SQL procedure successfully completed.

             

             

            Can I do anything more to get this things work?

             

            Thanks

             

            KZ

             

            XE_ora_1410trc.png

            sqlFromSession.png

            • 3. Re: Very Slow REST response
              user4472267

              Hi, Mr. Smith,

               

              Thanks to your hint about db tuning and additional investigation from my side,  I found the solution:

               

              exec dbms_stats.gather_fixed_objects_stats;

              -- PL/SQL procedure successfully completed.

               

              service oracle-xe stop

              service oracle-xe start

               

              ... and works perfectly.

               

               

              Thanks a lot

              br

               

              KZ

              1 person found this helpful