Re: too many ora_pXXX pop-up?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: Kellyn Pedersen <kjped1313@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx, mccdba1@xxxxxxxxx
- 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@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx; mccdba1@xxxxxxxxx; oratune@xxxxxxxxx
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@xxxxxxxxx> wrote:
>From: David Fitzjarrell <oratune@xxxxxxxxx>
>Subject: Re: too many ora_pXXX pop-up?
>To: oracle-l@xxxxxxxxxxxxx, mccdba1@xxxxxxxxx
>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@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
>>
>>
>>
>
>
Other related posts:
