Total Pageviews

Tuesday 9 July 2013

SQL tracing and Tkprof


If the value of the timed_statistics parameter is false, you set it to true with the following statement.
SQL> alter system set timed_statistics=true scope=both;

You can also set this parameter at the session level with the following statement:
SQL> alter session set timed_statistics=true
To find all trace files for the current instance, issue the following query:

SQL> select value from v$diag_info where name = 'Diag Trace';
VALUE
--------------------------------------------------------------------------------
e:\app\sumit.tyagi\diag\rdbms\sumit\sumit\trace
In Oracle Database 11g, the default value of the max_dump_file_size parameter is unlimited, as you
can verify by issuing the following command:

SQL> show parameter max_dump_file_size
NAME                  VALUE
------------------------------------ ----------- ----------
max_dump_file_size  unlimited

An unlimited dump file size means that the file can grow as large as the operating system permits
Tracing a Specific SQL Statement:
Switch on the SQl_tracing:
SQL> alter session set events 'sql_trace level 12';   
Execute  your query:
SQL> select count(*) from sales;
Switch of the tracing:
SQL> alter session set events 'sql_trace off';
You can trace a specific SQL statement running in a different session by issuing an alter system set events statement:

If you have the SQL_ID of the session to trace:

SQL> alter system set events 'sql_trace[sql:fb2yu0p1kgvhr] level 12';
SQL> alter system set events 'sql_trace[sql:fb2yu0p1kgvhr] off';
OR
If you have the sid and Serial# then use the below:
execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,
waits=>true,binds=>false);

Enabling Tracing in Your Own Session
SQL>execute dbms_session.session_trace_enable(waits=>true, binds=> false);

To disable tracing, the user must execute the session_trace_disable procedure, as shown here:

SQL> execute dbms_session.session_trace_disable();
Issue the following statement to set an identifier for your trace files, before you start generating the trace:
SQL> Alter session set tracefile_identifier='MyTraceFile';    
     
Queries which can help to find the correct session for tracing:

select a.sid, a.serial#, b.spid, b.pid, a.username, a.osuser, a.machine
from v$session a, v$process b where a.username IS NOT NULL
and a.paddr=b.addr;
select sid, serial#, username from v$session where status=’ACTIVE’;

Once you get the SID and SERIAL# from the previous query, invoke the session_trace_enable
procedure of the DBMS_MONITOR package, as shown here:

SQL> execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,
waits=>true,binds=>false);
PL/SQL procedure successfully completed.




1 comment:

  1. Jadwal Arena Sabung Ayam SV388 28 Februari 2019 - Kamis, Maluku 28 Februari 2019 – Pada Hari Tersebut Akan Di Laksanakan Berbagai Pertandingan Sabung Ayam Secara Live di Arena Sabung Ayam Thailand.

    Situs Judi Sabung Ayam Online SV388 Merupakan Situs Judi Asal Thailand Yang Sangat Terkenal Dengan Permainan Sabung Ayam Yang Fair dan Menghibur Para Penonton Judi Sabung Ayam.

    Untuk Info Lebih Lanjut Bisa Hub kami Di :
    wechat : bolavita
    line : cs_bolavita
    whatsapp : +628122222995
    BBM: BOLAVITA

    ReplyDelete