Este é um pequeno artigo sobre a opção RECORD do TKPROF, que é extremamente útil para capturar e armazenar declarações não recursivos de SQL em ordem de execução do arquivo de trace.

Sintaxe do TKPROF:

Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

Agora vou mostrar um processo que está sendo rastreado e iremos acompanhar o processo do tkprof em seguida:

SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(1) from t;

  COUNT(1)
----------
     17586

SQL> select count(1) from t where object_name = 'TABLE';

  COUNT(1)
----------
         1

SQL> select count(1) from t where object_TYPE ='TABLE';

  COUNT(1)
----------
      1915

SQL> DROP TABLE T PURGE;

Table dropped.

Finalizado a minha sessão o trace foi encerrado, posso rodar o TKPROF utilizando a opção RECORD:

tkprof record=meussqls.txt output=meutrace.txt trace=rsrac1_ora_18906_rafa.trc

TKPROF: Release 11.2.0.3.0 - Development on Thu Jan 10 17:35:09 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Agora vendo o arquivo meussqls.txt, deverá conter a sequencia acima dos comandos efetuados:

[oracle@rsrac11g1 trace]$ cat meussqls.txt
drop table t purge ;
create table t as select * from dba_objects ;
select count(1) from t ;
select count(1) from t where object_name = 'TABLE' ;
select count(1) from t where object_TYPE ='TABLE' ;
DROP TABLE T PURGE ;
[oracle@rsrac11g1 trace]$

Essa opção RECODR, está disponivel desde a versão Oracle 7.

Fonte:
Oracle 11g: http://download.oracle…/sqltrace.htm#PFGRF94987
Oracle 10g: http://download.oracle…/sqltrace.htm#sthref1517
Oracle 9i: http://download.oracle…/sqltrace.htm#1256
Oracle 8i: http://download.oracle…/ch14_str.htm#1256
Oracle 7: http://download.oracle…/stracea.gif

Autor: Rafael Stoever

Bacharel em Sistema de Informação pela Uniasselvi, atualmente cursando Gerenciamento de Projetos em TI pela Pós Graduação Uniasselvi. Atuo como Analista de suporte a banco de dados – DBA pela Lumina Serviços em TI residente de Blumenau/ SC, OPN Certified Specialist, Certificado OCP 10g/11g/12c, OCE RAC10g e Linux 10g. Conhecimentos em Microsoft SqlSever, Mysql e programação web (php,asp).

• 
• 
• 
• 
•