Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


quarta-feira, 1 de outubro de 2008

Rastreando operações DML executadas nas tabelas de dicionário de dados do Oracle ...

Por Eduardo Legatti

Olá,

Quem já usou o SQL trace sabe que o mesmo pode ser invocado no nível de instância ou de sessão, e que após a criação do arquivo de rastreamento no diretório especificado pelo parâmetro USER_DUMP_DEST, o mesmo pode ser formatado utilizando o TKPROF de forma a torná-lo mais legível para interpretar resultados como contadores de parse, fetch, execute, tempos de CPU e tempo gasto com as instruções, informações de leituras físicas e lógicas, etc...
SQL> show parameter user_dump_dest

NAME                TYPE        VALUE
------------------- ----------- -------------------------------------
user_dump_dest      string      C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP

Os nomes dos arquivos de rastreamento seguem a nomenclatura abaixo:

[SID]_ora_[SPID].trc

onde:
  • SID é o identificador do banco de dados no sistema, normalmente identificada pela variável $ORACLE_SID
  • SPID é o ID de processo do sistema operacional referente ao processo servidor, normalmente identificado pela coluna SPID da view V$PROCESS
Para uma melhor identificação do arquivo de rastreamento gerado, é possível adicionar uma TAG (etiqueta) no nome do arquivo de trace a ser gerado com o comando abaixo:

SQL> alter session set tracefile_identifier = 'nome_da_TAG';

Ao executar o comando acima, o nome da TAG será adicionado ao nome do arquivo de rastreamento como demonstrado abaixo:

[SID]_ora_[SPID]_[nome_da_TAG].trc

O método mais comum para ativar o rastreamento é definir o parâmetro SQL_TRACE no nível da instância. No entanto, esse método impõe um custo significativo em termos de desempenho, pois todas as sessões da instância serão rastreadas. Já o rastreamento no nível da sessão resulta em custo menor para o desempenho geral, pois permite o rastreamento de sessões específicas. Vale a pena salientar que a partir do Oracle 10g rel2, o parâmetro SQL_TRACE foi sinalizado como "deprecated" e mantido apenas por questões de retro-compatibilidade. Isso significa que o mesmo será considerado como obsoleto em futuras versões do Oracle.

SQL> select name,value,isdeprecated from v$parameter where name='sql_trace';

NAME         VALUE    ISDEP
------------ -------- -----
sql_trace    FALSE    TRUE

Bem, se o mesmo foi considerado como descontinuado, mesmo assim, acredito que este parâmetro será utilizado ainda por muito tempo pelo fato de o mesmo ser amplamente divulgado e de fácil utilização. De qualquer forma, ao invés de usá-lo, a Oracle recomenda utilizar os pacotes DBMS_MONITOR e DBMS_SESSION. Bem, dentre alguns dos métodos existentes para ativar o SQL Trace posso citar os alguns comandos abaixo:
  • ALTER SYSTEM SET SQL_TRACE = {True|False} - no nível de instância
  • ALTER SESSION SET SQL_TRACE = {True|False} - no nível de sessão
  • EXECUTE DBMS_SESSION.SET_SQL_TRACE {True|False} - no nível de sessão
  • EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE (sid) - no nível de sessão
  • EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(sid,serial#,waits,binds) - para ativar o rastreamento na sessão atual ou em uma sessão que não a atual, possibilitando que informações de espera e valores bind estejam presentes no arquivo de trace
  • EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid,serial#,{True|False}) - para ativar o rastreamento em uma sessão que não a atual
As informações relacionadas a todas as sessões Oracle estão contidas na view dinâmica de desempenho V$SESSION. Sabemos que uma sessão do Oracle é identificada exclusivamente pelos valores contidos nas colunas SID e SERIAL#. Assim sendo, a primeira etapa para rastrear uma outra sessão que não a atual é determinar esses valores.

Neste artigo irei apenas demonstrar, para quem tiver curiosidade ou para quem for aficionado em saber o que o Oracle realiza "por baixo dos panos" nas suas tabelas de dicionário de dados, quando submetemos um comando DDL (CREATE TABLE ..., DROP TABLE ...).

De acordo com os métodos mostrados acima, utilizarei o procedimento SET_SQL_TRACE_IN_SESSION do pacote DBMS_SYSTEM fornecido pela Oracle desde a versão 8.x para ativar o controle em uma sessão. Como curiosidade, o pacote DBMS_SYSTEM fornece diversos procedimentos utilitários que são de uso interno do pessoal de suporte da Oracle. Até então, a Oracle recomenda que os clientes usem apenas o procedimento SET_SQL_TRACE_IN_SESSION, que talvez seja o programa mais útil do pacote DBMS_SYSTEM.

Como primeira demonstração, irei simular uma conexão com o usuário SCOTT criado previamente utilizando a ferramenta Oracle SQL Developer. O objetivo é saber quais tabelas ou views do dicionário de dados o Oracle acessou para obter informações de objetos enquanto eu navegava nos objetos. Para não ficar muito extenso, irei apenas clicar em uma trigger para exibir o seu código PL/SQL. Supondo que eu já esteja conectado no Oracle SQL Developer antes de clicar na tree view Triggers, irei ativar o rastreamento da sessão como demonstrado abaixo:

-- conectando com SYS
C:\>sqlplus sys/manager as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Qua Out 1 07:56:13 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

-- Obtendo as informações necessárias para ativar o rastreamento na sessão SCOTT
SYS> select sid,serial# from v$session where username='SCOTT';

       SID    SERIAL#
---------- ----------
        30         56

-- Ativando o rastreamento na sessão SCOTT
SYS> exec dbms_system.set_sql_trace_in_session (30,56,true);

Procedimento PL/SQL concluído com sucesso.

Bem, agora irei clicar na tree view Triggers como mostrado na figura abaixo:



Após clicar na trigger de nome TRG_TEST, podemos ver abaixo que um arquivo de rastreamento xe_ora_3140.trc foi criado no diretório definido pelo parâmetro USER_DUMP_DEST. Podemos verificar neste arquivo o que foi feito pelo Oracle para que as informações da trigger TRG_TEST fossem retornadas para o Oracle SQL Developer.

-- Instrução SQL usada pelo Oracle para obter o nome da trigger
Dump file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3140.trc
PARSING IN CURSOR #1 len=414 dep=0 uid=45 oct=3 lid=45 tim=28678585022 hv=2027071221
 SELECT OBJECT_NAME, OBJECT_ID,
        DECODE(STATUS, 'INVALID', 'TRUE', 'FALSE') INVALID, 
        'FALSE' runnable
 FROM SYS.ALL_OBJECTS
 WHERE OWNER = :SCHEMA
 AND OBJECT_TYPE = 'TRIGGER'
 AND SUBOBJECT_NAME IS NULL
 AND OBJECT_ID NOT IN ( SELECT PURGE_OBJECT FROM RECYCLEBIN )
END OF STMT

Substituindo o valor da variável bind, poderemos executar esta mesma instrução no SQL*Plus para obtermos o nome da trigger

SYS> SELECT OBJECT_NAME, OBJECT_ID,
  2         DECODE(STATUS, 'INVALID', 'TRUE', 'FALSE') INVALID,
  3         'FALSE' runnable
  4  FROM SYS.ALL_OBJECTS
  5  WHERE OWNER = 'SCOTT'
  6  AND OBJECT_TYPE = 'TRIGGER'
  7  AND   SUBOBJECT_NAME IS NULL
  8  AND   OBJECT_ID NOT IN ( SELECT PURGE_OBJECT FROM RECYCLEBIN );

OBJECT_NAME                     OBJECT_ID INVAL RUNNA
------------------------------ ---------- ----- -----
TRG_TEST                            16649 FALSE FALSE


-- SQL usado pelo Oracle para obter o código fonte da trigger
Dump file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3140.trc
PARSING IN CURSOR #2 len=163 dep=0 uid=45 oct=3 lid=45 tim=28688964900 hv=2653203605
 select 'create or replace ' source from dual 
 union all 
 select text from sys.dba_source 
 where owner = :OBJECT_OWNER and
       name  = :OBJECT_NAME and 
       type  = :OBJECT_TYPE 
END OF STMT

Novamente substituindo os valores das variáveis bind, podemos executar a mesma instrução no SQL*Plus para obter o código PL/SQL da trigger TRG_TEST

SYS> select 'create or replace ' source from dual
  2  union all
  3  select text from sys.dba_source
  4  where owner = 'SCOTT' and
  5  name = 'TRG_TEST' and
  6  type = 'TRIGGER';

SOURCE
-----------------------------------------------------------
 create or replace
 trigger trg_test
   before insert or update on x
   for each row
   begin
     :new.data := sysdate;
   end;

7 linhas selecionadas.

-- Desativando o rastreamento para a sessão SCOTT
SYS> exec dbms_system.set_sql_trace_in_session (30,56,false);

Procedimento PL/SQL concluído com sucesso.

Para realizar uma segunda demonstração, irei abrir uma outra sessão com o usuário SCOTT de forma a criar uma nova tabela em seu schema. Então quando eu estiver conectado, irei de uma outra sessão (SYS) ativar o rastreamento da sessão SCOTT antes de realmente submeter o comando CREATE TABLE.

-- Conectando com o usuário SCOTT
C:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.2.0 - Production on Qua Out 1 08:08:17 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SCOTT>

-- Em outra sessão, obterei as informações necessárias para ativar o rastreamento
SYS> select sid,serial# from v$session where username='SCOTT';

       SID    SERIAL#
---------- ----------
        30         58

-- Ativando o rastreamento na sessão SCOTT
SYS> exec dbms_system.set_sql_trace_in_session (30,58,true);

Procedimento PL/SQL concluído com sucesso.

Agora que o rastreamento na sessão SCOTT foi ativado, irei criar uma tabela para então verificar no arquivo de rastreamento as operações que o Oracle realmente realizou.
-- Criando a tabela EMP
SCOTT> create table emp (id number);

Tabela criada.

Depois de realizados os procedimentos acima, exibirei abaixo algumas partes do arquivo de rastreamento xe_ora_1352.trc que foi criado no diretório definido pelo parâmetro USER_DUMP_DEST. Dentre as várias operações DML feitas no dicionário de dados, irei destacar algumas mais interessantes que o Oracle realizou:

-- Registrou o comando de criação da tabela EMP
PARSING IN CURSOR #2 len=28 dep=0 uid=45 oct=1 lid=45 tim=29363679158 hv=2182841362
 create table emp (id number)
END OF STMT

-- Realizou verificações na tabela de dicionário obj$, possivelmente para verificar
-- se o objeto criado já existe
PARSING IN CURSOR #1 len=198 dep=1 uid=0 oct=3 lid=0 tim=29363685248 hv=4125641360
 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
        spare1,spare2 from obj$ 
        where owner#=:1 and 
              name=:2 and 
              namespace=:3 and 
              remoteowner is null and 
              linkname is null and 
              subname is null
END OF STMT

-- Inseriu registro na tabela de dicionário obj$ de forma a registrar o novo
-- objeto EMP do tipo TABLE
PARSING IN CURSOR #1 len=205 dep=1 uid=0 oct=2 lid=0 tim=29363720156 hv=3861105107
 insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
                  remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,
                  spare2)
        values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
