How does Oracle view the execution plan of a stored procedure?

View the execution plan of an Oracle stored procedure: Enable the execution plan: SET EXPLAIN PLAN ON; Execute the stored procedure; Enable tracing: SET AUTOTRACE ON; View the execution plan output, including operations, row counts, costs, and additional information.

How to view the execution plan of Oracle stored procedures

Viewing the execution plan of Oracle stored procedures is helpful for understanding and Optimize query performance. The following steps illustrate how to view the execution plan of a stored procedure:

Step 1: Enable the execution plan

<code class="sql">SET EXPLAIN PLAN ON;</code>

Step 2: Execute the stored procedure

Execute the stored procedure to be analyzed.

Step 3: View the execution plan

<code class="sql">SET AUTOTRACE ON;</code>

Step 4: View the execution plan output

The execution plan output will be displayed in In the command line window or log file. The output contains the following information:

• Id: The unique identifier of the operation.
• Operation: The operation performed, such as TABLE ACCESS FULL, NESTED LOOPS.
• Rows: Estimate the number of rows the operation will return.
• Cost: The estimated cost of the operation, in CPU time.
• Additional Information: Additional information about the operation, such as connection conditions, filters, etc.

Example

The following is an example of viewing the execution plan of the stored procedure "GetCustomer":

<code class="sql">SET EXPLAIN PLAN ON;
EXEC GetCustomer 1234;
SET AUTOTRACE ON;</code>

The execution plan output may look like this:

<code>Id | Operation | Rows | Cost
---|-----------|------|-----
0   | SELECT STATEMENT | 1    | 1
1   | TABLE ACCESS FULL | 1    | 1</code>

This execution plan shows that the stored procedure obtains a single row of data by performing a full table scan from the table.

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