How many would you like?

You can configure most process counts.

Parallel processes. If a table/index has parallelism set, or the parallel hint is specified in the query, you'll get your SELECT statement broken down among those parallel processes.

Some things to check:

Look at DEGREE & INSTANCES from DBA_TABLES and DBA_INDEXES where these are > 1. If DEGREE > 1 then parallelism will be used as long as parallelism is enabled in the instance.

Check parallel_max_servers in v$parameter

Here's some documentation:

Using Parallel Execution
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com


CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

They are parallel processes. Likely someone is running a parallel query
once and awhile. They would come and go as needed depending on how the
database parameters are set.

Here is a place to start reading about Parallel:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/manproc.h
tm#i1010373

HTH

Ric Van Dyke
Hotsos Enterprises, Ltd.

Are they a problem?

As a DBA, considering that a little knowledge can be a dangerous thing, I'd definitely want to know if this is a degree setting on objects or a user/developer/analyst "sucking the resources dry".
Parallelism is a cool feature, but in the wrong hands, it can bring a production database down to it's knees...

Query sql_text for the one of the parallel processes next time and get the SQL Statement running.  Just join V$Session and V$Sql_Text on sql_id where program like 'oracle_at_(P%' and you'll have your query(ies).

-  Is there a hint in the statement requesting parallel?  It can be either a request for parallel DDL or parallel DML or even in a CTAS for both.
-  If not, then query dba_tables for the objects involved to see if degree is set > 0.
-  Check the parameter PARALLEL_THREADS_PER_CPU, (commonly set to two)  this could compound the issue by doubling the requested parallel hint...


I'd also check to see how long this has been going on and how much of an impact it might have on the system:
The Servers Highwater is your parallel_max_servers and it will tell you how much is in use vs. what resources are REALLY available for this large process:
select * from V$PX_PROCESS_SYSSTAT;

Have you suffered any "downgrading" in parallel due to resource limitations?
SELECT NAME, VALUE FROM GV$SYSSTAT

WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'

OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';


This will only tell you since the last database cycle, but it's worth the check...
Good luck,

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen

"Go away before I replace you with a very small and efficient shell script..."

On Mon, 1/4/10, dba1 mcc wrote:

From: dba1 mcc
Subject: too many ora_pXXX pop-up?
To: oracle-l@freelists.org
Date: Monday, January 4, 2010, 7:34 AM

I have ORACLE database 10.2.0.4 on Redhat AS 5.4 (X86-64).  Recently I found some time there have process call "ora_pXXX_dbname" will pop up and may up to 128 processes.  Those "ora_pxxx_dbanme" will disappear normally after 10 -20 minutes.

Does anyone know what is "ora_pxxx_dbname"?
Why it come and why it go?

Why some many processes pop-up?

Thanks.

oracle    4653     1  0 17:21 ?        00:00:11 ora_p000_db01
oracle    4655     1  0 17:21 ?        00:00:08 ora_p001_db01
oracle    4657     1  0 17:21 ?        00:00:08 ora_p002_db01
oracle    4659     1  0 17:21 ?        00:00:08 ora_p003_db01
oracle    4661     1  0 17:21 ?        00:00:08 ora_p004_db01
oracle    4663     1  0 17:21 ?        00:00:08 ora_p005_db01
oracle    4665     1  0 17:21 ?        00:00:09 ora_p006_db01
oracle    4667     1  0 17:21 ?        00:00:08 ora_p007_db01
oracle    4669     1  0 17:21 ?        00:00:09 ora_p008_db01

....

oracle    5851     1  0 17:36 ?        00:00:00 ora_p122_db01
oracle    5853     1  0 17:36 ?        00:00:00 ora_p123_db01
oracle    5855     1  0 17:36 ?        00:00:00 ora_p124_db01
oracle    5857     1  0 17:36 ?        00:00:00 ora_p125_db01
oracle    5859     1  0 17:36 ?        00:00:00 ora_p126_db01
oracle    5861     1  0 17:36 ?        00:00:00 ora_p127_db01

The following query (from Jonathan Lewis) is useful for reporting how many parallel operations have been downgraded:

