【博客文章2021】并行DML实践
Author: Bo Tang
1. 准备实验环境：
以SH用户连接数据库，创建一个间隔分区表：
SQL> create table newsales
2 ( prod_id number not null,
3 cust_id number not null,
4 time_id date not null,
5 channel_id number not null,
6 promo_id number not null,
7 quantity_sold number( 10,2 ) not null,
8 amount_sold number( 10,2 ) not null )
9 partition by range ( time_id )
10 interval ( numtodsinterval( 1,'DAY' ) )
11 ( partition p_before_1998
12 values less than ( to_date( '1998-01-01', 'YYYY-MM-DD' ) ) )
13 parallel; |
该表的DDL并行度是：
SQL> select table_name, degree from dba_tables where table_name='NEWSALES' and owner='SH'; --------------------------------------------------------------------------------------------------------------- NEWSALES DEFAULT
|
2. 并行插入实验：
SQL> conn sh/oracle_4U
Connected.
SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 0 0
DML Parallelized 0 0 0
DDL Parallelized 0 0 0
DFO Trees 0 0 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 0 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 0 0
Distr Msgs Recv'd 0 0 0 STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
DOP 0 0 0
Slave Sets 0 0 0 13 rows selected. SQL> insert into newsales select * from sales where rownum < 10000; 9999 rows created. SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 0 0
DML Parallelized 0 0 0
DDL Parallelized 0 0 0
DFO Trees 0 0 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 0 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 0 0
Distr Msgs Recv'd 0 0 0 STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
DOP 0 0 0
Slave Sets 0 0 0 13 rows selected. SQL> commit; Commit complete.
|
SQL> select s.sql_fulltext,s.px_servers_executions from v$sql s where sql_fulltext like ' insert%newsales%';
------------------------------------------------------------------------------------------------------------------------ insert into newsales select * from sales where rownum < 10000 0
|
查看刚才的会话是否并行执行了？从V$PQ_SESSTAT上查看，并没有。因为一条DML语句仅仅在会话中执行过ALTER SESSION ENABLE PARALLEL DML之后才能并行。
SQL> alter session enable parallel dml; Session altered. SQL> insert into newsales select * from sales where rownum < 10000;
9999 rows created. SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 0 0
DML Parallelized 1 1 0
DDL Parallelized 0 0 0
DFO Trees 1 1 0
Server Threads 8 0 0
Allocation Height 8 0 0
Allocation Width 1 0 0
Local Msgs Sent 51 0 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 56 0 0
Distr Msgs Recv'd 0 0 0 STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
DOP 8 0 0
Slave Sets 1 0 0 13 rows selected. SQL> commit; Commit complete.
|
SQL> select s.sql_fulltext,s.px_servers_executions from v$sql s where sql_fulltext like ' insert%newsales%';
------------------------------------------------------------------------------------------------------------------------ insert into newsales select * from sales where rownum < 10000 0 insert into newsales select * from sales where rownum < 10000 8
|
系统自动采用了8个并行度执行。并行DML执行完成DML后，如果不提交或者回滚在本会话中会直接去查询或做下一个DML，就会收到ORA-12838出错。下面我们尝试做一个新的并行DML操作，但是不提交：
SQL> conn sh/oracle_4U
Connected.
SQL> alter session force parallel dml parallel 20; Session altered. SQL> delete from newsales where rownum < 10000; 9999 rows deleted. SQL> select * from newsales;
select * from newsales
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> insert into newsales select * from sales where rownum < 10000;
insert into newsales select * from sales where rownum < 10000
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
并行DML执行完成DML后，如果不提交或者回滚在另外的会话中会直接去查询没问题，但是如果做下一个DML，就会挂起等待：
另一个会话：
SQL> conn sh/oracle_4U
Connected.
SQL> select count(*) from newsales; COUNT(*)
----------
19998 SQL> insert into newsales select * from sales where rownum < 10000;
挂起等待 |
查看哪个表上（下面查询中的ID1字段）上出现了表级别共享锁：
SQL> SELECT sid, type, lmode, id1, id2 FROM v$lock
WHERE sid IN (SELECT sid FROM v$session WHERE username='SH')
AND type='TM' AND lmode=6;
--------------------------------------------------------------
373 TM 6 94888 0
|
SQL> select owner,object_name from dba_objects where object_id=94888;
----------------------------------------------------------------
SH NEWSALES
|
ASH报告：
| 
