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.
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 thatI'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.
damorgan wrote: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.
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.
matthew_morris wrote:Thank you for the suggestion. I will try to get this run by one of the sysadmins.
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.
Brandon Barker wrote:You didn't answer that one. Did you check? Do you need help checking it?
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>
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.<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.
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.