Thanks for the question, Santhosh.
Asked: February 16, 2018 - 10:36 am UTC
Last updated: February 16, 2018 - 10:55 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Tom,
I have a very huge data aggregation which should be ideally done on a OLAP database using cube. Due to some contrains, I am doing it in my transactional database.
When I ran the SQL with multiple table joins, the SQL errored out due to Temp tablespace limitation. It needed 12 TB additional space to process the query. Due to that reason, I broke my data into smaller tables and tried processing smaller chunks in parallel using jobs. This approach was successful but I am unable to run more than 1000 jobs in parallel.
How can I overcome this? Can I run maximum number of jobs possible (e.g:5000) based on the temp table status.
Thank you
Santhosh.
and Chris said...
You're trying to run more than 1,000 parallel jobs to process a query? Really?
There's a couple of issues you're going to run into here.
The maximum number of concurrent jobs is controlled by the JOB_QUEUE_PROCESSES parameter. On 11.2 the upper bound for this is 1,000. It's increased to 4,000 in 12.2:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/JOB_QUEUE_PROCESSES.html#GUID-B8B68D16-00A3-43DD-BE39-01F877880955 But you're also limited by the PROCESSES parameter, which controls the total number of processes for the instance.
Unless you're on a really beefy (RAC) database, chances are you're going to hit this limit too. Particularly on your transactional database. You're stealing computing power from the work it should be doing!
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment