tkprof - SQLTrace

[thiago.sousa]

Guys,
Someone know how to explain how I do a trace in Oracle?
and how to format this using TKProf.

My greatest difficulty is to use TKProf, I can not use it.
Someone could help me?

[gilbertoca]

Thiago, this week I'm doing an Oracle course (database tuning), which speaks exactly the correct way to activate and manage this type of activity in the bank.
If you do not have Avechado, on Friday when you finish the course, I'll give you a summary of how to perform a trace in the bank.

Gilberto

[ualex]

I also have a lot of interest in knowing this tool ....

[gilbertoca]

Well, it's not quite what I'd like to describe! But as my service is pulled, I think this link http://www.oracle-base.com/articles/8i/ ... eTrace.php summarizes what can be done.

I hope it helps!
Gilberto

[dr_gori]

That's it guys!
TKProf is nothing more than a utility that converts Trace binary files to "understandable text" by us.

What are traces?
is a log of everything that happens in the bank. Each DML command executed, times, statistics, etc. This file is recorded on the Oracle server. To know where it writes, turn this SQL:

Select all

SQL> select * 
  2  from v$parameter 
  3  where name like 'user_dump_dest' 
  4  / 
 
       NUM NAME              TYPE VALUE                             
---------- ----------------- ---------- ------------------------------- 
       750 user_dump_dest             2 /app/oracle/admin/tsas/udump      
 
1 row selected. 
SQL>

Oracle generates a sequential file for the trace files. Maybe you have to know the name of the file by the creation date. Of course, you must have to read in this directory to access it.

With this file, you run the TKProf, so:

Select all

TKPROF <arquivo_origem.trc>  <seu_arq_de_saida.txt> 

But how am I active Trace?
There are several forms!
In SQL * Plus you can rotate the following command:

Select all

ALTER SESSION SET SQL_TRACE = TRUE; 
ALTER SESSION SET SQL_TRACE = FALSE;

How to activate Trace of another session?

Select all

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( <o_sid> ,  <o_serial#> ,true); 
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( <o_sid> ,  <o_serial#> ,false);

How to activate Trace in Reports? http://en.glufke.net/oracle/viewtopic.php?t=45
How to activate Trace Level 12? (which is the most detailed of all traces)

Select all

EXEC SYS.DBMS_SYSTEM.SET_EV( <sid>, <serial#>,10046,12, '');

's

[thiago.sousa]

Thank you guys .... Great.

[jlaudirt]

I need to know which syntax for me to run a trace per machine. I want to see if anyone is using SQL out of the program

[dr_gori]

In this case, I think the best way is to create a trigger at the bank level - after logon.

Hence you log in a table the session information (collected in the V $ session).