We are facing performance isues on a Windows 2008 Oracle database (188.8.131.52) SAP BI Loads. There is a huge chain of jobs that are started and runs on the database with talkes a long time to execute. Let's supose that in this chain there are 500 jobs inside and takes more ou less than 15 to 20 hours. But, sometimes, this time increases and takes 30, 35, 40 hours.
So, we need to understand in which steps of the chain it takes more time, and one of the activities that we are planing is to trace all the execution and identify the waits.
The problem is:
. If there is a "father" process that call's the others, it would be easy since we could set the trace in this session.
How can we guarantee to trace all the chain since each step is one session opened on the database? Anybody know the usage or viability to trace the user at all? Any other options that could help us in this issue?
Doubt: Even that we set a trace on a "father" session, and this session calls/opens other sessions, this trace would contain all the instructions for all the sessions ou just the father? The trace sees a relation and traces all that are started in parallel?
Please disconsider tracing all the database. Not useful for this situation.
this does not seem to be a security related issue. While the scheduler may know the notion of 'chains' the sql_trace facility
has no understanding of inheritance, so to gather trace info build it into the jobs so they can set event 10046 in their
respective job sessions. Also if you generate an AWR report for the duration of the jobs, you may be able to pinpoint
certain sql statements using more resources, compare with AWR report for the 'normal' situation,