1 2 Previous Next 22 Replies Latest reply: Dec 4, 2012 3:11 PM by 945687 RSS

    inserts/updates sporadically take 10min-2hrs instead of normal run in secs

    945687
      Basic database / table info:*

      Oracle DB Version= Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      Table in question= Custom table in custom schema, <10k records, no custom indexes, no triggers, 56 columns, LOGGING=YES

      Issue_
      We have a 3rd party java application that does sql inserts / updates / selects against the described custom table via custom procedure calls. Under normal circumstances, the sql inserts / update / selects complete successfully in mere seconds. However, seemingly sporadically ( at a high level ), the inserts / updates will take > 10 minutes to complete. The 3rd party java application currently has a time-out value set at 10 minutes for the given insert / update. The insert / update takes > 10 minutes, the application then times out and the insert / update never occurs. The application has sufficient error handling and it proceeds to try and redo the insert / update. Eventually it will complete and when it does, it only takes seconds. However this cycle of 10 minutes of time-out can go on for 2 to 3 hours some times.


      The insert / update is only performing 1 record insert / update. The custom procedure doing the inserts / updates are very basic and just make sql insert / update calls with basic error handling.


      Two 3rd party java applications do write to same table at the same times. However, we have similar situations on similar custom tables and only 1 java application writes to that table.


      Not to further complicate things, but just for more information, sometimes when we are executing a concurrent program via sql call in a custom procedure, we get the same timeout for a span of time and then it executes successfully in seconds per normal.


      Issue seems related to database load, as we cannot replicate this issue on the DEV / QA databases ( which match prod version ).   Any ideas?


      Don't want to overload with information on first post. Please let me know of any further info that might be helpful.
        • 1. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
          sb92075
          Brandon Barker wrote:
          Basic database / table info:*

          Oracle DB Version= Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
          Table in question= Custom table in custom schema, <10k records, no custom indexes, no triggers, 56 columns, LOGGING=YES
          how does custom schema differ from non-custom schema?
          how does custom index differ from non-custom index
          It would have made discussion easier if you had posted
          CREATE TABLE statement for this table.

          Is this table involved with any PK/FK relationship?
          Issue_
          We have a 3rd party java application that does sql inserts / updates / selects against the described custom table via custom procedure calls. Under normal circumstances, the sql inserts / update / selects complete successfully in mere seconds. However, seemingly sporadically ( at a high level ), the inserts / updates will take > 10 minutes to complete. The 3rd party java application currently has a time-out value set at 10 minutes for the given insert / update. The insert / update takes > 10 minutes, the application then times out and the insert / update never occurs. The application has sufficient error handling and it proceeds to try and redo the insert / update. Eventually it will complete and when it does, it only takes seconds. However this cycle of 10 minutes of time-out can go on for 2 to 3 hours some times.
          Do SELECT against table timeout?
          Or only DML timeout?

          Do DML against table also include SELECT clause?

          Do SQL that timeout utilize bind variables?
          Can you enable SQL_TRACE to see where time it actually being spent?
          • 2. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
            945687
            how does custom schema differ from non-custom schema?
            how does custom index differ from non-custom index
            It would have made discussion easier if you had posted
            CREATE TABLE statement for this table.
            I used the word custom to state that it was a table we have made in a schema we have made and NOT one made by Oracle EBS which is the primary / sole application on this Oracle database.
            I can certainly provide the create table statement if you would like, but since Oracle concurrent program executions give the same time-out sporadically, it leans us to believe it might not be an issue inherent to thse tables.

            Perhaps something to do with the number of sessions we are spawning with the same user for the various different java programs? I have looked at a variety of values / related values that are mentioned in google searches and we don't seem to be exceeding them so far from what I can tell.
            Is this table involved with any PK/FK relationship?
            Negative.
            Do SELECT against table timeout?
            Or only DML timeout?
            Only insert and update statements have been observed to give time-outs along with sporadic oracle concurrent program executions.
            Do DML against table also include SELECT clause?
            Negative.
            Do SQL that timeout utilize bind variables?
            Negative. although I don't know what the Oracle EBS sql procedures that we use to fire off Oracle concurrent programs / requests sets do internally:

            fnd_submit.set_request_set
            fnd_submit.submit_program
            fnd_submit.submit_set
            Can you enable SQL_TRACE to see where time it actually being spent?
            We can, I will see what hoops we need to jump through in the prod system to get this rolling.
            • 3. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
              Nikolay Savvinov
              Hi Brandon,

              1) query DBA_HIST_SQLSTAT to see if there are any clues such as:
              a) correlation between plan change and performance degradation
              b) dramatic change in number of rows processed, associated with performance degradation
              2) query ASH to see what kind of wait events the session is going through when the statements are slower than usual

              Best regards,
              Nikolay
              • 4. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                damorgan
                Step 1: Determine whether the wait is in the database or the application middleware.

                From what you have posted so far there is no information indicating that this is a database issue.
                • 5. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                  BrendanP
                  Further to some of the other comments, it's presumably a multi-user computer, so you might want to establish what else is going on when your process slows down - find out the CPU breakdown by process...
                  • 6. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                    Sven W.
                    Brandon Barker wrote:

                    Issue seems related to database load, as we cannot replicate this issue on the DEV / QA databases ( which match prod version ).   Any ideas?


                    Don't want to overload with information on first post. Please let me know of any further info that might be helpful.
                    it looks a little like sometimes you get ressource conficts. But it is hard to identify which is the blocking/restricting ressource. A good dba might be able to monitor or trace your system. Or have a look at it, while it is slow and then you should be able to find out what the bottleneck is.

                    Some general ideas, that are very often ignored in similar environments.

                    <ul><li>Is there an index on every foreign key? If not create them.</li>
                    <li>How is the isolation level for transactions set? Very often java guys change this using some o-r mapper (e.g. hiberate) and change it for the wrong reasons</li>
                    <li>Are all parameters binded?</li>
                    <li>Why does it take a second normally? This seems pretty long already. Optimize this to microseconds.</li>
                    <li>Check who does the commit/rollback and when. (Blocking locks?)</li>
                    </ul>

                    Something to read about transactional isolation levels: http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

                    Edited by: Sven W. on Aug 24, 2012 12:47 PM
                    • 7. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                      945687
                      Thanks for everyone's suggestions so far.
                      Sven W. wrote:
                      Some general ideas, that are very often ignored in similar environments.

                      <ul><li>Is there an index on every foreign key? If not create them.</li>
                      <li>How is the isolation level for transactions set? Very often java guys change this using some o-r mapper (e.g. hiberate) and change it for the wrong reasons</li>
                      Our java app is an integration appliance called Cast Iron that is now owned by IBM. I don't believe isolation level for transactions is a property we can change there.
                      <li>Are all parameters binded?</li>
                      I will post the procedure being used to insert at the bottom of this post.
                      <li>Why does it take a second normally? This seems pretty long already. Optimize this to microseconds.</li>
                      The timing I have to track is within the Cast Iron appliance logs. It is likely that the java/xml work / code being done for the database activity is inflating the time beyond what would be seen when executing the same insert via sql developer.
                      <li>Check who does the commit/rollback and when. (Blocking locks?)</li>
                      </ul>

                      Something to read about transactional isolation levels: http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

                      Edited by: Sven W. on Aug 24, 2012 12:47 PM
                      Here is the insert procedure being used.   I don't doubt there are improvements that could be made, but this procedure executes successfully.   However, per my posted issue, when Cast Iron calls it sometimes, it doesn't respond for 10 min and the Cast Iron call times out.

                      CREATE OR REPLACE
                      PROCEDURE XXPMRY.XXPMRY_CI_SR2I_INS_STG_SR (
                      p_Unique_ID IN NUMBER,
                           p_Reference1 IN VARCHAR2,
                           p_Reference2 IN VARCHAR2,
                           p_Summary IN VARCHAR2,
                           p_Note_Description IN CLOB,
                           p_Serial_Number IN VARCHAR2,
                           p_Caller_First_Name IN VARCHAR2,
                           p_Caller_Last_Name IN VARCHAR2,
                           p_Caller_Phone IN VARCHAR2,
                           p_Caller_Email IN VARCHAR2,
                           p_Caller_Address1 IN VARCHAR2,
                           p_Caller_Address2 IN VARCHAR2,
                           p_Caller_City IN VARCHAR2,
                           p_Caller_State IN VARCHAR2,
                           p_Caller_Zip IN VARCHAR2,
                           p_Caller_Country IN VARCHAR2,
                           p_Priority IN VARCHAR2,
                           p_Open_Datetime IN DATE,
                           p_Customer_Account_Number IN VARCHAR2,
                           p_Customer_Site IN VARCHAR2,
                           p_Contract_Number IN VARCHAR2,
                           p_Problem_Code IN VARCHAR2,
                           p_Call_Type IN VARCHAR2,
                           p_Creation_Source IN VARCHAR2,
                           p_Creation_Integration_Source IN VARCHAR2,
                           p_Alternate_First_Name IN VARCHAR2,
                           p_Alternate_Last_Name IN VARCHAR2,
                           p_Alternate_Phone IN VARCHAR2,
                           p_Alternate_Email IN VARCHAR2,
                           p_Alternate_Address1 IN VARCHAR2,
                           p_Alternate_Address2 IN VARCHAR2,
                           p_Alternate_City IN VARCHAR2,
                           p_Alternate_State IN VARCHAR2,
                           p_Alternate_Zip IN VARCHAR2,
                           p_Alternate_Country IN VARCHAR2,
                           p_Incident_Number IN VARCHAR2,
                           p_Processed_Flag IN VARCHAR2,
                           p_Processed_Date IN DATE,
                           p_Processed_Message IN VARCHAR2,
                           p_Incident_Status IN VARCHAR2,
                           p_Creation_Date IN DATE,
                           p_Created_By IN NUMBER,
                           p_Last_Update_Date IN DATE,
                           p_Last_Updated_By IN NUMBER,
                           p_Cust_Account_ID IN NUMBER,
                           p_Party_ID IN NUMBER,
                           p_Incident_Urgency_ID IN NUMBER,
                           p_Incident_status_ID IN NUMBER,
                           p_incident_Type_ID IN NUMBER,
                           p_Valid_Problem_Code IN NUMBER,
                           p_Incident_Severity_ID IN NUMBER,
                           p_Bill_to_Site_ID IN NUMBER,
                           p_Bill_to_Party_ID IN NUMBER,
                           p_Instance_Org_ID IN NUMBER,
                           p_Ship_to_Site_ID IN NUMBER,
                           p_Ship_to_Party_ID IN NUMBER,
                      x_retcode OUT NOCOPY VARCHAR2,
                      x_retmsg OUT NOCOPY VARCHAR2
                      )
                      IS

                      l_btd_user_id VARCHAR2 (50);
                      l_next_seq NUMBER;

                      BEGIN
                      l_btd_user_id := '';
                      l_next_seq := -1;

                      SELECT user_id
                      INTO l_btd_user_id
                      FROM APPS.FND_USER
                      WHERE
                      user_name = 'abc';

                      -- Grab the next value in the staging table unique_id sequence
                      SELECT XXPMRY.XXPMRY_SEQ_S.NEXTVAL
                      INTO l_next_seq
                      FROM DUAL;

                      INSERT INTO XXPMRY.XXPMRY_SR_IB_STG
                           (
                                UNIQUE_ID,
                                REFERENCE1,
                                REFERENCE2,
                                SUMMARY,
                                NOTE_DESCRIPTION,
                                SERIAL_NUMBER,
                                CALLER_FIRST_NAME,
                                CALLER_LAST_NAME,
                                CALLER_PHONE,
                                CALLER_EMAIL,
                                CALLER_ADDRESS1,
                                CALLER_ADDRESS2,
                                CALLER_CITY,
                                CALLER_STATE,
                                CALLER_ZIP,
                                CALLER_COUNTRY,
                                PRIORITY,
                                OPEN_DATETIME,
                                CUSTOMER_ACCOUNT_NUMBER,
                                CUSTOMER_SITE,
                                CONTRACT_NUMBER,
                                PROBLEM_CODE,
                                CALL_TYPE,
                                CREATION_SOURCE,
                                CREATION_INTEGRATION_SOURCE,
                                ALTERNATE_FIRST_NAME,
                                ALTERNATE_LAST_NAME,
                                ALTERNATE_PHONE,
                                ALTERNATE_EMAIL,
                                ALTERNATE_ADDRESS1,
                                ALTERNATE_ADDRESS2,
                                ALTERNATE_CITY,
                                ALTERNATE_STATE,
                                ALTERNATE_ZIP,
                                ALTERNATE_COUNTRY,
                                INCIDENT_NUMBER,
                                PROCESSED_FLAG,
                                PROCESSED_DATE,
                                PROCESSED_MESSAGE,
                                INCIDENT_STATUS,
                                CREATION_DATE,
                                CREATED_BY,
                                LAST_UPDATE_DATE,
                                LAST_UPDATED_BY,
                                CUST_ACCOUNT_ID,
                                PARTY_ID,
                                INCIDENT_URGENCY_ID,
                                INCIDENT_STATUS_ID,
                                INCIDENT_TYPE_ID,
                                VALID_PROBLEM_CODE,
                                INCIDENT_SEVERITY_ID,
                                BILL_TO_SITE_ID,
                                BILL_TO_PARTY_ID,
                                INSTANCE_ORG_ID,
                                SHIP_TO_SITE_ID,
                                SHIP_TO_PARTY_ID     
                           )
                           VALUES (
                                l_next_seq, -- 'UNIQUE_ID'
                                p_Reference1, -- 'REFERENCE1'
                                p_Reference2, -- 'REFERENCE2'
                                p_Summary, -- 'SUMMARY'
                                p_Note_Description, -- 'NOTE_DESCRIPTION'
                                p_Serial_Number, -- 'SERIAL_NUMBER'
                                p_Caller_First_Name, -- 'CALLER_FIRST_NAME'
                                p_Caller_Last_Name, -- 'CALLER_LAST_NAME'
                                p_Caller_Phone, -- 'CALLER_PHONE'
                                p_Caller_Email, -- 'CALLER_EMAIL'
                                p_Caller_Address1, -- 'CALLER_ADDRESS1'
                                p_Caller_Address2, -- 'CALLER_ADDRESS2'
                                p_Caller_City, -- 'CALLER_CITY'
                                p_Caller_State, -- 'CALLER_STATE'
                                p_Caller_Zip, -- 'CALLER_ZIP'
                                p_Caller_Country, -- 'CALLER_COUNTRY'
                                p_Priority, -- 'PRIORITY'
                                p_Open_Datetime, -- 'OPEN_DATETIME'
                                p_Customer_Account_Number, -- 'CUSTOMER_ACCOUNT_NUMBER'
                                p_Customer_Site, -- 'CUSTOMER_SITE'
                                p_Contract_Number, -- 'CONTRACT_NUMBER'
                                p_Problem_Code, -- 'PROBLEM_CODE'
                                p_Call_Type, -- 'CALL_TYPE'
                                p_Creation_Source, -- 'CREATION_SOURCE'
                                p_Creation_Integration_Source, -- 'CREATION_INTEGRATION_SOURCE'
                                p_Alternate_First_Name, -- 'ALTERNATE_FIRST_NAME'
                                p_Alternate_Last_Name, -- 'ALTERNATE_LAST_NAME'
                                p_Alternate_Phone, -- 'ALTERNATE_PHONE'
                                p_Alternate_Email, -- 'ALTERNATE_EMAIL'
                                p_Alternate_Address1, -- 'ALTERNATE_ADDRESS1'
                                p_Alternate_Address2, -- 'ALTERNATE_ADDRESS2'
                                p_Alternate_City, -- 'ALTERNATE_CITY'
                                p_Alternate_State, -- 'ALTERNATE_STATE'
                                p_Alternate_Zip, -- 'ALTERNATE_ZIP'
                                p_Alternate_Country, -- 'ALTERNATE_COUNTRY'
                                p_Incident_Number, -- 'INCIDENT_NUMBER'
                                p_Processed_Flag, -- 'PROCESSED_FLAG'
                                p_Processed_Date, -- 'PROCESSED_DATE'
                                p_Processed_Message, -- 'PROCESSED_MESSAGE'
                                '', -- 'INCIDENT_STATUS'
                                SYSDATE, -- 'CREATION_DATE'
                                l_btd_user_id, -- 'CREATED_BY'
                                NULL, -- 'LAST_UPDATE_DATE'
                                '', -- 'LAST_UPDATED_BY'
                                '', -- 'CUST_ACCOUNT_ID'
                                '', -- 'PARTY_ID'
                                '', -- 'INCIDENT_URGENCY_ID'
                                '', -- 'INCIDENT_STATUS_ID'
                                '', -- 'INCIDENT_TYPE_ID'
                                '', -- 'VALID_PROBLEM_CODE'
                                '', -- 'INCIDENT_SEVERITY_ID'
                                '', -- 'BILL_TO_SITE_ID'
                                '', -- 'BILL_TO_PARTY_ID'
                                '', -- 'INSTANCE_ORG_ID'
                                '', -- 'SHIP_TO_SITE_ID'
                                '' -- 'SHIP_TO_PARTY_ID'
                           );

                      COMMIT;

                      x_retcode := l_next_seq;
                      x_retmsg := '';
                      DBMS_OUTPUT.put_line ('x_retcode=' || x_retcode || '; x_retmsg=' || x_retmsg);

                      EXCEPTION
                      WHEN OTHERS THEN
                      ROLLBACK;

                      x_retcode := -1;
                      x_retmsg := 'Insert Failed: ' || SQLCODE || ': ' || SQLERRM;
                      DBMS_OUTPUT.put_line ('x_retcode=' || x_retcode || '; x_retmsg=' || x_retmsg);

                      END XXPMRY_CI_SR2I_INS_STG_SR;
                      • 8. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                        945687
                        One thing I noticed while the time-outs were occuring... While looking at some oracle session tables, these entries below showed up with long SECONDS_IN_WAIT values from v$session


                        This is the value from v$sql.sqltext:

                        SELECT UNIQUE POSITION, ARGUMENT_NAME, decode(DATA_TYPE, 'UNDEFINED', TYPE_NAME, DATA_TYPE), IN_OUT, OVERLOAD FROM SYS.ALL_ARGUMENTS WHERE OBJECT_ID = 614881 AND OBJECT_NAME = 'XXPMRY_CI_SR2I_INS_STG_SR' AND DATA_LEVEL=0 ORDER BY POSITION DESC

                        v$active_session_history.row_wait_obj value was for SYS.ARGUMENT$


                        Not exactly telling, but I only tend to see this entry during timeouts. I am sure there are additional tables / fields that might provide everyone here more helpful details.
                        • 9. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                          damorgan
                          Nothing in this thread has yet to establish that this is a database issue.
                          • 10. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                            Nikolay Savvinov
                            Hi,

                            be careful when looking at SECONDS_IN_WAIT, in some cases in can mean "seconds since the last wait":

                            http://oracledoug.com/serendipity/index.php?/archives/1510-Wait-Times.html

                            Best regards,
                            Nikolay
                            • 11. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                              945687
                              damorgan wrote:
                              Nothing in this thread has yet to establish that this is a database issue.
                              What type of application monitoring / research would you suggest I perform / post to provide more information? 3rd party houses and maintains the application, so depending on the specifics, I may be able to get the results myself or I may need to have them do it and it could take a few days for the results.
                              • 12. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                                damorgan
                                The first thing you need to do is stop assuming you are looking at a database issue.

                                You may be but you've presented not a single byte of information here that supports that conclusion.

                                From my read of what you've posted, unless you have your Oracle co-sharing an antique server with 64K of RAM and a 10K RPM hard disk ... "mere seconds" is outrageous. I would expect mere milliseconds. On my laptop I have no problem demonstrating 200,000 inserts per second. Granted a highly optimized demo but still what you are reporting as good behaviour is to me, screaming for triage.

                                My guess, unless this is one of the world's worst Oracle databases, is that you have latency issues with the application and the middle-tier. You need to establish, with carefully timed metrics, where the time is being spent.

                                This will be my last time responding to this thread unless you do some work and establish that anything you are experiencing has anything to do with an Oracle database. Guesses do not substitute for facts and this thread is essentially devoid of facts.
                                • 13. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                                  matthew_morris
                                  One thing I haven't seen yet in this thread (apologies if I missed it -- I arrived late to the party and have skimmed) is anyone suggesting that you run an AWR report. It's one of my preferred starting points when troubleshooting performance problems in the database.
                                  • 14. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
                                    BrendanP
                                    Why are you assuming that a resource problem on a multi-process computer is caused by one of those processes only, when there's no evidence at all for that, as two posters, damorgan (first) and myself have pointed out?

                                    The obvious possibility is be that the computer is just a lot busier at some times than at others (issues with resource allocation are also possible). You have to rule those out first.

                                    Edited by: BrendanP on 31-Aug-2012 14:42
                                    1 2 Previous Next