Oracle uses parallel pitfalls, and Oracle uses parallel

Oracle uses parallel pitfalls, and Oracle uses parallel

How can we quickly delete tens of millions of data records from tables with 10 million data records? You cannot directly delete the file. The best method is:

1. Use create table

2. Concurrent primary key and index creation

The problem is that the operation was performed the night before, and a very serious performance problem was found at work the next day. What is the problem? Let's create an experiment!

SQL> create table test as select * from dba_objects where object_id is not null;
SQL> alter table test add constraint pk_t_object_id primary key (object_id) parallel 16 nologging;
SQL> create index idx_t_object_name on test (object_name) parallel 16 nologging;
SQL> select s. table_name, s. degree
From user_tables s
Where s. table_name = 'test ';
TABLE_NAME DEGREE
-----------------------------------------------
TEST 16

SQL> select s. index_name, s. degree
From user_indexes s
Where s. table_name = 'test ';
INDEX_NAME DEGREE
-----------------------------------------------
PK_T_OBJECT_ID 1
IDX_T_OBJECT_NAME 16

There are two pitfalls: 1. Check whether the degree of parallelism in the table has changed. 2. Has the degree of parallelism in the index changed?

You need to return the degree of parallelism.

SQL> alter table test noparallel;
SQL> alter index idx_t_object_name noparallel;
SQL> select s. table_name, s. degree
From user_tables s
Where s. table_name = 'test ';
TABLE_NAME DEGREE
--------------------------------------------------
TEST 1
SQL> select s. index_name, s. degree
From user_indexes s
Where s. table_name = 'test ';
INDEX_NAME DEGREE
---------------------------------------------------
PK_T_OBJECT_ID 1
IDX_T_OBJECT_NAME 1

When the degree of parallelism is used, a SQL statement is executed on only one CPU without the degree of parallelism. If parallel execution is added, it will be executed on multiple CPUs, and a large number of concurrent execution will inevitably lead to Contention. In the database report, there will be db file parallel read wait events.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.