EXPLAIN PLAN and DBMS_PROFILER in Short

When exactly both EXPLAIN PLAN and DBMS_PROFILER comes into picture.

EXPLAIN PLAN (Before Query Execution)

EXPLAIN PLAN is used before the query is executed to provide an execution plan for how Oracle intends to run the SQL statement. It doesn't actually run the query but predicts how Oracle will access and process the data. This helps you identify potential inefficiencies, such as unnecessary table scans or poorly chosen indexes, and optimize the query before it's executed.

When: Before the query is executed.

What: It shows you the planned execution path, the order of operations (e.g., scans, joins), and how Oracle will access the data (e.g., using indexes or full table scans).

Purpose: To analyze and optimize the query structure before running it.

Example:

EXPLAIN PLAN FOR

SELECT * FROM employees WHERE department_id = 10;

This will show you how Oracle plans to access the employees table (e.g., whether it will use an index on department_id or perform a full table scan). The actual data is not fetched or altered, it’s just a prediction of the execution process.

DBMS_PROFILER (Post Execution of PL/SQL Code)

DBMS_PROFILER, on the other hand, is used after PL/SQL code has been executed. It profiles the execution of the PL/SQL program (such as a procedure, function, or trigger) to collect performance data like the time spent on each line of code, the number of times each statement was executed, and so on. This information helps identify bottlenecks and performance issues in your PL/SQL logic after it has run, allowing you to improve and optimize the code.

When: After the PL/SQL code has been executed.

What: It collects performance data about the execution of each PL/SQL statement, including how long each part took to execute, how many times each part was executed, and more.

Purpose: To analyze and optimize the PL/SQL logic after it’s executed, helping identify performance bottlenecks in stored procedures, functions, etc.

Example:

-- Start profiling

EXEC DBMS_PROFILER.START_PROFILING;

-- Execute your PL/SQL code

EXEC some_procedure;

-- Stop profiling

EXEC DBMS_PROFILER.STOP_PROFILING;

-- View the profile results

SELECT * FROM DBA_PROFILER_DATA;

After running the procedure, you can analyze how long it took for each part of the code to execute, and see if there are areas where performance can be improved.

Summary:

EXPLAIN PLAN:

Before the query execution.

Provides the execution plan.

Helps optimize SQL queries.

DBMS_PROFILER:

After the PL/SQL code execution.

Provides execution statistics (e.g., time spent on each line).

Helps optimize PL/SQL procedures, functions, or triggers.

So, in essence:

EXPLAIN PLAN is for understanding the plan before executing the query.

DBMS_PROFILER is for profiling and improving the performance of PL/SQL code after it has been executed.