Method for capturing and storing long-running transactions real-time

pts.
Tags:
Oracle
We are running into a situation whereby we experience a build-up of long running transactions in the database, eventually bringing everything (including App servers with severe JVM spikes) to a halt. I'd like to find a way to capture long-running transactions real-time so as to alleviate the spikes before they get out of hand. Any recommendations? Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

What do you mean by “capture”? You can identify them in v$transaction. You can limit them with profiles. You might be able to find them in v$session_longops.

It really depends on your environment and what it is you’d like to do with these long running transactions.

((MrO))

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • LeChuck
    Like MrOracle mentions, you need to inform us on what environment you're running, in order for anyone to help you! Assuming you're running on a SQL Server and want to trace long running transactions, try setting up a trace in SQL Profiler, tracing on any process running for more than i.e. 10 seconds! - If no hits, set i to 5 or 2 seconds... Focussing on duration time and process/CPU-time will tell you which transactions/Stored Procedures are taking the longest time - there's a good chance, that one of those is the one causing your problems - but it's not for sure... It might in stead be for instance a SP running extremely frequent, but for every run it takes about a second, causing the system to build up a que of transactions to run, and causing the system in the end to lock those data almost constantly! Good luck Jacob
    0 pointsBadges:
    report
  • Orion2
    Well you can get use of any third party tool like toad to get that information, it will show you transaction in progress. If you want to dig in more technical why it is happening. Think of using statspack, good starting point to understand performance issues. But it will be nice if you provide more information reqarding your system, like its been asked in previous responses. Thanks
    0 pointsBadges:
    report
  • Clb108
    Thanks for the replies. This is an Oracle 9i database running on Solaris. We want to be able to capture long running queries when these spikes occur - sometimes the spike will disappear before anyone has a chance to login and look at it. Having that info will then allow us narrow our search as to where the issue originates. My thinking is that it isnt necessarily an database issue (or a SQL tuning issue), but I don't have any evidence.
    0 pointsBadges:
    report
  • MrOracle
    Now we can answer your question. :) Short of buying something that stores you SQL history, which is an option, you can run a recurring job every hour or more frequently to take snapshots of your top 10 queries. You would find them in V$SQLAREA, but the text is abbreviated there for long SQL statements. So, you can join to V$SQLTEXT_WITH_NEWLINES to get a sometimes sloppy version of the full text. Unfortunately, numbers there are CUMULATIVE, so I tend to divide by EXECUTIONS to find out how much resource they use EACH TIME THEY RUN. Note also that a long running query that is running for the first time - shows ZERO executions until it is done! This is what makes life fun, isn't it? I hope this helps. ((MrO))
    0 pointsBadges:
    report
  • Bozzo999
    If not already you should consider running StatsPack. Often helpful dealing with performance issues "after the fact". HTH Ken
    0 pointsBadges:
    report
  • Bozzo999
    If not already you should consider running StatsPack. Often helpful dealing with performance issues "after the fact". HTH Ken
    0 pointsBadges:
    report
  • Clb108
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/method-for-capturing-and-storing-long-running-tr... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following