Query running does not take 16 parallel sessions

[skdas]

Hi,

I am running queries, in 16 parallel sessions.

Some times it takes 4 session some time 8 sessionssome time 10 sessions. It does not take fixed sessions every time.

What parameter I need to change so that it will take 16 parallel sessions.???

[davey23uk]

you can hint it if you wish, but why not let the database do what it needs to do by itself instead of forcing into a potentially sub optimal plan

[skdas]

I am using parallel hints.

When I use parallel hint 16, some time I get less sessions some time more seesions.

When I get around 14 sessions this job get finished within the expected time. But when I get less sessions , it finishes in less time.

I just want to know, what factor make it to run some time 16 sesions some time less sessions. Any parameter needs to be changed..?

[waitecj]

Well, if you have parallel_automatic_tuning set to true, your server processes will utilize all the server processes it has available to them up the the upper bound of parallel_max_servers. If there are multiple server processes requesting parallel processes then the number they request may be different to the number that they receive, its as simple as that. Post the output of "show parameter parallel" here.

[skdas]

SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 200
parallel_min_percent integer 0
parallel_min_servers integer 1
parallel_server boolean FALSE
parallel_server_instances integer 1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

[waitecj]

Now check the number of parallel processes being used at any one time (select sid, req_degree "requested_dop", degree "actual_dop" from v$px_session order by sid; ). You might find that you don't have the maximum set high enough for your demand.