Como capturar SQLs ( DML /DDL ).

Versão para impressãoEnviar para amigoVersão PDF

É possível capturar todos os SELECTs/DELETEs/UPDATEs/INSERTs além dos outros comandos como CREATE/DROP e etc.
Para isso deve-se utilizar o recurso de SQL TRACE do Informix.

Lembrete: Como os comandos do SQL TRACE são executados através das funções task/admin, apenas os usuários informix, DBSA e root (se tiverem permissão de acesso ao sysadmin) poderão executar as funções.

  1. Histórico
  2. Sobre o SQL TRACE
  3. Sintaxe e Exemplos, como definir o TRACE
    1. Sintaxe
    2. Exemplos
  4. Cuidado com a alocação de memória
  5. Consultando histórico
    1. onstat
    2. Tabelas syssqltrace
    3. OAT - OpenAdminTools


Histórico


Na versão 7 não era possível capturar SQLs , para isso era necessário adquirir ferramentas de terceiros ou utilizar o comando set explain explicitamente na aplicação, sendo que este comando não possui este objetivo e além do incomodo de capturar apenas SELECTS e gerar os dados em um único arquivo no servidor.
Na versão 9 e 10 também não era possível capturar, mas para quem utilizava o set explain ficou um pouco mais facil pois passou a ser possível ativa-lo dinamicamente em uma sessão (sem a necessidade de alterar a aplicação) e ele também passou a ser gerado na tabela sysmaster:syssqexplain quando definido a variável SQLSTATS antes de iniciar a sessão do usuário ou o banco de dados (valor 1 = statement trace; valor 2 = +timing e profile), facilitando a captura.
A partir da versão 11.10 finalmente as coisas mudaram e foi incluído um novo recurso no banco chamado SQL TRACE, onde permitiu ativar um rastreamento de todo DML/DDL da instancia ou de uma sessão já existente.
Na versão 11.50 xC3 o recurso foi consideravelmente melhorado permitindo filtrar por banco de dados, nome de usuário e sessão atual. Também passou a ser permitido suspender e reativar a captura.

ATENÇÃO: Este trace também captura DML/DDL executadas em SPL (stored procedure) porém se você precisa debugar e capturar o funcionamento lógico de uma SPL você deverá utilizar os comandos TRACE e SET DEBUG FILE (disponíveis desde a versão 7) , específicos para este fim.


Sobre o SQL TRACE


O SQL TRACE faz mais do que apenas capturar os comandos enviados ao banco de dados, com ele também é capturado algumas estatísticas de execução destes comandos, informações como:
  • Quantas paginas foram lida/gravada
  • Se fez sequencial scan,
  • Quando registros foram lidos
  • Se fez sort em memória ou em disco
  • Quanto tempo levou a execução
  • Se teve algum gargalo no I/O (I/O wait)
A sua configuração padrão é feita no ONCONFIG da instancia , mas pode ser modificada através dos recursos de administração por SQL, no banco de dados sysadmin, comandos task/admin.
A vantagem de utilizar os comandos task/admin é que com ele você faz alterações no seu rastreamento dinamicamente sem a necessidade de reiniciar a instancia.

Para ler os dados restreados você tem dois meios, o comando onstat ou as tabelas syssqltrace*.
Uma das características do SQL TRACE é que os dados ficam armazenados nas tabelas syssqltrace* e estas tabelas são "rotativas", ou seja, você define um tamanho fixo na quantidade de dados que será salvo e quando este limite é atingido os dados mais antigos são automaticamente descartados.


Sintaxe e Exemplos, como definir o TRACE


Sintaxe


Sintaxe do parâmetro no arquivo ONCONFIG
# Para desabilitar o trace, no ONCONFIG basta remover ou comentar 
# o parametro do arquivo
SQLTRACE [level=low|med|high], [ntraces=number of traces],
[ size=size of each trace buffer] ,[mode=global|user]
  level   - Define o nivel do rastreamento:
            low  - Captura o comando, estatísticas e joins explain
            med  - low + nomes de tabelas/banco e SPLs
            high - med + variáveis do host (parâmetros de DML).
            * Para visualizar esta variaveis é possível apenas
              através do comando onstat -g his, e não através das
              tabelas syssqltrace*.
  ntraces - Quantidade de comandos que será armazenado, antes de 
            começar a reutilizar o recurso
  size    - Tamanho máximo em KBytes de cada trace (comando), 
            limite de 100KB
  mode    - Escopo do rastreamento, global para toda a instancia 
            ou apenas para usuários específicos

Sintaxe quando utilizado as funções task/admin (clique no comando):

  • set sql tracing
    Identifica modo de execução do TRACE, inicia/para trace, pausa/retorna.
  • set sql user tracing
    Interrompe/inicia um trace para uma ou mais sessões de usuários
    Funciona em modo GLOBAL e USER.
  • set sql tracing user
    Filtra trace por nome de usuário, adiciona/remove usuário , limpa lista de usuários.
    Funciona apenas no modo USER. No modo GLOBAL não tem efeito.
    Quando informado rastreia apenas os usuários informados.
  • set sql tracing database
    Filtra trace por nome de banco de dados, adiciona/remove banco , limpa lista de banco de dados.
    Funciona em modo GLOBAL e USER.
  • set sql tracing session
    Interrompe/inicia um trace *apenas* para a sessão atual, não importa qual parâmetro de sessão seja informado.
    Funciona em modo GLOBAL e USER.

Exemplos


Exemplos quando utilizado as funções task/admin
# Pegar informações sobre a configuração atual do trace
execute function task('set sql tracing info');
(expression)  SQL Tracing OFF.

# Iniciar trace utilizando as configurações definidas no ONCONFIG ou
# a ultima configuração definida dinâmicamente
execute function task('set sql tracing on');
(expression)  SQL Tracing ON: ntraces=1000, size=2024, level=High, mode=Global.
execute function task('set sql tracing info');
(expression)  SQL Tracing ON: ntraces=1000, size=2024, level=High, mode=Global.

# Desativar todos SQL TRACE
execute function task('set sql tracing off');
(expression)  SQL Tracing OFF.

# Iniciar trace especificando a nova configuração
# Obs.: Para que funcione sem problemas, desative o trace antes.
execute function task('set sql tracing on',1500,60,'low','user');
(expression)  SQL Tracing ON: ntraces=1500, size=61416, level=Low, mode=User

# Lista usuários definidos para realizar trace
execute function task('set sql tracing user list');
(expression)  NO USERS

# Adiciona e remove usuários do filtro
execute function task('set sql tracing user add','cmartins');
(expression)  SQL User Trace added cmartins.
execute function task('set sql tracing user add','informix');
(expression)  SQL User Trace added informix. 
execute function task('set sql tracing user list');
(expression)  cmartins informix
execute function task('set sql tracing user remove','informix');
(expression)  SQL User Trace removed user informix.

# Adiciona e remove banco de dados do filtro
execute function task('set sql tracing database list');
(expression)  SQLTrace is tracing all databases. 
execute function task('set sql tracing database add', 'dbcim');
(expression)  SQL Trace is tracing databases [ dbcim ]
execute function task('set sql tracing database add','teste1');
(expression)  SQL Trace is tracing databases [ dbcim  teste1 ]
execute function task('set sql tracing database remove','dbcim');
(expression)  SQLTrace removed [dbcim] from the database filter list.
execute function task('set sql tracing database list');
(expression)  teste1

# Limpa filtros de banco, usuários e sessões dos usuários
execute function task('set sql tracing database clear');
(expression)  SQL Trace is clearing all traced databases.
execute function task('set sql tracing user clear');
(expression)  SQL Trace is clearing all names.
select task('set sql user tracing clear',sid) from sysmaster:syssessions;
(expression)  SQL user tracing cleared for sid(91).
(expression)  SQL user tracing cleared for sid(86).
(expression)  SQL user tracing cleared for sid(33).
(expression)  SQL user tracing cleared for sid(32).
(expression)  SQL user tracing cleared for sid(30).

# Pausa e retorna trace
execute function task('set sql tracing suspend');
(expression)  SQL Trace is suspended.
execute function task('set sql tracing resume');
(expression)  SQL Trace is resuming trace.


Cuidado com a alocação de memória


