Hi,
I got a stuck process and I having trough identify why and where the process stuck.
It got stuck on procedures that takes hours (4-7h) and when I run it on procedure that runs few minutes it finish successfully.
When I say stuck I mean that by user_parallel_execute_task it finished but the call 'exec update_a;' is still running...
create or replace procedure UPDATE_A(in_proc_name in varchar2 default 'UPDATE_A_INNR', in_chunked_table_name in varchar2 default 'A') as
l_task VARCHAR2(30);
l_status NUMBER;
l_try number;
l_sql_stmt varchar2(4000);
l_chunk_size number := 5000 ;
l_parallel_level number;
l_username VARCHAR2(100);
BEGIN
select user
into l_username
from dual;
select display_value - 2
into l_parallel_level
from v$parameter where name='cpu_count';
SELECT to_char(sysdate, 'YYMMDDHHMISS') || '_PRLL_TSK'
into l_task
FROM DUAL;
DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
table\_owner => l\_username,
table\_name => in\_chunked\_table\_name,
by\_row => TRUE,
chunk\_size => l\_chunk\_size);
l_sql_stmt := 'BEGIN '||in_proc_name||'(:start_id, :end_id , '||l_chunk_size||' ); END;';
DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
sql\_stmt => l\_sql\_stmt,
language\_flag => DBMS\_SQL.NATIVE,
parallel\_level => l\_parallel\_level);
-- If there is error, RESUME it for at most 2 times.
DBMS_OUTPUT.put_line('1 - '||l_status);
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
DBMS_OUTPUT.put_line('2 - '||l_status);
WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
Loop
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.resume_task(l_task);
l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
END LOOP;
--DBMS_PARALLEL_EXECUTE.drop_task(l_task);
EXCEPTION
when OTHERS then
dbms\_output.put\_line('The error occured in procedure ,details : '||SQLERRM);
rollback;
END;
/
create or replace PROCEDURE UPDATE_A_INNR (in_start_rowid rowid, in_end_rowid rowid, in_limit_bulk IN NUMBER DEFAULT 100) AS
BEGIN
DBMS_OUTPUT.ENABLE(10000000);
DBMS_OUTPUT.put_line(to_char(sysdate,'ddMMyyyy-hh24miss'));
-- here i have usually update by select
update A
set name='1'
where a.rowid between in_start_rowid AND in_end_rowid;
DBMS_OUTPUT.put_line(to_char(sysdate,'ddMMyyyy-hh24miss'));
EXCEPTION
when OTHERS then
dbms\_output.put\_line('The error occured in procedure ,details : '||SQLERRM);
rollback;
END;
/
Thank you!