Skip to Main Content
  • Questions
  • 1st Transaction of the day is always the slowest

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sridhar.

Asked: August 30, 2017 - 1:50 pm UTC

Last updated: August 30, 2017 - 2:06 pm UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Masters,

We use a screen for updating the underlying tables with certain values. And always the 1st transaction of the day is always the slowest, it takes 20 mins. And 2nd transaction onwards it completes in a minute or two. This being a bank, we don't bounce the DB. I am not sure what details I need to provide continue with this investigation. If you can point me to right direction - where to look, what to look for, I will provide the details.
Many thanks.

Regards,
Sri

and Chris said...

You need to trace the session to see what it's doing.

If you're able to identify the session id for the first transaction in advance, you can trace just this session by calling:

exec dbms_monitor.session_trace_enable(session_id =><session id>, serial_num=><serial #>, waits=>true, binds=>false);


Then once the transaction completes, stop tracing with:

exec dbms_monitor.session_trace_disable(session_id =><session id>, serial_num=><serial #>);


Then find the log file and analyze it with tkprof.

If you're not able to identify the session in advance, you can enable monitoring for a particular client ID, service or the whole DB:

http://docs.oracle.com/database/122/ARPLS/DBMS_MONITOR.htm#ARPLS091

This will trace all the matching sessions. So do this for as short a time as possible as it adds extra overhead to your DB. The upside is you'll capture some "fast" transactions too. This makes it easy to compare what's different and get to the root cause.

If you need further help once you've got these details, post your findings in a followup.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database