Thanks for the question.

Asked: July 21, 2001 - 10:16 am UTC

Last updated: November 06, 2010 - 7:29 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom

Just one more ..

I have query which is taking 50 minutes to run...and is doing a full table scan
Now, I want to know if I can use parallel query in any way to parallelly process this query..

Before that..

In which version was this concept introduced..For parallel query is it necessary that we should have multiple processors.. What happens behind the scene , when we divide a query into a parallel degree of 2 or 4.. what happens internally..
What is the criteria to use parallel query

Is there any way you send voice emails for some answers.. I have an account which converts voice mails into emails and emails to my account.

and Tom said...

Yes, a full scan is a good candidate for parallel query.

You'll want to make sure you have sufficent reserve IO capabilities... If you are totally IO bound now, parallel query will only make that worse.

it is very easy to try, won't hurt anything. Give it a go.

Oracle7.1 introduced parallel query in 1994. You do not need more then one CPU, you frequently use a degree of parallelism greater then the number of cpus on the system. Consider this -- when doing an index build you read alot, sort alot, write alot. When you are reading, you are not using the CPU (hopefully you don't have ide drives!). So, if there were another process sorting -- that would be great. Likewise when you are sorting, you are not using the disk -- if someone else could be reading or writing, that would be great.

Read this chapter:

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#365 <code>

it covers all about parallel query, how it works, what happens when you use it.

No, you cannot use voice mail to contact me ;)

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PQ and CPUs

Billy Verreynne, July 24, 2001 - 5:47 am UTC

Good stuff TK. It is important to note that it is an OWT (old wife's tale) that you need multiple CPUs for using PQ. It is simply not true.

As long as the CPU is not 100% busy, you can squeeze more performance from it - which means loading the CPU with more processing.

PQ is simply another form of multithreading. Essentially, taking a big tasks (like a full table scan) and breaking it into smaller chunks of work (like Oracle adding the rowid clause to your SQL) and running these at the same time. Irrespective of a single CPU or multiple CPU. Having multiple CPUs simply allow you to load the CPUs with more PQs, nothing more.

how to set degree?

Rahul, January 02, 2003 - 4:29 am UTC

Hi Tom

On which basis we should set the degree of parallelism and PARALLEL_MIN_SERVERS AND PARALLEL_MAX_SERVERS parameters?

If Oracle server on which instance is running has 2 CPU then what are the appropriate values for these parameters?

Rahul.

January 02, 2003 - 7:37 am UTC

use parallel automatic tuning instead. the database will pick your appropriate values.

parallel execution parameters in 9.2.0.6

Stewart W. Bryson, February 23, 2005 - 12:45 pm UTC

Hello Tom.

I'm a little confused about some of the parallel query parameters, and was wondering if you could help me make sense of them.

The Oracle documentation tells me that PARALLEL_MAX_SERVERS is  set to a default when PARALLEL_AUTOMATIC_TUNING is set to true:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1153.htm#1022180


Below is my parallel configuration:

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     24
parallel_min_percent                 integer     0
parallel_min_servers                 integer     4
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL> 

Does this mean that Oracle has configured my value of PARALLEL_MAX_SERVERS to be 24? Or does it mean that Oracle is ignoring my setting for this and using something else instead?

The reason I ask is that I can change my setting for PARALLEL_MAX_SERVERS with an ALTER SYSTEM command, at least at the spfile level:

SQL> alter system set parallel_max_servers=30 scope=spfile;

System altered.

Elapsed: 00:00:00.05
SQL> 

I've inherited my environment, so what I want to know is if 24 is what Oracle has configured, is it what some one else set the value to, or is it meaningless with PARALLEL_AUTOMATIC_TUNING set to true?

If PARALLEL_MAX_SERVERS can indeed be changed with an ALTER SYSTEM statement, how do I get it back to the default?

Thanks.

February 24, 2005 - 4:58 am UTC

it says parallel max servers DEFAULT value is derived from the values of CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, and PARALLEL_ADAPTIVE_MULTI_USER.  


you can see if things are defaulted or not by:

ops$tkyte@ORA9IR2> select name, value, isdefault from v$parameter where name like '%parallel%';
 
NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
log_parallelism                1                    TRUE
parallel_server                FALSE                TRUE
parallel_server_instances      1                    TRUE
recovery_parallelism           0                    TRUE
fast_start_parallel_rollback   LOW                  TRUE
parallel_min_percent           0                    TRUE
parallel_min_servers           0                    TRUE
parallel_max_servers           10                   TRUE
parallel_instance_group                             TRUE
parallel_execution_message_siz 4096                 TRUE
e
 
parallel_adaptive_multi_user   TRUE                 TRUE
parallel_threads_per_cpu       2                    TRUE
parallel_automatic_tuning      TRUE                 FALSE
 
13 rows selected.


so, here this says oracle set my max servers to 10 based on my other settings (it is defaulted).

now, if I:

ops$tkyte@ORA9IR2> alter system set parallel_max_servers = 15 scope=spfile;
 
System altered.
 
ops$tkyte@ORA9IR2> connect / as sysdba
Connected.
ops$tkyte@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  126948840 bytes
Fixed Size                   452072 bytes
Variable Size              58720256 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
ops$tkyte@ORA9IR2> select name, value, isdefault from v$parameter where name like '%parallel%';
 
NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
log_parallelism                1                    TRUE
parallel_server                FALSE                TRUE
parallel_server_instances      1                    TRUE
recovery_parallelism           0                    TRUE
fast_start_parallel_rollback   LOW                  TRUE
parallel_min_percent           0                    TRUE
parallel_min_servers           0                    TRUE
parallel_max_servers           15                   FALSE
parallel_instance_group                             TRUE
parallel_execution_message_siz 4096                 TRUE
e
 
parallel_adaptive_multi_user   TRUE                 TRUE
parallel_threads_per_cpu       2                    TRUE
parallel_automatic_tuning      TRUE                 FALSE
 
13 rows selected.


I can see I overrode the default.... and to put it back:


ops$tkyte@ORA9IR2> alter system reset parallel_max_servers scope=spfile sid='*';
 
System altered.
 
ops$tkyte@ORA9IR2> startup force;
ORACLE instance started.
 
Total System Global Area  126948840 bytes
Fixed Size                   452072 bytes
Variable Size              58720256 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
ops$tkyte@ORA9IR2> select name, value, isdefault from v$parameter where name like '%parallel%';
 
NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
log_parallelism                1                    TRUE
parallel_server                FALSE                TRUE
parallel_server_instances      1                    TRUE
recovery_parallelism           0                    TRUE
fast_start_parallel_rollback   LOW                  TRUE
parallel_min_percent           0                    TRUE
parallel_min_servers           0                    TRUE
parallel_max_servers           10                   TRUE
parallel_instance_group                             TRUE
parallel_execution_message_siz 4096                 TRUE
e
 
parallel_adaptive_multi_user   TRUE                 TRUE
parallel_threads_per_cpu       2                    TRUE
parallel_automatic_tuning      TRUE                 FALSE
 
13 rows selected.

Thanks Tom

Stewart W. Bryson, February 24, 2005 - 10:18 am UTC

You always come through when I can't find the answer anywhere else.

Resetting parallel_max_servers when using pfile

Stewart W. Bryson, February 05, 2007 - 12:46 pm UTC

Sorry to follow-up on this two years later.

Your solution to unsetting the PARALLEL_MAX_SERVERS worked successfully when using an spfile, but now I have a different client that has hard-coded these values and they are using a pfile. I don't know how to do the "unset" equivalent with a pfile.

Thanks.

February 05, 2007 - 6:19 pm UTC

comment out the line (#)
delete the line

NEVERMIND!

Stewart W. Bryson, February 05, 2007 - 2:32 pm UTC

Just remove it from the PFILE, right?

Sorry.

February 05, 2007 - 6:30 pm UTC

yeah

Curious ...

Greg, July 25, 2007 - 10:47 am UTC

I noticed you said earlier on this thread:

"You'll want to make sure you have sufficent reserve IO capabilities... If you are totally
IO bound now, parallel query will only make that worse."

That makes perfect sense to me and what I figured originally .. but as I was reading up on these parallel parameters and such ... I came across this:

"You should increase the value if the system is I/O bound."
(and so you get the full context .. here's the link:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams156.htm#REFRN10163
)

Ok ... so maybe I'm just confused over nothing, but just wondering - if we are IO bound ... how does increasing the parallel_threads_per_cpu help? (assuming as well - as you said - if you're IO bound, parallel isn't going help?)

... or am I just taking everything completely out of context!??

Hoping you can help explain this better than the documentation.

July 26, 2007 - 5:27 pm UTC

I don't see how it would help, it would simply allow us to flood the system with even more concurrent IO requests.

I believe it to be "wrong"

value of recovery_parallelism not been set to def value on no of cpus on DR side

Ann, November 05, 2010 - 4:58 pm UTC

Tom i had to reset the paramter recovery_parallelism so oracle can set to the no of cpus but after resetting i bounced the DR side so the parameter takes effect and the show parameter shows
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0

Should it now show 8 which is what the value of cpu is

November 06, 2010 - 7:29 pm UTC

define what you mean by "reset" and where you did this reset

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer