Re: too many ora_pXXX pop-up?
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 5 Jan 2010 05:00:13 -0800 (PST)
Date: Tue, 5 Jan 2010 05:00:13 -0800 (PST)
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; email@example.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, firstname.lastname@example.org >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; email@example.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 >> >> >> > >Received on Tue Jan 05 2010 - 07:00:13 CST