Oracle explain plan ,,,, hummmmm ok before we get into it we need to know some sky view. I will not start it as this as a performance tuning tool. Just to emphasis here. It is always not possible to improve performance of an SQL just by explain plan. Yes but this is one among the many factors.
So one can use oracle explain plan only when he has good understanding of business data and data processing needs. It is not always a guru mantra to make an SQL execution like jet.
What is oracle explain plan?
EXPLAIN PLAN statement displays execution plans for DML statements. DML like SELECT, UPDATE, INSERT, and DELETE statements.
Execution plan is the sequence of operations Oracle performs to run the query or statement.
It focus and show the below details.
– Sequence of the tables referenced in the statement.
– Access method for every table listed in the given query.
– Join method for tables if any.
– Operations such as filter, sort, or aggregation or any grouping.
– Optimization details such as cost and cardinality of each execution.
– Partition details.
Great, why do you need these details? Understanding the execution of your query and optimizer decisions will help you to understand the performance of the query.
Can execution plans be changed with oracle explain plan ? – Yes, of course
The underlying optimizer input is the key in generating the execution plan. These are subjected to change provided if the optimizer input like execution and explain plan environment changes. Let’s talk more about it. Say a query is doing really well in my production environment. As part of the regression testing, this code is picked in development / testing environment. Where you found the performance of the query is degraded heavily. What when you started working on the tuning of the SQL which is already doing good in production.
Key point. The real reason most of the times is environment. The query is tuned and execution plan is freezed on the basis of resources available in production environment. Since the production environment is generally maintained with huge resources compared to development environment, you will find the performance bottleneck.
# WHAT are you supposed to do in such case?
Thinking to work on query tuning / performance to subside the performance globally? Give it up. Performance in fact is a subjective thing. So let’s not get into a major discussion on what performance is. I consider that performance can only be compared given the two instances have same resources available, same database global and session parameters are set.
# Understand the outcome of EXPLAIN PLAN output
Look to eliminate full scans, Incorrect join orders, filters at the end of processing.
# PLAN Table – Do you want to understand more?
CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE$
STATEMENT_ID VARCHAR2(30 BYTE),
REMARKS VARCHAR2(4000 BYTE),
OPERATION VARCHAR2(30 BYTE),
OPTIONS VARCHAR2(255 BYTE),
OBJECT_NODE VARCHAR2(128 BYTE),
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ALIAS VARCHAR2(65 BYTE),
OBJECT_TYPE VARCHAR2(30 BYTE),
OPTIMIZER VARCHAR2(255 BYTE),
OTHER_TAG VARCHAR2(255 BYTE),
PARTITION_START VARCHAR2(255 BYTE),
PARTITION_STOP VARCHAR2(255 BYTE),
DISTRIBUTION VARCHAR2(30 BYTE),
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE),
PROJECTION VARCHAR2(4000 BYTE),
QBLOCK_NAME VARCHAR2(30 BYTE)
ON COMMIT PRESERVE ROWS
# How to read PLAN_TABLE?
Below is the basic syntax –
SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY (‘PLAN_TABLE_Name’, ‘STATEMENT_ID_Name’,’DETAIL_Level’));
PLAN_TABLE_Name – Name of your plan_table.
STATEMENT_ID_Name – Name given to the statement (EXPPLAN_DEMO is the value set in the example in snapshot).
DETAIL_Level – There are four variants available – BASIC, SERIAL, TYPICAL, ALL.
ALL will give you the details of –
1. Execution flow.
2. Table aliases used if any.
3. Join information and predicates used in the joins.
4. Column projections etc.
You can choose the right detail you would want to see.
# looking beyond just oracle explain plan or execution plan..?
Explain plan alone cannot always help you to decide if your queries are well tuned or not. If a explain plan shows up that a table is being index scanned, might not mean it is tuned. We should also look for a fact that what part of the table data is being accessed and many more.
More to come in Oracle space ….