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@xxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx; mccdba1@xxxxxxxxx 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@<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@xxxxxxxxx> wrote: >From: dba1 mcc <mccdba1@xxxxxxxxx> >Subject: too many ora_pXXX pop-up? >To: oracle-l@xxxxxxxxxxxxx >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 > > > > > > > > >-- >//www.freelists.org/webpage/oracle-l > > >