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
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
| |
For: |
|
Use: |
| & | | & |
| < | | < |
| > | | > |
| [ | | [ |
| ] | | ] |
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.