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).
-� 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-l
Received on Mon Jan 04 2010 - 15:33:08 CST

Original text of this message