Detailed information on different methods of generating explain plan

Whenever an SQL statement is executed, Oracle (that is the RBO or CBO) designs an execution plan for it. This execution plan is basically a step by step instruction for how the statement must be executed. That is, the order in which tables are read, if indexes are used, which join methods are used to join tables and so on. The execution plan for an SQL statement can be viewed with the explain plan statement. The query execution plan is elaborated during an SQL statement's parse phase.

For generating execution plan or explain plan we can use different method depends upon the condition,
1. By using explain plan for command 
2. By using the package DBMS_XPLAN
3. You can use the V$SQL_PLAN views to display the execution plan of a SQL statement
4. Using SQL*Plus Autotrace

You can execute the explain plan for command from sql*plus , however it will not show the plan but save it into a table named PLAN_TABLE.

The PLAN_TABLE Output Table

Starting with release 10g, this table is automatically available as a global temporary table. With previous releases, you have to create it in each schema as needed. You can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. On Unix, it is located in the $ORACLE_HOME/rdbms/admin directory.
SQL>conn sys/password as sysdba -- or mention your schema/password

SQL@$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL>CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
SQL>GRANT ALL ON sys.plan_table TO public;

PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. Oracle Corporation recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

1. By using explain plan for command 
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.To explain a SQL statement,we can use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

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>

Displaying PLAN_TABLE Output

After you have explained the plan, use the following method to retrieve the explain plan. 

The explain plan for command explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. For that you have to query PLAN_TABLE

SQL> rem  Show plan_table execution from Plan table.

col COL1        format A6 head 'Id-Pid'

col parent_id   format 99 head pnt

col operation   format A18

col object_name format A18 truncate 

col object_type format A18

col options     format A20 truncate

col optimizer   format A15  head 'Optmzer'

select    id||'-'||parent_id COL1

         ,operation

         ,object_name

         ,object_type

         ,options

         ,optimizer

from plan_table 

order by id

;

The output should be like this,

Querying PLAN_TABLE gives too much headache as we have to know each field in details and it is rarely used. From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables. One is for serial plans and the other is for parallel plans. They can be found under $ORACLE_HOME/rdbms/admin. Examples of their usage are below.

To obtain a formatted execution plan for serial plans:

SQL> set lines 150
SQL> set head off
SQL> @?/rdbms/admin/utlxpls.sql

To obtain a formatted execution plan for parallel plans:
SQL> set lines 150
SQL> set head off
SQL> @?/rdbms/admin/utlxplp.sql

For example we can use utlxpls.sql to display the formatted explain plan after executing explain plan for command , The output should be like this ,

Identifying Statements for EXPLAIN PLAN

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using SET STATEMENT ID, remove any existing rows for that statement ID. The above example we can rewrite as follow ,

SQL> EXPLAIN PLAN

  SET STATEMENT_ID = 'st1' FOR

  SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno 
  AND    e.ename  = 'SMITH';

  Explained.

SQL>

Specifying Different Tables for EXPLAIN PLAN

You can specify the INTO clause to specify a different table. Here is the example, 

SQL> EXPLAIN PLAN

INTO my_plan_table

FOR

SELECT last_name FROM employees;
Explained.

SQL>

You can also specify a statement Id when using the INTO clause.

SQL>EXPLAIN PLAN

 SET STATEMENT_ID = 'st1'

 INTO my_plan_table

 FOR

 SELECT last_name FROM employees;
 Explained.

 SQL>

2. By using the package DBMS_XPLAN
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package. The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.

2.1 Display function:- 
The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement by using explain plan for command. The DBMS_XPLAN.DISPLAY function will accept mainly 3 optional parameters in the following order.

• table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
• statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
• format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

Example:- 
SQL> EXPLAIN PLAN FOR
SELECT * FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';
explained.
SQL>
You can use dbms_xplan  with display option to generate the execution plan ,

You can specify the format parameter to give different look to the explain plan , here i used BASIC parameter.

Basic usage of display function

To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
To display the plan for a statement identified by 'foo', such as statement_id='foo':
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));


2.2 Display_cursor function:- 
In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts  three optional parameters in the following order

• sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
• child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are displayed.
• format - In addition to the setting available for the DISPLAY function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".

Example:- 
The following example show the advanced output from a query on the SCOTT schema.

SQL> CONN / AS SYSDBA
Connected.
SQL> GRANT SELECT ON v_$session TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan_statistics_all TO scott;
Grant succeeded.
SQL> CONN scott/scott
Connected.


