Understanding Parallel SQL using HINTS

Single Process (with out HINTS)
SELECT *FROM sh.customersORDER BY cust_first_name, cust_last_name, cust_year_of_birth

55500 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 2792773903
----------------------------------------------------------------------------------------| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |           | 55500 |  9810K|       |  2612   (1)| 00:00:32 ||   1 |  SORT ORDER BY     |           | 55500 |  9810K|    12M|  2612   (1)| 00:00:32 ||   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |  9810K|       |   406   (1)| 00:00:05 |----------------------------------------------------------------------------------------

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets       1456  consistent gets       1454  physical reads          0  redo size    6391082  bytes sent via SQL*Net to client      41212  bytes received via SQL*Net from client       3701  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)      55500  rows processed============================================================================================================Parallel Process (with HINTS)select /*+ parallel(d,3) */ *FROM sh.customers dORDER BY cust_first_name, cust_last_name, cust_year_of_birth;

55500 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 2648474980
--------------------------------------------------------------------------------------------------------------------------| Id  | Operation               | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |           | 55500 |  9810K|       |   152   (2)| 00:00:02 |        |      |            ||   1 |  PX COORDINATOR         |           |       |       |       |            |          |        |      |            ||   2 |   PX SEND QC (ORDER)    | :TQ10001  | 55500 |  9810K|       |   152   (2)| 00:00:02 |  Q1,01 | P->S | QC (ORDER) ||   3 |    SORT ORDER BY        |           | 55500 |  9810K|    12M|   152   (2)| 00:00:02 |  Q1,01 | PCWP |            ||   4 |     PX RECEIVE          |           | 55500 |  9810K|       |   150   (0)| 00:00:02 |  Q1,01 | PCWP |            ||   5 |      PX SEND RANGE      | :TQ10000  | 55500 |  9810K|       |   150   (0)| 00:00:02 |  Q1,00 | P->P | RANGE      ||   6 |       PX BLOCK ITERATOR |           | 55500 |  9810K|       |   150   (0)| 00:00:02 |  Q1,00 | PCWC |            ||   7 |        TABLE ACCESS FULL| CUSTOMERS | 55500 |  9810K|       |   150   (0)| 00:00:02 |  Q1,00 | PCWP |            |--------------------------------------------------------------------------------------------------------------------------

Statistics----------------------------------------------------------         31  recursive calls          0  db block gets       1498  consistent gets       1454  physical reads          0  redo size    6391492  bytes sent via SQL*Net to client      41212  bytes received via SQL*Net from client       3701  SQL*Net roundtrips to/from client          4  sorts (memory)          0  sorts (disk)      55500  rows processed


Comments

Popular posts from this blog

ORA-01565: error in identifying file '?/dbs/spfile@.ora'

Reboot Exadata Machine

How to combine Oracle .ova files