0

I have three tables with below structure:

create table customer_info
(customer_num        number,
 customer_firstName  varchar2(100),
 customer_lastName   varchar2(100),
 branch_code         varchar2(100));
 
 
create table branch_info
(branch_code   varchar2(100),
 branch_name   varchar2(100));
 
 
create table customer_transaction
(transaction_date  date,
 customer_num      number,
 branch_code       varchar2(100),
 transaction_count number
  )

Among these tables the one which has the biggest amount of data is customer_transaction with nearly 10 million records right now. The table has partition on column transaction_date .I want to know:

1)whether there is a difference between these two queries in terms of performance.

2)In what way these two queries are different from each other.

The point is that in the first query I'm using this /*+parallel(4)*/ and in the second query I'm referencing the table which has the biggest amount of data /*+parallel(t2,4)*/.I must say that both queries produce different execution plans.

First Query:

Select /*+parallel(4)*/
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Second Query:

Select /*+parallel(t2,4)*/ -----> t2 is "customer_transaction" 
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Thanks in advance.

5
  • 2
    The easiest way to compare the two is to run them a few times and see what’s faster. But realistically you need to question why you’d want to run this query and return so many rows, what is the client process going to do with them? At some point the query will need to serialise to talk to the client anyway - there are better ways to achieve this if it’s really required. Jan 10 at 13:01
  • 1
    Why asking the question when you can just run both and compare the performance ? You should also compare the two query plans. Jan 10 at 14:58
  • @AlbertGodfrind you're right :) I wanted to know whether someone has better information and experience regarding this issue so that he/she can share it with me . I'm studying about parallelism in Oracle and I found out that we are able to mention the name of the table in Parallel hint and wanted to know whether someone can explain in detail what happens exactly when we specify the name if anyone has come across this before.Thanks
    – Pantea
    Jan 10 at 17:11
  • 1
    I see. The best way to understand how parallelism is used in the two cases is by looking at the query plan generated by the optimizer. Just to an explain plan on both variants and see how they differ. Jan 10 at 17:40
  • 1
    But the key deciding factor is how long each takes to complete and what resources each uses. Jan 10 at 17:41
1
  1. Sure there is, but this may or may not be noticeable. We can not know this just by looking at your table definitions.
  2. See below.

The first query does all operations in parallel with degree 4, including table accesses and joins:

Plan hash value: 2107928671
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR        |                      |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | P->P | HASH       |
|  17 |       PX BLOCK ITERATOR           |                      |  Q1,03 | PCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | PCWP |            |
-----------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint

The second query accesses only CUSTOMER_TRANSACTION in parallel. Other tables are not accessed in parallel. Joins to CUSTOMER_TRANSACTION are however processed in parallel.

Plan hash value: 1958681835
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | S->P | HASH       |
|   9 |          PX SELECTOR              |                      |  Q1,00 | SCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | SCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | S->P | HASH       |
|  17 |       PX SELECTOR                 |                      |  Q1,03 | SCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | SCWP |            |
-----------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property
1
  • Thanks for your answer,Could you please explain more on execution plans? What's the different exactly? From which part of the execution plan did you find out exactly that query accesses only CUSTOMER_TRANSACTION in parallel , in second query? Thanks
    – Pantea
    Jan 11 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.