END OF STMT

-- Realizou verificações na tabela de dicionário seg$
PARSING IN CURSOR #1 len=206 dep=1 uid=0 oct=3 lid=0 tim=29363745623 hv=2888850659
 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
        NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
        NVL(scanhint,0) 
        from seg$ 
        where ts#=:1 and 
              file#=:2 and block#=:3
END OF STMT

-- Inseriu registro na tabela de dicionário seg$ para armazenar informações
-- do novo segmento EMP
PARSING IN CURSOR #1 len=256 dep=1 uid=0 oct=2 lid=0 tim=29363763315 hv=2325978899
 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
                   extsize,extpct,user#,iniexts,lists,groups,cachehint,
                   bitmapranges,hwmincr, spare1, scanhint) 
        values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,
                :16,DECODE(:17,0,NULL,:17),:18)
END OF STMT

-- Inseriu registro na tabela de dicionário tab$ para armazenar informações
-- da tabela EMP
PARSING IN CURSOR #3 len=494 dep=1 uid=0 oct=2 lid=0 tim=29363775415 hv=2283232350
 insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,
                  clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,
                  rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,
                  samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,
                  flbcnt,trigflag,spare1,spare6)
        values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,
               decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
               :20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),
               decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
END OF STMT

-- Inseriu registro na tabela de dicionário col$ possivelmente para armazenar
-- informações da coluna EMP.ID
PARSING IN CURSOR #3 len=453 dep=1 uid=0 oct=2 lid=0 tim=29363788520 hv=224718466
 insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,
                  scale,null$,offset,fixedstorage,segcollength,deflength,
                  default$,col#,property,charsetid,charsetform,spare1,
                  spare2,spare3)
        values(:1,:2,:3,:4,:5,:6,
               decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),
               decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,
               :8,181,:8,182,:8,183,:8,231,:8,null),
               :9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
END OF STMT

-- Realizou alguma alteração na tabela de dicionário seg$
PARSING IN CURSOR #1 len=296 dep=1 uid=0 oct=6 lid=0 tim=29363820404 hv=2379717279
 update seg$ 
  set 
    type#=:4,
    blocks=:5,
    extents=:6,
    minexts=:7,
    maxexts=:8,
    extsize=:9,
    extpct=:10,
    user#=:11,
    iniexts=:12,
    lists=decode(:13, 65535, NULL, :13),
    groups=decode(:14, 65535, NULL, :14),
    cachehint=:15,
    hwmincr=:16,
    spare1=DECODE(:17,0,NULL,:17),
    scanhint=:18 
    where ts#=:1 and 
          file#=:2 and 
          block#=:3
