jkbops.blogg.se

1. #Oracle pl sql execute procedure example how to#
2. #Oracle pl sql execute procedure example serial#
3. #Oracle pl sql execute procedure example code#

That procedure does exactly what its name implies, it ‘chunks up’ a table by ROWID ranges in a manner similar to what we just did above.

Second we invoked the CREATE_CHUNKS_BY_ROWID procedure. That is just a unique name we’ll use to refer to our big process. We started by creating a named task – ‘PROCESS BIG TABLE’ in this case. So, we’ll make the following API calls:Ģ dbms_parallel_execute.create_task(‘PROCESS BIG TABLE’) ģ dbms_parallel_execute.create_chunks_by_rowid We’ll use the ROWID range, I find that to simply be the most efficient – it creates non-overlapping ranges of the table (contention free) and doesn’t require querying the table to decide the ranges, it just uses the data dictionary. We can do this by some numeric range – useful for tables that use a SEQUENCE to populate their primary key, by any arbitrary SQL you want to code, or by ROWID ranges. Now, to start the process – we first need to break up the table into small pieces. I am using SYS_CONTEXT to get the SESSIONID so we can monitor how much work was done by each ‘thread’, each parallel session.

#Oracle pl sql execute procedure example code#

The modified code has not changed much at all. That was it – just add the ROWID inputs and the predicate.

#Oracle pl sql execute procedure example serial#

We will not cover every possible use of this package (it is fully documented in the Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) guide) – but rather use just enough of it to implement the process I’ve just described.Īssuming we start with the empty table T2 – we’ll modify our serial process now to look like this – additions to the original, simple serial process are in bold:Ģ procedure serial( p_lo_rid in rowid, p_hi_rid in rowid )ĥ for x in ( select object_id id, object_name textġ1 insert into t2 (id, text, session_id )ġ2 values ( x.id, x.text, sys_context( 'userenv', 'sessionid' ) ) We’d like to do it with as little work as possible – modify as little code as possible and be responsible for generating very little new code. Let’s start with the premise that we have a SERIAL routine, that we’d like to execute in parallel against some large table. The beauty of the new package though, is that it eliminates much of the tedious work that you otherwise need to perform. You can implement the same approach yourself, manually, and I show how in the section on “Old School Do-It-Yourself Parallelism” that follows. Using it, you can execute a SQL or PL/SQL statement in parallel by taking the data to be processed and breaking it up into multiple, smaller streams. Starting with Oracle Database 11g Release 2 and above – we have a new way to implement parallelism via the DBMS_PARALLLEL_EXECUTE builtin package. We cannot afford a rather extensive rewrite of the implementation, but we would like to execute it in parallel. !Say we have that same process as in the preceding section: the serial, simple procedure. You might consider breaking the table up into a few hundred pieces and doing parallel somewhere between 32 and 48 concurrent processes (I based that on your 30% cpu utilization - you don't want to kill the machine - you don't want to be at over 90%'ish utilization in general - really close but not too much over)

#Oracle pl sql execute procedure example how to#

Here is an excerpt from my last book that details how to do this. Procedure p( lo_rowid in rowid, hi_rowid in rowid )įor x in (select * from t where rowid between lo_rowid and hi_rowid)Īnd then we can run many of these at the same time. Right now your code looks something like: This would be a good time to look at dbms_parallel_execute! I understand how to set the parallel degree in the DBMS_JOB package, but I'm not sure that my procedure (looping over a cursor) can take advantage of that. How can I make use of my hardware to speed up this process? I would love to see this thing use every core (or at least 14 or 15 cores) at 100% utilization and finish the job in less than 24 hours. My code is very simple in that is creates a cursor to loop over all the rows in the table with the BLOB data, then for each one, it calls CTX_DOC.POLICY_FILTER() to run the BLOB through the AUTO_FILTER, then inserts the plain text output into a new table. When running my procedure, one core is getting utilized at a steady 30%. My DB server has a little juice, boasting 16 cores and 128 GB RAM. At this rate, I am half way through a 6.5 day run for a very simple task. I am successfully using the CTX_DOC.POLICY_FILTER() procedure to accomplish this, but when run in serial cursor loop, the stored proc processes about 5 BLOBs per second. I need to convert these BLOBS to their plain text equivalent to index the file contents in a system external to Oracle. I have a table with 2.8 million rows, and one of the columns is a BLOB b/c this table holds binary attachments.