Habilitando um trace para um SQL ID específico

Algumas vezes, quando precisamos realizar tuning de um SQL específico, ao invés de habilitar o trace para toda sessão, ou módulo, ou serviço, etc, podemos utilizar o seguinte comando:

alter system set events 'sql_trace [sql:|] … rest of event specification';

O SELECT abaixo será usado como exemplo:

select * from hr.employees where employee_id=100;

Buscando o SQL ID do SELECT executado:

select sql_id, sql_text from v$sql where upper(sql_text) like '%FROM HR.EMPLOYEES%';

SQL_ID SQL_TEXT
-------------------- --------------------------------------------------
31d96zzzpcys9 select * from hr.employees where employee_id=100

Habilitando o trace para o SQL ID (evento 10046):

alter system set events 'sql_trace[SQL:31d96zzzpcys9] plan_stat=all_executions,wait=true,bind=true';

Após habilitar o trace, basta executar novamente o SELECT.

Com isso será gerado o seguinte arquivo:

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32081.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_2
System name: Linux
Node name: oracle01
Release: 3.8.13-16.2.1.el6uek.x86_64
Version: #1 SMP Thu Nov 7 17:01:44 PST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 32081, image: oracle@oracle01


*** 2015-06-02 19:30:25.636
*** SESSION ID:(20.10934) 2015-06-02 19:30:25.636
*** CLIENT ID:() 2015-06-02 19:30:25.636
*** SERVICE NAME:(SYS$USERS) 2015-06-02 19:30:25.636
*** MODULE NAME:(SQL Developer) 2015-06-02 19:30:25.636
*** CLIENT DRIVER:(jdbcthin) 2015-06-02 19:30:25.636
*** ACTION NAME:() 2015-06-02 19:30:25.636

=====================
PARSING IN CURSOR #139909740052304 len=48 dep=0 uid=0 oct=3 lid=0 tim=150929961385 hv=4283857673 ad='e7b85120' sqlid='31d96zzzpcys9'
select * from hr.employees where employee_id=100
END OF STMT
EXEC #139909740052304:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1833546154,tim=150929961383
WAIT #139909740052304: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=95264 tim=150929961433
FETCH #139909740052304:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1833546154,tim=150929961499
STAT #139909740052304 id=1 cnt=1 pid=0 pos=1 obj=92593 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=0 pw=0 time=34 us cost=0 size=69 card=1)'
STAT #139909740052304 id=2 cnt=1 pid=1 pos=1 obj=92705 op='INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=16 us cost=0 size=0 card=1)'

Desabilitando o trace:

alter system set events 'sql_trace[SQL:31d96zzzpcys9] off';

Também é possível gerar um trace do tipo 10053 (optimizer trace):

alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:31d96zzzpcys9]';

Desabilitando o trace:

alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:31d96zzzpcys9] off';

Referências:

            https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a

Abraço,

Alex Zaballa