END OF STMT

Após a finalização do comando CREATE TABLE realizado na sessão SCOTT, poderei desativar o rastreamento na mesma com o comando abaixo:

SYS> exec dbms_system.set_sql_trace_in_session (30,58,false);

Procedimento PL/SQL concluído com sucesso.

Além de eu ter demonstrado o que o Oracle realizou em suas tabelas de dicionário de dados, foi demonstrado também como ativar o rastreamento em uma outra sessão utilizando o pacote DBMS_SYSTEM. Abaixo, irei demonstrar o rastreamento da sessão atual utilizando o comando ALTER SESSION SET SQL_TRACE=TRUE para verificar o que o Oracle realiza quando dropamos uma tabela de um schema.

-- Irei conectar utilizando o usuário SCOTT
C:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.2.0 - Production on Qua Out 1 08:42:41 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

-- Para fins de demonstração irei nomear o arquivo de trace a ser gerado
SCOTT> alter session set tracefile_identifier ="meutrace";

Sessão alterada.

-- Irei ativar o rastreamento da sessão
SCOTT> alter session set sql_trace=true;

Sessão alterada.

-- Irei dropar a tabela EMP
SCOTT> drop table emp;

Tabela eliminada.

-- Irei desativar o rastreamento da sessão
SCOTT> alter session set sql_trace=false;

Após realizado os procedimentos acima, um arquivo de rastreamento xe_ora_meutrace.trc foi criado no diretório definido pelo parâmetro USER_DUMP_DEST. Dentre as várias operações DML feitas no dicionário de dados, irei destacar as 3 mais interessantes que o Oracle realizou:

-- Pelo fato de eu ter ter incluído a cláusula PURGE ao dropar a tabela, e pelo
-- fato de a lixeira estar ativa, o Oracle inseriu um registro na tabela de
-- dicionário RecycleBin$
PARSING IN CURSOR #2 len=244 dep=1 uid=0 oct=2 lid=0 tim=34496913737 hv=1380832165
 insert into RecycleBin$ (obj#, owner#, original_name, operation, type#, 
                          droptime, dropscn, flags, related, bo, purgeobj, con#)
        values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)
END OF STMT

-- Deletou o registro referenre ao obeto EMP na tabela de dicionário obj$
PARSING IN CURSOR #2 len=32 dep=2 uid=0 oct=7 lid=0 tim=34496911297 hv=933734747
 delete from obj$ where obj# = :1
END OF STMT

-- Atualizou a tabela de dicionário seg$ possivelmente para indicar que o segmento
-- EMP está retido na lixeira
PARSING IN CURSOR #4 len=296 dep=1 uid=0 oct=6 lid=0 tim=34496918731 hv=2379717279
 update seg$ 
  set 
    type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,
    iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14),
    cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 
    where ts#=:1 and 
          file#=:2 and 
          block#=:3
END OF STMT

Caso eu tivesse utilizado a cláusula PURGE ao dropar a tabela EMP, o Oracle realizaria as operações abaixo como demonstradas em outro arquivo de rastreamento gerado posteriormente:

-- Deleção de registro na tabela de dicionário col$ referente à coluna EMP.ID
PARSING IN CURSOR #10 len=30 dep=1 uid=0 oct=7 lid=0 tim=6065952372 hv=1961033163
delete from col$ where obj#=:1
END OF STMT

-- Deleção de registro na tabela de dicionário tab$ referente à tabela EMP
PARSING IN CURSOR #10 len=30 dep=1 uid=0 oct=7 lid=0 tim=6066083907 hv=104333213
delete from tab$ where obj#=:1
END OF STMT

-- Deleção de registro na tabela de dicionário obj$ referente ao objeto EMP
PARSING IN CURSOR #10 len=32 dep=1 uid=0 oct=7 lid=0 tim=6066201319 hv=933734747
delete from obj$ where obj# = :1
END OF STMT

-- Deleção de registro na tabela de dicionário seg$ referente ao segmento EMP
PARSING IN CURSOR #8 len=56 dep=1 uid=0 oct=7 lid=0 tim=6066243097 hv=1954230687
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
END OF STMT



Postado por Eduardo Legatti às 09:20

Marcadores: ,

Posts Relacionados

2 comentários:

Anônimo disse...

Sensacional Eduardo, esse artigo é pra lá de interessante, sem falar em toda a didática e elucidação do assunto. Parabéns meu velho.Abraço.

David DBA

27 de março de 2009 às 12:18
Anônimo disse...

Seu blog é nota 10!

15 de junho de 2016 às 15:17

Postar um comentário

Assinar: Postar comentários (Atom)

Postagens populares