弹冠相庆

October 10, 2010

一个并行查询的例子

Filed under: Uncategorized — Corey @ 22:02
Tags:

系统有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

———————————————————————————————————–
| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
———————————————————————————————————–
|   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’)<TO_DATE(’10/29/2008
              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

——————————————————————————————————-
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
——————————————————————————————————-
|   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’)<TO_DATE(’10/29/2008
              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

———————————————————————————————————————————
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
———————————————————————————————————————————
|   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’)<TO_DATE(’10/29/2008 23:50′,’MM/DD/YYYY HH24:MI:SS’))
   5 – filter(TO_DATE(’10/28/2008 00:00′,’MM/DD/YYYY HH24:MI:SS’)<TO_DATE(’10/29/2008 23:50′,’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秒.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: