ORACLE的并行处理之并行查询

--并行查询
可以使用并行查询的情况
1. Full table scans, full partition scans, and fast full index scans
2.  Index full and range scans, but only if the index is partitioned (at a given time, a partition
  can be accessed by a single slave process only; as a side effect, the degree of parallelism
  is limited by the number of accessed partitions)
3.  Joins (Chapter 10 provides some examples)
4.  Set operators
5.  Sorts
6.  Aggregations

在会话级启动 或禁用并行查询
 SQL> ALTER SESSION ENABLE PARALLEL QUERY;
 或
 SQL> ALTER SESSION DISABLE PARALLEL QUERY;
 
启动并行查询时同覆盖表或索引定义的并行度
 SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
 

查看当前会话的并行查询状态
 SELECT pq_status
 FROM v$session
 WHERE sid = (select sid from v$mystat where rownum=1);

--并行查询的例子

--Full table scans
SQL>select /*+ full(t) */ *
  from big_table t;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2481K|   340M|  2924 |
|   1 |  TABLE ACCESS FULL   | BIG_TABLE   |  2481K|   340M|  2924 |
--------------------------------------------------------------------

--fast full index scans
SQL>select /*+INDEX_FFS(t)*/ count(*)
   from big_table t;
  
------------------------------------------------------------------------------------------------------------------
| Id  | Operation              |  Name                      | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                            |     1 |       |     4 |        |      |            |
|   1 |  SORT AGGREGATE        |                            |     1 |       |       |        |      |            |
|   2 |   SORT AGGREGATE       |                            |     1 |       |       | 60,00  | P->S | QC (RAND)  |
|   3 |    INDEX FAST FULL SCAN| BIG_TABLE_OBJECT_NAME_IND  |  2481K|       |     4 | 60,00  | PCWP |            |
------------------------------------------------------------------------------------------------------------------

--join

SQL>select /*+  leading(t1) use_NL(t1,t2)
           index(t2) parallel_index(t2 2)
           parallel(t1 2)*/
       *
     from ALL_TAB_PART t1, big_table t2
      where t2.owner = t1.table_owner
       and t2.object_name = t1.table_name
  
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   |  Name                      | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |  2761 |  6072K|   901 |        |      |            |
|*  1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE                  |     3 |   432 |     2 | 59,00  | PCWC |            |
|   2 |   NESTED LOOPS              |                            |  2761 |  6072K|   901 | 59,00  | P->S | QC (RAND)  |
|   3 |    TABLE ACCESS FULL        | ALL_TAB_PART               |   899 |  1850K|     2 | 59,00  | PCWP |            |
|*  4 |    INDEX RANGE SCAN         | BIG_TABLE_OBJECT_NAME_IND  |  2761 |       |     1 | 59,00  | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------

--sort
SQL>select /*+ parallel(t1,4) */ distinct object_name
  from big_table t1

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             |  Name                      | Rows  | Bytes |TempSpc| Cost  |  TQ    |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |  2481K|    40M|       |  3194 |        |      |            |
|   1 |  SORT UNIQUE          |                            |  2481K|    40M|   114M|  3194 | 61,01  | P->S | QC (RAND)  |
|   2 |   INDEX FAST FULL SCAN| BIG_TABLE_OBJECT_NAME_IND  |  2481K|    40M|       |     1 | 61,00  | P->P | HASH       |
-------------------------------------------------------------------------------------------------------------------------
 
SQL>select /*+ full(t1) parallel(t1,4) */ *
  from big_table t1
  order by object_name

---------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes |TempSpc| Cost  |  TQ    |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2481K|   340M|       | 19327 |        |      |            |
|   1 |  SORT ORDER BY       |             |  2481K|   340M|   861M| 19327 | 62,01  | P->S | QC (ORDER) |
|   2 |   TABLE ACCESS FULL  | BIG_TABLE   |  2481K|   340M|       |   731 | 62,00  | P->P | RANGE      |
---------------------------------------------------------------------------------------------------------

--分区
SQL>select /*+ parallel_index(t1 2)*/ *
  from MESSAGE_SEND_LOG t1
  where id< '2010010100000000'

SQL>select /*+ parallel(t1)*/ *
  from MESSAGE_SEND_LOG t1
   where id< '2010010100000000'

 

 

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

转载于:http://blog.itpub.net/22782597/viewspace-622717/