Hmm... actually I made a little mistake reading your performance output, there... thought it was saying that each operation took 7.56 seconds (which is part of why I was thinking network problems). But it's actually saying that just the prepare phase was, and that is indicative of an entirely different problem: slow parses. You could have some issues with your v$sqlarea and/or your shared pool (either of which could interfere with your soft-parse), or you might have some problems with your optimizer, which I'd assume is cost-based, which means that it could be a problem with your statistics.
So, here's some questions:
- Do you bind the data in your sql? If not, you could be causing your sqlarea to get out of control, size-wise, and cause soft-parses (part of the prepare-phase) to take a really long time. However, if that were the case, exactly, you'd be more likely to see a steady degredation of your performance (not so much that just one query would hang forever, but all others were nice and snappy).
- Do you regularly flush your shared pool? When you do flush your shared pool, do you see big chunks of it lingering around anyways? This can happen due to some problems analagous to how circular references can interfere with reference-counting garbage-collectors.
- Which optimizer mode are you using (cost or rule)? If you are using the cost-based optimizer mode, then when/how often do you generate/regenerate statistics. How do you generate statistics? Problems with you statistics could (I imagine, in extreme cases) cause the optimizer to take crazy-long periods of time during a hard-parse (first parse of a query... the time at which the optimizer builds an execution plan for your query and stores it in the shared pool).
- Do you have a DBA? This is really more of a question for a real DBA (which I am not... although I do know a good bit of random oracle administration trivia)
------------
:Wq
Not an editor command: Wq