| Parallel hint [message #325785] |
Sun, 08 June 2008 12:46  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
Hi,
This is more of design approach question so please bear with me..
I have 10 jobs that have to complete in 2 hours and can be run in parallel. As per my knowledge, Oracle by default optimizes the execution by 'doing its best' - in this case, it will implicitly run the jobs using multiple processes. So, if the parallelism degree is 16, then it will, by default, run using that degree -- it may distribute this parallelism across the 10 queries.
My question is if I put a PARALLEL hint on one of the queries using /*+ PARALLEL (a 16) */, will it degrade the performance of the other 9 queries?? -- which will not really improve the overall performance (of the 10 jobs altogether).
Thanks in advance.
|
|
|
|
| Re: Parallel hint [message #325787 is a reply to message #325785] |
Sun, 08 June 2008 12:59  |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | As per my knowledge, Oracle by default optimizes the execution by 'doing its best' - in this case, it will implicitly run the jobs using multiple processes.
|
No unless you specify a parallism on the table or query and in session/instance parameters and have available server processes.
| Quote: | So, if the parallelism degree is 16, then it will, by default, run using that degree -- it may distribute this parallelism across the 10 queries.
|
Do you mean Oracle parallelism or OS one?
Oracle does not distribute parallelism accross queries, it gives it to each query (as long as there are server processes).
| Quote: | I put a PARALLEL hint on one of the queries using /*+ PARALLEL (a 16) */
|
Then each step of the query using "a" will use a parallelism of 16 (if there are available server processes).
Note that parallelism may be used ONLY if you have available DISK and cpu resources.
Regards
Michel
|
|
|
|
| Re: Parallel hint [message #325850 is a reply to message #325787] |
Mon, 09 June 2008 04:03 |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
|
Thanks for the info - am learning a lot. My follow up question is how do we know the available server processes and what happens if the DBA configures a degree of 10 on db level, and I put a degree of 16 in my query?
|
|
|
|
| Re: Parallel hint [message #325854 is a reply to message #325850] |
Mon, 09 June 2008 04:20 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | how do we know the available server processes
|
See parameter parallel_max_servers.
| Quote: | what happens if the DBA configures a degree of 10 on db level, and I put a degree of 16 in my query?
|
It depends on parameter parallel_min_percent which indicates the minimum number of available processes for Oracle to use parallelism, if there are not available then Oracle use serial execution plan.
In your example, if parallel_min_percent is lower or equal than 100*10/16 (=62) then Oracle will use the 10 processes, if it is greater then Oracle will not use parallelism.
There are other parameters regarding parallelism like PARALLEL_ADAPTIVE_MULTI_USER and PARALLEL_AUTOMATIC_TUNING.
See Reference Manual
Regards
Michel
[Updated on: Mon, 09 June 2008 04:20] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
