No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1485
Act. users last 24h4
Act. users last hour0
Registered user hits last week184
Registered user hits last month815
Go up

Number of thread associated with DOP
Next thread: Table decorated with parallel option and DOP is AUTO
Prev thread: Soving library cache lock cursor

Message Score Author Date
Hi, Examining the current queries that used par...... Lauri Nov 26, 2019, 12:08
Hi Lauri, You use a query much like "How To Mon...... Philip Wisse Nov 26, 2019, 13:07
Hi Philip, Here is the SQL I used: select ...... Lauri Nov 26, 2019, 13:46

Follow up by mail Click here


Subject: Number of thread associated with DOP
Author: Lauri, Netherlands
Date: Nov 26, 2019, 12:08, 10 days ago
Os info: Linux x86 64bit
Oracle info: 12c and higher
Error info: n/a
Message: Hi,

Examining the current queries that used parallel processes with DOP (parallel_degree_policy=AUTO), by querying gv$px_session, gv$session, gv$process, v$sqlarea, v$sqltext, I have this result:

Elapsed
Logon time Buffer SQL Requested Actual
time Username SPID SID Serial Execs (sec) gets ID DOP DOP
------------------- --------------- ------- ------- ------- -------- ------------ ---------------- ------------------------- --------- ------
2019/11/26:11:52:10 A_KUTLU 28045 226 35155 1 355,597 1,306,346 449kmg6hj8jnq
2019/11/26:11:55:45 A_KUTLU 5177 1177 29909 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:55:45 A_KUTLU 5185 426 26744 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:55:45 A_KUTLU 5189 614 47796 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:55:45 A_KUTLU 5181 51 58373 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:58:47 A_KUTLU 5193 1002 51318 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:58:47 A_KUTLU 5203 62 63703 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:58:47 A_KUTLU 5195 823 25667 1 355,597 1,306,346 449kmg6hj8jnq 2 2
2019/11/26:11:58:47 A_KUTLU 5199 1386 33318 1 355,597 1,306,346 449kmg6hj8jnq 2 2

(I am sorry for the ugky display)

SQL 449kmg6hj8jnq used DOP=2, but I have 9 rows returned for this SQL.
How can I get 9 rows instead of 2 x Y rows (y = number of active session for SQL 449kmg6hj8jnq)?

Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Number of thread associated with DOP
Author: Philip Wisse, Netherlands
Date: Nov 26, 2019, 13:07, 10 days ago
Message: Hi Lauri,

You use a query much like "How To Monitor Parallel Queries In Oracle DB":
http://sachinramesh12cdba.blogspot.com/2019/06/oracle-dba-scripts-all-in-one-place.html

I can see you get 9 rows. The number of rows matches the number of sessions. What is your SQL?

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Number of thread associated with DOP
Author: Lauri, Netherlands
Date: Nov 26, 2019, 13:46, 10 days ago
Message: Hi Philip,

Here is the SQL I used:

select
to_char(s.logon_time, 'YYYY/MM/DD:HH24:MI:SS') as logon_time,
s.username username,
p.spid as spid,
s.sid sid,
s.serial# serial,
sa.executions executions,
sa.elapsed_time/1000 elapsed_time_sec,
sa.buffer_gets buffer_gets,
s.sql_id sql_id,
-- decode(px.qcinst_id, NULL, 'QC', '(Slave)') as qc_slave,
-- to_char(px.server_set) as slave_set,
px.req_degree as requested_dop,
px.degree as actual_dop
from
gv$px_session px,
gv$session s,
gv$process p,
v$sqlarea sa,
v$sqltext st
where
px.sid = s.sid (+)
and
px.serial# = s.serial#
and
px.inst_id = s.inst_id
and
p.inst_id = s.inst_id
and
p.addr = s.paddr
and
s.sql_address = st.address
and
s.sql_hash_value = st.hash_value
and
st.address = sa.address
and
st.hash_value = sa.hash_value
and
round(sa.buffer_gets / decode(sa.executions, 0, 0.01, sa.executions),2) >= 100
group by
s.logon_time,
s.username,
p.spid,
s.sid,
s.serial#,
sa.executions,
sa.elapsed_time,
sa.buffer_gets,
s.sql_id,
-- decode(px.qcinst_id,NULL, 'QC', '(Slave)'),
-- to_char(px.server_set),
px.req_degree,
px.degree
order by
s.username,
s.logon_time,
s.sql_id,
px.req_degree,
px.degree;

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here