SELECT nvl(count(DISTINCT qcsid), 0) parallel_queries,
nvl(count(*), 0) parallel_operations,
nvl(sum(decode(degree, 1, 1, 0)), 0) operations_serialized,
nvl(sum(decode(degree / req_degree, 1, 1, 0)), 0) operations_not_downgraded,
nvl(sum(decode(req_degree - degree, 0, 0, decode(degree, 1, 0, 1))), 0) operations_downgraded,
100 - nvl((sum(degree) * 100) / decode(sum(req_degree), 0, 1, sum(req_degree)), 0) downgrade_severity
FROM (SELECT qcsid, server_group, server_set, max(degree) degree, min(req_degree) req_degree
FROM v$px_session
WHERE degree IS NOT NULL
GROUP BY qcsid, server_group, server_set)
/


David Fitzjarrell

________________________________
From: Kellyn Pedersen
To: oracle-l@freelists.org; mccdba1_at_yahoo.com
Sent: Mon, January 4, 2010 4:33:08 PM
Subject: Re: too many ora_pXXX pop-up?

As a DBA, considering that a little knowledge can be a dangerous thing, I'd definitely want to know if this is a degree setting on objects or a user/developer/analyst "sucking the resources dry".
Parallelism is a cool feature, but in the wrong hands, it can bring a production database down to it's knees...

Query sql_text for the one of the parallel processes next time and get the SQL Statement running.  Just join V$Session and V$Sql_Text on sql_id where program like 'oracle_at_(P%' and you'll have your query(ies).
-  Is there a hint in the statement requesting parallel?  It can be either a request for parallel DDL or parallel DML or even in a CTAS for both.
-  If not, then query dba_tables for the objects involved to see if degree is set > 0.
-  Check the parameter PARALLEL_THREADS_PER_CPU, (commonly set to two)  this could compound the issue by doubling the requested parallel hint...

I'd also check to see how long this has been going on and how much of an impact it might have on the system:
The Servers Highwater is your parallel_max_servers and it will tell you how much is in use vs. what resources are REALLY available for this large process:select* fromV$PX_PROCESS_SYSSTAT;

Have you suffered any "downgrading" in parallel due to resource limitations?
SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

This will only tell you since the last database cycle, but it's worth the check...
Good luck,

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen

"Go away before I replace you with a very small and efficient shell script..."

--- On Mon, 1/4/10, dba1 mcc wrote:

Thank you for the assist on the scripts!  I lost my PC harddrive this morning and am still restoring back to my full glory...:)  Sheesh, how many scripts can I collect in just four months! :)

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen

"Go away before I replace you with a very small and efficient shell script..."

On Mon, 1/4/10, David Fitzjarrell wrote:

From: David Fitzjarrell
Subject: Re: too many ora_pXXX pop-up?
To: oracle-l@freelists.org, mccdba1_at_yahoo.com
Date: Monday, January 4, 2010, 3:56 PM

The following query (from Jonathan Lewis) is useful for reporting how many parallel operations have been downgraded:

SELECT nvl(count(DISTINCT qcsid), 0) parallel_queries,
nvl(count(*), 0) parallel_operations,
nvl(sum(decode(degree, 1, 1, 0)), 0) operations_serialized,
nvl(sum(decode(degree / req_degree, 1, 1, 0)), 0) operations_not_downgraded,
nvl(sum(decode(req_degree - degree, 0, 0, decode(degree, 1, 0, 1))), 0) operations_downgraded,
100 - nvl((sum(degree) * 100) / decode(sum(req_degree), 0, 1, sum(req_degree)), 0) downgrade_severity
FROM (SELECT qcsid, server_group, server_set, max(degree) degree, min(req_degree) req_degree
FROM v$px_session
WHERE degree IS NOT NULL
GROUP BY qcsid, server_group, server_set)
/


David Fitzjarrell


From: Kellyn Pedersen
To: oracle-l@freelists.org; mccdba1_at_yahoo.com
Sent: Mon, January 4, 2010 4:33:08 PM
Subject: Re: too many ora_pXXX pop-up?

