Miladin Modrakovic's Blog: Oraclue

 

 

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.