DML concurrency limit

DML statement restrictions:

1. If the DML statement uses the degree of parallelism, you must set the alter session enable parallel dml for both HINT and directly set the degree of parallelism on the table. This step is essential!

2. If the parallel table update is not submitted, any operations on this base will fail, regardless of the update or query. Therefore, the table with a degree of parallelism is still risky. Here at least one conclusion is drawn. If the application inserts a table with a degree of parallelism in multiple tables and has a transaction type, the application must be submitted together. This application will definitely fail! Therefore, avoid setting the attribute of the table to parallel as much as possible, which leaves a hidden risk. We recommend that you use the HINT to set the degree of parallelism for the application. Therefore, we recommend that you do the following at last:

SQL> alter table ljb_parallel noparallel;

Table altered

3. In addition, if the index only has a degree of parallelism and the degree of parallelism of the strap does not exist in the table, no error will be reported if you do not submit the operation.

SQL> insert into ljb_test select/* + parallel (a, 4) */* from ljb_test;

3. parallel statements, whether DDL, DML, or query parallelism, are sorted in three ways. If the user's PGA settings are not large enough, the sort_area_size area is insufficient, or the user's data volume is too large, in other words, the number of parallel operations is too large, which may lead to large sorting actions. There may be a large number of sorting operations in the Temporary tablespace. The speed is getting slower and slower, and even the temporary tablespace is not enough! Finally, we also found that if indexes need to be maintained during parallel insertion, the number of sorting tasks will be much larger than that without indexes!

1. operations to be sorted:

A. Create an index and re-create an index;

B. Concurrent Insertion involving index Maintenance

C. order by or group by should sort the index fields as much as possible)

D. Distinct

E. union all/intersect/minus

F, sort-merge join

G. The analyze command can only use estamate instead of compute)

2. Diagnosis and measures

Select * from v $ sysstat where name like '% sort % ';

Sort (disk): number of orders that require I/O to be sorted in the temporary tablespace

Sort (memory): number of orders completed completely in memory

Sort (rows): Total number of sorted rows

Sortdisk)/Sortmemory) <5%. If it exceeds 5%, the value of sort_area_size is increased.

SELECT disk. value disk, mem. value mem, (disk. value/mem. value) * 100 ratio FROM v $ sysstat disk, v $ sysstat mem WHERE mem. NAME = 'sorts (memory) 'AND disk. NAME = 'sorts (disk )';

-- Query the degree of parallelism of an index

SQL> select table_name, index_name, degree from user_indexes where table_name = 'ljb _ PARALLEL ';

TABLE_NAME INDEX_NAME DEGREE

------------------------------------------------------------------------

LJB_PARALLEL IDX_LJB_PARALLEL 1

-- Query the parallelism settings of a table

SQL> alter index IDX_LJB_PARALLEL parallel 4;

Index altered

SQL> select table_name, index_name, degree from user_indexes where table_name = 'ljb _ PARALLEL ';

TABLE_NAME INDEX_NAME DEGREE

---------------------------------------------------------------------------

This article is from the "wushuang City" blog and is not reposted!