Post date: 20-Jun-2009 10:14:27
select * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;
Where can I find more information about Parallel Execution activites on the system?
SELECT
PS.SID,
DECODE(SERVER_SET, NULL, 'COORDINATOR', 1, ' CONSUMER',
' PRODUCER') ROLE,
DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU') ACTION,
SQ.SQL_TEXT
FROM
V$PX_SESSION PS,
V$SESSION_WAIT SW,
V$SQL SQ,
V$SESSION S,
AUDIT_ACTIONS AA
WHERE
PS.SID = SW.SID AND
S.SID = PS.SID AND
S.SQL_ADDRESS = SQ.ADDRESS (+) AND
AA.ACTION = S.COMMAND
ORDER BY PS.QCSID, NVL(PS.SERVER#, 0), PS.SERVER_SET;
How can the progress of a parallelized full table scan be monitored?
Create the following fuction in the schema of a user who has the the the privileges
grant analyze any to <user>;
grant select any table to <user>;
directly (not via role) granted. Usually not even the user 'SYS' has these privileges granted directly.
create or replace function sap_fts_progress_parallel
(owner varchar2, tab_name varchar2, sess number, blocks number)
RETURN char IS
stat_nr number;
tot_blk number;
unu_blk number;
dummy number;
query_act number;
perc number;
hwm number;
begin
if blocks=-1 then
dbms_space.unused_space(owner, tab_name, 'TABLE', tot_blk,
dummy, unu_blk, dummy, dummy, dummy, dummy, dummy);
hwm := tot_blk-unu_blk;
else
hwm := blocks;
end if;
select statistic# into stat_nr from v$statname
where name='physical reads direct';
select count(1) into query_act from v$px_sesstat
where statistic#=stat_nr
and qcsid=decode(sign(sess),1,sess,qcsid);
if query_act=0 then
return 'Query not active';
else
select sum(value)/decode(hwm,0,1,hwm)*100 into perc
from v$px_sesstat where statistic#=stat_nr
and sid <>qcsid
and qcsid=decode(sign(sess),1,sess,qcsid);
return to_char(perc,'999.9')||'% of '||to_char(hwm)||' Blocks';
end if;
end;
/
Doublecheck if the character '|' is copied when you cut and paste the function definition.Execute the function with
select sap_fts_progress_parallel('<OWNER>','<TABLE>',<SID>,-1) from
dual;
when your parallel query is running to get the percentual progress and the total number of blocks to be read
(example output: '43.7% of 6973 Blocks'). <OWNER>.<TABLE> is the object on which the query runs. <SID> specifies the session id which initiated the query.
For larger tables you can increase performance if you specify the total number of blocks given back from the first query at the second and all further executions:
select sap_fts_progress_parallel('<OWNER>','<TABLE>',<SID>,<BLOCKS>)
from dual;
If you are sure that there is no other parallel query running concurrently then you can also set <SID> equal '-1'.
If - against the prerequisite - another query runs then the percentual value is undefined.