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 Go to next message
Messages: 1
Registered: September 2007
Junior Member
Oracle version - 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 ;
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 ;

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
si.block_changes DESC,
si.physical_reads DESC,
s.logon_time DESC
Re: Enabling parallel DML in dbms_scheduler [message #270462 is a reply to message #270422] Thu, 27 September 2007 00:40 Go to previous message
Michel Cadot
Messages: 64823
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Previous Topic: To get all privileges, roles granted to a user
Next Topic: how to create partition
Goto Forum:

Current Time: Wed May 24 18:41:02 CDT 2017

Total time taken to generate the page: 1.47020 seconds