SQL> SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename  = 'SMITH';

--- out put omitted ---


SQL> SET LINESIZE 130
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  am2k9vaqsq9c9, child number 0
-------------------------------------
SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename  =
'SMITH'

Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|          |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |         |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    37 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / E@SEL$1

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   3 - SEL$1 / D@SEL$1
   4 - SEL$1 / D@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   2 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
       "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

60 rows selected.

SQL>
Basic usage of display_cursor function 

To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS_ LAST');

2.3 Other Functions
There are some other useful functions in this package, but they are not using very often, so they are summarized below.
DISPLAY_AWR - Introduced in Oracle 10g Release 1, this function displays an execution plan of stored sql statement  in the Advanced Workload Repository (AWR).
DISPLAY_SQLSET - Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.
DISPLAY_SQL_PLAN_BASELINE - Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
DISPLAY_PLAN - Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.

Out of which display_awr is commonly used rather than others,
2.4 Display_awr function:- 
This table function displays the contents of an execution plan stored in the AWR, and its syntax is ,
DBMS_XPLAN.DISPLAY_AWR( 
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER DEFAULT NULL,
   db_id             IN      NUMBER DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT TYPICAL);

sql_id	Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
plan_hash_value	Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.
db_id	Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, thedatabase_id of the local database will be used, as shown in V$DATABASE.
format	Controls the level of details for the plan. It accepts four values: BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option. TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below). SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

Basic usage of display_awr function 
To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
To display all execution plans of all stored SQL statements containing the string 'TOTO':
SQL>SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
    (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf 
     WHERE ht.sql_text like '%TOTO%';

3.By using V$SQL_PLAN view  (commonly used in 9i and earlier )

Many DBAs query views such as V$SQL or V$SQLAREA to find queries to tune. They then take that query and run it through explain plan. The problem with the explain plan statement, used in this manner, is that it determines how Oracle would execute the given SQL statement. It does not tell you how Oracle has executed that SQL statement.

Oracle 9i introduced the V$SQL_PLAN dynamic performance view. The purpose of this view is to tell you how Oracle has executed a specific SQL statement. With this view, the DBA can be sure they know how the SQL statement was executed. This view is commonly used in oracle 9i  and earlier as dbms_xplan.display_Cursor later introduced in 10g can display the execution plan from v$Sql_plan and associated structure  in a better format .

4. Using SQL*Plus Autotrace

The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement. The utility requires a plan table (for the Explain Plan) under the current user’s schema. In addition, it requires the plustrace or DBA role for the user executing the trace. 

The AUTOTRACE facility is only available in SQL*Plus. Unlike the EXPLAIN PLAN command, the AUTOTRACE facility must use a table called PLAN_TABLE. The EXPLAIN PLAN command can use a plan table of any name. 

Setting up AUTOTRACE

Follow these steps to set up the AUTOTRACE facility to display an explain plan.

1. Ensure that the PLAN_TABLE table exists. To see if your schema has a

PLAN_TABLE, try to describe it:

SQL> desc plan_table;

If the PLAN_TABLE doesn’t exist, you need to create one. Run this script to create the PLAN_TABLE in

your schema:

SQL> @?/rdbms/admin/utlxplan.sql

2. Your schema also needs access to the PLUSTRACE role. You can verify access to

the PLUSTRACE role using the following:

select username,granted_role from user_role_privs

where granted_role='PLUSTRACE';

If you don’t have access to the PLUSTRACE role, run steps 3 and 4 as the SYS schema:

3. Connect as SYS and run the plustrce.sql script:

SQL> conn / as sysdba

SQL> @?/sqlplus/admin/plustrce.sql

4. Grant the PLUSTRACE role to developers (or to a specific role) who want to use

the AUTOTRACE facility:

SQL> grant plustrace to star1;

AUTOTRACE OPTIONS: -

1) SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default. Queries are run as normal.

2) SET AUTOTRACE ON - The query execution takes place and it gives the output

and the AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

3) SET AUTOTRACE ON EXPLAIN - The query is run as normal and it gives the output

and the AUTOTRACE report shows only the optimizer execution path.

4) SET AUTOTRACE ON STATISTICS - The query is run as normal  and it gives the output

and the AUTOTRACE report shows only the SQL statement execution statistics.

5) SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the query output, if any.

