Re: too many ora_pXXX pop-up?
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Mon, 4 Jan 2010 13:33:08 -0800 (PST)
Message-ID: <372958.29620.qm_at_web32006.mail.mud.yahoo.com>
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_<host>(P%' and you'll have your query(ies).
�
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..."
Date: Mon, 4 Jan 2010 13:33:08 -0800 (PST)
Message-ID: <372958.29620.qm_at_web32006.mail.mud.yahoo.com>
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_<host>(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 <mccdba1_at_yahoo.com> wrote:
From: dba1 mcc <mccdba1_at_yahoo.com>
Subject: too many ora_pXXX pop-up?
To: oracle-l_at_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
� � �
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 04 2010 - 15:33:08 CST