Query running does not take 16 parallel sessions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query running does not take 16 parallel sessions

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Query running does not take 16 parallel sessions

    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.???

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Jul 2002
    Posts
    205
    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..?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.
    Assistance is Futile...

  5. #5
    Join Date
    Jul 2002
    Posts
    205
    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

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.
    Last edited by waitecj; 11-08-2006 at 06:32 AM. Reason: Stoopid emoticon at the end of "sid"
    Assistance is Futile...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width