6) SET AUTOTRACE TRACEONLY EXPLAIN - Like SET AUTOTRACE TRACEONLY,but suppresses the display of the execution statistics,showing only the query plan. This setting does not actually execute the query. It only parses and explains the query.

7) SET AUTOTRACE TRACEONLY STATISTICS - Like SET AUTOTRACE TRACEONLY,but suppresses the display of the query plan. It shows only the execution statistics.

Note:- Just SET AUTOTRACE ON, returns all the rows of the query followed by the execution

plan and statistics of the query run. Now, SET AUTOTRACE TRACE which is synonymous  to SET AUTOTRACE TRACEONLY does not return the rows of the query but returns the  count of rows (like it always does at the end of every SELECT query) along with the  execution plan and the statistics of the query.

This implies that SET AUTOTRACE ON is really of no use for us, DBAs, since it  returns the result set of the query before it shows up the explain plan and the  statistics of the query - If the result set is of say 10 million records then one  has to wait for all the records to come up before (s)he gets a chance to look at the  explain plan and statistics. It is suggested to use SET AUTOTRACE TRACE(ONLY) as it gives us the information of number of rows returned, explain plan and statistics.

Different methods to trace a session in oracle

Sometimes a user may be running an application in which hundreds of queries are executed. In such scenarios, it may be hard to pinpoint exactly which SQL statement is causing performance issues can turn on SQL tracing to capture statistics regarding all SQL statements run by a user. Through trace you can instruct Oracle to capture resources consumed and execution plans for all SQL statements run by a session.

Here are the general steps to take to trace a session:

1. Enable tracing.

2. Run the SQL statements you want traced.

3. Disable tracing.

4. Use a utility such as tkprof, trcsess, or the Oracle Trace Analyzer to translate

the trace file into a human-readable format

Oracle provides a wide variety of methods to generate a SQL resource usage trace file (quite frankly, to the point of being confusing), including:

• DBMS_SESSION
• DBMS_MONITOR ( available from 10g onwards and is recommended )
• DBMS_SYSTEM
• DBMS_SUPPORT  (not installed by default)
• ALTER SESSION
• ALTER SYSTEM
• oradebug  (commonly used in oracle 8i and older version)

The method you use depends on your personal preferences and various aspects of your

environment, such as the version of the database and PL/SQL packages installed. Each of these tracing methods is described briefly in the following subsections.

Trace levels
Trace levels can be used to define the type of information in the trace file.  There are a number of different trace levels, which generate different logging information.
0 - trace is off
1 – standard SQL (execute-fetch-parse) trace no, no wait events, or bind variables. level 1 is the default trace level.
2.same as level 1 and not commonly used
4 – Bind variables only
8 – Wait events only
12 – Bind Variables and Wait Events

Init.ora/spfile parameters

There are a few init.ora parameters that need to be set for the sessions you want to trace.  They are as follows:
timed_statistics=true
statistics_level=typical|all
This is only really required for the waits or sql timing information.  Should be enabled by default on most system. 
max_dump_file_size=unlimited
This allows the trace file to grow as required by the trace.  If this is not set to unlimited, then you may find your trace file does not cover all activity, and you will see a message similar to “*** DUMP FILE IS LIMITED TO xxxxx BYTES ***” at the end of your trace file..

11g R1 and above: 
With the introduction of the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, traces and core files are placed in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
To show the location of the DIAGNOSTIC_DEST, the following command can be used:   

SQL> show parameter diagnostic_dest

Oracle allows you to set the TRACEFILE_IDENTIFIER parameter at session level, allowing you to include some recognizable text into the trace file name.

SQL>alter session set tracefile_identifier='TEST1';

The tracefile name will be something like <instance><_ora_<spid>.trc.
in case if you give any tracefie identifier like alter session set tracefile_identifier='TEST1'; then trace file will be something like  <instance><_ora_<spid>_TEST1.trc.

Using DBMS_SESSION  (Available from oracle 7.3.4)

Using the DBMS_SESSION package was covered in the solution section of this recipe. Here’s a brief summary of how to enable and disable tracing using DBMS_SESSION:
SQL> exec dbms_session.set_sql_trace(sql_trace=>true);
SQL> -- run sql statements that you want to trace...
SQL> exec dbms_session.set_sql_trace(sql_trace=>false);

Using DBMS_SYSTEM  (Available from oracle 7.3.4)

To enable SQL tracing in another session, you can use the DBMS_SYSTEM package. You first must identify the session you want to trace:

SQL> select username,sid,serial# from v$session;

Pass the appropriate values to the following lines of code:

SQL> exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5, sql_trace=>true);

Run the following to disable tracing for the session:

SQL> exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5, sql_trace=>false);

You can also use DBMS_SYSTEM to capture wait events:

SQL> exec dbms_system.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

SQL> exec dbms_system.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
where si=sid , se=serial# , ev=events , le=level and nm= name.

Using DBMS_SUPPORT  (Available from oracle 8.0.6)

This technique requires that you first load the DBMS_SUPPORT package (it’s not created by default):
SQL> @?/rdbms/admin/dbmssupp.sql
Use the following syntax to enable and disable tracing in your current session:
SQL> exec dbms_support.start_trace(waits=>TRUE, binds=>TRUE);
SQL> -- run sql statements that you want to trace...
SQL> exec dbms_support.stop_trace;
Use this syntax to enable and disable tracing in session other than your own:
SQL> exec dbms_support.start_trace_in_session(sid=>123, serial=>1234,-
> waits=>TRUE, binds=>TRUE);
SQL> exec dbms_support.stop_trace_in_session(sid=>123, serial=>1234);

Using DBMS_MONITOR (Available from oracle 10.1)

If you are using Oracle Database 10g or higher, it is  recommend to use DBMS_MONITOR package, which offers a high degree of flexibility, for facilitating tracing. To enable and disable tracing within the current session use the following statements:

SQL> exec dbms_monitor.session_trace_enable;

SQL> -- run sql statements that you want to trace...

SQL> exec dbms_monitor.session_trace_disable;

Use the WAIT and BINDS parameters to enable tracing with wait and bind variable information:

SQL> exec dbms_monitor.session_trace_enable(waits=>TRUE, binds=>TRUE);

Note:- Wait events track the amount of time spent waiting for a resource. Bind variables are substitution variables used in place of literal variables.

Use the SESSION_ID and SERIAL_NUM parameters to enable and disable tracing for an already connected session. First run this SQL query to determine the SESSION_ID and SERIAL_NUM for target session:

Use the following query 
column line format a88
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username ='&USER'and s.status='ACTIVE' and s.type='USER'; 
 

Now use the appropriate values when calling DBMS_MONITOR

SQL> exec dbms_monitor.session_trace_enable(session_id=>1234, serial_num=>12345);

To stop the session level tracing .

SQL> exec dbms_monitor.session_trace_disable(session_id=>1234, serial_num=>12345);

You can also enable the tracing of wait and bind variable information as follows:

SQL> exec dbms_monitor.session_trace_enable(session_id=>1234, serial_num=>12345, waits=>TRUE, binds=>TRUE);

check: 
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;
for trace enbaled session

From 11g onwards to identify the trace file execute the following query 
SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = &sid
 You can also specify client_id in dbms_monitor,
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');
You can turn on trace at database level,
SQL>exec DBMS_MONITOR.DATABASE_TRACE_ENABLE (waits => TRUE,binds => TRUE,instance_name > NULL);
turn off
SQL>exec DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name > NULL);
check:
SQL>SELECT * FROM dba_enabled_traces;

Altering Your Session

You can use ALTER SESSION to turn tracing on and off:

SQL> alter session set sql_trace=true;

SQL> -- run sql commands...

SQL> alter session set sql_trace=false;

Note:-  The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR or DBMS_SESSION packages to enable tracing.

Altering the System

You can turn on tracing for all sessions in the database by using this ALTER SYSTEM statement:

SQL> alter system set sql_trace=true;

Use the following SQL to disable system-wide tracing:

SQL> alter system set sql_trace=false;

Caution:-  it is not  recommend to set SQL tracing at the system level as it can severely degrade system performance.

Using oradebug

The oradebug utility can be used to enable and disable tracing for a session. You need SYSDBA privileges to run this utility.

You can identify a process to trace either by its operating system process ID or its Oracle process ID.

To determine these IDs, run the following SQL query for the username you want to trace (in this example the user is mahi ):

SQL> select spid os_pid, pid ora_pid from v$process

where addr=(select paddr from v$session where username='MAHI');

Here is the output for this example:

OS_PID ORA_PID

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

8828 17

Next, use oradebug with either the SETOSPID or SETORAPID option to attach oradebug to a session. This example uses the SETOSPID option:

SQL> oradebug setospid 8828; - here we started tracing on os process id 8828

Now you can set tracing on for the session:

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8;

You can verify that tracing is enabled by viewing the trace file name:

SQL> oradebug TRACEFILE_NAME;

Use the following syntax to disable tracing:

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT OFF;

Use the HELP option to view all features available in the oradebug utility:

SQL> oradebug help

Diagnostic events

when insufficient information is available to resolve a given problem we can use oracle
diagnostic events to produce additional diagnostic information.The Oracle database kernel is instrumented with over four hundred so-called “pseudoerror debugging events.”
Most of these events are primarily used to produce additional diagnostic inforamation to change Oracle's behaviour and to trace the inner working of oracle because these events can enable some of the undocumeted features of oracle. For example most commonly used diagnostic events are 10046 , 10053 etc.Oracle diagnostic events are classified into 4 major categories based on their usage,

Immediate dump events
immediate dump event will dump the diagnostic information in a trace file as soon as the command is fired. Normally immediate dump are used to dump the system state , process state, and file headers (controlf , redo_hdrs,file_hdrs) .It can't invoked by setting EVENT parameter in the init file. It can be triggered by alter session set events command or by using oradebug command.
 SQL> alter session set events 'immediate trace name systemstate level 8';
 The preceding command creates a trace file , which contains detailed information
about every oracle process active on the system, as well as the details about the
resources held/request by those process. Another example of an immediate dump event is
 SQL>alter session set events 'immediate trace name controlf level 10';

On error dump events
On error dump events  are similar to immediate dump events but they are invoked only
when the error occurs. They are not used to dump the system state , prcoess state or
error stack when a particular oracle error occures .Normally set using the EVENT parameter in the INIT.ora file. Following example shows an entry in the init.ora file to dump the error stack to trace file when ORA-04020 (Dead lock detected when waiting for an object ) is encountered.
event= "4020 trace name errorstack level 1"
On error dumps events are most commonly used to identify the cause of a particular error, and the oracle support may ask you to dump the error stack for further investigation. For example , to diagnose the shared memory leak (ORA- 04031) will need the following settings at init.ora file
event = " 4031 trace name errorstack level 10"
Usaully on error  dumps level set between 1 and 10.

Change behaviour events
change behaviour events are set using EVENT parameter in the init.ora file . These events are very powerful events and should be used with care.These events are used to enable or disable certain functionalitied of the oracle kernel.Unlike other diagnostic events these events do not have level numbers because these event do not create a trace file.
Change behaviour events have additional keyword : forever, without these keyword
the event id fired only once and no subsequent actions are triggered. Change behaviour events uses a reserverd set of numbers to bring about the change in oracle behaviour.
for example following event disables the automatic shrinking of rollback segment by smon process. which is usually happnes every 12 hrs .
event = " 10512 trace name context forever"
Many of the change behaviour events are potentially dangerous to the database as they can change the default behaviour of database .

Process trace events
proces strace events are used to get additional information when the process is running .These events are normally harmless , and they do generate a trace file. Following are the valid example for setting two events,
event="4031 trace name context forever, level 10"
evnet="10046 trace name context forever, level 12"
or you can
event="4031 trace name context forever, level 10: 10046 trace name context forever, level 12"or by
SQL>alter session set events '10046 trace name context forever, level 12';

we  came to know that  what these events does, it enables extended SQL trace. But why such difficult syntax?
This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):
10046
The first word in event string (the 10046) specifies the when should some action should be taken. 10046 events enable sql statement timing ,and everything after that (trace name context forever, level 12) is that action.
trace
The first word in action (trace) specifies what type of action Oracle needs to take if that event (10046) occurs. In event syntax, “trace” is most generic action, which is used for tracing, behavior changing and Oracle dumps.
name
The “name” specifies that the name of what to dump/trace will follow. The “name” is always present when “trace” option is used (as far as I know).
context
Now the next keyword (context) is the one where you can define whether you want Oracle to dump something when the event is hit or just do context specific trace. If you replace the “context” with “errorstack” for example, you wouldn’t get SQL trace output, but rather an Oracle errorstack dump whenever event 10046 is hit.You can use “oradebug dumplist” to find all the possible dump commands what you can set as actions for an event.
forever
The next keyword (forever) is actually an option to the action, not an action keyword itself. Forever means that keep invoking the action when the 10046 event is hit, forever (or until explicitly disabled). If we don’t specify forever, then the action would be invoked only once and the event will disable itself after that.
level 12
The “level 12? is also just another option to the action, specifying that the value for that event handler in given session should be 12. This means that whenever some Oracle function is checking whether that event is set, they will be returned value 12, the calling function interprets the value and acts appropriately (traces both binds and waits in our case).

