What is Explain Plan in Oracle | Simple Explain

Explain plan in Oracle is the sequence of operations for a statement that is chosen by the Oracle optimizer.  This is the flow of execution of a statement. The execution plan shows much information by looking at tree structure and other referring information.

• Execution order of an SQL statement by Oracle optimizer.
• Join methods when joining the table.
• Filter, order, sorting, or aggregation operation information.
• Operation Cost, Cardinality, Byte, Time, and other operation-related information
• Partitioning and parallel execution distribution
• Index Scanning methods like Range, Skip, Unique, etc.
• Table join order
• Table Join method (Hash Join)
• Data operations like Filter, Sort, aggregations, etc
•  

Execution Plan used to understand the current behavior of the query. How query execute and what resources are used by the query at the moment? This is NOT for the ALL THE TIME. because the Execution plan changes with Oracle statistics, Data lord, and other gathered information at the current time.

Oracle displays execution plan for SELECT, UPDATE, INSERT, and DELETE statements. This helps to understand the poor-performing queries and Query improvement tips by looking at information.  Below is the oracle-generating execution plan.

SELECT, INSERT, UPDATE, DELETE, MERGE, CREATE TABLE, CREATE INDEX, or ALTER INDEX … REBUILD. 

Explain Plan Usage

SQL statements are passed to a Cost-based Optimizer and use database statistics then create execution plans and select the best execution plan. This plan changes according to data load and changes the statistics as well.

Explain plan in Oracle shows the different columns including relevant values for each step. The below image shows what type of information is available on the Explain Plan. Cost, Cardinality, Bytes, Filter predicates, Optimizer, and Object names are important.

Those values are used to identify the dropbacks on queries and in which area can we improve the performance. We will discuss simple and bit complex examples later part of the article. Explain Plan tree structure helps to identify the execution flow. Also, structure helps to determine whether indexes are used in queries or do the full table scan to retrieve data from a database.

Example of Explain Plan in Oracle

Let’s take a simple query to explain the plan. First, take a simple query to take an execution plan. This citizen table contains all the population in the country. The data pump query and the table creation code in the end of the article.

--
select citizen_id, citizen_name, Citizen_nic 
from citizen_table where citizen_city='City_5'
and citizen_tax<1000
--

• Step 1: In this example According to the tree structure first scan through the INDEX RANGE SCAN. The cost for the operation is 1. Cardinality is 1000 out of 10000 records. We can see Citizen_nic_ix1(Object Name) index used to fetch relevant records.
• Step 2: Now we fetched the required rowids from the index. Then must access the required rowids from a table. In this case, it’s a batch call for all the records. It’s taken some time because there are 7 IO calls cost (IO COST). Also, it must fetch the required records from the table (Object Type).
• Step 3:  Then look for select statement values for all the rows (Optimizer) Now everything is completed and happy to execute the plan. Additionally, the filter predicates column shows filters used in the query.

Example 2

Initially, I have to say this is not a good query, but I want to show the Explain Plan for you. There are some problems in this query. Try to think about what we can improve on this.

--
select citizen_id, citizen_name, Citizen_nic 
from citizen_table a ,
(select max (citizen_tax), citizen_city
from citizen_table 
group by citizen_city
having max (citizen_tax) >10000
)
 b where a.citizen_city=b.citizen_city
 Order by citizen_name
--

• There are two table accesses full which means Oracle has to traverse through the entire table twice. That is not good.
• We don’t have an index for citizen tax if we need improvement on this query, we can create a B-TREE index. (There are 20 more index types we can create. As Example BITMAP, DOMAIN, FUNCTION BASED).
• COST is a bit high. Because There is a lot of IO COST and CPU COST is also high. Maybe it’s because there are loops.
• Start the query with TABLE ACCESS FULL for 100000 records.
• Other than the earlier query here we have FILTER, HASH JOIN, STATISTICS COLLECTOR steps. Those are not taking much time.

Why Different Costs

Because every time the data load changes statistics update according to that. So same query does not execute the same way all the time. Cost-based optimizer changes execution plan according to the following.

• Data volume
• Database statistics
• Variable types and values

What is PLAN_TABLE

PLAN_TABLE keeping sample output of the Explain Plans. There are description records about the Explain Plan in Oracle.

--
EXPLAIN PLAN FOR
<<SQL Statement>>
EXPLAIN PLAN
    SET STATEMENT_ID = <<identifier>> FOR
<<SQL Statement>>

SELECT cardinality "Rows",
       lpad(' ',level-1)||operation||' '||
       options||' '||object_name "Plan"
  FROM PLAN_TABLE
--

Sample Data Set

--
CREATE TABLE citizen_table(
    citizen_id VARCHAR2(50) ,
    citizen_nic VARCHAR2(50) ,
    citizen_tax NUMBER ,
    citizen_city VARCHAR2(50),
    citizen_name VARCHAR2(100) ,
    PRIMARY KEY(citizen_id)
);
declare 
  -- insert 1000000 records into database
  i integer:=0;
type namesarray IS VARRAY(10) OF VARCHAR2(50); 
   names namesarray; 
begin
names := namesarray('City_1', 'City_2', 'City_3', ' City_4', 'City_5','City_6',' City_7','City_8','City_9','City_10');
LOOP
   i:= i+1;
     INSERT INTO citizen_table (citizen_id, citizen_nic, citizen_tax,citizen_city, citizen_name) 
   VALUES ('CT-'||i,1000000+i, round(dbms_random.value(100,100000),0), names(round(dbms_random.value(1,10),0)),'CT-name'||i);
   EXIT WHEN i > 99999; END LOOP;
end;
--

EXPLAIN PLAN Column Values(Options / Operations)

Below are the options and operations that can be visible in the Explain Plan in Oracle tree structure and referring column values. By looking at we can understand what is happening in that step.

Reference: Document