I like unknow stuff so here is latest addition..

As you know  ADR  tool can be invoked using adrci command:
-sh-3.2$ adrci

ADRCI: Release 11.1.0.6.0 – Beta on Fri Mar 13 12:06:38 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = “/oracle/admin”

and with help command with can get available options:

adrci> help

 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

 There are other commands intended to be used directly by Oracle, type
 ”HELP EXTENDED” to see the list

and some more:

adrci> help extended

 HELP [topic]
   Available Topics:
        BEGIN BACKUP
        CD
        DDE
        DEFINE
        DESCRIBE
        END BACKUP
        LIST DEFINE
        MERGE ALERT
        MERGE FILE
        QUERY
        SET COLUMN
        SHOW CATALOG
        SHOW DUMP
        SHOW SECTION
        SHOW TRACE
        SHOW TRACEMAP
        SWEEP
        UNDEFINE
        VIEW

But there is more than this.Oracle does not have them in this list.That’s why I call them hidden and btw Oracle call some of them hidden too.So if I issue command  help hidden I got few more commands e.g.
adrci> help hidden

 HELP [topic]
   Available Topics:
        CREATE HOME
        CREATE INCIDENT
        REGISTER INCIDENT FILE
and again I will use help ( Oracle is helping me .. Once you get first command ).Commands are self explanatory.
adrci> help create home

  Usage:  CREATE HOME keyname=key_value [keyname=key_value ...]

  Purpose: Create an ADR home.

  Arguments:
    <keyname>: Name of parameter for creating home. The current parameters
               are “BASE”, “PRODUCT_TYPE”, “PRODUCT_ID”, and
               “INSTANCE_ID”. All these parameters are mandatory.
    <key_value>: Value of the associated keyname.

  Example:
    create home base=/tmp product_type=rdbms product_id=db1
                instance_id=inst1

adrci> help create incident

  Usage:  CREATE INCIDENT keyname=key_value [keyname=key_value ...]

  Purpose: Create an incident with the input argument values.
  Arguments:
    <keyname>: Name of parameter for creating incident. For exmaple,
               “PROBLEM_KEY”. Note that some of the keynames are
               mandatory for the command. The current mandatory keynames
               are “PROBLEM_KEY” “ERROR_FACILITY”, “ERROR_MESSAGE”,
               “ERROR_NUMBER”.
    <key_value>: Value of the associated keyname.

  Example:
    create incident problem_key=”ORA-00600: [Memory corruption]                    ” error_facility=ORA
                    error_number=600

adrci> help register incident file

  Usage: REGISTER INCIDENT FILE keyname=key_value [keyname=key_value ...]

  Purpose: Register a dump file as an incident file.

  Arguments:
    <keyname>: Name of parameter for adding incident file. The current
    parameters are “FILENAME”, “INCIDENT_ID”, “PATHNAME”.
               “FILENAME” and “INCIDENT_ID” are mandatory keys.
    <key_value>: Value of the associated keyname.

  Example:
    register incident file filename=foo.trc incident_id=1

Any more commands?  Yes…

Read more of this post

This time I will focus only on one event sql_trace or 10046 .

Oracle 11g greatly extended options for this event.I will start with SQL_ID option.

Now I can set this event to trace only specific sql_id or few of them:

Syntax is :
alter session/system set events ‘sql_trace [sql:<sql_id>|<sql_id>] … rest of event specification ‘;

So if I would like to know what is going on only for one specific sql_id :

SQL> l
  1  select sql_id, sql_text
  2  from v$sql
  3* where sql_text = ‘select * from dual’
SQL> /

SQL_ID        SQL_TEXT
————- ———————————————————————-
a5ks9fhw2v9s1 select * from dual

SQL> alter session set events ‘sql_trace [sql:a5ks9fhw2v9s1]‘;

Session altered.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dba_tables;

  COUNT(*)
———-
      2636

SQL>  alter session set events ‘sql_trace [sql:a5ks9fhw2v9s1] off’;

Session altered.
and here is output:
PARSING IN CURSOR #1 len=18 dep=0 uid=0 oct=3 lid=0 tim=1237831879652913 hv=942515969 ad=’e5f92ab0′ sqlid=’a5ks9fhw2v9s1′
select * from dual
END OF STMT
EXEC #1:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1237831879652906
FETCH #1:c=1000,e=62,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1237831879653507
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=’RESULT CACHE  9p1ghjb9czx4w7vqtuxk5zudg6 (cr=0 pr=0 pw=0 time=0 us)’
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115 op=’TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)’
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1237831879653934
Although I executed select count(*) from dba_tables my trace file shows only information related to sql_id=a5ks9fhw2v9s1 .I can see sqlid in trace to be sure that’s the one.
This time I will collect trace for both sql_ids:

SQL> select sql_id, hash_value
from v$sql
where sql_text in (‘select * from dual’,'select count(*) from dba_tables’); 

SQL_ID        HASH_VALUE
————- ———-
56bs32ukywdsq 2783852310
a5ks9fhw2v9s1  942515969

SQL> l
  1  select sql_id,sql_text
  2  from v$sql
  3* where sql_text in (‘select * from dual’,'select count(*) from dba_tables’)
SQL> /

SQL_ID        SQL_TEXT
————- ——————————————————————————–
56bs32ukywdsq select count(*) from dba_tables
a5ks9fhw2v9s1 select * from dual

Note char “|” which separates sql_ids and run trace and queries:

SQL> alter session set events ‘sql_trace [sql:a5ks9fhw2v9s1|56bs32ukywdsq]‘;

Session altered.

Run my queries

SQL> select * from dual;

D
-
X

SQL> select count(*) from dba_tables;

  COUNT(*)
———-
      2636
     
     
and finally turn event off:     

SQL> alter session set events ‘sql_trace [sql:a5ks9fhw2v9s1|56bs32ukywdsq] off’;

Session altered.

 
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=0 oct=3 lid=0 tim=1237832416432899 hv=942515969 ad=’e5f92ab0′ sqlid=’a5ks9fhw2v9s1′
select * from dual
END OF STMT
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1237832416432879
FETCH #1:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1237832416433545
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=’RESULT CACHE  9p1ghjb9czx4w7vqtuxk5zudg6 (cr=0 pr=0 pw=0 time=0 us)’
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115 op=’TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)’
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1237832416433900
…….
=====================
PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=1237832421115866 hv=2783852310 ad=’110353330′ sqlid=’56bs32ukywdsq’
select count(*) from dba_tables
END OF STMT
EXEC #1:c=0,e=165,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1237832421115859
FETCH #1:c=91986,e=99570,p=0,cr=2802,cu=0,mis=0,r=1,dep=0,og=1,tim=1237832421215563
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=2802 pr=0 pw=0 time=0 us)’
STAT #1 id=2 cnt=2636 pid=1 pos=1 obj=0 op=’HASH JOIN RIGHT OUTER (cr=2802 pr=0 pw=0 time=1028 us cost=771 size=1283040 card=8910)’
STAT #1 id=3 cnt=7935 pid=2 pos=1 obj=14 op=’TABLE ACCESS FULL SEG$ (cr=231 pr=0 pw=0 time=18 us cost=63 size=87285 card=7935)’
STAT #1 id=4 cnt=2636 pid=2 pos=2 obj=0 op=’HASH JOIN RIGHT OUTER (cr=2571 pr=0 pw=0 time=1000 us cost=707 size=352317 card=2649)’
STAT #1 id=5 cnt=88 pid=4 pos=1 obj=47 op=’INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=1 us cost=1 size=352 card=88)’
STAT #1 id=6 cnt=2636 pid=4 pos=2 obj=0 op=’HASH JOIN OUTER (cr=2570 pr=0 pw=0 time=978 us cost=705 size=341721 card=2649)’

This tracing was done for my own session.Same thing can be done for other users SQL_IDs  using alter system command.
Here is example:

on different session I am running:

SQL> select count(*) from dba_triggers;

  COUNT(*)
———-
       487

on second window I will get sql_id.I can also use OEM or any other tools to get it:
SQL> l
  1  select sql_id, sql_text
  2      from v$sql
  3*    where sql_text = ‘select count(*) from dba_triggers’
SQL> /

SQL_ID        SQL_TEXT
————- ———————————————————————-
b6z8h59a39gv9 select count(*) from dba_triggers

SQL> alter system set events ‘sql_trace [sql:b6z8h59a39gv9]‘;

System altered.

SQL> alter system set events ‘sql_trace [sql:b6z8h59a39gv9] off’;

System altered.
and again here is output:

=====================
PARSING IN CURSOR #3 len=33 dep=0 uid=0 oct=3 lid=0 tim=1237833092872029 hv=1412743017 ad=’111129950′ sqlid=’b6z8h59a39gv9′
select count(*) from dba_triggers
END OF STMT
EXEC #3:c=0,e=116,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1237833092872020
FETCH #3:c=7999,e=7969,p=0,cr=803,cu=0,mis=0,r=1,dep=0,og=1,tim=1237833092880538
FETCH #3:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1237833092881221

