Oracle优化Hints功能并行parallel（二）
一、Hinst简介
Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。
1、如何使用Hints:
使用注释(comment)来为一个语句添加Hints，一个语句块只能有一个注释，在select、insert、update、delete关键字标识后面，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个 sql语句，如果只在一个sql语句上有Hints，则该Hints不会影响另一个sql语句。
语法如下：select /*+ ALL_ROWS*/ count(1) from dual; +”号表示该注释是一个Hints，该加号必须立即跟在”/*”的后面，中间不能有空格。
如果你没有正确的指定Hints，Oracle将忽略该Hints，并且不会给出任何错误。Hints用法很多，但非DBA都不常用，可以自行查询“oracle hints”。
二、并行 parallel
并行使oracle开启多个进程来同时执行任务，就是把任务拆开分成多份交给不同的cpu去执行。并行（parallel）是项目中最常用的设置，oracle 优化器会自动开启并行，下面讲述的是手动开启并行的设置。
1、并行语法和版本
10g 及以下：每个需要并行的表都要指定并行，如 /*+ parallel(a 4) parallel(b 4) */ ，很多文档这样写的，忘记吧
11g以上：只要在整个sql的任何一个关键字（select、update、insert、delete、merge）后面出现一次parallel(n)，那么整个SQL相关的表，都会使用并行，在写法上非常的简洁，而且不会遗漏。现在新开发的应用都应该是11gR2以上了，请使用这种写法
注意：参数必须加（），不然也会开启oracle默认的并行，但是参数无效。
2、并行的使用场景
在增加索引和分区情况下还是慢，10分钟以上的sql添加，也可以根据具体情况添加。注意： parallel 的hint并不能保证sql一定会使用并行，如果优化器认为sql使用索引更高效，可能会使用索引而不使用并行。
3、并行的个数
根据cpu的核心数来决定，一般来说4c，开启6个，8个就可以，具体要看测试的情况，可以加大或缩小。基本都是偶数，没看见有奇数的，后面有机会测测。
4、具体写法
union 或union all 语句每个语句都要加并行
select /*+parallel(8) */* From a union select /*+parallel(8) */* from b
insert into ， 需要同时加并行才能分别影响insert 和select语句。因为查询和插入不是一个动作。
insert /*+parallel(8) */ into xxxx select /*+parallel(8) */ * from xxx a;
对象级别的并行需要手动释放，不然会造成资源争抢的后续问题。这些包括；
建表
create table test parallel 16 as select /*+parallel(8) */ *from t1,t2 where .....;
alter table test noparallel;--释放语句
创建或重建索引
create index idx_test on table_A(name) parallel 8;
alter index idx_test noparallel;--释放语句
5、查看并行生效的方式
通过执行计划查看，例：
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | TB_LHR20160518 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
PX 开头的都是并行的，并行生效。
DML生效需要执行ALTER SESSION ENABLE PARALLEL DML，不然insert ，update，delete，等加了并行也不会生效，下面是引用别人的文章，会具体介绍。
并行执行的使用范围
Oracle的并行技术在下面的场景中可以使用：
（1）PARALLEL QUERY（并行查询，简称PQ）。
（2）PARALLEL DDL（并行DDL操作，简称PDDL，例如建表、建索引等）。
（3）PARALLEL DML（并行DML操作，简称PDML，例如INSERT、UPDATE、DELETE等）。
并行查询（PQ）
并行查询可以在查询语句、子查询语句中使用，但是不可以使用在一个远程引用的对象上（例如DBLINK）。当一条SQL语句发生全表扫描、全分区扫描及索引快速全扫描的时候，若优化器满足下面的条件之一就可以使用并行处理：
① 会话级别，会话设置了强制并行，例如，“ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;”，执行“SELECT COUNT(*) FROM TB_PART_LHR;”，这里的TB_PART_LHR为分区表。
② 语句级别，SQL语句中有Hint提示，例如，使用PARALLEL或者PARALLEL_INDEX。如，“SELECT /*+ PARALLEL(T 4) */ FROM T;”。
③ SQL语句中引用的对象被设置了并行属性。在表和索引的定义中增加并行度属性，该属性可以在创建表和索引时设置，也可对已创建的表和索引的并行度属性进行修改。例如，“ALTER TABLE TB_NAME PARALLEL 4;”、“ALTER TABLE TB_NAME PARALLEL (DEGREE DEFAULT);”。取消表或索引的并行度的SQL为：“ALTER TABLE TB_NAME NOPARALLEL;”。
示例如下：
SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE 10);
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
--------------------
10
SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
-------------------
DEFAULT
SYS@orclasm > ALTER TABLE SH.SALES NOPARALLEL;
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
-------------------
1
SYS@orclasm > CREATE TABLE SCOTT.AA AS SELECT * FROM DUAL;
Table created.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='AA' AND OWNER='SCOTT';
DEGREE
--------------------
1
在日常使用上，一般不建议在对象级别定义并行度，因为这会导致相关对象的操作都变为并行处理，而并行处理会占用大量的CPU资源，导致数据库整体性能失控。一般在会话或语句级别进行处理。
并行DDL操作（PDDL）
表或索引的CREATE或ALTER操作可以使用并行。例如，以下表操作可以使用并行执行：
建表：CREATE TABLE … AS SELECT（CTAS）
表移动：ALTER TABLE … MOVE
表分区移动：ALTER TABLE … MOVE PARTITION
表分区并行分解：ALTER TABLE … SPLIT PARTITION
表分区并行合并：ALTER TABLE … COALESCE PARTITION
创建和校验约束：ALTER TABLE … ADD CONSTRAINT
创建索引：CREATE INDEX
重建索引：ALTER INDEX … REBULD
重建索引分区：ALTER INDEX … REBULD PARTITION
索引分区的分解：ALTER INDEX … SPLIT PARTITION
并行DML操作（PDML）
Oracle可以对DML操作使用并行执行。如果要让DML操作使用并行执行，那么必须显式地在会话里执行如下命令
ALTER SESSION ENABLE PARALLEL DML;
只有执行了这个命令，Oracle才会对之后符合并行条件的DML操作并行执行，如果没有这个设定，那么即使SQL中指定了并行执行，Oracle也会忽略它。
以下给出一个并行UPDATE的示例：
LHR@TEST> CREATE TABLE TB_LHR20160518 AS SELECT * FROM DBA_OBJECTS;
Table created.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 2194116729
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | TB_LHR20160518 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
Explained.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2194116729
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | TB_LHR20160518 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
LHR@test> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
Explained.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3729706116
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | TB_LHR20160518 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
通过执行计划可以看出，只有执行了“ALTER SESSION ENABLE PARALLEL DML;”后，UPDATE操作才真正地实现了并行操作，如果不执行该语句，那么只是执行了并发查询，并没有实现并发更新操作。
下表列出了这3种并行处理方式的开启及禁用语句：
类别 | 区别 | |
并行查询（PQ） | 默认 | 开启 |
查询 | SELECT D.PQ_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); | |
启用、禁用 | ALTER SESSION ENABLE PARALLEL QUERY;--启用 | |
并行DDL（PDDL） | 默认 | 开启 |
查询 | SELECT D.PDDL_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); | |
启用、禁用 | ALTER SESSION ENABLE PARALLEL DDL; --启用 | |
并行DML（PDML） | 默认 | 关闭 |
查询 | SELECT D.PDML_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); | |
启用、禁用 | ALTER SESSION ENABLE PARALLEL DML; --启用 |
RAC中的并行
如果连接Oracle RAC数据库，那么一个节点上的并发操作可以分布到多个节点上同时执行。可以使用视图GV$PX_SESSION查询并行会话的进程
请先 后发表评论～