6 Replies Latest reply: May 22, 2013 7:26 PM by 1008459 RSS

    Too many rows hanging the DB or APP server not sure ?

    1008459
      Hello

      On post staging server, query of 10,000 rows of data extraction on a DB is fine but on a Websphere application server HTML, it loads after long time, So when changed the query to result in 1000 rows then it loads faster. Performance issue?

      Is it at the application server level or DB ? Perhaps! Pagination will help.
      Could be caching issue on application server ?

      Any ideas guys to resolve this ?

      Note: Staging and Post staging servers have idential code. Oracle 10g, IBM AIX
        • 1. Re: Too many rows hanging the DB or APP server not sure ?
          sb92075
          Ranker wrote:
          Hello

          On post staging server, query of 10,000 rows of data extraction on a DB is fine but on a Websphere application server HTML, it loads after long time, So when changed the query to result in 1000 rows then it loads faster. Performance issue?

          Is it at the application server level or DB ? Perhaps! Pagination will help.
          Could be caching issue on application server ?

          Any ideas guys to resolve this ?

          Note: Staging and Post staging servers have idential code. Oracle 10g, IBM AIX
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: Too many rows hanging the DB or APP server not sure ?
            ji li
            Start by isolating the problem.

            Open SQL*Plus session on database server, set timing on, set autotrace on, and execute query.
            Compare timing results with doing it from your websphere server.
            Compare timing results with doing it from your browser connected to the application (on the websphere server).

            Be sure to flush the buffer cache between each test, otherwise, data will already be in SGA and will return much faster than normally when it would have to read it from disk.

            This will at least isolate where the problem is.

            Edited by: ji li on May 17, 2013 2:04 PM
            • 3. Re: Too many rows hanging the DB or APP server not sure ?
              1008459
              Hi Li

              Thank you, I see what you are saying, need explanation

              Compare timing results with doing it from your websphere server. - you mean webserver to the browser directly without application server ?
              Compare timing results with doing it from your browser connected to the application (on the websphere server). - routine test ?

              Be sure to flush the buffer cache between each test, otherwise, data will already be in SGA and will return much faster than normally when it would have to read it from disk. - how to flush the cache ?
              • 4. Re: Too many rows hanging the DB or APP server not sure ?
                ji li
                Hi Ranker.

                Here are answers to your questions:

                Q. Compare timing results with doing it from your websphere server. - you mean webserver to the browser directly without application server ?

                A. yes, or if you have a separate application server, you can run the test from there also. In this case, assuming you have an Oracle client installation on your WebSphere server, you can open a SQLPLUS session and connect to the database (that I assume resides on a different server). Again, set timing on, set autotrace, and execute the same query. With timing on, it will give you an "Elapsed: ..." time that you can compare to what you ran on the database server. If you do not have the Oracle client installed on the websphere server, than you can not run this test, but I believe WebSphere requires Oracle client on the WebSphere server. (Its been a while since I've installed WebSphere.)

                Q. Compare timing results with doing it from your browser connected to the application (on the websphere server). - routine test ?

                A. In this case, this is a little trickier since you will actually use your regular browser to perform the function that calls the query. In this case, you will have to do a manual timing of the test (using your watch or something like that). But yes, a routine test.

                So when you are done, you should have three (or four) times: One from the database server, possibly one from your separate application server, one from the WebSphere server, and one from your standard client (PC/Laptop) browser. If the time it takes to execute the query on the database server is very fast, then you know the problem is not within the database or its configuration or need of sql tuning.

                Q. Be sure to flush the buffer cache between each test, otherwise, data will already be in SGA and will return much faster than normally when it would have to read it from disk. - how to flush the cache ?

                Before each test, as sysdba, run the following two commands:

                SQL> alter system flush buffer_cache;

                This will flush any residual data blocks in the buffer cache from previous runs against this table.
                You can also flush shared_pool as well by a similar command.
                By flushing the shared_pool, you will flush any execution plans that may already be resident in the shared_pool.

                SQL> alter system flush SHARED_POOL;

                Hope this helps.
                • 5. Re: Too many rows hanging the DB or APP server not sure ?
                  1008459
                  Yes, I understand now and hope it helps :)

                  Thank you
                  • 6. Re: Too many rows hanging the DB or APP server not sure ?
                    rp0428
                    >
                    On post staging server, query of 10,000 rows of data extraction on a DB is fine but on a Websphere application server HTML, it loads after long time, So when changed the query to result in 1000 rows then it loads faster. Performance issue?
                    >
                    Hmm - no. It takes longer to load more data.

                    How are 10,000 rows of html data even useful to anyone?