ATENÇÃO! Muito cuidado com a definição nos parâmetros ntraces e size pois a alocação de memória do SQLTRACE é feita no momento que ele é ativado, alocando toda a memória de uma única vez.

Veja o exemplo abaixo:

# Logo após iniciar minha instancia, com a configuração de SQLTRACE
# desativada, o banco possui 185 MB alocados e apenas 1 segmento 
# da area virtual
$ onstat -g seg
IBM Informix Dynamic Server Version 11.50.UC5DE -- On-Line -- Up 00:00:13 -- 185420 Kbytes
Segment Summary:
id         key        addr     size       ovhd     class blkused  blkfree
2523136    52604801   44000000 122765312  942444   R     29969    3
2555905    52604802   4b514000 66560000   391112   V     6191     10059
2588674    52604803   4f48e000 544768     4160     M     133      0
Total:     -          -        189870080  -        -     36293    10062
   (* segment locked in memory)

# Deixei a configuração dos segmentos adicionais de memória 
# propositalmente pequena (5 MB) para facilitar a demonstração
$ onstat -c |grep ^SHMADD
SHMADD 5120

# Habilito o SQL TRACE pra salvar 1000 comandos, alocando 95 KBytes 
# para cada um. O que da um total de 95 MB .
$ echo "execute function task('set sql tracing on',1000,95,'high','global');"|  dbaccess -e sysadmin
Database selected.
execute function task('set sql tracing on',1000,95,'high','global');
(expression)  SQL Tracing ON: ntraces=1000, size=98280, level=High, mode=Global
1 row(s) retrieved.
Database closed.

# Ao verificar a utilização de memória total, ela cresceu 60 MB (onde
# reaproveitou 35 MB da memória livre, já alocada anteriormente).
# Nestes 60 MB que cresceu, alocou 12 segmentos de 5 MB.
$ onstat -g seg
IBM Informix Dynamic Server Version 11.50.UC5DE -- On-Line -- Up 00:01:17 -- 246860 Kbytes

Segment Summary:
id         key        addr     size       ovhd     class blkused  blkfree
4227072    52604801   44000000 122765312  942444   R     29969    3
4259841    52604802   4b514000 66560000   391112   V     16045    205
4292610    52604803   4f48e000 544768     4160     M     133      0
4325379    52604804   4f513000 5242880    31760    V     1280     0
4358148    52604805   4fa13000 5242880    31760    V     1280     0
4390917    52604806   4ff13000 5242880    31760    V     1280     0
4423686    52604807   50413000 5242880    31760    V     1280     0
4456455    52604808   50913000 5242880    31760    V     1280     0
4489224    52604809   50e13000 5242880    31760    V     1280     0
4521993    5260480a   51313000 5242880    31760    V     1280     0
4554762    5260480b   51813000 5242880    31760    V     1280     0
4587531    5260480c   51d13000 5242880    31760    V     1280     0
4620300    5260480d   52213000 5242880    31760    V     1280     0
4653069    5260480e   52713000 5242880    31760    V     1280     0
4685838    5260480f   52c13000 5242880    31760    V     248      1032
Total:     -          -        252784640  -        -     60475    1240
   (* segment locked in memory)

# Verificando nos pools de memória, agora temos um com o nome de 
# sqlhistory com pouco mais de 95 MB.
$ onstat -g mem |egrep "totalsize|sqlhistory"
name         class addr     totalsize freesize #allocfrag #freefrag
sqlhistory   V     4cef1028 98312192  2456     1002       2

# Também é possível identificar a quantidade de memória
# alocada através da tabela syssqltrace_info.
$ echo "select memoryused from syssqltrace_info" | dbaccess -e sysmaster
Database selected.
select memoryused from syssqltrace_info
          memoryused
            98309736


Consultando histórico


onstat


O comando onstat -g his irá lhe retornar os ultimos ou todos os comandos salvos no seu histórico.

Para visualizar este comando é preciso ser DBSA ou ter o parâmetro UNSECURE_ONSTAT ativado.

# Sintaxe
 onstat -g his [qtde_traces]

# Exemplo
$ onstat -g his 1
IBM Informix Dynamic Server Version 11.50.UC5DE -- On-Line -- Up 00:58:57 -- 303180 Kbytes

