How to read the Oracle stored procedure execution plan

Oracle stored procedure execution plan provides execution information, including access path, estimated number of rows, connection sequence and cost. To view the execution plan, execute the EXPLAIN PLAN command and look for the "Execution Plan" section. The execution plan contains a header and body, showing in detail the ID, operation type, number of rows, cost, access path, filter conditions, involved tables and indexes, and the connection sequence if there is a connection.

Viewing Oracle stored procedure execution plan

Oracle stored procedure execution plan provides information about how the stored procedure is executed. Detailed insights, including:

• Access path: Path used to access data, such as a table scan or index lookup
• Estimated number of rows: Estimated number of rows to be processed for each access path
• Connection order: The order in which connections are executed in the stored procedure
• Cost: Execution Estimated cost of the plan

View the steps to execute the plan:

1. Execute the EXPLAIN PLAN command:

EXPLAIN PLAN FOR <存储过程名称>;

2. Look for the "Execution Plan" section:

The results include the "Execution Plan" section, which displays the execution plan in detail.

Understand the execution plan:

Header:

• ID: Access path ID
• Operation: Type of operation, such as table scan or index lookup
• Rows: Estimated number of rows
• Cost: Estimated cost

Text:

• Access Path: Used to access data Path
• Filter: Any filters applied to the data
• Tables: Involved tables
• Index: If used, the index used
• CONNECT BY: If the stored procedure contains connections, the connection order is displayed

Example execution plan :

EXPLAIN PLAN FOR get_customer_orders;
   ID | Operation                             | Rows  | Cost
  ----|----------------------------------------|-------|-----
    0  | SELECT STATEMENT                       | 1000  | 100
    1  |  TABLE ACCESS FULL                    | 1000  | 100
         |   ORDER_HDR                           |

This execution plan indicates that:

• The stored procedure get_customer_orders will access the ORDER_HDR table.
• The access path is a table scan, which means the entire table will be scanned.
• The estimated number of rows is 1000 and the estimated cost is 100.

The above is the detailed content of How to read the Oracle stored procedure execution plan. For more information, please follow other related articles on the PHP Chinese website!