Re: too many ora_pXXX pop-up?

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 <kjped1313_at_yahoo.com>
To: oracle-l_at_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 <oratune_at_yahoo.com> wrote:


>From: David Fitzjarrell <oratune_at_yahoo.com>
>Subject: Re: too many ora_pXXX pop-up?
>To: oracle-l_at_freelists.org, mccdba1@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 <kjped1313_at_yahoo.com>
>To: oracle-l_at_freelists.org; mccdba1@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_<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* 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 <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