Home » SQL & PL/SQL » SQL & PL/SQL » Enabling parallel DML in dbms_scheduler
Enabling parallel DML in dbms_scheduler [message #270422] |
Wed, 26 September 2007 19:08  |
mitarupa
Messages: 1 Registered: September 2007
|
Junior Member |
|
|
Oracle version - 10.2.0.2.0 running on two node RAC (Linux Red Hat)
I have a large insert for which I need to use parallel DML. My stored procedure looks like the following
CREATE OR REPLACE procedure test_scheduler is
l_cnt number ;
begin
execute immediate 'truncate table ccss_key_maps_temp drop storage' ;
execute immediate 'alter session force parallel dml';
insert /*+ append nologging parallel(b,40) */ into ccss_key_maps_temp b select /*+ parallel(a,40) */ * from ccss_key_maps a ;
commit;
end;
/
I am using dbms_scheduler to schedule this program.
exec dbms_scheduler.drop_job('BO_AGG_ETL',TRUE) ;
exec dbms_scheduler.drop_program('BO_AGGREGATE_PGM') ;
exec dbms_scheduler.create_program (
program_name => 'BO_AGGREGATE_PGM',
program_type => 'STORED_PROCEDURE',
program_action => 'TEST_SCHEDULER',
number_of_arguments => 0,
enabled => TRUE ,
comments => 'Program for populating BO summary table'
) ;
exec dbms_scheduler.create_job (
job_name => 'BO_AGG_ETL',
job_class => 'ADHOC_CLASS',
program_name => 'BO_AGGREGATE_PGM',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Job to run BO aggregate ETL') ;
When this job gets executed I could see only thread is doing the block changes using the following query. PDML_STATUS is disabled for all of them except one.
If I try to invoke the stored procedure from sqlplus session , then I could see pdml_status enabled for all slaves. Consequently the external invocation runs significantly faster than the one triggered by scheduler. Apparently, altering session in the second execute immediate statement is not working. Can anybody please tell me how I can enable PDML when running a job from dbms_scheduler? Any help will be appreciated.
SELECT /* Physical Writes/Reads */ si.SID, s.username,pdml_status, pddl_status, pq_status,
TO_CHAR (s.logon_time, 'MM/DD HH24:MI:SS') "Connect Time",
ROUND ((SYSDATE - s.logon_time) * 24 * 60, 2) "mins elapsed",
p.inst_id, s.osuser Client_User, SUBSTR (s.module, 1,
25) Module,
SUBSTR (sql_text, 1, 200) sqltext,
SUBSTR (s.program, 1, 25) "Program",
si.physical_reads "Physical Reads",
si.block_changes block_changes, si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.consistent_changes "Consistent Changes", s.process "Process",
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.action
FROM gv$session s, gv$process p, SYS.gv_$sess_io si, gv$sqltext vt
WHERE s.paddr = p.addr(+)
AND s.inst_id = p.inst_id(+)
AND si.SID(+) = s.SID
AND si.inst_id(+) = s.inst_id
AND (s.username IS NOT NULL)
AND s.status = 'ACTIVE'
AND s.sql_hash_value = vt.hash_value(+)
AND s.inst_id = vt.inst_id(+)
AND piece(+) = 0
ORDER BY
si.block_changes DESC,
si.physical_reads DESC,
s.logon_time DESC
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 16:58:04 CST 2025
|