Dml Does Not Run Parallel In Deeper Nesting In Dbms_job And Pl/Sql On 10.2.0.5
Last updated on MARCH 27, 2014
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.5 to 10.2.0.5 [Release 10.2]Information in this document applies to any platform.
Symptoms
=== ODM Issue Clarification ===
DML is not executed in parallel when requested from within a job on 10205, same job used to work in 10204.
--~ PACKAGE declaration
create or replace package useretsting.testcase_a is
procedure run_testcase;
procedure run_testcase_job;
end testcase_a;
/
---------------------------------------------
--~ PACKAGE body
create or replace package body useretsting.testcase_a is
procedure run_testcase is
sqlstmt varchar2(4000);
begin
--~ force parallel query and tracing
execute immediate 'alter session force parallel query';
execute immediate 'alter session set "_px_trace" = "all"';
--~ sql to execute
select sqlstmt into sqlstmt from
(select
'begin INSERT INTO useretsting.endtab
SELECT *
FROM useretsting.btab1 MINUS
SELECT *
FROM useretsting.btab2;
END;' sqlstmt
from dual);
--~ sql execute
execute immediate sqlstmt;
end;
procedure run_testcase_job is
begin
DBMS_JOB.isubmit(333333,'begin testcase_a.run_testcase; end;',next_date => sysdate + 1/17280);
commit;
end;
end testcase_a;
/
create or replace package useretsting.testcase_a is
procedure run_testcase;
procedure run_testcase_job;
end testcase_a;
/
---------------------------------------------
--~ PACKAGE body
create or replace package body useretsting.testcase_a is
procedure run_testcase is
sqlstmt varchar2(4000);
begin
--~ force parallel query and tracing
execute immediate 'alter session force parallel query';
execute immediate 'alter session set "_px_trace" = "all"';
--~ sql to execute
select sqlstmt into sqlstmt from
(select
'begin INSERT INTO useretsting.endtab
SELECT *
FROM useretsting.btab1 MINUS
SELECT *
FROM useretsting.btab2;
END;' sqlstmt
from dual);
--~ sql execute
execute immediate sqlstmt;
end;
procedure run_testcase_job is
begin
DBMS_JOB.isubmit(333333,'begin testcase_a.run_testcase; end;',next_date => sysdate + 1/17280);
commit;
end;
end testcase_a;
/
--~ run procedure to create job to run problematic insert
conn useretsting/oracle
begin
testcase_allianz.run_testcase_job;
end;
/
conn useretsting/oracle
begin
testcase_allianz.run_testcase_job;
end;
/
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms