请问加了PARALLEL提示后，是否一定会走全表扫描，不会走索引了

whmng

parallel 看样子限制还是比较多的

zxofkm

我理解PARALLEL的执行计划会降低全表扫描的COST估算值，使优化器有更大几率用全表扫描替代索引扫描。
索引也可以PARALLEL，不过我理解仅对全索引扫描的场景有效。

handsomeSJG

原帖由 eagle_fan 于 2008-12-23 11:51 发表
对于索引来说，index range scan是没有办法parallel的，只有index fast full scan时可以使用paralle

你可以使用hint: PARALLEL_INDEX(t1, index, 4)

测试了一下：this hint  is ok，只是我的测试环境添加hint后执行时间并未明显变化

加hint前：
select count(id)  from dev   where id!=1;
执行计划：
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |    16 | 79003   (4)| 00:15:49 |       |       |
|   1 |  SORT AGGREGATE        |             |     1 |    16 |            |          |       |       |
|   2 |   PARTITION HASH ALL   |             |   103M|  1577M| 79003   (4)| 00:15:49 |     1 |     8 |
|*  3 |    INDEX FAST FULL SCAN| PK_DEV      |   103M|  1577M| 79003   (4)| 00:15:49 |     1 |     8 |
------------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TO_NUMBER("ID")<>1)


加hint后：
select/*+parallel_index(t,pk_dev,4)*/ count(t.id)  from dev  t where t.id!=1;
执行计划：
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     1 |    16 | 79003   (4)| 00:15:49 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |             |     1 |    16 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |             |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |    16 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |             |     1 |    16 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |             |   103M|  1577M| 79003   (4)| 00:15:49 |     1 |     8 |  Q1,00 | PCWC |            |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       INDEX FAST FULL SCAN| PK_DEV      |   103M|  1577M| 79003   (4)| 00:15:49 |     1 |     8 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(TO_NUMBER("T"."ID")<>1)

[ 本帖最后由 handsomeSJG 于 2010-3-3 17:53 编辑 ]

Yong Huang

> 只是我的测试环境添加hint后执行时间并未明显变化

Make sure you flush buffer every time before you test. Always tell us Oracle version. In different versions, the command to flush is different.

> select/parallel_index(t,pk_dev,4)/ count(id)  from dev_info where id!=1;

That probably was not the actual command you used? The hint syntax is wrong and the table alias is missing.

Yong Huang

handsomeSJG

ORACLE版本是10.2.0.4 ，帖子是经过了编辑后发上来的(只是改了表名和索引名)，帖子已经重新修正了。我没有去刷新buffer，因为我只是利用explain plan for.......select * from table(dbms_xplan.display)语句查看了一下2个语句的执行计划，不刷新buffer应该没事吧？如果表有数据存在于buffer中，应该不会影响explain plan 的结果吧？
下面是我真实执行的情况：（说明：表dev是基于id字段上一个hash分区表，有12个分区，id字段上的索引pk_dev是一个local索引，索引pk_dev的 buffer_pool是 keep 池）
SQL> set timing on
SQL> set time on
17:28:44 SQL> set autotrace on
17:28:55 SQL> select count(id) from dev   where id!='1';

COUNT(ID)
-----------
  103585040

Elapsed: 00:00:48.59

Execution Plan
----------------------------------------------------------
Plan hash value: 781422705

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |    16 | 78170   (3)| 00:15:39 |       |       |
|   1 |  SORT AGGREGATE        |             |     1 |    16 |            |          |       |       |
|   2 |   PARTITION HASH ALL   |             |   103M|  1577M| 78170   (3)| 00:15:39 |     1 |     8 |
|*  3 |    INDEX FAST FULL SCAN| PK_DEV      |   103M|  1577M| 78170   (3)| 00:15:39 |     1 |     8 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ID"<>'1')


Statistics
----------------------------------------------------------
         10  recursive calls
         16  db block gets
     436098  consistent gets
       5329  physical reads
      15136  redo size
        522  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


17:32:13 SQL> select/*+parallel_index(t,pk_dev ,4)*/ count(t.id) from dev  t where t.id!='1';

COUNT(T.ID)
-------------
    103585040

Elapsed: 00:00:39.88

Execution Plan
----------------------------------------------------------
Plan hash value: 275642002

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     1 |    16 | 78170   (3)| 00:15:39 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |             |     1 |    16 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |             |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |    16 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |             |     1 |    16 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |             |   103M|  1577M| 78170   (3)| 00:15:39 |     1 |     8 |  Q1,00 | PCWC |            |
|*  6 |       INDEX FAST FULL SCAN| PK_DEV      |   103M|  1577M| 78170   (3)| 00:15:39 |     1 |     8 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("T"."ID"<>'1')


Statistics
----------------------------------------------------------
         42  recursive calls
          3  db block gets
     438313  consistent gets
     435592  physical reads
        632  redo size
        524  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

从我实际执行情况来看，explain plan for显示的Time与我实际结果执行的消耗的时间差距比较大，我认为主要是因为索引已经有很多数据块存在于keep池中。
另外又有个疑问出来了：
    第一次执行的   physical reads＝5329
    第二次的  physical reads＝435592
为什么第二次多出了这么多物理读，第二次执行是在第一次执行结束之后立即执行的。

我继续用这两个语句个各执行了一次，发现结果跟上次执行的结果差不多：
18:45:35 SQL> select count(id) from dev   where id!='1';
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
     436026  consistent gets
        720  physical reads
       7000  redo size
        522  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


18:46:27  SQL> select/*+parallel_index(t,pk_dev ,4)*/ count(t.id) from dev  t where t.id!='1';
Statistics
----------------------------------------------------------
         42  recursive calls
          3  db block gets
     437920  consistent gets
     435592  physical reads
        680  redo size
        524  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

从这个实验来看，并行执行的优势并没有发挥出来。

[ 本帖最后由 handsomeSJG 于 2010-3-3 19:04 编辑 ]

Yong Huang

Parallel scans read blocks from disk to PGA, bypassing buffer cache. That's why I want you to alter system flush buffer_cache before the speed test. If you do that, your non-parallel scan may have physical reads (actually blocks that are read physically, not number of times of physical read) of about 436000, i.e. about the same as consistent gets.

In most SQL tuning, you should use consistent gets as a metric. But if parallel scan is involved, the goal is changed to getting the query result as fast as possible. Then query elapsed time becomes more important.

The reason why your parallel scan has a little higher consistent gets than the non-parallel is probably because of the overhead of parallelism, spawning more processes, distributing and merging the intermediate data (a.k.a table queue), etc.

Whether you should use serial (non-parallel) scan and make use of the buffer cache depends on whether this query will be run many times, possibly concurrently by many sessions.

Yong Huang

handsomeSJG

谢谢HY定位问题。
“Parallel scans read blocks from disk to PGA, bypassing buffer cache.”
在asktom网站上也有提到过：http://asktom.oracle.com/pls/ask ... :907479900346445834
“However, there is the opportunity to do "direct io", normally used with parallel query - your server process will bypass the buffer cache and read right from disk into its own memory and process the blocks there. Generally used for a large table full table scan.
”
从其他网站上看到ORACLE 的隐藏参数_serial_direct_read 设为true时，可以让select用 "direct path reads" 方式取数据，没实验过。

huanhuanlove

学习了  yong   I F l U。。。。。  很猛。。。