Trace event to check explain plan + bind variable peeking + hints + CBO environment

 

Here is description and example about usefull event in 10g:
ORA-10132 dump plan after compilation

When events 10046 level 4 and 10053 are too verbose, this event dumps - for each parsed query:
- the sql statement
- the execution plan ( with predicates, parallel info ... as dbms_xplan does)
- the bind variables used by bind variable peeking
- the hints that can be used to force this execution plan (as outlines do)
- the optimizer environment

Notes:
- it can be used in 9i but there is not all those information available in 10g
- you will have dump generated only when statement is parsed
- information comes from http://jonathanlewis.wordpress.com/2006/11/27/event-10132/
- you can have more trace on 'why' the CBO choose that execution plan with event 'ORA-10053: CBO Enable optimizer trace'

Here is an example: the following script

column value new_value uniquenumber
select value from v$sysstat where statistic#=6;
variable e number;
exec :e:=7698;
alter session set events='10132 trace name context forever, level 1';
select /* &uniquenumber used to parse each time */count(*) from scott.emp where empno > :e;
alter session set events='10132 trace name context off';

generates the following trace:

sql_id=3w7fap1vgs6ph.
Current SQL statement for this session:
select /* 8144 */count(*) from scott.emp where empno > :e

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1 | |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX RANGE SCAN | PK_EMP | 6 | 18 | 1 | 00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("EMPNO">:E)

Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : FRANCK
plan_hash : 109489892
Peeked Binds
============
Bind variable information
position=1
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=7698
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/

Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
... all parameters (documented or hidden) that are used by the CBO ...