Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

2 Answers

up vote 5 down vote accepted

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:

When concurrent users have too many query server processes, memory contention (paging), I/O contention, or excessive context switching can occur. This contention can reduce system throughput to a level lower than if parallel execution were not used.

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.

share|improve this answer
Thanks Gaius! About your last point.. I tried doing an alter table tablename parallel;. After this I tried seeing the query plan for an insert statement on the table, and it was not parallel. What exactly then this alter table statement do? – Lazer Feb 16 '11 at 21:14
Oracle is still being "intelligent" in the query optimizer; if there's not enough work in your INSERT to justify spinning up parallel query slaves, it won't. What happens if you do INSERT INTO tablename SELECT (...) for a few million rows? (I guess you would also want PARALLEL set on the table you are selecting from). – Gaius Feb 16 '11 at 21:25
up vote 3 down vote

I believe you want the parallel_automatic_tuning=true setting.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.