Como capturar SQLs ( DML /DDL ).
É 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.
- Histórico
- Sobre o SQL TRACE
- Sintaxe e Exemplos, como definir o TRACE
- Cuidado com a alocação de memória
- Consultando histórico
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 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.
- 6000 leituras
Tags:
Comentar