Statement history:
Trace Level                  High
Trace Mode                   User
Number of traces             1500
Current Stmt ID                 7
Trace Buffer size           61416
Duration of buffer             30 Seconds
Trace Flags            0x00007F21
Control Block          0x4ca5a018

Statement # 7:     @ 0x4cee1018

 Database:        dblbac_v03
 Statement text:
  select * from t_orgao

  SELECT using table [ t_orgao ]

 Iterator/Explain
 ================
    ID   Left  Right   Est Cost   Est Rows   Num Rows    Partnum Type
     1      0      0          2          3          3   13631566 Seq Scan

 Statement information:
  Sess_id  User_id  Stmt Type        Finish Time    Run Time   TX Stamp   PDQ
  245      1004     SELECT           17:28:21       0.0019     49faf49    0

 Statement Statistics:
  Page       Buffer     Read       Buffer     Page       Buffer     Write
  Read       Read       % Cache    IDX Read   Write      Write      % Cache
  0          4          100.00     0          0          0          0.00

  Lock       Lock       LK Wait    Log        Num        Disk       Memory
  Requests   Waits      Time (S)   Space      Sorts      Sorts      Sorts
  6          0          0.0000     0.000 B    0          0          0

  Total      Total      Avg        Max        Avg        I/O Wait   Avg Rows
  Executions Time (S)   Time (S)   Time (S)   IO Wait    Time (S)   Per Sec
  1          0.0025     0.0025     0.0019     0.000000   0.000000   1557.7460

  Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL
  Cost       Rows       Rows       Error      Error      Level      Memory
  2          3          3          0          0          CR         8000

Tabelas syssqltrace


Também é possível ler os dados capturados através de SQL nas tabelas syssqltrace, syssqltrace_info e syssqltrace_iter.

select * from syssqltrace_info

flags       32545
ntraces     1500
tracesize   61416
duration    30
sqlseen     7
starttime   1258054029
memoryused  92167736

select * from syssqltrace where sql_id = 7

sql_id            7
sql_address       1290670104
sql_sid           245
sql_uid           1004
sql_stmttype      2
sql_stmtname      SELECT
sql_finishtime    1258054101
sql_begintxtime   77573961
sql_runtime       0,001925818199
sql_pgreads       0
sql_bfreads       4
sql_rdcache       100,0000000000
sql_bfidxreads    0
sql_pgwrites      0
sql_bfwrites      0
sql_wrcache       0,00
sql_lockreq       6
sql_lockwaits     0
sql_lockwttime    0,00
sql_logspace      0
sql_sorttotal     0
sql_sortdisk      0
sql_sortmem       0
sql_executions    1
sql_totaltime     0,002457852558
sql_avgtime       0,002457852558
sql_maxtime       0,001925818199
sql_numiowaits    0
sql_avgiowaits    0,00
sql_totaliowaits  0,00
sql_rowspersec    1557,779442421
sql_estcost       2
sql_estrows       3
sql_actualrows    3
sql_sqlerror      0
sql_isamerror     0
sql_isollevel     2
sql_sqlmemory     8000
sql_numiterators  1
sql_database      dblbac_v03
sql_numtables     8
sql_tablelist     t_orgao
sql_statement     select * from t_orgao
sql_stmtlen       22
sql_stmthash      3652608252
sql_pdq           0
sql_num_hvars     0
sql_dbspartnum    13631584


select * from syssqltrace_iter where sql_id = 7

sql_id           7
sql_address      1290670104
sql_itr_address  1290670276
sql_itr_id       1
sql_itr_left     0
sql_itr_right    0
sql_itr_cost     2
sql_itr_estrows  3
sql_itr_numrows  3
sql_itr_type     1
sql_itr_misc     65672
sql_itr_info     Seq Scan
sql_itr_time     9,65647112e-05
sql_itr_partnum  13631566

OAT - OpenAdminTools

Nesta ferramenta você também poderá consultar os dados capturado, uma vez que ela utiliza as tabelas para ler os dados. Por ser um ambiente gráfico WEB você terá uma tela mais amigável.

5
Média: 5 (2 votos)
Sua avaliação: Nenhum

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.