Second very usefull option for sql_trace is tracing using process information.
Syntax is:

alter session/system set events ‘sql_trace {process : pid = <pid1>, pname = <pname>, orapid = <orapid>} rest of event specification’;

Read more of this post

This is my 4th post on Oracle events.. Oracle really extended event syntax .In this post I will focus only on Trace event..Even I am getting lost in all combinations.One day I will sort it in nice format but for now I am just dumping knowledge.Also I will use spefic troubleshooting case and used them to resolve the issue.

I will start with  shortest  command for trace event:

alter session set events ‘trace[target]‘;

so question is what we can trace? which words I can use as TARGET?

I will list only ones less danger targets and more usefull ( who am I to decide which ones are more usefull than others? )

alter session set events ‘trace[<target>]‘;

Targets:

bind_capture  -  capturing binds

dirpath_load  - direct load

explain       -  explain plan

sql_apa       - access path analysis

sql_analyze   -  sql analyze

sql_perf      -  performance analyzer

sql_tune      -  tuning advisor

sql_mon       -  sql monitor

sql_optimizer -  optimizer

sql_planmanagement – plan management

sql_costing        - cost based analysis

sql_transform     - sql transformation

sql_execution     - sql execution

I intentionally did not list “danger” targets. As always use this firs on you test database until you are 100% confident that command will not crash your prod database…

Some examples how to use them:

SQL> alter session set events ‘trace[bind_capture]‘;

Session altered.

This command will trace binds

e.g.

 SQL> alter session set events ‘trace[bind_capture]‘;

 Session altered.

 SQL> variable deptno number

 SQL> exec :deptno := 10

 PL/SQL procedure successfully completed.

 SQL> select * from scott.emp where deptno = :deptno;

      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

 ———- ———- ——— ———- ——— ———- ———- ———-

       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

       7839 KING       PRESIDENT            17-NOV-81       5000                    10

       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 SQL>  alter session set events ‘trace off’;

and here is output:

kxsCaptureBindValues(): cur#2(0x2aaaac09c340) sqlid=1a8n1zgb7m90w capture bind value bndPos=0 primaryPos=-1 length=5

kxsCaptureBindValues(): cur#2(0x2aaaac09c340) sqlid=1a8n1zgb7m90w captured 5 bytes of binds

kxsCaptureBindValues(): cur#2(0x2aaaac0f46d0) sqlid=9tgj4g8y4rwy8 capture bind value bndPos=0 primaryPos=-1 length=2

kxsCaptureBindValues(): cur#2(0x2aaaac0f46d0) sqlid=9tgj4g8y4rwy8 capture bind value bndPos=1 primaryPos=-1 length=2

kxsCaptureBindValues(): cur#2(0x2aaaac0f46d0) sqlid=9tgj4g8y4rwy8 capture bind value bndPos=2 primaryPos=-1 length=2

kxsCaptureBindValues(): cur#2(0x2aaaac0f46d0) sqlid=9tgj4g8y4rwy8 captured 6 bytes of binds

kxsCaptureBindValues(): cur#2(0x2aaaac16a3d8) sqlid=53saa2zkr6wc3 capture bind value bndPos=0 primaryPos=-1 length=3

kxsCaptureBindValues(): cur#2(0x2aaaac16a3d8) sqlid=53saa2zkr6wc3 captured 3 bytes of binds