Note:-
1. you can also set event using oracle supplied package procedure called  DBMS_SYSTEM.SET_EV
2. alter session set sql_trace = true; which in functionality is equivalent to the
alter session set events '10046 trace name context forever, level 1'; and dbms_session.set_sql_trace (true);

what is histogram in oracle and its importance

DBMS_STATS by default calculates the number of rows in a table,The number of distinct values in a column etc.Without additional information, optimizer assumes that values are evenly distributed .But if they are not evenly distributed,  inefficient execution plans can result. A histogram holds the data distribution of values within a column of a table.Number of occurrences for a specific value/range .

Histogram is collected by using DBMS_STATS.Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer to determine the fastest table join order.

When a query executes in database, Optimizer records the predicate usage in sys.col_usage$ (like number of equi joins, non equi joins, between operators & like operators). This recorded values in sys.col_usage$ will be used by dbms_stats to decide the need for histograms on the columns or *NOT*.

Types of Histograms

Histograms are classified into frequency histograms and height-balanced histograms .
Type of histogram is stored in the HISTOGRAM  column of the *tab_col_statistics views (USER/DBA) and its Value will be (‘HEIGHT BALANCED’, ‘FREQUENCY’,  or ‘NONE’).
For example,

SQL>select column_name, num_distinct, histogram FROM user_tab_col_statistics
WHERE table_name = 'SALES';

Histogram and Buckets
A bucket is a range of value for a particular column, for example if we had a column named true_or_false , and it could only have two values  true or false ,then we could have upto 2 buckets and these two buckets would have a value describing the number of times that where true and false.When Histograms are created the number of  buckets can be specified otherwise oracle will automatically chose the right choice.It is this number that controls the type of histogram created. 
When Distinct Values <= Buckets 
– Then Frequency Histogram is created
– Else Height-Balanced Histogram is created

One limitation of Oracle's frequency histograms is that it has a hard limit of 254 buckets.
If you have a small number of distinct values, say less than 100 and queries are frequently executed against these columms for specific values and column is skewed, then Frequency Histograms are your best option.If your number of distinct values is more than 254, Frequency Histograms are out of the question. You will have to use Height Balanced Histograms.

Frequency Histograms
Each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value.Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. 

Height-Balanced Histograms
In a height-balanced histogram, the column values are divided into bands so that each 
band contains approximately the same number of rows. The column values are divided into bands so that each band contains approximately the same number of rows. For instances, we have 10 distinct values in the column and only five buckets. It will create height based(Height balanced) histograms and it will evenly spread values through the buckets. A height-based histogram is when there are more distinct values than the number of buckets and the histogram statistics shows a range of rows across the buckets .The useful information that the histogram  provides is where in the range of values the 
endpoints fall.

** EPN - end point number & EPV - end point value 

How to Create a Histogram

You have the following options to create histograms using Oracle:
ANALYZE TABLE 
As part of the ANALYZE TABLE command for compiling statistics, you can specify
FOR ALL [INDEXED] COLUMNS [SIZE <buckets>]
to create histograms (<buckets> > 1). If you do not specify the SIZE, 75 buckets are used. For example, with the following command, histograms are created with 75 buckets for all indexed columns of the T100 table:
SQL> ANALYZE TABLE T100 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

DBMS_STATS
You can also specify the METHOD_OPTS argument as part of the DBMS_STATS command GATHER_*_STATS, the contents of this argument are syntactically identical to the FOR expression of ANALYZE TABLE.
Therefore, the following is comparable with the above ANALYZE TABLE example:
SQL>DBMS_STATS.GATHER_TABLE_STATS('SAPR3', 'T100',
 METHOD_OPT=>'FOR ALL INDEXED COLUMNS');

Method_opt Parameter: This is the parameter which tells about creating histogram while collecting the statistics. The default is FOR ALL COLUMNS SIZE AUTO in Oracle10g. But in oracle9i, the default is FOR ALL COLUMN SIZE 1 which will turn off the histogram collection. 

METHOD_OPT dictates values how histograms are done ,

• for all columns - gather histograms on all columns -- hugely expensive and rarely should be used
• for all indexed columns - gather histograms on all columns that are currently indexed. This is typically to be the "most useful" although you can arguably skip nearly unique columns such as primary keys
• for all hidden columns - gather histograms on columns you cannot see, usefulif using the object relational features which incorporate tons of hidden columnsin your objects.
• for columns <list> - gather histograms on these specific columns, could be useful to skip primary key indexes or just to get the 2 or 3 columns of interest

Following options are available fro size clause,

• integer : Number of histogram buckets. Must be in the range [1,254] (if you not specify the integer oracle will take its default 75)
• REPEAT : says to do the histograms just like you did them last time . It reads the data dictionary to figure our what to do.
• AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. We have a table called sys.col_usage$ that stores information about column usage. dbms_stats use this information to determine whether histogram is required for the columns.
• SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

For example,

SQL> execute dbms_stats.gather_table_stats  (ownname => 'oe', tabname => 'inventories', 

METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');

Demonstration for how optimizer  work with and without histogram
SQL> create table t nologging as
  2  select case when rownum = 1 then 5 else mod(rownum,4) end as id, a.* from all_objects a;

Table created.

SQL> select count(*),id from t group by id;

  COUNT(*)         ID
---------- ----------
     12508          1
     12508          2
        1              5
     12508          3
     12508          0

SQL> create index t_ind on t(id) nologging;

Index created.

SQL> begin

  2  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME =>     'T',ESTIMATE_PERCENT =>

  3  10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

  4  end;

  5  /

PL/SQL procedure successfully completed.

Note:- Histogram collection is disabled by "FOR ALL COLUMNS SIZE 1"

SQL> set autotrace traceonly explain;

SQL> select * from t where id = 1;

Execution Plan

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 12455 |  1167K|   162   (1)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 12455 |  1167K|   162   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=1)

SQL> select * from t where id = 5;

Execution Plan

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |  4152 |   389K|   162   (1)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    |  4152 |   389K|   162   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=5)

SQL>

Conclusion: Optimizer is using full table scan for the query which returns 12508  records as well as it using full table scan for query which returns just only one record. This is obviously incorrect. This problem will be resolved by collecting histogram. Let us see in the next scenario.

Scenario 2 : Let us generate the statistics with histogram and see what kind of execution path optimizer is using. FOR COLUMN SIZE 5 ID will create 5 bucket for column ID. If we are not sure the distinct number of values in the column, then we can use AUTO option to collect histogram. With this histogram, oracle optimizer knows that, the column ID is highly skewed and it has 5 bucket and 4 bucket has around  12508  records with values 0 to 3 and another bucket has only one record with ID equal to 5 . Now depends upon the query, optimizer decides whether to use index or Full table scan.

SQL> set autotrace off;
SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'T',ESTIMATE_PERCENT =>
  3  10, METHOD_OPT => 'FOR COLUMNS SIZE 5 ID',CASCADE => TRUE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain;
SQL> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12150 |  1139K|   162   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 12150 |  1139K|   162   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

SQL> select * from t where id = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2025 |   189K|   119   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2025 |   189K|   119   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_IND |  2025 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=5)

SQL>

Conclusion: Optimizer is using full table scan for the query which returns 12508   records. At the same time, optimizer is using index scan when for other query which returns one record. This scenario, the optimizer choose the right execution plan based on the query WHERE clause.

Rules On When To Create Histograms

• First there are no rules of thumb that are always correct.
• When creating using dbms_stats, the use of the DEGREE (for parallel) can skew the value of density
• Running dbms_stats can drop histograms ,so Always check if histogram exists on table before DBMS_STATS is run.Use METHOD_OPT FOR ALL COLUMNS REPEAT to prevent deletion of histograms  data.
• Histograms are stored in the dictionary.
• There is a maintenance and space cost for using histograms.
• Only create histograms on columns that are used in WHERE clauses of queries and have a highly-skewed data distribution.Method_opt “size auto” is supposed to do just that but does not always get the second part right.

When Not To Use Histograms

• all predicates on the column use bind variables 
• the column data is uniformly distributed 
• the column is not used in WHERE clauses of queries 
• the column is unique and is used only with equality predicates
• If your database exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histogram generation (method opt) for any future dbms_stats analysis.  This approach will use the number if distinct values to determine the selectivity of a column.

Reference :- http://rajeshwaranbtech.blogspot.in &  http://myorastuff.blogspot.in