I have an Activity table which is getting all the table events of the system. Events like new orders, insertion/deletion on all the system tables will be inserted into this table. So, the no of events/sec is really huge for Activity table.
Now, I want to process the incoming events based on the business logic depending on the table responsible for raising the event. Every table may have different procedure to do the processing.
I used the same link Parallelizing calls in PL/SQL
As a solution I have created multiple
dbms_scheduler
jobs which will be called at the same time. All these jobs (JOB1, JOB2--- - -JOB10
) will have the same procedure (ProcForAll_Processing
) as JOB_ACTION
to achieve parallel processing. Transfer settlers fallout 4.Parallel Processing In Peoplesoft
ProcForAll_Processing
: This procedure in turn will call 6 other proceduresProc1,proc2,proc3 --- -- - -- - Proc6
in sequential manner. I want to achieve parallel processing for these as well.P.S: We can’t create further jobs to achieve parallel processing in
ProcForAll_Processing
proc as it may lead to consume further resources and also DBA is not agreeing for creating further jobs. Also, I can't use dbms_parallel_execute
for parallel processing.Please help me as I am really stuck to get it done
Community♦
Ankur BhutaniAnkur Bhutani2,03522 gold badges2222 silver badges2222 bronze badges
3 Answers
It is impossible in general case without jobs, and it will make multiple sessions for this. There is no such thing as multithreading
PLSQL
with a few exceptions. One of them is parallel execution of sql statements [1]. So there are some attempts to abuse this stuff for parallel execution of PLSQL code, for example try to look here [2].But as i've said it's abuse IMHO.
Reference:
Ilia MaskovIlia Maskov
Get a new DBA. Or even better, cut them out of any decision making processes. A DBA should not review your code and should not tell you to not create jobs, unless there is a good, specific reason.
Using DBMS_SCHEDULER to run things in parallel is by far the easiest and most common way to achieve this result. Of course it's going to consume more resources, that's what parallelism will inevitably do.
Another, poorer option, is to use parallel pipelined table functions. It's an advanced PL/SQL feature that can't be easily explained in a simple example. The best I can do is refer you to the manual.
Jon HellerJon Heller25.7k33 gold badges5050 silver badges9696 bronze badges
You should try to use DBMS_PARALLEL_EXECUTE (since RDBMS 11).
https://blogs.oracle.com/warehousebuilder/entry/parallel_processing_in_plsql
https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2 Rome total war ii units.
https://blogs.oracle.com/warehousebuilder/entry/parallel_processing_in_plsql
https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2 Rome total war ii units.
LeonLeon
Not the answer you're looking for? Browse other questions tagged oracleplsqloracle11gparallel-processingdbms-scheduler or ask your own question.
It works with SELECT and UPDATE statements.
Are there some more statements with which it works?
The prices that are listed only need to be paid once. Autoaid car diagnostic tools work with your PCs, laptops, or tablets with Windows 7, 8, or 10. They contain the described range of functions for an unlimited period of time and any number of diagnoses. Headlight level adjustment vectra b wiring. The autoaid diagnostic software is included as a download. Because of being designed a cloud-based device, an internet connection is required while running a diagnosis.
I was able to use it with create, but am not sure if the hint was actually considered.
Except INSERT and UPDATE, when all is the PARALLEL hint considered?
Gaius10.3k22 gold badges2626 silver badges6363 bronze badges
LazerLazer1,5731313 gold badges3434 silver badges5353 bronze badges
2 Answers
The Data Warehousing Guide has a chapter on parallel operations that goes over what operations can be parallelized.
You can't parallel-ize a simple CREATE TABLE statement (nor would you want to-- there wouldn't be enough work to be able to parcel it out to multiple worker threads). You can, however, parallel-ize a CREATE TABLE AS SELECT statement either by specifying the PARALLEL hint in the SELECT or by using the PARALLEL keyword in the CREATE.
Justin CaveSet Processing In Peoplesoft
Justin Cave17.5k22 gold badges3838 silver badges5050 bronze badges
Oracle supports parallel processing for a wide range of operations, including queries, DDL, and DML:
- Queries that involve table or index range scans
- Bulk insert, update, or delete operations
- Table and index creation
- The collection of object statistics using
DBMS_STATS
- Backup and recovery operations using Recovery Manager (RMAN)
4,05933 gold badges1919 silver badges5454 bronze badges
Shoaib MohammedShoaib Mohammed