DML并行操作导致其他DML操作等待

当一个表的数据量非常大时,如200G,而此时又不得不对这张表做全表扫描时,如何加快查询速度呢,并发是一个不错的选择.(提示,并发只能用于FTS全表扫描或者单个分区查询,或者分区索引,其他操作不一定能使用并发),同样的,对于DML操作的并发同样能加快执行速度,但是这里存在着一个致命的问题.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

'HELLO,WELCOMETOCOMETOSQLPLUS'
--------------------------------
hello,welcome to come to sqlplus

SQL> create table t_object as select * from all_objects where 1=2;

Table created.
10:24:03 SQL> alter session enable parallel dml;

Session altered.
不更改session状态,DML操作无法使用并发

10:25:38 SQL>  insert /*+ parallel(a,4)*/ into t_object a select * from t_object b;

0 rows created.

这时我们在另外一个session执行dml,

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

'HELLO,WELCOMETOCOMETOSQLPLUS'
--------------------------------
hello,welcome to come to sqlplus

SQL> insert into t_object select * from t_object;
可以发现session被hang住了,理论上insert into操作不是只产生SX锁,怎么会等待呢

这时我们通过以下脚本查询下目前锁的状态

SELECT s.SID, s.program, s.username, o.object_name, o.object_type,
       DECODE (l.locked_mode,
               0, 'none',
               1, 'null',
               2, 'row-s(SS)',
               3, 'row-x(SX)',
               4, 'share(S)',
               5, 'S/ROW(SSX)',
               6, 'exclusive(X)'
              ) as lock_own--q.sql_fulltext
  FROM v$session s, v$locked_object l, all_objects o
 WHERE s.SID = l.session_id AND l.object_id = o.object_id

10:30:24 SQL> select distinct sid from v$mystat;

       SID
----------
      3273

10:30:39 SQL> SELECT s.SID, s.program, s.username, o.object_name, o.object_type,
10:31:26   2         DECODE (l.locked_mode,
10:31:26   3                 0, 'none',
10:31:26   4                 1, 'null',
10:31:26   5                 2, 'row-s(SS)',
10:31:26   6                 3, 'row-x(SX)',
10:31:26   7                 4, 'share(S)',
10:31:26   8                 5, 'S/ROW(SSX)',
10:31:26   9                 6, 'exclusive(X)'
10:31:26  10                ) as lock_own--q.sql_fulltext
10:31:26  11    FROM v$session s, v$locked_object l, all_objects o
10:31:26  12   WHERE s.SID = l.session_id AND l.object_id = o.object_id ;

       SID PROGRAM                                          USERNAME                       OBJECT_NAME                    OBJECT_TYPE            LOCK_OWN
---------- ------------------------------------------------ ------------------------------ ------------------------------ ------------------- ------------
      3273 sqlplus@xxxxxx   (TNS V1-V3)                SF_COS                         T_OBJECT                       TABLE       exclusive(X)
      3292 sqlplus@xxxxxx (TNS V1-V3)                SF_COS                         T_OBJECT                       TABLE       none
我们发现session3273产生了X锁,为什么insert操作会产生x锁呢,我想关键是我使用了并发导致的.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14474335/viewspace-662589/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14474335/viewspace-662589/