Hi,
My Company customer has observed that there are some night time jobs, i.e., 1am-8am that are long running or taking long and long time. They did not gave any information about the total no. of jobs in the DB or which jobs are taking long running time. I have to find it out myself, and then analyze why these jobs are taking so long running time. Please suggest me your valuable tips so that I approach this work properly.
DB = 10g RAC (Production DB with 2 instances on two different hosts)
OS= RedHat Linux
I have only option of STATSPACK (no AWR licence). On one of the instance I generated based on snap id's by using statspack. I have checked through crontab -l that there are no operating system jobs, there might be some application jobs, database jobs are queried as following: (that looks standard)
SYS@rac1p1 AS SYSDBA> select job_name, next_run_date, state from dba_scheduler_jobs;
JOB_NAME NEXT_RUN_DATE STATE
------------------------------ --------------------------------------------------------------------------- ---------------
PURGE_LOG 25-JAN-12 03.00.00.000000 AM EUROPE/PARIS SCHEDULED
FGR$AUTOPURGE_JOB DISABLED
GATHER_STATS_JOB DISABLED
AUTO_SPACE_ADVISOR_JOB SCHEDULED
MGMT_CONFIG_JOB SCHEDULED
MGMT_STATS_CONFIG_JOB 01-FEB-12 01.01.01.100000 AM +01:00 SCHEDULED
C_TBLSIZE_COLLECTDU_JOB 24-JAN-12 12.05.41.000000 PM EUROPE/BERLIN SCHEDULED
C_TBSSIZE_JOB 24-JAN-12 10.40.41.000000 AM EUROPE/BERLIN SCHEDULED
Question: Is there any other way/view(s) too to find out the total database jobs?
Quesiton: What information to look in the STATSPACK generated report that suggest the long running jobs reasons? (the long running job problem is from the last 1 month approx.)
Question: As the client has no info or whatever, so what should be my approach to look for long running jobs from 1am-8am?
Question: How to look for overall SQL queries which are taking lot of time to run?
Please suggest your useful opinions asap, as I have less time to handle this.
Thanks a lot.
Regards..
John
Edited by: John-M on Jan 24, 2012 2:15 AM