总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 5098 | 回复: 0

主题： 【转帖】 ORACLE+UNIX易错问题集锦

shixisheng

注册用户

等级：上尉
经验：646
发帖：52
精华：0
注册：2013-5-27
状态：离线
发送短消息息给shixisheng

加好友发送短消息息给shixisheng

发消息

发表于：

2013-5-30 9:24:37 | [全部帖] [楼主帖]

楼主

ORACLE篇

1．专题

1.1．并行度易错点

现实中大家应该都有用到并行度，并行度有一个显著特点就是可以最大限度的利用到多个CPU，在系统不忙或者是错开高峰期的时候，应该是一个非常不错的选择，那在使用并行度时，一般都会遇到哪些问题呢？讨论如下：

1.1.1并行DML无法生效

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> drop table ljb_parallel;
Table dropped
SQL> create table ljb_parallel as select * from dba_objects;
Table created

先来看查询语句用并行度的情况，可以看出有利用到并行度，没问题。

SQL> explain plan for select /*+parallel(a,4)*/ count(*) from ljb_parallel a;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 2855666597
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    55   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |              |     1 |            |          |
|   2 |   PX COORDINATOR       |              |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000     |     1 |            |          |
|   4 |     SORT AGGREGATE     |              |     1 |            |          |
|   5 |      PX BLOCK ITERATOR |              | 60761 |    55   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| LJB_PARALLEL | 60761 |    55   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement
17 rows selected

接着看看DDL语句用并行度的情况，查看如下，发现也有用到并行度，也没问题。

SQL> explain plan for create table ljb_parallel2 parallel as select * from ljb_parallel;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1946516457
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Ti
----------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |               | 60761 |    10M|   125   (1)| 00
|   1 |  PX COORDINATOR        |               |       |       |            |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000      | 60761 |    10M|    56   (2)| 00
|   3 |    LOAD AS SELECT      | LJB_PARALLEL2 |       |       |            |
|   4 |     PX BLOCK ITERATOR  |               | 60761 |    10M|    56   (2)| 00
|   5 |      TABLE ACCESS FULL | LJB_PARALLEL  | 60761 |    10M|    56   (2)| 00
----------------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement
16 rows selected
SQL> drop table ljb_parallel2;
Table dropped
SQL> create table ljb_parallel2 as select * from ljb_parallel;
Table created

最后来看看DML更新语句用并行度的情况，这次却发现根本用不到并行度！

SQL> explain plan for insert /*+parallel(a,4)*/ into ljb_parallel a select * from ljb_parallel2  ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 383256514
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |               | 58563 |     9M|   203   (3)| 00:00:0
|   1 |  TABLE ACCESS FULL| LJB_PARALLEL2 | 58563 |     9M|   203   (3)| 00:00:0
---------------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement
12 rows selected

究竟是什么原因呢？原来DML语句要用到并行度，就必须先做alter session enable parallel dml; 这样参数调整设置才可以生效！

执行命令如下：

SQL> alter session enable parallel dml;
alter session enable parallel dml

ORA-12841: 无法改变事务处理中的会话并行 DML 状态

大家是否觉得奇怪，为什么会提示这个错误，这里先卖个关子不说，在后面并行度的另外类型的错误中会做描述，这里先做COMMIT，提交事务。

SQL> commit;
Commit complete

这下会话可以允许更改了。

SQL> alter session enable parallel dml;
Session altered

继续进行测试，看看现在并行DML能否生效。

SQL> explain plan for insert /*+parallel(a,4)*/ into ljb_parallel a select * from ljb_parallel2 ;
Explained

查看如下，发现真的OK了，从执行计划中分明清楚的看到了利用到了并行度。

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 326818390
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               | 58563 |     9M|   203   (3)|
|   1 |  PX COORDINATOR          |               |       |       |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001      | 58563 |     9M|   203   (3)|
|   3 |    LOAD AS SELECT        | LJB_PARALLEL  |       |       |            |
|   4 |     BUFFER SORT          |               |       |       |            |
|   5 |      PX RECEIVE          |               | 58563 |     9M|   203   (3)|
|   6 |       PX SEND ROUND-ROBIN| :TQ10000      | 58563 |     9M|   203   (3)|
|   7 |        TABLE ACCESS FULL | LJB_PARALLEL2 | 58563 |     9M|   203   (3)|
---------------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement
18 rows selected

到这里大家应该看明白了，如果不注意到这点，写了脚本却根本没有达到自己想要的目的，却又浑然不知，无论在计费系统还是结算系统中，我都有看到数据库中存在这样的并行DML语句，但是由于没有改变会话属性，很遗憾，其实都没有生效！

另外还有一种情况，就是表本身设置了并行度，而不是用/*+parallel(a,4)*/之类 HINT，这种情况和HINT是一样的：“DML之外的SQL语句可以直接用到并行度，但是DML如果没有加alter session enable parallel dml ，也是用不到并行度的”，简要举例：

重新开一窗口

SQL> select table_name,degree from  user_tables where table_name='LJB_PARALLEL';
TABLE_NAME                     DEGREE
------------------------------ ---------------------------------
LJB_PARALLEL                      1
SQL> alter table ljb_parallel parallel 4;
Table altered
SQL> select table_name,degree from  user_tables where table_name='LJB_PARALLEL';
TABLE_NAME                     DEGREE
------------------------------ --------------------------------
LJB_PARALLEL                       4
SQL> explain plan for insert into ljb_parallel select * from ljb_parallel2;
Explained

果然没有用到并行度

对SESSION做了并行设置后如下

SQL> alter session enable parallel dml;
Session altered

OK，DML语句可以用到并行度了

SQL> explain plan for insert into ljb_parallel select * from ljb_parallel2;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 326818390
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               | 58563 |     9M|   203   (3)|
|   1 |  PX COORDINATOR          |               |       |       |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001      | 58563 |     9M|   203   (3)|
|   3 |    LOAD AS SELECT        | LJB_PARALLEL  |       |       |            |
|   4 |     BUFFER SORT          |               |       |       |            |
|   5 |      PX RECEIVE          |               | 58563 |     9M|   203   (3)|
|   6 |       PX SEND ROUND-ROBIN| :TQ10000      | 58563 |     9M|   203   (3)|
|   7 |        TABLE ACCESS FULL | LJB_PARALLEL2 | 58563 |     9M|   203   (3)|
----------------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement
18 rows selected

总结：大家要切记，在并行操作中默认情况并行查询和并行DDL操作是可以成功的，但是如果你想让并行DML能生效，alter session enable parallel dml;的这步操作是必不可少的！