I created one application where requests in XML format come to my application, my application runs some select and update SQLs and then responds to the other system. Total TAT for this whole process is 1.2 seconds max, but 2 requests took more than 3 seconds to respond.

So I asked for the ASH report from the DBA team to find out why this delay happened and I can see one blocking session during both of these requests. Now in the ASH report, I have the data like the following:

enter image description here

But to be sure I want to find out when this blocking session started and when it ended, if it coincides with request entry time to my application, I can be sure.

So is there a way to do so? I tried to find from Google but could not find any substantial answer.

  • hmm 'gcs' i.e. this seems like RAC. LGWR is writing blocks (or preparing to write to) to redologs, it is an essential process and there isn't much you can do other than ensuring you have no contention over interconnect and storage is very responsive. However, head out to oracle support website, download lfsdiag.sql and have your DBAs run it. It will show if there are issues that are slowing or affecting log writer process. – Raj Jul 14 '17 at 11:31
  • Unless the session is audited, full details of session history are not retained. Query dba_hist_active_sess_history directly for details of active sessions. It's based on periodic polling so that data is not completely accurate, but it should tell you more that the ASH report. The report shows 6 samples of the session so you should see 6 rows in the table for this session. – Andrew Brennan Jul 14 '17 at 12:00
  • @Raj, it seems like the sql is not allowed to be downloaded in any personal account. How can I get it? – Mistu4u Jul 14 '17 at 22:01
  • I am fairly sure, you can download it from support website (i have done it in the past when needed). You may need to run it as sysdba to identify issues or ask your dba team to do it for you. – Raj Jul 17 '17 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.