Hi I am using, Release 18.104.22.168.0 of oracle.
In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'.
After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention ' that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),Now i can see two of the oracle jobs has been scheduled at this particular time.
And these were having frequency daily.So, i am suspecting these were experiencing the waits.
Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB?
Or should change the schedule time to some less peak hour
or whether these are 'not required' and can be disabled?
Do you suffer from Compulsive Tuning Disorder?
Post SQL & results that show these two tasks are having a measurable (negative) impact on end user sessions in the DB.
Explain in detail how eliminating the observed wait will improve the performance of end user sessions in this DB.
Oracle will always rank any wait event. This means the waits caused by background jobs will automatically show when your applications are predominantly idle.
If you want to reduce the impact of those wait events I recommend you issue a shutdown abort on your database.
You will not have a problem anymore.
Senior Oracle DBA
The autopurge defaults to keeping job logs for 30 days. If you run it less frequently, it will take longer to run. You might look at the log it creates to see what impact it is actually having, then decide if you are being compulsive about some waits. You could change the 30 days to something else, but before you do that you ought to think hard about throwing away information about something where you might not notice something wrong for a week or two. If you have a less busy time you want to change it to, go ahead, but be sure you know what else is running - statistics gathering, for example, can be hefty, not something you'd want to fight with.
The other one might be ADR maintenance.