系统有2个cpu，每个4核，raid 1+0，db为oracle 10gr2 64bit

1. 先直接来个查询看看

SQL> set autot trace

SQL> SELECT COUNT(*)

FROM channels

WHERE channels.snapshot_time > to_date('10/28/2008 00:00'

,'MM/DD/YYYY HH24:MI:SS')

AND channels.snapshot_time <= to_date('10/29/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS');

Elapsed: 00:01:15.96

Execution Plan

----------------------------------------------------------

Plan hash value: 1848729565

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 11 | 533K (7)| 01:46:45 | | |

| 1 | SORT AGGREGATE | | 1 | 11 | | | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 89M| 936M| 533K (7)| 01:46:45 | KEY | KEY |

|* 4 | INDEX FAST FULL SCAN | CHANNELS_UNQ | 89M| 936M| 533K (7)| 01:46:45 | KEY | KEY |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')

23:50','MM/DD/YYYY HH24:MI:SS'))

4 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND

"CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))

Statistics

----------------------------------------------------------

8962 recursive calls

0 db block gets

308209 consistent gets

306423 physical reads

0 redo size

518 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

141 sorts (memory)

0 sorts (disk)

1 rows processed

可以看到，默认走了索引，用时1分16秒左右

2. 强制走全表扫描看看

SQL> alter system flush buffer_cache;

SQL> alter system flush shared_pool;

SQL>

SELECT /*+ full(channels)*/

COUNT(*)

FROM channels

WHERE channels.snapshot_time > to_date('10/28/2008 00:00'

,'MM/DD/YYYY HH24:MI:SS')

AND channels.snapshot_time <= to_date('10/29/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS');

Elapsed: 00:00:49.31

Execution Plan

----------------------------------------------------------

Plan hash value: 4225188383

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 11 | 714K (6)| 02:22:51 | | |

| 1 | SORT AGGREGATE | | 1 | 11 | | | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 89M| 936M| 714K (6)| 02:22:51 | KEY | KEY |

|* 4 | TABLE ACCESS FULL | CHANNELS | 89M| 936M| 714K (6)| 02:22:51 | KEY | KEY |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')

23:50','MM/DD/YYYY HH24:MI:SS'))

4 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS')

AND "CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))

Statistics

----------------------------------------------------------

8922 recursive calls

0 db block gets

404835 consistent gets

403115 physical reads

0 redo size

518 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

141 sorts (memory)

0 sorts (disk)

1 rows processed

全表扫描，用时49秒

3. 并行度为4的查询

SQL> alter system flush buffer_cache;

SQL> alter system flush shared_pool;

SQL>

SELECT /*+ full(channels) parallel(channels,4) */

COUNT(*)

FROM channels

WHERE channels.snapshot_time > to_date('10/28/2008 00:00'

,'MM/DD/YYYY HH24:MI:SS')

AND channels.snapshot_time <= to_date('10/29/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS');

Elapsed: 00:00:11.96

Execution Plan

----------------------------------------------------------

Plan hash value: 166461870

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 11 | 198K (6)| 00:39:37 | | | | | |

| 1 | SORT AGGREGATE | | 1 | 11 | | | | | | | |

|* 2 | PX COORDINATOR | | | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 11 | | | | | Q1,00 | P->S | QC (RAND) |

| 4 | SORT AGGREGATE | | 1 | 11 | | | | | Q1,00 | PCWP | |

|* 5 | FILTER | | | | | | | | Q1,00 | PCWC | |

| 6 | PX BLOCK ITERATOR | | 89M| 936M| 198K (6)| 00:39:37 | KEY | KEY | Q1,00 | PCWC | |

|* 7 | TABLE ACCESS FULL| CHANNELS | 89M| 936M| 198K (6)| 00:39:37 | KEY | KEY | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')

5 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')

7 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND

"CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))

Statistics

----------------------------------------------------------

9661 recursive calls

4 db block gets

405834 consistent gets

403140 physical reads

632 redo size

518 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

152 sorts (memory)

0 sorts (disk)

1 rows processed

全表加并行，用时11秒.

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

上一篇： Oracle10gR2在RHEL 5下开启异步IO

下一篇：用pl/sql分割字符串

请登录后发表评论登录

全部评论

rsay

注册时间：2009-11-01

博文量
9
访问量

42460

ITPub博客

一个并行查询的例子

最新文章