As a DBA, considering that a little knowledge can be a dangerous thing, I'd definitely want to know if this is a degree setting on objects or a user/developer/analyst "sucking the resources dry".
Parallelism is a cool feature, but in the wrong hands, it can bring a production database down to it's knees...

Query sql_text for the one of the parallel processes next time and get the SQL Statement running.  Just join V$Session and V$Sql_Text on sql_id where program like 'oracle_at_(P%' and you'll have your query(ies).

-  Is there a hint in the statement requesting parallel?  It can be either a request for parallel DDL or parallel DML or even in a CTAS for both.
-  If not, then query dba_tables for the objects involved to see if degree is set > 0.
-  Check the parameter PARALLEL_THREADS_PER_CPU, (commonly set to two)  this could compound the issue by doubling the requested parallel hint...


I'd also check to see how long this has been going on and how much of an impact it might have on the system:
The Servers Highwater is your parallel_max_servers and it will tell you how much is in use vs. what resources are REALLY available for this large process:
select * from V$PX_PROCESS_SYSSTAT;

Have you suffered any "downgrading" in parallel due to resource limitations?
SELECT NAME, VALUE FROM GV$SYSSTAT

WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'

OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';


This will only tell you since the last database cycle, but it's worth the check...
Good luck,

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen

"Go away before I replace you with a very small and efficient shell script..."

On Mon, 1/4/10, dba1 mcc wrote:

From: dba1 mcc
Subject: too many ora_pXXX pop-up?
To: oracle-l@freelists.org
Date: Monday, January 4, 2010, 7:34 AM

I have ORACLE database 10.2.0.4 on Redhat AS 5.4 (X86-64).  Recently I found some time there have process call "ora_pXXX_dbname" will pop up and may up to 128 processes.  Those "ora_pxxx_dbanme" will disappear normally after 10 -20 minutes.

Does anyone know what is "ora_pxxx_dbname"?
Why it come and why it go?

Why some many processes pop-up?

Thanks.

oracle    4653     1  0 17:21 ?        00:00:11 ora_p000_db01
oracle    4655     1  0 17:21 ?        00:00:08 ora_p001_db01
oracle    4657     1  0 17:21 ?        00:00:08 ora_p002_db01
oracle    4659     1  0 17:21 ?        00:00:08 ora_p003_db01
oracle    4661     1  0 17:21 ?        00:00:08 ora_p004_db01
oracle    4663     1  0 17:21 ?        00:00:08 ora_p005_db01
oracle    4665     1  0 17:21 ?        00:00:09 ora_p006_db01
oracle    4667     1  0 17:21 ?        00:00:08 ora_p007_db01
oracle    4669     1  0 17:21 ?        00:00:09 ora_p008_db01

....

oracle    5851     1  0 17:36 ?        00:00:00 ora_p122_db01
oracle    5853     1  0 17:36 ?        00:00:00 ora_p123_db01
oracle    5855     1  0 17:36 ?        00:00:00 ora_p124_db01
oracle    5857     1  0 17:36 ?        00:00:00 ora_p125_db01
oracle    5859     1  0 17:36 ?        00:00:00 ora_p126_db01
oracle    5861     1  0 17:36 ?        00:00:00 ora_p127_db01

I've lost count of how many scripts I have collected/written over the years; it's in the thousands, I suspect (20+ years of being an Oracle DBA) and I still have a good number of them on a flash drive for safekeeping.  If there's anything you need or want just ask as I may have a script (or two dozen) you might like.

David Fitzjarrell

________________________________
From: Kellyn Pedersen
To: oracle-l@freelists.org; mccdba1_at_yahoo.com; oratune@yahoo.com
Sent: Mon, January 4, 2010 6:01:12 PM
Subject: Re: too many ora_pXXX pop-up?

Thank you for the assist on the scripts!  I lost my PC harddrive this morning and am still restoring back to my full glory...:)  Sheesh, how many scripts can I collect in just four months! :)

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen

"Go away before I replace you with a very small and efficient shell script..."

--- On Mon, 1/4/10, David Fitzjarrell wrote:
________________________________
From: Kellyn Pedersen