Amit Oracle DBA Blog

Obtaining Execution Plan in Oracle

What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.

Sample Execution Plan:

1. Access Method & Join type of data access.

2. Name of the objects on which operation is going to perform.

3. Cardinality: Estimated number of rows.

4. Bytes: How much byte of data needs to process for each operation.

5. Cost: Cost require to perform operation.

6. Time: Estimated time require to complete the operation.

Displaying Execution plan:

Below are the two mostly used methods to display an execution plan.

1. EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.

2. V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.

DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.

Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:

Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.

SQL> explain plan for select * from test1 a,test2 b where a.emp_no=b.emp_no;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 497311279

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 40 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST1 | 1 | 20 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| TEST2 | 1 | 20 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."EMP_NO"="B"."EMP_NO")

Note

-----

- dynamic sampling used for this statement (level=2)

19 rows selected.

Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:

Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_cursor('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0kkhhb2w93cx0, child number 0

-------------------------------------

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 3 (100)| |

| 1 | UPDATE | SEG$ | | | | |

| 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 72 | 3 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)

Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function

Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

SQL_ID 0kkhhb2w93cx0

--------------------

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 2 (100)| |

| 1 | UPDATE | SEG$ | | | | |

| 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 71 | 2 (0)| 00:00:01 |

| 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

19 rows selected.

Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.
The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

STEP 1: Create Table for test

SQL> create table test ( x number );

Table created.

STEP2: Create index on table.

SQL> create index test_idx on test(x);

Index created.

STEP3: Check stats are not locked on table.

When stats is not locked the value of stattype_locked is NULL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–

STEP4: Lock the statistics

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

STEP5: Now check stats are locked status

When stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

STEP 6: Try to gather statistics on locked table and index

SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');

BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

SQL> analyze index ajaffer.test_idx compute statistics;

analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

STEP7: Unlock the statistics

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Amit Oracle DBA Blog

MYSQL

Tuesday, 26 May 2015

Obtaining Execution Plan in Oracle

Thursday, 30 April 2015

How to lock/unlock statistics on a table