How to write oracle parallelism

Oracle attempts to automate system configuration to maximize the performance of parallel operations. However, there is still a lot of room for manual tuning, and we can tune the database to optimize SQL parallel performance.

Judge the degree of parallelism.

Proper parallelism DOP is key to good parallelism. Oracle sets the DOP as follows:

1. If parallel execution is specified or requested, but no DOP is specified, the default DOP will be set to twice the number of CPU cores on the system. For a RAC system, the DOP value is twice as many as the number of cores in the entire cluster. The default value is controlled by the configuration parameter "parallel threads per cpu".

2. For versions later than Oracle 11G R2, if "Parallel Degree Policy" is set to "Auto", Oracle will automatically adjust the DOP value according to the nature of the execution and the size of the objects involved.

3. If "Parallel Adaptive Multi User" is set to "True", Oracle will adjust the DOP based on the entire load of the system. When the system is subjected to heavier loads, the DOP value will decrease.

4. In Oracle 11G or later, if "Parallel IO Cap" is set to true, Oracle will limit the DOP to the value that the IO subsystem can support. These IO subsystem limits can be computed via the DBMS Resource stored procedure.

5. DOP can be assigned to the table or index level, which can be achieved by using "parallel" clauses in "create table", "create index", "alter table" or "alter index".

6. The "parallel" keyword can be used to specify the DOP of a specified table in a query.

7. Regardless of any other settings, the number of DOPs cannot exceed the number that can be supported by "Parallel Max Servers". For most SQL statements, the number of servers needs to be twice as large as the request DOP.

As we can see, adding or subtracting DOP beyond the optimization point will result in the failure of further performance improvements. However, forcibly increasing the DOP value beyond the optimal optimization limit can have a significant negative impact on the overall system performance. Although the parallelized SQL may not decrease with the increase in DOP, the load on the system will continue to increase, and other concurrent SQL will suffer from reduced response times.

When we reach the optimal DOP point (the value in this system is about 8), the reduction in query time becomes more modest. However, the time spent by other sessions waiting for the CPU to become available continues to grow. Other sessions waiting to access the CPU will need to continue waiting, which will result in slower response times.