kksRequestBindCapture(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 pos#1: bind cap requested, primaryPos=1

kksPrepareBindCapture(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 keep bind at bndPos=0 len=22

kksPrepareBindCapture(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 allocated 22 bytes

kksPrepareBindCapture(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 set CTXXZBCAP=1 for cursor

kxsCaptureBindValues(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 capture bind value bndPos=0 primaryPos=-1 length=2

kxsCaptureBindValues(): cur#1(0x2aaaac099058) sqlid=6gyb7zuq3dv42 captured 2 bytes of binds

SQL>  alter session set events ‘trace[sql_costing]‘;

Session altered.

SQL>  select * from scott.emp where deptno =10;

will dump optimizer information:

OPTIMIZER INFORMATION

******************************************

—– Current SQL Statement for this session (sql_id=9hursgp8jmmug) —–

 select * from scott.emp where deptno =10

*******************************************

Legend

The following abbreviations are used by optimizer trace.

CBQT – cost-based query transformation

JPPD – join predicate push-down

OJPPD – old-style (non-cost-based) JPPD

FPD – filter push-down

PM – predicate move-around

CVM – complex view merging

SPJ – select-project-join

SJC – set join conversion

SU – subquery unnesting

My next post will  cover in detail components related to trace event and sql_trace event in detail. 

This is the 3th blog about Oracle events.. So many things to cover..

Let me start with this giant command:

SQL> alter session set events ’10046 trace name context level 12, type decr, lifetime 1, after 5 times’;

Session altered.

Many combinations in one command..

All these commands are self explanatory ( tried them anyway and see for yourself )

These are different options that you can use:immediate, forever, name context, name all,life <number> , lifetime,after time, after occurences, type incr, type increment ,type decr, type decrement, type constant, type const ,  off

Examples:

NAME CONTEXT
NAME ALL

SQL> alter session set events ’10046 trace name context level 12′;

Session altered.

SQL>  alter session set events ’10046 trace name all’;

Session altered.

IMMEDIATE
FOREVER

SQL> alter session set events ’10046 trace name context level 12, forever’;

Session altered.

LIFE < number>
LIFETIME

AFTER   < number > TIMES
AFTER   < number > OCCURENCES

SQL> alter session set events ’10046 trace name context level 12, life 10′;

Session altered.

alter session set events ’10046 trace name context level 12, lifetime 10000, after 500 times’;

Session altered.

alter session set events ’10046 trace name context level 12, lifetime 10000, after 500 occurences’;

Session altered.

TYPE DECR
TYPE INCREMENT
TYPE DECREMENT
TYPE CONST
TYPE CONSTANT

SQL> alter session set events ’10046 trace name context level 12, type incr’;

Session altered.

SQL> alter session set events ’10046 trace name context level 12, type decr’;

Session altered.

SQL> alter session set events ’10046 trace name context level 12, type constant’;

Session altered.

SQL> alter session set events ’10046 trace name context level 12, type const’;

OFF

SQL> alter session set events ’10046 trace name context level 12, off’;

Session altered.

Here is event syntax again .In addition to these options you can also use charachters like (, ), [ , ] . *  etc…

<event_spec>   ::= ‘<event_id> [<event_scope>]
                                   [<event_filter_list>]
                                   [<event_parameters>]
                                   [<action_list>]
                                   [off]‘
    <event_id>     ::= <event_name | number>[<target_parameters>]
    <event_scope>  ::= [<scope_name>: scope_parameters]
    <event_filter> ::= {<filter_name>: filter_parameters}
    <action>       ::= <action_name>(action_parameters)
    <*_parameters> ::= <parameter_name> = <value>[, ]

I guess I can write all white paper just to cover all  possible combinations.. Hopefully this will give you enough knowledge to build your own events command..

This is my second post on Oracle events only but with more details.Every Oracle release has new stuff so I decided best way would be to dig version 11g… So here is what I got :

Let’s start with event syntax  what is most important part of all:

    <event_spec>   ::= ‘<event_id> [<event_scope>]
                                   [<event_filter_list>]
                                   [<event_parameters>]
                                   [<action_list>]
                                   [off]‘
    <event_id>     ::= <event_name | number>[<target_parameters>]
    <event_scope>  ::= [<scope_name>: scope_parameters]
    <event_filter> ::= {<filter_name>: filter_parameters}
    <action>       ::= <action_name>(action_parameters)
    <*_parameters> ::= <parameter_name> = <value>[, ]

As you can see  plenty of  different options to play with.

I got a chance to try  few of them .Just imagine how many combinations you can make..

Some examples:

 Most famous event 10046  (SQL_TRACE ) :

turn on: alter session set events ‘sql_trace’;  ( level 1)

turn off :    alter session set events ‘sql_trace off’;

and  ones that I have never used it before:

alter session set events ‘sql_trace wait=true, plan_stat=never’;

Next event will set SQL_MONITOR component  at level high to get high resolution time for each trace:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

and  with all components  (.*)

alter session set events ‘trace[sql_mon.*] memory=high,get_time=highres’;

sql_mon plus sql_optimizer and sql_id   ( use ‘|’ charachter to select multiple scopes, filter or targets ):

alter system set events ‘trace[sql_mon | sql_optimizer.*] [sql: ,<sql_id>]‘;

Immediate events:

alter session set events ‘immediate eventdump(process)’;

alter session set events ‘immediate eventdump(system)’;

Labeled events :

( on incident with specific error in this case ):

alter session set events ‘<ora_error_number>  incident(myincident)’;

with process id:

alter session set events ’942  {process: <process_id>}  incident(table_missing)’;

Events are big help  for troubleshooting and understanding how Oracle works..