In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
In Oracle, how to globally enable parallel execution
oracleoracle-11g-r2
Related Question
- Update field1.table1 based on compound join between table1 and table2 – Oracle
- Fighting filter (nested loops) execution plan from Oracle
- Oracle 11g Enterprise downgrade to Standard One
- Oracle 11g R2 Audit – Statement Specific Auditing
- Oracle – How PARALLEL_MIN_PERCENT Affects Parallel Execution
- Oracle Monitoring – Long Running Statement Not Monitored by Oracle
- Oracle 11g R2 – How to Enable Database In-Memory Feature to Reduce Report Generation Time
- Oracle 11g alternative options for oracle 12c with function
Best Answer
Simple answer: Yes. Unfortunately it is not quite a simple as that, as if you want to enable parallelism, then you are obviously concerned about performance, and it is extremely tunable.
Start with
alter system set paralllel_automatic_tuning=true scope=spfile;
then restart. This will have Oracle do what it thinks is best. There are many other parameters to set (e.g. parallel_execution_message_size) and you will need to experiment to find the best. Be careful with this; it is easy when you start out with parallel query to get carried away in benchmarking then discover in the "real world" a few queries fly but the overall throughput of the system actually decreases as some sessions are starved of CPU or the system is forced into swap. The documentation warns:So I strongly advise putting some limits in place, e.g. with resource consumer groups.
Also for any table you can do
ALTER TABLE table_name PARALLEL (DEGREE x);
where x is a number, 4 or 8 might be good places to start.