OraFAQ Forum: SQL & PL/SQL » Contention between JOB_QUEUE

Home » SQL & PL/SQL » SQL & PL/SQL » Contention between JOB_QUEUE_PROCESSES and PROCESSES ? (11.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521498]

Tue, 30 August 2011 10:42

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Hi all,

In the program i wrote, posted in this thread I parallelize 340 jobs.
To do this, according to oracle documentation, i set the job_queue_processes parameters to 450.
This works fine, all jobs are submitted, but, when i chek the dba_scheduler_job_run_details, i notice that there is a difference between REQ_START_DATE, which is my reqested start date, when i enabled my job, and the ACTUAL_START_DATE, which is the date on which Oracle start this process.
And the more i submit job, the more difference between ACTUAL_START_DATE and REQ_START_DATE increase.

But for each job, run duration is less than 1second.

I check the processes parameter, and it is set to 100.

Anyone know if i have to increase the processes parameter to 450 too ?

Thanx for replies.

Report message to a moderator

Re: Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521499 is a reply to message #521498]

Tue, 30 August 2011 10:47

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes you have to and even more.

Regards
Michel

Report message to a moderator

Re: Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521571 is a reply to message #521499]

Wed, 31 August 2011 04:12

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Hi,

Thanx for your responses Michel.
I increase my processes parameter to 450, but it seems that something else is locking my process to be started at the time i submit them.

(i would post some examples of the logs, but i don't find how to relise tables....)

The first jobs submitted are delayed batween 4 and 15 seconds
A little bit later (2-3 minutes), the actual_start_date is equal to the req_start_date, modulo 1 to 10 mili second

It seems to be cyclic.

Do you know if i must increase others parameters.... ?

Report message to a moderator

Re: Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521573 is a reply to message #521571]

Wed, 31 August 2011 04:59

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Processes needs to be big enough to support all the job processes and all the other processes. That's why Michel said "and even more". Try 550.

Report message to a moderator

Re: Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521574 is a reply to message #521573]

Wed, 31 August 2011 05:01

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And the question has to be asked - is your hardware powerful enough to support so many simultaneous processes?
You're asking a lot of it here.

Report message to a moderator

Re: Contention between JOB_QUEUE_PROCESSES and PROCESSES ? [message #521588 is a reply to message #521574]

Wed, 31 August 2011 06:23

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Thanx cookiemonster.
The hardware is well sized to bear thoses processes in production and preproduction server.
I am in developpement right know, and this hardwre may be not enough powerful.
I would just have to be sure that this problem won't appaer on the others platforms.

It seems that the 330 processes don't run at the same time.

I just post the parameters which can, for me, slow down the launch of my jobs.
Please, just tell me if you think that those, parameters are non well sized.

Code: [Select all] [Show/ hide]

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     450
log_archive_max_processes            integer     4
processes                            integer     450

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
session_cached_cursors               integer     50
session_max_open_files               integer     10
sessions                             integer     704
shared_server_sessions               integer

SQL> show parameters parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     435
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     256
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL> show parameters queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     450

SQL> show parameters target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     600
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     256
pga_aggregate_target                 big integer 500M
sga_target                           big integer 1G

Thanks in advance for your comments and suiggestions about my parameters.

Report message to a moderator

Previous Topic:	Help in Pivot Query
Next Topic:	SQL help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 15:00:11 CDT 2025