Announcement

Collapse
No announcement yet.

Shipments with Many Orders and / or Stops

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Shipments with Many Orders and / or Stops

    We are using OTM v5.5.3.6 on a Windows 2003 64 bit platform.

    We recently implemented a new customer that regularly has 20 to 30 orders on the same shipment and frequently has 15-25 stops on the same shipment. We are finding that viewing these shipments in OTM is causing the system to slow down to a halt until the viewing is done. No editing of the shipments is being done - just viewing. Editing is even slower. Also, once the screen for the shipment details is open, we get the same delay and slow down when we try to switch from one tab (eg. Identification) to another (eg. Stops). It can take up to 5 mins sometimes to get the details screen when you click on the shipment you wish to view.

    Is there somewhere to check why this happens? Is this a configuration issue or is there a setting somewhere that allows OTM to perhaps show the initial screen of details to the user while the rest loads in the background?

    Regards,
    CS

  • #2
    Re: Shipments with Many Orders and / or Stops

    Hi Carm,

    It sounds like a performance problem with the database query driving this screen. You can either look in Enterprise Manager to see the top running SQL or you can pull up SQL Statements under Technical Support/Performance Collection (you need to login as DBA.ADMIN to see this menu).

    Thanks,
    Alan

    Comment


    • #3
      Re: Shipments with Many Orders and / or Stops

      Hi Alan...where can I find the Technical Support/Performance Collection in OTM? I'm logged in as DBA.ADMIN but do not see it.

      Comment


      • #4
        Re: Shipments with Many Orders and / or Stops

        Carm,

        Please see the attached screenshot. Also I should mention that the query needs to be running in another window while you do this or it won't show up. What you can then do is to try running it via SQL*Plus with VPD enabled (exec vpd.set_user('the otm user id'); ) you can also grab the explain plan using "set autotrace traceonly explain statistics" setting.

        -Alan
        Attached Files
        Last edited by acuartero; September 23, 2009, 19:46.

        Comment


        • #5
          Re: Shipments with Many Orders and / or Stops

          Hi Alan...I don't think we have that option. Please see attached screenshot.
          Attached Files

          Comment


          • #6
            Re: Shipments with Many Orders and / or Stops

            Carm,

            My apologies. I believe you're correct I didn't notice that you were on CU3 the option is on CU4 and higher (I think).

            In any event all that the screen does is run Eric Rosenbloom's pig SQL below (from his OTM SIG 2008 Presentation). Try running this SQL to find the query.

            -- Eric Rosenbloom's Pig SQL
            select vsa.sql_text,
            (vsa.disk_reads /
            decode (vsa.executions,0,1,vsa.executions) / 50)
            "Est_Response",
            vs.sid, vs.serial#, vs.username
            from v$sqlarea vsa, v$session vs
            where vsa.address = vs.sql_address
            and (vsa.disk_reads / decode
            (vsa.executions,0,1,vsa.executions) / 50) > 30;

            you can add to that vsa.SQL_FULLTEXT if vsa.sql_text does not display the full SQL statement.

            Thanks,
            Alan

            Comment


            • #7
              Re: Shipments with Many Orders and / or Stops

              Hi Alan...I tried to view a couple of different shipments that have a lot of orders on them. For each, I tried to run the SQL you provided but I did not get any results for any of the shipments I tried to view. I ran the SQL while the screen was attempting to load.

              Comment


              • #8
                Re: Shipments with Many Orders and / or Stops

                It looks like a Web Server performance issue and I notice that you are running CU3.
                So, did you follow Metalink Note 471363.1 for Updating Third Party Jar Files For Improved UI Performance?

                The following steps can be used to determine if the new .jar files are installed on your OTM instance. This check should be done on the OTM Web and Application servers.

                cd < OTM Home >/glog/gc3webapp/WEB-INF/lib
                md5sum standard.jar
                cd 3rdparty
                md5sum standard.jar
                md5sum jaxen-full.jar

                The md5sum results should be "fcd8b968265683bafae757f94af19548" for the standard.jar The md5sum results should be "261d1aa59865842ecc32b3848b0c6538" for the jaxen-full.jar

                If your md5sum results do not match the values mentioned previously the new .jar files have not been installed on your OTM instance and you should follow the download and installation instructions provided below.
                --
                Joseph Liang
                MavenWire APAC
                http://www.mavenwire.com/

                Comment


                • #9
                  Re: Shipments with Many Orders and / or Stops

                  Hi Carm,

                  I agree with Joseph you should check to make sure you have the correct jar files. I believe that if that is the culprit one of the symptoms is that you will also see high CPU utilization on the web servers. If you can eliminate them as a possibility that would be helpful.

                  You may also want to turn on the SQL log detail to see if you can capture the query associated with moving between the tabs on the detail screen since you were not able to find it as a top query using the pig SQL query.

                  Thanks,
                  Alan

                  Comment


                  • #10
                    Re: Shipments with Many Orders and / or Stops

                    Thanks to both Joseph and Alan for your suggestions.

                    It turns out that we did not have the updated jar files. I have put them into our TEST servers and I noticed some increase in performance. However, the true test will be in PROD - which we will probably do later today when we schedule an outage.

                    However, I still think it is an issue with the way this latest customer was implemented and configured so I will take Alan's suggestion about sql logging and run a few tests. We have other customers implemented that also have double-digit numbers of orders and stops and they do not seem to have the same issue. I will run my tests over the next couple of days to verify this.

                    Thanks again for your assistance.

                    Carm

                    Comment


                    • #11
                      Re: Shipments with Many Orders and / or Stops

                      Carm,

                      For this customer is there perhaps an unusually large number of child objects (number of ship units or line items for example)? This too could be a contributing factor.

                      Thanks,
                      Alan

                      Comment


                      • #12
                        Re: Shipments with Many Orders and / or Stops

                        Hi Alan, your assumption is correct. This customer does have a large number of ship units per shipment but so do some of our other customers. Although the ship units for this customer are higher than other customers, I do not think it is the major contributor to the slowness it causes. However, it definitely does contribute to the slowness.

                        We will test this later (after our users have finished for the day) by trying this with different customer shipments and checking the logging for each to determine where the differences may be in the way OTM handles the request to view the shipments.

                        Carm

                        Comment


                        • #13
                          Re: Shipments with Many Orders and / or Stops

                          Just wanted to update the thread and thank Alan and Joseph for their help. Updating the JAR files resulted in a noticeable improvement in performance and the screens are appearing much faster now.

                          Comment

                          Working...
                          X