Home » RDBMS Server » Performance Tuning » Parallel hint (10g)
Parallel hint [message #325785] Sun, 08 June 2008 12:46 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64130
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

Previous Topic: increase the Oracle datafile size or add another datafile
Next Topic: Scheduled jobs
Goto Forum:
  


Current Time: Wed Dec 07 09:02:08 CST 2016

Total time taken to generate the page: 0.13146 seconds