This discussion is archived
8 Replies Latest reply: Apr 19, 2013 8:02 AM by Mohamed ELAzab RSS

How to monitor database performance during job runs.

1003624 Newbie
Currently Being Moderated
Hi All,

We are running a Batch Job , which fetches data from thousands of table for the 15 days volume and creates the report.

I would like to know what all are the possible way of monitoring the Database performance during the report runs. I need to know some basic performance views and SQL queries from DB which will give the DB performance overview. It should be apart from AWR report .

Please suggest me , your reply will be highly appreciable.

Kind Reagrds
Shankar N
  • 1. Re: How to monitor database performance during job runs.
    DK2010 Guru
    Currently Being Moderated
    Hi,

    You can trace the session using the Event Parameter and get the report O/P using Tkprof
  • 2. Re: How to monitor database performance during job runs.
    1003624 Newbie
    Currently Being Moderated
    Hi,

    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.

    Thanks
  • 3. Re: How to monitor database performance during job runs.
    Mohamed ELAzab Pro
    Currently Being Moderated
    Hello,

    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';


    Kind Regards
    Mohamed ELAzab
  • 4. Re: How to monitor database performance during job runs.
    saratpvv Journeyer
    Currently Being Moderated
    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.
    First of all you need to find SID,SERIAL of that session from v$session for that you can run simple query

    select sid , serial# , username , program,status from v$session where username =<'username'>;

    After identifiy the SID,Serial from the above query then
    exec dbms_monitor.session_trace_enable(&sid,&serial,TRUE,FALSE);
    It will generate file in you UDUMP directory then you can use TKPOOF utility
    tkprof <file>.trc alligned.txt explain=system/<password>
  • 5. Re: How to monitor database performance during job runs.
    Justin_Mungal Journeyer
    Currently Being Moderated
    DK2010 wrote:
    Hi,

    You can trace the session using the Event Parameter and get the report O/P using Tkprof
    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.

    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.
  • 6. Re: How to monitor database performance during job runs.
    1003624 Newbie
    Currently Being Moderated
    Hi ,

    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.
    5 Etc...

    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.

    Kind Reagrds
    Shankar
  • 7. Re: How to monitor database performance during job runs.
    DK2010 Guru
    Currently Being Moderated
    Hi,

    These need lot of Script to monitor individual usages. You can get some script from there.. May it Help You

    http://oracle-base.com/dba/scripts.php
  • 8. Re: How to monitor database performance during job runs.
    Mohamed ELAzab Pro
    Currently Being Moderated
    Hello Shankar,

    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.

    Kind Regards
    Mohamed ELazab

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points