When a SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.
Plan Table
The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows.
-- Creating a shared PLAN_TABLE prior to 11g
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
In Oracle 11g a shared PLAN_TABLE is created by default, but you can still create a local version of the table using the "utlxplan.sql" script.
USING AUTOTRACE
Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.
SQL> SET AUTOTRACE ON
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM FARHAT.BAS_USER_LOG WHERE BRANCH_NUM ='45';
COUNT(*)
----------
1318829
Elapsed: 00:00:19.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3940904115
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 146K (1)| 00:29:18 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| BAS_USER_LOG | 131K| 386K| 146K (1)| 00:29:18 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BRANCH_NUM"=45)
Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
539420 consistent gets
539366 physical reads
0 redo size
349 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
############################################################
As you can see there is no index on column "Branch_Num" which is causing full table scan which needs too many physical reads (539366) and query takes 19 seconds. Now lets add an index to the column Branch_Num.
create index FARHAT.IDX$$_497A0002 on FARHAT.USER_LOG("BRANCH_NUM");
SQL> SET TIMING ON
COUNT(*)
----------
1318829
Elapsed: 00:00:19.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3940904115
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 146K (1)| 00:29:18 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| BAS_USER_LOG | 131K| 386K| 146K (1)| 00:29:18 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BRANCH_NUM"=45)
Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
539420 consistent gets
539366 physical reads
0 redo size
349 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
############################################################
As you can see there is no index on column "Branch_Num" which is causing full table scan which needs too many physical reads (539366) and query takes 19 seconds. Now lets add an index to the column Branch_Num.
create index FARHAT.IDX$$_497A0002 on FARHAT.USER_LOG("BRANCH_NUM");
Index created
Let's run same query again
Let's run same query again
SQL> SELECT COUNT(*) FROM FARHAT.USER_LOG WHERE BRANCH_NUM ='45';
COUNT(*)
----------
1318829
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 3139255900
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 261 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IDX$$_497A0002 | 131K| 386K| 261 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BRANCH_NUM"=45)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2574 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Query execution time reduced to 00:00:00.19 seconds because and index range is scanned.
This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.
CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/
Function created.
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
SQL> SELECT pause_for_secs(10) FROM DUAL;
PAUSE_FOR_SECS(10)
------------------
10
1 row selected.
Elapsed: 00:00:10.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT pause_for_secs(10) FROM DUAL;
1 row selected.
Elapsed: 00:00:10.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.
The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL>
Then the execution plan displayed.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------
8 rows selected.
SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.
Statement ID
If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @explain.sql TIM
PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement 57 4
1.1 Nested Loops 57 4
2.1 Table Access (Full) EMP TABLE 37 3
2.2 Table Access (By Index Rowid) DEPT TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
SQL>
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.
Reading Execution Plans
There is an explanation of how to read execution plans
This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.
CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/
Function created.
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
SQL> SELECT pause_for_secs(10) FROM DUAL;
PAUSE_FOR_SECS(10)
------------------
10
1 row selected.
Elapsed: 00:00:10.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT pause_for_secs(10) FROM DUAL;
1 row selected.
Elapsed: 00:00:10.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.
The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL>
Then the execution plan displayed.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------
8 rows selected.
SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.
Statement ID
If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @explain.sql TIM
PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement 57 4
1.1 Nested Loops 57 4
2.1 Table Access (Full) EMP TABLE 37 3
2.2 Table Access (By Index Rowid) DEPT TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
SQL>
By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.
Reading Execution Plans
EXPLAIN PLAN SET STATEMENT_ID = 'PLAN1' FOR SELECT * FROM FARHAT.BAS_USER_LOG WHERE LOG_USER_ID ='FARHAT'
select plan_table_output from table(dbms_xplan.display('plan_table','PLAN','serial'))
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
There is an explanation of how to read execution plans
You can also follow below link
https://oracle-base.com/articles/9i/dbms_xplan#reading-execution-plans
Below is example of reading execution plan we run a query on tables dept and emp
Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
When first looking at an explain plan and wanting to quickly get an idea of the steps in which the query will be executed, do the following:
Once you have an explain plan for a query, and can understand the sequence of how the query will be processed, you then can move on and perform some analysis to determine if the explain plan you are looking at is efficient. When looking at your explain plan, answer these questions and consider these factors when determining if you have an efficient plan:
By looking at our original explain plan, we determined that the EMP table is larger in size, and also that there is no index present on the DEPTNO column, whic h is used within a join condition between the DEPT and EMP tables. By placing an index on the DEPTNO column on the EMP table and gathering statistics. on the EMP table, the plan now uses an index:
This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.
CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/
Function created.
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
SQL> SELECT pause_for_secs(10) FROM DUAL;
PAUSE_FOR_SECS(10)
------------------
10
1 row selected.
Elapsed: 00:00:10.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT pause_for_secs(10) FROM DUAL;
1 row selected.
Elapsed: 00:00:10.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.
The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL>
Then the execution plan displayed.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------
8 rows selected.
SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.
Statement ID
If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @explain.sql TIM
PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement 57 4
1.1 Nested Loops 57 4
2.1 Table Access (Full) EMP TABLE 37 3
2.2 Table Access (By Index Rowid) DEPT TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
SQL>
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
Reading Execution Plans
There is an explanation of how to read execution plans
This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.
CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/
Function created.
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
SQL> SELECT pause_for_secs(10) FROM DUAL;
PAUSE_FOR_SECS(10)
------------------
10
1 row selected.
Elapsed: 00:00:10.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT pause_for_secs(10) FROM DUAL;
1 row selected.
Elapsed: 00:00:10.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.
The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL>
Then the execution plan displayed.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------
8 rows selected.
SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.
Statement ID
If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @explain.sql TIM
PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement 57 4
1.1 Nested Loops 57 4
2.1 Table Access (Full) EMP TABLE 37 3
2.2 Table Access (By Index Rowid) DEPT TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
SQL>
By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.
Reading Execution Plans
EXPLAIN PLAN SET STATEMENT_ID = 'PLAN1' FOR SELECT * FROM FARHAT.BAS_USER_LOG WHERE LOG_USER_ID ='FARHAT'
select plan_table_output from table(dbms_xplan.display('plan_table','PLAN','serial'))
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
There is an explanation of how to read execution plans
You can also follow below link
https://oracle-base.com/articles/9i/dbms_xplan#reading-execution-plans
Below is example of reading execution plan we run a query on tables dept and emp
Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
- The PK_DEPT index is scanned (ID 3).
- All EMP table rows are scanned (ID 5).
- Rows are retrieved from the DEPT table based on the matching entries in the PK_DEPT index (ID 2).
- Resulting data from the EMP table is sorted (ID 4).
- Data from the EMP and DEPT tables are then joined via a MERGE JOIN (ID 1).
- The resulting data from the query is returned to the user (ID 0).
When first looking at an explain plan and wanting to quickly get an idea of the steps in which the query will be executed, do the following:
- Look for the most indented rows in the plan (the right-most rows). These will be executed first.
- If multiple rows are at the same level of indentation, they will be executed in top-down fashion in the plan, with the highest rows in the plan first moving downward in the plan.
- Look at the next most indented row or rows and continue working your way outward.
- The top of the explain plan corresponds with the least indented or left-most part of the plan, and usually is where the results are returned to the user.
Once you have an explain plan for a query, and can understand the sequence of how the query will be processed, you then can move on and perform some analysis to determine if the explain plan you are looking at is efficient. When looking at your explain plan, answer these questions and consider these factors when determining if you have an efficient plan:
- What is the access path for the query (is the query performing a full table scan or is the query using an index)?
- What is the join method for the query (if a join condition is present)?
- Look at the columns within the filtering criteria found within the WHERE clause of the query, and determine if they are indexed.
- Get the volume or number of rows for each table in the query. Are the tables small,medium-sized, or large? This may help you determine the most appropriate join method. See Table 9-3 for a synopsis of the types of join methods.
- When were statistics last gathered for the objects involved in the query?
- Look at the COST column of the explain plan to get a starting cost.
By looking at our original explain plan, we determined that the EMP table is larger in size, and also that there is no index present on the DEPTNO column, whic h is used within a join condition between the DEPT and EMP tables. By placing an index on the DEPTNO column on the EMP table and gathering statistics. on the EMP table, the plan now uses an index:
No comments:
Post a Comment