sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle parallel并行来提高SQL效率 提升数据分析效率

时间:2017-04-10 16:31   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 oracle研究中心学习笔记:分享一篇关于Oracle数据库Parallel并行的使用案例,案例详细介绍了parallel提升数据库分析效率的方法,提高sql语句的效果。详见文章内容

Oracle作为一种大型数据库,在我国已成为大型企事业单位(如公立医院)的主流数据库并占有了绝对的市场份额。这就意味着审计工作同Oracle的交集越来越多,同时这种数据库的使用也意味着远超SQL SERVER的海量数据信息,其数据分析效率成为严重制约审计工作进度的瓶颈。本文将介绍在Oracle中如何通过采用PARALLEL(并行)方式最大化调用计算机资源来成倍提高数据分析效率。

什么是PARALLEL

  在Oracle中,对于一条执行语句,默认是用一个CPU进程进行串行执行(就好比我们一个审计项目从审计调查到最后出报告都只由一个人来一步一步执行,执行效率的低下是显而易见的)。而现在的CPU一般都拥有多核心多线程,这时我们就可以将一条执行语句交由这些多线程进行并行执行(就像一个审计项目成立了一个审计组,由各不同的人员分任务分别执行,最后汇总出报告)。这也就是Oracle的PARALLEL方式的基本原理。排除存储介质带宽等因素,理论上来说,调用了多少个CPU线程就可以缩短语句执行时间多少倍。以较为常见的单路至强处理E3-1230V3服务器为例,CPU总线程为8,因此如果采用PARALLEL调取8个线程执行查询语句,理论上会使执行时间缩短8倍。如果在双路甚至四路服务器上更会有几十倍的效率提升。

如何查看能够利用的并行进程数

  如上所说,CPU的总线程决定了PARALLEL能调用的最大资源,因此,要知道计算机的总线程数才能确定能利用的最大资源数。

      一般来说计算机总线程数=CPU个数×每个CPU的物理核心数量×每个核心的线程数量。


      以上面提到的单路至强E3-1230V3 CPU为例,总线程数为=1(1个CPU)×4(4个物理核心)×2(每个核心2个线程)=8。如果用的Windows操作系统,查看计算机总线程数最简单的办法是查看“任务管理器-资源监视器-CPU”资源监视窗口个数,有几个CPU监视窗口就表示有几个线程。非Windows操作系统可以在Oracle命令提示符中输入Show Parameters CPU来查看CPU线程数。

如何使用PARALLEL执行查询

  Oracle中PARALLEL的查询执行方式有三种。一是修改会话(Session)级别默认的并行度;二是修改表级别的并行度;三是采用Hint方式设置语句级别的并行度。因为Hint方式具有灵活度较高的优点,因此本文主要介绍通过Hint方式现实PARALLEL的查询执行。其查询执行语句格式为:

SELECT  /*+PARALLEL(n)*/  *  FROM table

  其中的(n)为设置要调用的并行度数量。

PARALLEL的运用实例

  下面以某医院的入院费用明细表inp_bill_detail(含有记录23704010行)为例,实测运用PARALLEL方式查询效率。

1.查看Oracle能利用的最大并行度:

SQL> show parameters cpu

NAME                                       TYPE        VALUE
------------------------------------   -----------    -------
cpu_count                                integer         8
parallel_threads_per_cpu                 integer         2
resource_manager_cpu_allocation          integer         8

  结果显示CPU逻辑核心数为8,每个CPU的并行线程数为2(物理核心数为8/2=4核),这个8就是本计算机的总线程数也就是PARALLEL能利用的最大并行度。

2.PARALLEL效率测试。先测试在默认状态下对此表进行全表查询的计划用时:

SQL> EXPLAIN PLAN FOR
   2  SELECT  *  FROM  inp_bill_detail ;

已解释。

SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1223321316
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    23M|  4362M|   204K  (2)| 00:40:54 |
|   1 |  TABLE ACCESS FULL| INP_BILL_DETAIL |    23M|  4362M|   204K  (2)| 00:40:54 |
-------------------------------------------------------------------------------------

  可以看到进行全表查询的预计执行时间为40分54秒。

3.将并行度设置为8,测试在此状态下进行全表查询的计划用时:

SQL> EXPLAIN PLAN FOR
  2  SELECT  /*+PARALLEL(8)*/ *  FROM  inp_bill_detail ;

已解释。

SQL> SELECT × FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 2466333535
---------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |    23M|  4362M| 28341   (2)| 00:05:41 |
|   1 |  PX COORDINATOR      |                 |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)| :TQ10000        |    23M|  4362M| 28341   (2)| 00:05:41 |
|   3 |    PX BLOCK ITERATOR |                 |    23M|  4362M| 28341   (2)| 00:05:41 |
|   4 |     TABLE ACCESS FULL| INP_BILL_DETAIL |    23M|  4362M| 28341   (2)| 00:05:41 |
---------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint

  可以看到将并行度设置为8后,进行全表查询的预计执行时间大大缩短到5分41秒,效率提升近七倍。

值得注意的是,在实际使用中,计算机除了运行Oracle外,同时还在运行其他程序(如系统程序)。因此,用PARALLEL调用CPU线程值应设定为小于最大CPU线程的数值(笔者一般采用的值为:CPU线程总数-2)以避免将所有CPU资源强行分配给Oracle使用后导致系统无响应等情况发生。


并行主要用在DDL上,也可以说是DBA的一个强有力的工具,较少使用在查询当中。对于查询,调优SQL是最有力的方法。关于并行的使用很简单,可以通过参数,语法,或提示来进行。下面,我将一一进行介绍,并给出相应的例子(由于我的机器不是很好,可能达不到预想的效果,我会进行知识点与流程的说明)。

要使用并行,首要要确定并行度。并行度(DOP)定义了将创建并行流个数,最优的并行度对于获得好的并行性能到至关重要。Oracle可根据下面的规则来确实并行度。

(1)如果指定或请求并行执行,但没有指定并行度,默认的并行度被设置为系统CPU核数的两倍。通过参数parallel_threads_per_cpu来控制,如下:

SQL> show parameters parallel_thread

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
parallel_threads_per_cpu             integer
2


1.查询

Sql代码 
SELECT /*+ Parallel(t,8) */ * FROM emp t; 
SELECT /*+ Parallel(8) */ * FROM emp t; 
SELECT /*+ Parallel */ * FROM emp t;        

2.创建索引

Sql代码 
create index idx_emp_test on emp(empno,ename,job) nologging parallel 32; 

3.执行表分析

Sql代码 
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',tabname => 'emp',    degree => 32,cascade => true); 


4.插入

Sql代码 
INSERT /*+ append parallel(30) */  
INTO t_a 
SELECT /*+ parallel(30) */ 
FROM t_b 

-- PS:使用insert select做测试, insert加 Append后, 加不加Parallel 效果没有什么差别,不知为何.  但后面的select 不加Parallel 差别太大了 

SQL> create table t
  2  parallel (degree default)
  3  as
  4  select *
  5  from all_objects
  6  /

Table created.

SQL> create index t_idx on t(object_id) parallel (degree 4)
  2  /

Index created.

SQL>
说明:在创建表时,我使用了默认的并行度,在创建索引时,我手工指定了并行度为4。
(6)在查询或DML中,可以使用提示(hint)来指定并行度,如下:
SQL> select /*+parallel(t 4)*/ count(*)
  2  from t
  3  where object_name like 'D%'
  4  /

  COUNT(*)
----------
      1995

SQL> update /*+parallel(t)*/ t set created = sysdate
  2  /

7851 rows updated.

SQL> commit;

Commit complete.

SQL> merge /*+parallel(t) parallel(o)*/ into t
  2  using all_objects o on (t.object_id = o.object_id)
  3  when matched then
  4  update set t.created = o.created
  5  /

7851 rows merged.

SQL> commit;

Commit complete.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle parallel并行来提高SQL效率 提升数据分析效率

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1470.html

Oracle研究中心

关键词:

oracle parallel

oracle sql并行优化

如何使用oracle 并行提高sql性能

oracle parallel使用案例