tag:blogger.com,1999:blog-5124641802818980374.post939085113823299915..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Performing PL/SQL functions in parallel which are not "pure"Unknownnoreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5124641802818980374.post-63468878277395366382009-09-11T00:00:54.895+02:002009-09-11T00:00:54.895+02:00Mike,
as long as you don't know where most of...Mike,<br /><br />as long as you don't know where most of your time is spent, i.e. why it takes 1 to 2 minutes to create the file, everything is just speculation. This means even when running your process in parallel you still might need the same time if the jobs wait in parallel for the same bottleneck.<br /><br />You can actually call your function in parallel, e.g. by simply submitting a job per group and making sure the database is allowed to spawn a sufficient number of job processes, e.g. using the JOB_QUEUE_PROCESSES parameter when using DBMS_JOB.<br /><br />Search e.g. on AskTom for "Do it yourself parallelism" or e.g. <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211" rel="nofollow">this</a><br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-75551754551929660542009-09-10T11:33:27.889+02:002009-09-10T11:33:27.889+02:00Hi,
I am looking around to find an answer to my p...Hi,<br /><br />I am looking around to find an answer to my problem and stumbled over your site.<br />Perhaps you can share me some of your insights...<br />I am not really looking for ready baked code, just a hint or answer if it is possible what i want.<br />Anyway ....<br /><br />In my plsql function we currently do this:<br />-look at table A, group by Colom A<br />-for every row in group by set call another function, B.<br />-Function B just writes all entries in Table A (that match entry) to a file.<br />-Thus creating a file for every group by record conatining all records belonging to that group by record.<br /><br />We use utl_file for this and buffer technique so that it is sped up the most.<br />Indeed the time to create a 12Mb file is roughly the same as it is for a 10K file.<br />However every file takes about 1 to 2 minutes to create.<br />When there are 70 files to be made it takes 1,5 hrs !<br /><br />So this is my problem: can i speed this up by creating these files in parallel ?<br />Sort of like calling Function B in parallel ?<br /><br />kind regards MikeMichael Reitsmahttps://www.blogger.com/profile/04370984334273990742noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-49472206562321675522007-11-18T13:36:00.000+01:002007-11-18T13:36:00.000+01:00We're using the SYS_CONTEXT trick for implementing...We're using the SYS_CONTEXT trick for implementing parametrized views on "Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit" - Solaris E25k. So this seems to work already below 9.2.0.8. Reason we needed parametrized views: aggregate in parallel on instance A, transmitting result only to instance B over DB link, and inserting stuff on instance B in parallel. Accessing table functions seem to not work (cannot access remote object type). Btw.: intersting blog!Anonymousnoreply@blogger.com