This content has been marked as final. Show 8 replies
enabling a trace on the user level in case of a patch job is ok but you will enable the tracing on the user level and a trace will be generated for each session which i think is a very time consuming task.
I would use the tracing to trace error or abnormal behavior myself.
I would generate the AWR before starting the patch job and then generate AWR on the snapshots that the patch job was running and then compare the 2 or 3 reports and see where the top time wait events going and top sql and check if i can tune those sql statements.
You can check for example the event parameter in v$session:
select event from v$session where username='your+username';
First of all you need to find SID,SERIAL of that session from v$session for that you can run simple query
Thanks for the response. Could you please let me know what all are the parameters should i trace in prespective of Report job performance. If possible will you be able to provide me the query for tracing it.
select sid , serial# , username , program,status from v$session where username =<'username'>;
After identifiy the SID,Serial from the above query then
It will generate file in you UDUMP directory then you can use TKPOOF utility
tkprof <file>.trc alligned.txt explain=system/<password>
DK2010 wrote:I disagree. The OP is asking about overall database performance while a batch process is running. Tracing a single session is not useful in this case.
You can trace the session using the Event Parameter and get the report O/P using Tkprof
Shankar, can you tell me why you don't want to use an AWR report?
This is what I would recommend, if you are licensed for it:
1)Create AWR snapshots as previously suggested, immediately before and after the job runs.
2)Rather than depending solely on an AWR report of just that time period, I would recommend using the Compare Periods Report, which will let you compare normal performance to performance of the database while the job is running. I believe this is exactly what you're looking for, and so I recommend you give it a chance.
Thanks for the reply. It will be very useful to me.
Actually what i am looking for here is. I need to know the following elements for my performance understanding.
1. Number of transaction happened during the specific period.
2.The top 10 SQL statement which consumend most of the DB time during the specific period.
3. Number of connection which utilziing the higher resources.
4.Number of objects which utilziing the higher resources.
I need to know the exact quries for this , I know we will be getting it from AWR report but apart from it , is there any SQl statement i can execute which makes my job easier.
You can get all this using AWR using sql statements will help you to get this information but it will be both time consuming and harder because this is a patch job that runs many sql statments not only one statemnet.
You can also use Active session history to get more information regarding sql statments.