CODEWITHSUNDEEP
oracle-databasequery-optimizationoracle12cquery-performance
user3779043
user3779043

Reputation: 41

Which of the following three is the optimum Oracle SQL plan?

The below three queries result the same data. Please check these queries and let me know your feedback about the best plan. Also, how can I find the overall final cost of a query?

 *****************Plan: 1***************** With Parallel
Plan hash value: 5652955961
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 54279 |  1855K|       |   200   (2)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR             |           |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10001  | 54279 |  1855K|       |   200   (2)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY            |           | 54279 |  1855K|  2776K|   200   (2)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE              |           | 54279 |  1855K|       |   200   (2)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH           | :TQ10000  | 54279 |  1855K|       |   200   (2)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY         |           | 54279 |  1855K|  2776K|   200   (2)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|   7 |        PX PARTITION HASH ALL|           | 54279 |  1855K|       |   199   (2)| 00:00:01 |     1 |   256 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL   | T_EMPLOYEE | 54279 |  1855K|       |   199   (2)| 00:00:01 |     1 |   256 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter("DD"."COL1"=1 AND "DD"."COL2"=1 AND "DD"."COL3"=0)
 
Note
-----
   - Degree of Parallelism is 16 because of table property


************Plan: 2******************** normal
Plan hash value: 54134798
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 54279 |  1855K|       |  3310   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY      |           | 54279 |  1855K|  2776K|  3310   (2)| 00:00:01 |       |       |
|   2 |   PARTITION HASH ALL|           | 54279 |  1855K|       |  2867   (2)| 00:00:01 |     1 |   256 |
|*  3 |    TABLE ACCESS FULL| T_EMPLOYEE | 54279 |  1855K|       |  2867   (2)| 00:00:01 |     1 |   256 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("DD"."COL1"=1 AND "DD"."COL2"=1 AND "DD"."COL3"=0)


************Plan: 3******************** with sub_query
Plan hash value: 883964996
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           | 54279 |    14M|       |  3313   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY        |           | 54279 |    14M|       |  3313   (2)| 00:00:01 |       |       |
|   2 |   VIEW                |           | 54279 |    14M|       |  3310   (2)| 00:00:01 |       |       |
|   3 |    HASH GROUP BY      |           | 54279 |  1855K|  2776K|  3310   (2)| 00:00:01 |       |       |
|   4 |     PARTITION HASH ALL|           | 54279 |  1855K|       |  2867   (2)| 00:00:01 |     1 |   256 |
|*  5 |      TABLE ACCESS FULL| T_EMPLOYEE | 54279 |  1855K|       |  2867   (2)| 00:00:01 |     1 |   256 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("DD"."COL1"=1 AND "DD"."COL2"=1 AND "DD"."COL3"=0)

Upvotes: 0

Views: 30

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9865

Really these plans are all pretty much the same:

  • They all full scan all 256 partitions of t_employee
  • They all expect this to return 54,279 rows

Running in parallel can make the query complete faster. This works by throwing more resources (CPU) at the query. So the total database time is similar to the serial version, but the wall clock time (can be) notably less.

That said if the plan estimates are accurate (~50k rows & 0.01s execution time), the overheads of parallel query may make this slower than plan 2. You'll need to get the exact figures from the execution plan to find out.

I would also expect the third plan to take the longest, because it has two hash group by operations vs one in the second plan. As a general rule, doing something twice will take longer than doing it once.

Though in practice, if the row estimates are accurate (around 50k) you probably won't notice the difference.

Also, how can I find the overall final cost of a query?

The total cost for the query is the value reported in the Cost column in the top line (operation 0) of the plan. So they are 200, 3310, and 3313 respectively.

But this figure isn't really much use to you - it's just a figure the optimizer uses to rank possible plans to choose which it thinks will be fastest. While queries with lower costs are generally faster, it's not a strict rule. And certainly not a measure of how long the query is likely to run for.

Upvotes: 1

Related Questions