If i have below configuration on DB server , please suggest in SQL query how much MAX parallel i can use in Parallel hint /*+ parallel (table_name ,1) */ and if any other points to consider while using parallel hint
Platform = Linux x86 64-bit , CPU =16 , Cores= 8 , Memory (GB)=94.40
and parameter parallel_max_servers = 96
With 8 cores, you can use a Degree of Parallelism (DoP) of 8; at least to start with. I presume that you are trying to increase your query performance. Other factors to consider:
Execution Plan. A SQL Monitor report is by far the best too to examine the execution of your query.
Bandwidth. Very often in order to be able to use parallel processing effectively, you will need sufficient bandwidth. If you do not have enough bandwidth, you will not be able to keep the CPUs busy.
Concurrency. Will there be only one user or one query running on the machine at any given time? If you have 50 users for example, you probably cannot all run queries with a DOP of 8 concurrent.
Resource Management. It's important to have resource controls in place to prevent over subscribing the machine.