Principles and examples of Oracle execute plan

Oracle execution plans are not unfamiliar to us, and often we only look at the performance bottlenecks in our work and have no idea how the execution plan is generated. The following examples are used to simulate the evolution of data access and data processing methods.

1. Execution plan-Accessing data table through tables access full

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects; We create a table leo1

Table created.

Leo1@leo1> Select COUNT (*) from LEO1; This chart has 71,955 records.

COUNT (*)

----------

71955

leo1@leo1> set Autotrace trace exp; Start Execution Plan

Leo1@leo1> select * from Leo1;

Execution Plan

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

Plan Hash value:2716644435

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |      SELECT STATEMENT | |    83162 |   16m| 287 (1) | 00:00:04 |

|  1 | TABLE ACCESS full| LEO1 |    83162 |   16m| 287 (1) | 00:00:04 |

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

Note

-----

-Dynamic sampling used for this statement (level=2)

Data access mode: Go to the full table scan, because there is no index, so no way to go index, this is an inefficient way of data access, in the actual application of less.

2. Execution plan-parallel access to data through tables PARALLEL

Leo1@leo1> Select/*+ Parallel */COUNT (*) from LEO1; Automatic evaluation of parallelism

Execution Plan

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

Plan Hash value:452265093

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

| Id | Operation | Name | Rows | Cost (%CPU) |    Time | TQ | In-out| PQ Distrib |

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

| 0 |          SELECT STATEMENT |     |   1 | 159 (0) |        00:00:02 |      |            | |

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

|   2 |          PX Coordinator |       |            |          |        |      |            | |

|   3 | PX SEND QC (RANDOM) |     : TQ10000 |        1 |       |  | q1,00 | P->s | QC (RAND) |

|     4 |          SORT AGGREGATE |     |            1 |          |  | q1,00 |            PCWP | |

|      5 |          PX Block iterator| |   71955 | 159 (0) |  00:00:02 | q1,00 |            PCWC | |

|       6 | TABLE ACCESS full| LEO1 |   71955 | 159 (0) |  00:00:02 | q1,00 |            PCWP | |

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

Note

-----

-Automatic dop:computed Degree of Parallelism is 2

If you do not specify the degree of parallelism, the optimizer automatically evaluates the parallelism to 2, because my small notebook is dual-core, the maximum degree of parallelism is only 2

Leo1@leo1> Select/*+ Parallel (leo1 4) */COUNT (*) from LEO1; Specify 4 degrees of parallelism

Execution Plan

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

Plan Hash value:452265093

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

| Id | Operation | Name | Rows | Cost (%CPU) |    Time | TQ | In-out| PQ Distrib |

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

| 0 |          SELECT STATEMENT |     |    1 | 80 (2) |        00:00:01 |      |            | |

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