How To Enable SQL Tracing For Discoverer Sessions

How To Enable SQL Tracing For Discoverer Sessions

PURPOSE
=======
This bulletin describes how enable trace and level 4 trace for
Discoverer User Edtion.

SCOPE & APPLICATION
===================
This bulletin could be useful for consultants and support analysts
investigating Discoverer issues.

ENABLE TRACE AT LEVEL 1
=======================
You could be interested in capturing SQL generated by Discoverer User Edition
in order to run the same statements from SQL PLUS.
Remind Discoverer User Editon opens two sessions on the database and you need
system privileges, i.e. log in as SYS.
First you get SID, SERIAL#, PADDR from V$SESSION, for example:

SQL> select username, sid, serial#, paddr from v$session
where username=’VIDEO31′;

USERNAME SID SERIAL# PADDR
———————————————————————
VIDEO31 14 13202 820532C8
VIDEO31 15 4665 82053EC8

Once you know the SID and the SERIAL#, you can enable the trace for each session
running the command:

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(, , TRUE)

Everything the user does will now be traced at trace level 1 until you execute
the SQL command again, but replacing ‘TRUE’ with ‘FALSE’.

Usually you find the trace files into /admin//udump. Anyway
you can know the destination from svrmgrl executing the following command:
show parameter user_dump_dest

It’s not easy read SQL from the trace files, you can get only the SQL statements
using TKPROF (Note 41634.1). For example:
tkprof tracefile.trc t1 RECORD=sqlfile.sql

ENABLE TRACE AT LEVEL 4
=======================
Sometimes the trace at level 1 isn’t enough, because in the sql statements there
are some bind variables. You need their values before you run the query into SQL
PLUS. In this case you have to perform trace at level 4, so that you have the
value of each bind variable in the .trc file.

Important: At the moment of writing this Note, there is a bug in ORADEBUG on
Windows NT. ORADEBUG just hangs, eating all CPU. (Bug 771064).

Enabling the level 4 trace for a Discoverer user, first get SID, SERIAL#, PADDR
from V$SESSION. Remind Discoverer User Editon opens two sessions on the database
and you need system privileges. For example:

SQL> select username, sid, serial#, paddr from v$session
where username=’VIDEO31′;

USERNAME SID SERIAL# PADDR
———————————————————————
VIDEO31 14 13202 820532C8
VIDEO31 15 4665 82053EC8

Then you get SPID from the following query:

SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = ;

For example:

SQL> SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = ‘820532C8’;

ADDR PID SPID
—————————————–
820532C8 9 5408

SQL> SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = ‘82053EC8’;

ADDR PID SPID
——————————————
82053EC8 13 5410

You can then enable the level 4 trace:

SVRMGRL
CONNECT INTERNAL
ORADEBUG SETOSPID
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4

This should now trace at level 4 everything the Discoverer user does.

Turn off the SQL trace for the session.

ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF

For example:
bash-2.02$ ORACLE_SID=owbdw; export ORACLE_SID;
bash-2.02$ svrmgrl
Oracle Server Manager Release 3.1.6.0.0 – Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.6.2.0 – Production
With the Partitioning option
JServer Release 8.1.6.2.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> ORADEBUG SETOSPID 5408
Oracle pid: 9, Unix process pid: 5408, image: oracle@misun08 (TNS V1-V3)
SVRMGR> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.
SVRMGR> ORADEBUG SETOSPID 5410
Oracle pid: 13, Unix process pid: 5410, image: oracle@misun08 (TNS V1-V3)
SVRMGR> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.
SVRMGR> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
Statement processed.

Once you have trace files you find the bind variables values in these files and
you can also use tkprof to get only the SQL statements.

PERFORMANCE EVALUATION
======================
If you are investigating Discoverer performance issues, remember to set
TIMED_STATISTICS parameter to TRUE when tracing sessions, otherwise no time
based comparisons can be made.
After you have enabled the trace for the sessions, you execute the following
command from svrmgrl:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Tkprof is again useful to get the explain plans of the queries from the trace
files (see Note 41634.1). For example:
TKPROF dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

TKPROF formats the trace file named “dsdb2_ora_18468.trc” and writes it to a
formatted output file named “dsdb2_trace.out”.
‘EXPLAIN’ determines the execution plan for each SQL statement in the trace
file and writes these execution plans to the output file. The specified user
must have CREATE SESSION privilege.
‘SYS’ enables and disables the listing of SQL statements issued by the user SYS,
or recursive SQL statements into the output file. The default value of YES
causes TKPROF to list these statements. The value of NO causes TKPROF to omit
them.
Note that if the command is likely to be longer than a single line on your
terminal screen you may have to use continuation characters.

Once you have the explain plan you can interpret. Note 46234.1 will help you.

RELATED DOCUMENTS
=================
Note 41634.1 TKPROF and Problem Solving
Note 32951.1 Tkprof Interpretation
Note 46234.1 Interpreting Explain plan

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em TRACE, VARIAVEL BIND e marcado , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s