Select statements
Group by query coordinator
select
count(*),
pxs.qcsid,
spx.osuser,
spx.username,
max(case when spx.sid = pxs.qcsid then spx.program end) program,
spx.module,
round((sysdate - min(spx.sql_exec_start)) * 24*60*60) sql_start_s_ago,
round(max(spx.wait_time_micro) / 1000/1000, 2) max_wait_s,
round(min(spx.wait_time_micro) / 1000/1000, 2) min_wait_s,
max(spx.event) keep (dense_rank first order by spx.wait_time_micro desc) event_max_waited,
sql.sql_text,
max(pxs.qcserial#) qcserial#,
count( spx.taddr) cnt_txn,
count(distinct spx.taddr) cnt_dist_txn
from
v$px_session pxs join
v$session spx on pxs.sid = spx.sid and
pxs.serial# = spx.serial# left join
v$sqlarea sql on spx.sql_id = sql.sql_id
where
spx.osuser = 'rnyffenegger'
group by
spx.osuser,
spx.username,
spx.module,
sql.sql_text,
pxs.qcsid
--pxs.qcserial#
order by
count(*) desc;
Joins to other dynamic performance views
select
pxs.qcsid,
-- spx.osuser,
-- spx.username,
-- spx.program,
-- spx.module,
round((sysdate - spx.sql_exec_start ) * 24*60*60) sql_start_s_ago,
round((sysdate - to_date(trx.start_time, 'mm/dd/rr hh24:mi:ss')) * 24*60*60) trx_start_s_ago,
round(spx.wait_time_micro / 1000/1000, 2) max_wait_s,
trx.used_ublk,
trx.used_urec,
spx.event,
-----------------------
slo.opname slo_opname,
slo.target slo_target,
slo.sofar slo_sofar,
slo.totalwork slo_totalwork,
slo.elapsed_seconds slo_elapsed_sconds,
-- round((sysdate - slo.start_time ) * 24*60*60) slo_start_s_ago,
slo.time_remaining slo_time_remaining,
-----------------------
sql.sql_text,
pxs.qcserial# qcserial#
from
v$px_session pxs join
v$session spx on pxs.sid = spx.sid and
pxs.serial# = spx.serial# left join
v$sqlarea sql on spx.sql_id = sql.sql_id left join
v$transaction trx on spx.taddr = trx.addr left join
v$session_longops slo on spx.sid = slo.sid and
spx.serial# = slo.serial#
where
-- spx.osuser = 'rnyffenegger'
pxs.qcsid = 1661
order by
pxs.qcsid,
slo.time_remaining desc
;
Column SERVER_SET
A session can execute multiple parallel queries concurrently. The value of server_set identifies each of these statements.
The following statements try to demonstrate this. The statements are inspired by
Jonathan Lewis.
First, we need a few tables:
create table tq84_t2 nologging as
select *
from
dba_objects
;
create table tq84_t3 nologging as select * from tq84_t2;
create table tq84_t4 nologging as select * from tq84_t2;
create table tq84_t5 nologging as select * from tq84_t2;
begin
dbms_stats.gather_table_stats(user,'tq84_t2', method_opt=>'for all columns size 1');
dbms_stats.gather_table_stats(user,'tq84_t3', method_opt=>'for all columns size 1');
dbms_stats.gather_table_stats(user,'tq84_t4', method_opt=>'for all columns size 1');
dbms_stats.gather_table_stats(user,'tq84_t5', method_opt=>'for all columns size 1');
end;
/
Then we execute four parallel statements concurrently:
declare
cursor c2 is select /*+ parallel (t 2) */ object_id from tq84_t2 t;
cursor c3 is select /*+ parallel (t 3) */ object_id from tq84_t3 t;
cursor c4 is select /*+ parallel (t 4) */ object_id from tq84_t4 t;
cursor c5 is select /*+ parallel (t 5) */ object_id from tq84_t5 t;
o2 number;
o3 number;
o4 number;
o5 number;
begin
open c2; fetch c2 into o2;
open c3; fetch c3 into o3;
open c4; fetch c4 into o4;
open c5; fetch c5 into o5;
dbms_output.put_line(o2 || ' - ' || o3 || ' - ' || o4 || ' - ' || o5);
dbms_session.sleep(20);
close c2;
close c3;
close c4;
close c5;
end;
/
select
px.sid, px.qcsid,
px.server_group, px.server_set, px.server#,
sq.sql_text
from
v$px_session px join
v$session ss on px.sid = ss.sid join
v$sql sq on ss.sql_id = sq.sql_id and ss.sql_child_number = sq.child_number
where
ss.username = user and
ss.sid = px.sid
order by
px.server_group nulls first,
px.server_set,
px.server#
;
Cleaning up
drop table tq84_t2;
drop table tq84_t3;
drop table tq84_t4;
drop table tq84_t5;