|DBMS_PARALLEL_EXECUTE - Use the DBMS_PARALLEL_EXECUTE package to break down large workloads into manageable chunks that can be run in parallel.|
Tomasz said...It's very nice :)
David Mann said...Didn't even know this existed, thanks for sharing!
SysDba said...Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) Part Number E16760-05
DBMS_PARALLEL_EXECUTE >> download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_parallel_ex.htm
Don't you just hate it when Oracle break their own links. :)
I've corrected it now. Thanks for the heads-up.
guest said...example copied from MOS
Tim... said...No it wasn't! I did it myself. There was no MOS note available when I wrote this. Perhaps they copied me!
Jim said...In the second of the "Complete Examples", I got a compilation error when trying to create the process_update procedure. I fixed it by changing the line
t.session_id = SYS_CONTEXT(''USERENV'',''SESSIONID'')
t.session_id = SYS_CONTEXT('USERENV','SESSIONID')
(I've been using your articles to help me study for my OCP upgrade exams ever since 9i - many thanks for your fine work!)
Yep. A bad bit of copy&paste. :) I've corrected it now. Thanks for the heads-up.
Ashraf Nashed said...In your example here : "User-defined framework",
when you use "EXECUTE IMMEDIATE l_sql_stmt USING l_start_rowid, l_end_rowid;"
Does this still support parallelism or it turns to sequential ?
Tim... said...Ashraf: You would run that same code in several sessions. Each would just get the next chunk and processes it.The more sessions you run it in, the more parallel the operation would be.
It would make sense to submit these as jobs. :)
Deepak said...Awesome Article . One question here. how can update a table from another table through this utility?. Let’s say I have to create rowid chunk on staging table and want to update records of main table based to common surrogate key of both tables?
It's exactly the same. You generate the chunks on the staging table, but your update happens on destination table. The SQL does not have to be on the same table where the chunks were generated.
Deepak said...Hi Tim , If I create chunk on Stging table then it doesen't work. i.e. below update is not working
update tgt set (c1,c2) = (select c1,c2 from stg src where src.pk = tgt.pk)
where rowid between :start_id and :end_id;
but if create chunk on tgt table table then above statment works.
Depends which side you are driving the update from.
Why not do this?
ALTER SESSION ENABLE PARALLEL DML;
MERGE /*+ append parallel(tgt 8) */ INTO tgt
ON (tgt.id = stg.id)
WHEN MATCHED THEN
UPDATE SET tgt.c1 = stg.c1,
tgt.c2 = stg.c2;
Deepak said...Staging and main table has same structure . I have 60 staging tables and similar 60 main tables. Procedure take table name as input and fetch all columns of source table (Staging table) and target table(main table) through ALL_TAB_COLUMNS view and generate generic update statement via dynamic sql. I am not able to create a generic merge statment.
Tim... said...Fine, but your performance will suck! :(
See this for ways to write an update based on a query.
Deepak said...Hi Tim, How can be captured error at individual record level. I saw exception handling at last example via DBMS_PARALLEL_EXECUTE.set_chunk_status . But this will give error at chunk_id level. If chunk has 1000 of rows and if error comes at 50th record then is there any way to capture it? I want to save error into error log table.
It is DML, so normal rules apply. Try DBMS_ERRLOG:
Deepak said...Hi Tim, I copy and pasted your code but getting error ORA-29495: invalid state for resume task
... I coundnt find any soulation . Please hel me.
I just ran all the examples in my article and it all worked. I can't recreate your issue...
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!