This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Dec 4, 2012 1:11 PM by 945687 Go to original post RSS
  • 15. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    sb92075 Guru
    Currently Being Moderated
    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.

    we would all benefit if we had actual visibility as to where time was being spent;
    otherwise you are simply shooting in the dark while hoping to get lucky.
  • 16. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    damorgan Oracle ACE Director
    Currently Being Moderated
    sb ... when you write "We can" are you also Brandon Barker? This thread just got very confusing.

    Thank you for clearing things up.
  • 17. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    matthew_morris Expert
    Currently Being Moderated
    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
    I'm not actually assuming anything. I suggested the use of a tool that is specifically designed to help determine what is happening on the Oracle database that might be impacting performance. AWR reports are what you use to get evidence, so I'm not tracking here.
    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)
    This by contrast is an assumption. It may be the right one, but it is an assumption. In any event, AWR reports are designed to help you diagnose issues with resource allocations, so I don't see where this is a reason not to use them. If I have a database performance issue that happens sometimes, but not others, I'll run two AWR reports -- one from an interval where the performance is degraded and a second AWR report from an interval where it is not. Comparing the two can go a long way to determining why the database is acting differently.
  • 18. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    945687 Newbie
    Currently Being Moderated
    damorgan wrote:
    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.
    I am not assuming this is a database issue. Granted, this post is in the Oracle Database forum but that was intended as a starting point based upon the information available to me.

    Not to rant too much here, but I am a java / sql developer. The 3rd party that houses / supports the Oracle DB / Oracle EBS app has not provided any solutions / suggestions / analysis for this issue, so I am attempting to dive into the issue fairly blind and see if I can determine the problem and come up with any possible solutions. I have database access / sql experience, so that is where I am starting. I wouldn't doubt that the issue may be at the app level but I likely won't have the system access to do the analysis / research at that level.
  • 19. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    945687 Newbie
    Currently Being Moderated
    damorgan wrote:
    sb ... when you write "We can" are you also Brandon Barker? This thread just got very confusing.

    Thank you for clearing things up.
    Negative, this was not me. This user just didn't fully quote the previous post properly.
  • 20. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    945687 Newbie
    Currently Being Moderated
    matthew_morris wrote:
    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.
    Thank you for the suggestion. I will try to get this run by one of the sysadmins.
  • 21. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    Sven W. Guru
    Currently Being Moderated
    Brandon Barker wrote:
    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>
    You didn't answer that one. Did you check? Do you need help checking it?
    <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.
    Ok, then we assume the single row insert is fast. Whenever you get the timeout you probably have some kind of locking conflict or suddanly a huge operation going on. Could be on database level or application level. But we focus on the database level first.
    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.
    The procedure looks clear enough. I see a potential problem with rollbacks however.
    Let analyze it step by step.

    Potential locking conflicts could exists
    <ul><li>if some other session insert into the same table useing the same UNIQUE KEY without commit.</li>
    We know already that there is a primary key filled hopefully by a sequence. => IF some other session does not use the seqeunce, then this can explain the scenario.
    Is there another unique constraint on the table XXPMRY_SR_IB_STG?
    <li>if this function is called very often very fast (massive parallel access)</li>
    Several issues can arise from that. Because of the frequent commit point, your database might get a ressource problem. The following is not an entirely correct explaination. However I try to emphazie a point. Each commit forces writing the log buffer to the redo log files (several commits are needed to fill the redo log file). If the archive process is slow, then after some time, you might not have any new free redo logs. The insert will wait until the archive process archives the older logs. Issue is: frequent commits can slow down your database.
    <li>if a rollback is done, then not only the insert is rolled back</li>
    Everything that happened before this procedure is called, is also rolled back. This might be a big chuck of data and this rollback can cause the timeout.
    The procedure would rollback, whenever a UK or FK is violated. For example if a reference column value is connected with a parent table that does not hold this value.
    You might never know that this procedure encountered such an error, because the error is simply hidden instead of being reraised.
    </ul>

    Edited by: Sven W. on Sep 4, 2012 6:11 PM

    How to get better information:

    Use a tool like Enterprise Manger or Oracle SQL Developer. THose tools have options/reports that give more information.
    The Oracle SQL Developer has a session monitor where you can see currently running sessions (you need the DBA role or at least select any dictionary). It will also show the active sql statement, waits and locking conflicts. And there are some more reports that help to diagnose later.
    For example:
    Reports>Database Administration>Waits and Events>Waits in the last hour
    Reports>Database Administration>Top_SQL>Top_SQL by Waits
    ...

    Edited by: Sven W. on Sep 4, 2012 6:20 PM
  • 22. Re: inserts/updates sporadically take 10min-2hrs instead of normal run in secs
    945687 Newbie
    Currently Being Moderated
    Just to provide a final update on the solution in case any others that come upon this thread has similar symptoms...

    A few things were done to finally resolve this issue:
    1 - The ALG setting on the data center Cisco firewall needed to be set to disabled
    2 - Increase the Oracle DB INBOUND_CONNECT_TIMEOUT in sqlnet and listener from current value 120 to 240 secs
    3 - Apply Oracle DB patch 8554900 suggested in Metalink Note: 1190683.1 to fix a bug in SQLNet.
    4 - Increase the Oracle DB Queuesize of the listener to 256.

    It is not clear if items 2-4 had any noticable impact since the action taken in step 1 had immediate positive impact.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points