[PL/SQL] Hints Parallel multithreading

Posted Jun 27, 20202 min read

After reading this chapter you will learn the following:

  1. Why use this Hints to force optimization?
  2. Precautions before use?
  3. Query the number of cores and processes?

Forced optimization reasons:

  1. For a large task, the general approach is to use a process, serial execution.

If system resources are sufficient, parallel technology can be used,
The core idea:divide a large task into several small tasks.
Specific meaning:Simultaneously enable n processes/threads to process these small tasks in parallel. These concurrent processes are called parallel execution servers. These concurrent processes are managed by a process called concurrent coordination process.


Precautions before enabling Parallel:
Only when you need to process a large task, such as a job that takes tens of minutes or hours, and has enough system resources(these resources include CPU, memory, io). You should consider using parallel.
Otherwise, in a multi-concurrent user, when the system itself has a heavy resource burden, enabling parallel will cause one session to try to occupy all resources, and other sessions have to wait, which leads to a decline in system performance. Case.


General syntax:

Parallel(table name or alias, parallel number -> even number)

 select /*+ parallel(emp,4)*/
  * from emp where deptno=200 and sal>300;

In addition:

  1. After each SELECT/INSERT/UPDATE/DELETE command, there can only be one/+/
    However, there can be multiple prompts, **can be separated by commas, and spaces can also be
    .
    For example:including both indexing and multithreading.

    /+INDEX_ASC(TABLE INDEX_NAME) parallel(emp,4)*/

  2. If the table has an alias, then write an alias, otherwise it will not go to Hints optimization.
    Picture.png

    Select /+ parallel(e,4)/--- Will take Hints optimization;

    • from emp e where deptno=200 and sal>300;

      Select /+ parallel(emp,4)/--- Will not take Hints optimization;

    • from emp e where deptno=200 and sal>300;


Q:How to query the core number of this machine?
The purpose is to know the upper limit of the core number of this machine, and the allocable range.
Open the Command Window and enter the following commands:

SQL> show parameter CPU;

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
cpu_count integer 8
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 8

SQL>

The following three parameters are returned:

  • cpu_count:indicates the number of cpu
  • parallel_threads_per_cpu:indicates the number of parallel processes allowed per CPU
  • resource_manager_cpu_allocation:This parameter determines the number of CPUs that the Resource Manager can use. This parameter has been deprecated and is only for backward compatibility.

Reference: