Hi Mark,
Thanks, that makes sense. I think our application scheduled too many jobs
in the database. Last time we saw more than 1000+ AQ$ jobs along with other
scheduled job. which really caused the issue and consumed all the cpu
resource. We ended up have to bounce the database.
We still working with Oracle trying to find the root cause, so far there's
no positive solution for that.
Your suggestion is really worth to try. meantime, our manager asked the
application team to migration the jobs running outside the database using
window scheduler to run.
Thanks always!
Joan
Joan
On Wed, Mar 6, 2019 at 6:39 PM Mark Burgess <mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
We have seen a similar problem before for a customer who runs a large
number of scheduler jobs.
The environment is on 11.2.0.4 however so it may not be the same problem.
When restarting the database the CJQ process executes SQL to determine
which jobs to run. The SQL queries the AQ scheduler event tables. The query
plan used by the CJQ process selects a bad plan on startup, so it takes
approx 35-45 mins before scheduled jobs start running. We workaround this
issue by stopping the scheduler (job_queue_processes=0, aq_tm_processes=0)
and then killing the CJQ background process (which is still executing the
slow SQL). Restart the scheduler and the jobs start running straight away
as the CJQ process uses a “good” plan.
Another thing to consider is that If completed jobs are not purged the SQL
issued by the CJQ process on startup takes much longer.
Apologies for not having the specifics around the actual SQL run and the
query plan as I don’t have the details handy at the moment.
On 7 Mar 2019, at 8:18 am, Powell, Mark <mark.powell2@xxxxxxx> wrote:
Joan, let us know if the patch bundle fixes the issue or not.
Mark Powell
Database Administration
(313) 592-5148
------------------------------
*From:* Joan Hsieh <joanhsieh08@xxxxxxxxx>
*Sent:* Monday, March 4, 2019 8:52 PM
*To:* Powell, Mark
*Cc:* ORACLE-L
*Subject:* Re: dbms_scheduler jobs stop running after reboot.
Hi Mark,
Thanks for the information. The problem is the scheduler jobs have been
run over years, it all of sudden stopped to run after the this rebooting.
Nothing were changed.
Oracle SR suggested to apply the bundle patch 28729169 and didn't give any
explanation.
Regards,
Joan
On Mon, Mar 4, 2019 at 1:57 PM Powell, Mark <mark.powell2@xxxxxxx> wrote:
Joan, I went looking for the support article Chris mentions and found the
following which you may want to review
-- 12.2
Some Scheduler Jobs Are Not Rescheduling Properly Or Not Running At
Rescheduled Time (Doc ID 2421574.1)
DBMS_SCHEDULER - When Hour Is Specified As 00 In Bytime Clause Next Run
Date Not Set Properly (Doc ID 2405053.1)
-- 12.1 and below
Bug 18884444 - Jobs stuck in SCHEDULED status after calling
DBMS_SCHEDULER.stop_job with force=true (Doc ID 18884444.8)
- -
Mark Powell
Database Administration
(313) 592-5148
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
*Sent:* Sunday, March 3, 2019 11:32 PM
*To:* gogala.mladen@xxxxxxxxx; ORACLE-L
*Subject:* Re: dbms_scheduler jobs stop running after reboot.
Joan,
If you're running 12.1 (maybe 12.2) there's a bug with scheduler jobs not
firing after a database restart . Oracle support should have the metalink
article as I don't have it in front of me at the moment.
We run into this on our db occasionally.
Chris
On Sun, Mar 3, 2019, 7:22 PM Mladen Gogala <gogala.mladen@xxxxxxxxx wrote:
Hi Joan!
Each and every queue process has a trace. To enable an additional trace,
you can use event 10046, level 16 on few of those processes and see what
is going on within the traced processes. Looking into DBA_SCHEDULER_JOBS,
DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS would also be a
good idea. And what was that about "unsupported page size"? What OS are you
using? What version of Oracle? However, the very first place to look in
would be the alert log. ADRCI can be useful here, too.
Regards
On 2/28/19 11:55 AM, Joan Hsieh wrote:
Hi List,
We planned to increase the SGA_target from 50G to 64G. Ended up we
had to change back to 50G due to the page size was not supported.
However, after the database rebooted all of the scheduler jobs
stopped to execute. (There are more than 500 jobs, fired every 5 min
in application)
The last_start_date in dba_scheduler_jobs was the time prior to the
database rebooting, which was 2/23 9pm, and the state was SCHEDULED.
The job queue is 50, cjg0 is running. we don't use oracle OIM.
The developer had tried to disable/enable the scheduler. They had
also tried to use exec DBMS_SCHEDULER.run_job to test. The job ran,
but did not fire after 5 min for the next run.
The database is 4 TB in size. Previously the database had bounced
several times, but it had never caused any scheduler jobs issue
before. We are trying to find any resource and had opened an Oracle
SR for this issue. We just wanted to find out if you have have any
insight.
Much thanks in advance.
Joan
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
DXC Technology Company - Headquarters: 1775 Tysons Boulevard, Tysons,
Virginia 22102, USA.
DXC Technology Company -- This message is transmitted to you by or on
behalf of DXC Technology Company or one of its affiliates. It is intended
exclusively for the addressee. The substance of this message, along with
any attachments, may contain proprietary, confidential or privileged
information or information that is otherwise legally exempt from
disclosure. Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient of this message, you are
not authorized to read, print, retain, copy or disseminate any part of this
message. If you have received this message in error, please destroy and
delete all copies and notify the sender by return e-mail. Regardless of
content, this e-mail shall not operate to bind DXC Technology Company or
any of its affiliates to any order or other contract unless pursuant to
explicit written agreement or government initiative expressly permitting
the use of e-mail for such purpose. --.
