2 Replies Latest reply: Feb 28, 2013 8:34 AM by AlbertoFaenza RSS

    doubt on setting parallel_max_servers

    user12869307
      Hi,

      I work on below version of oracle
      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
      NLSRTL Version 10.2.0.4.0 - Production
      And My parallel parameters set are given below
      SQL> show parameter parallel
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      fast_start_parallel_rollback         string      LOW
      parallel_adaptive_multi_user         boolean     TRUE
      parallel_automatic_tuning            boolean     FALSE
      parallel_execution_message_size      integer     2152
      parallel_instance_group              string
      parallel_max_servers                 integer     48
      parallel_min_percent                 integer     0
      parallel_min_servers                 integer     0
      parallel_server                      boolean     FALSE
      parallel_server_instances            integer     1
      parallel_threads_per_cpu             integer     2
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      recovery_parallelism                 integer     0
      I understood that parallel_automatic_tuning need not to be mentioned true to run in parallel by reffering documents. If That is not right, please correct me

      my system cpu details are

      | TOPAS_NMON |
      ¦ 10 - CPUs currently ¦
      ¦ 10 - CPUs configured ¦
      ¦ 3108 - MHz CPU clock rate ¦
      ¦ PowerPC_POWER7 - Processor ¦
      ¦ 64 bit - Hardware ¦
      ¦ 64 bit - Kernel ¦
      ¦ 16,server_name - Logical Partition ¦
      ¦ 5.3.12.5 TL12 - AIX Kernel Version ¦
      ¦ server_name - Hostname ¦
      ¦ server_name - Node/WPAR Name ¦
      ¦ 10ACD6F - Serial Number ¦
      ¦ IBM,9117-MMB - Machine Type |

      Is it okay to keep parallel_max_serverrs = 48 for 10 CPU server like the one given above?
      Is there any disadvantages in keeping the parallel_max_servers to such high value like 48 in my scenario?
      This question is being asked because one of the query which runs using 120 million rows of data with full(hash ) join with some other big tables is faster when run in parallel
      It is very slow when it is not running in parallel. this parallel_max_servers=48 scope=both; makes it run in parallel. Otherwise it is not running in parallel.

      Can someone please explain the behaviour of this parameter or point me some oracle links where I will get to know more about this parameter and it's settings

      Thanks much for the help