Re: too many ora_pXXX pop-up?
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, mccdba1@xxxxxxxxx, oratune@xxxxxxxxx
- Date: Mon, 4 Jan 2010 15:01:12 -0800 (PST)
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 * 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@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:
