1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Usage of EXPLAIN PLAN in oracle pl/sql tuning

EXPLAIN PLAN Usage
When an 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
    AUTOTRACE - The Easy Option?
    EXPLAIN PLAN
    Statement ID
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:

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;
AUTOTRACE - The Easy Option?
Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON
SQL> SELECT *
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.ename = 'SMITH';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO DEPTNO DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800
20 20 RESEARCH DALLAS




Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)




Statistics
----------------------------------------------------------
81 recursive calls
4 db block gets
27 consistent gets
0 physical reads
0 redo size
941 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

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 supresses 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.

<BLOCKQUOTE
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.

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.

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 to not accept a
statement_id parameter

DBMS_XPLAN
The DBMS_XPLAN package is used to format the output of an explain plan. It is intended as a replacement for the utlxpls.sql script.
If it is not already present create the SCOTT schema:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE if it does not already exist:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;
Next we explain an SQL statement:
conn scott/tiger
EXPLAIN PLAN FOR
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
Finally we use the DBMS_XPLAN.DISPLAY function to display the execution plan:
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 3 |
| 1 | NESTED LOOPS | | 1 | 57 | 3 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")

Note: cpu costing is off

18 rows selected.
The DBMS_XPLAN.DISPLAY function can accept 3 parameters:
    table_name - Name of plan table, default value 'PLAN_TABLE'.
    statement_id - Statement id of the plan to be displayed, default value NULL.
    format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'.
EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';

SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
----------------------------------------------------

10 rows selected.

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy