.comment-link {margin-left:.6em;}

Tuesday, May 31, 2005

Canivete Suiço (SQL Scritps) - Parte I

Vou compartilhar uma série de scripts que uso no dia-a-dia. Qualquer pessoa pode copiar e usá-los a vontade, a maioria deles vem da AskTom ou inspirada em sites da internet e necessidade que foram aparecendo ao longo da jornada.

Para quem me conhece, não é segredo - eu simplesmente abomino qualquer tipo de ferramenta gráfica, seja ela: Toad, tORA, SQLPLUSW, OEM, etc. Eu uso sqlplus, aquele em modo caracter mesmo. Isso! Bem feio e tem que digitar muito, porém em qualquer lugar que vou, eu o econtro lá. Não fico gerenciando tnsnames, pacotes de instalação, nada. Para mim é telnet (ou algum outro bom emulador) e o nosso amigo sqlplus.

Para configurá-lo eu uso os scripts login.sql e connect.sql e a variável de ambiente $SQLPATH que é o PATH para o sqlplus. Defina esta variável de ambiente para um diretório qualquer, onde estarão seus scripts. No meu caso, eu uso o ~/sqlplus_admin e no .profile eu já exporto a variável.

[mportes@lath09 /home01/mportes]$ env | grep SQLPATH
SQLPATH=/home01/mportes/sqlplus_admin

[mportes@lath09 /home01/mportes]$ ll $SQLPATH/login.sql $SQLPATH/connect.sql
-rw-rw-r-- 1 mportes dba 80 Aug 4 2004 /home01/mportes/sqlplus_admin/connect.sql
-rw-rw-r-- 1 mportes dba 1473 Jan 5 07:29 /home01/mportes/sqlplus_admin/login.sql
[mportes@lath09 /home01/mportes]$


Agora, basta conectar-me no Oracle e terei meu usuário e a instância onde estou conectado no prompt. Como? É que toda a vez que o sqlplus é invocado, ele vai até seu path e executa o login.sql, isso acontece somente na entrada (até a versão 9i) depois, para cada connect dentro do sqlplus, ao invés de usar o connect eu uso @connect (script) apenas para ele executar o login.sql novamente e atualizar o usuário@instância.

Outro script de uso diário é dbls.sql para listar meus objetos. A saída para este comando fica assim:

ops$mportes@FCCUAT9I> @dbls %

OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
FUNCTION GET_SEARCH_CONDITION
RLS_EXAMP

INDEX PK_DEPT TOOLS
PK_EMP TOOLS
T_PK TOOLS
XDUAL_PK TOOLS

PROCEDURE GEN_DATA
P
PRINT_TABLE
RTS_SECURITY

SEQUENCE S

TABLE DEPT TOOLS
EMP TOOLS
EMP_EXT SYSTEM
EMP_NOBANCO TOOLS
GTT
PLAN_TABLE TOOLS
T TOOLS
T_PHYS
XDUAL

TRIGGER TPHYS_BI_FER


21 rows selected.

A saída vem organizada e de visão confortável. Facilmente eu posso filtrar também:

ops$mportes@FCCUAT9I> @dbls emp

OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX PK_EMP TOOLS

TABLE EMP TOOLS
EMP_EXT SYSTEM
EMP_NOBANCO TOOLS


4 rows selected.

Monday, May 30, 2005

Audit Trail - FGA (Fine-Grained Audit) 10g

No artigo anterior, vimos uma necessidade que levou-me a optar por uma solução caseira, ou seja, tive que desenvolver o código para instrumentar as DMLs, valores anteriores e novos para cada tabela.

Vou discutir hoje, uma auditoria built in, o FGA (Fine-grained Audit) espécie de controle de acesso granular. Através da DBMS_FGA podemos cadastrar políticas para determinada tabela e ver o resultado na DBA_FGA_AUDIT_TRAIL.

Primeiro vamos entender a DBMS_FGA e o subprograma que adiciona política a ADD_POLICY.

DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);

Parâmetros

object_schema => Esquema do objeto.
Valor default NULL. Se null ele assume o schema corrente

object_name => Nome do objeto.

policy_name => Nome único da política

audit_condition => Indica uma condicional para a auditoria, exemplo: auditar
as queries que possuem, na sua cláusula where "sal > 1000".
NULL é o default.

audit_column => Coluna que será checada por acesso, NULL é default e causará
cheque em todas as colunas acessadas.

handler_schema => Esquema onde está o event handler. Um event hander, pode ser
escrito para tomar alguma decisão quando o audit_condition
for verdadeiro.

handler_module => É o módulo (programa) que gerencia a auditoria, geralmente usado
como alerta, exemplo, uma procedure que envia e-mail a gerência
alertando sobre uma query contra a tabela de ações, por ai vai
conforme a necessidade do negócio. O handler_module deve possuir
a seguinte interface:

PROCEDURE (
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 ) AS ...

Onde fname é o nome da procedure, object_schema é o nome do
esquema, object_name nome da tabela que está auditada e
policy_name nome da política.

enable => true/false - true a política estará ativa. Default TRUE.

statement_types => Onde haverá auditoria. Valores aceitos: insert, update,
delete ou select. Podemos utilizar todos separados por vírgula.
Default: SELECT

audit_trail => Determina se será gravado ou não o sql bind e o sql text na
sys.fga_log$. O default é para que seja populado DB_EXTENDED
caso contrário, use DB.


audit_column_opts => Determina se a sentença SQL será audita quando ela acessa
QUALQUER (ANY) ou TODAS (ALL) as colunas. Default ANY_COLUMNS.

Partiremos para um exemplo bastante simples, apenas para apresentar o conceito. Será criado um esquema chamado MARCIO e nele criaremos a tabela EMP do SCOTT. Após, faremos a coleta de estatística - a FGA funciona corretamente somente em CBO - e criaremos a política, mais abaixo estarão vários selects com bind variable sem bind, insert e update e logo o resultado na DBA_FGA_AUDIT_TRAIL.

ops$marcio@ORA10G> connect / as sysdba
Connected.
sys@ORA10G> truncate table fga_log$;

Table truncated.

sys@ORA10G> create user marcio identified by marcio default tablespace users;

User created.

sys@ORA10G>
sys@ORA10G> grant connect, resource to marcio;

Grant succeeded.

sys@ORA10G> grant execute on dbms_lock to marcio;

Grant succeeded.

sys@ORA10G> create table marcio.emp as select * from scott.emp;

Table created.

sys@ORA10G>
sys@ORA10G> exec dbms_stats.gather_table_stats( 'MARCIO', 'EMP', cascade => true);

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> begin
2 dbms_fga.add_policy
3 ( object_schema => 'MARCIO',
4 policy_name => 'marcio_fga',
5 object_name => 'EMP',
6 statement_types => 'insert, select, update'
7 );
8 end;
9 /

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> exec ops$marcio.print_table('select * from dba_audit_policies');
OBJECT_SCHEMA : MARCIO
OBJECT_NAME : EMP
POLICY_NAME : MARCIO_FGA
POLICY_TEXT :
POLICY_COLUMN :
PF_SCHEMA :
PF_PACKAGE :
PF_FUNCTION :
ENABLED : YES
SEL : YES
INS : YES
UPD : YES
DEL : NO
AUDIT_TRAIL : DB_EXTENDED
POLICY_COLUMN_OPTIONS : ANY_COLUMNS
-----------------

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> connect marcio/marcio
Connected.
marcio@ORA10G>
marcio@ORA10G> var x number
marcio@ORA10G> var z varchar2(10)
marcio@ORA10G>
marcio@ORA10G> exec :x := 7900

PL/SQL procedure successfully completed.


X
-------------
7900

marcio@ORA10G> exec :z := 'JAMES'

PL/SQL procedure successfully completed.


Z
--------------------------------
JAMES

marcio@ORA10G>
marcio@ORA10G> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/0087 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/0087 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10

14 rows selected.

marcio@ORA10G> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

marcio@ORA10G>
marcio@ORA10G> select * from emp where empno = :x and ename = :z;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30

1 row selected.

marcio@ORA10G> exec :x := 7782

PL/SQL procedure successfully completed.


X
-------------
7782

marcio@ORA10G> /

no rows selected

marcio@ORA10G> insert into emp values ( 100, 'MARCIO','DBA', 7698, '15/08/2004 00:00:00', 1950, NULL, 30);

1 row created.

marcio@ORA10G> commit;

Commit complete.

marcio@ORA10G> exec :x := 100

PL/SQL procedure successfully completed.


X
-------------
100

marcio@ORA10G> select * from emp where empno = :x;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
100 MARCIO DBA 7698 15/08/2004 00:00:00 1950 30

1 row selected.

marcio@ORA10G> update emp
2 set sal = 10000
3 where empno = :x;

1 row updated.

marcio@ORA10G>
marcio@ORA10G> connect /
Connected.
ops$marcio@ORA10G>
ops$marcio@ORA10G> column sql_text format a40 word_wrap
ops$marcio@ORA10G> column os_user format a10
ops$marcio@ORA10G> column db_user format a10
ops$marcio@ORA10G> column sql_bind format a25
ops$marcio@ORA10G>
ops$marcio@ORA10G> select timestamp, db_user, os_user, sql_text, sql_bind
2 from dba_fga_audit_trail
3 order by timestamp desc
4 /

TIMESTAMP DB_USER OS_USER SQL_TEXT SQL_BIND
------------------- ---------- ---------- ---------------------------------------- -------------------------
31/05/2005 00:58:35 MARCIO marcio select * from emp where empno = :x and #1(4):7782 #2(5):JAMES
ename = :z

31/05/2005 00:58:35 MARCIO marcio insert into emp values ( 100,
'MARCIO','DBA', 7698, '15/08/2004
00:00:00', 1950, NULL, 30)

31/05/2005 00:58:35 MARCIO marcio select * from emp where empno = :x #1(3):100
31/05/2005 00:58:35 MARCIO marcio update emp #1(3):100
set sal = 10000
where empno = :x

31/05/2005 00:58:34 MARCIO marcio select * from emp where empno = :x and #1(4):7900 #2(5):JAMES
ename = :z

31/05/2005 00:58:29 MARCIO marcio select * from emp

6 rows selected.

ops$marcio@ORA10G>

Labels:


Friday, May 27, 2005

Audit trail - Solução Caseira

Certa vez, em minha carreira, me deparei com esse problema: necessidade de fazer auditoria em um esquema. Naquela época fiz uma pesquisa para evitar codificar e não encontrei nada built in, então encontrei esse exemplo abaixo na asktom e funcionou perfeitamente bem.

O problema era simples, saber quando, quem, qual tabela, qual campo, valor antigo e valor novo no esquema inteiro. Fácil não!

Primeiro tabela que irá manter a auditoria.

ops$marcio@ORA10G> create table audit_tbl
2 ( timestamp date,
3 who varchar2(30),
4 tname varchar2(30),
5 cname varchar2(30),
6 old varchar2(2000),
7 new varchar2(2000)
8 )
9 /

Table created.

Package para identificar os valores. Notem o uso de morfologia na package.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace package audit_pkg
2 as
3 procedure check_val( l_tname in varchar2,
4 l_cname in varchar2,
5 l_new in varchar2,
6 l_old in varchar2 );
7
8 procedure check_val( l_tname in varchar2,
9 l_cname in varchar2,
10 l_new in date,
11 l_old in date );
12
13 procedure check_val( l_tname in varchar2,
14 l_cname in varchar2,
15 l_new in number,
16 l_old in number );
17 end;
18 /

Package created.

ops$marcio@ORA10G>
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace package body audit_pkg
2 as
3
4 procedure check_val( l_tname in varchar2,
5 l_cname in varchar2,
6 l_new in varchar2,
7 l_old in varchar2 )
8 is
9 begin
10 if ( l_new <> l_old or
11 (l_new is null and l_old is not NULL) or
12 (l_new is not null and l_old is NULL) )
13 then
14 insert into audit_tbl values
15 ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
16 l_old, l_new );
17 end if;
18 end;
19
20 procedure check_val( l_tname in varchar2, l_cname in varchar2,
21 l_new in date, l_old in date )
22 is
23 begin
24 if ( l_new <> l_old or
25 (l_new is null and l_old is not NULL) or
26 (l_new is not null and l_old is NULL) )
27 then
28 insert into audit_tbl values
29 ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
30 to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
31 to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ) );
32 end if;
33 end;
34
35 procedure check_val( l_tname in varchar2, l_cname in varchar2,
36 l_new in number, l_old in number )
37 is
38 begin
39 if ( l_new <> l_old or
40 (l_new is null and l_old is not NULL) or
41 (l_new is not null and l_old is NULL) )
42 then
43 insert into audit_tbl values
44 ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
45 l_old, l_new );
46 end if;
47 end;
48
49 end audit_pkg;
50 /

Package body created.

Script para gerar a trigger genericamente, isso ajuda quando o esquema é grande, ou seja, tem muitas tabelas.

set serveroutput on
set feedback off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on

@tmp

Agora basta rodar o script contra as tabelas.

ops$marcio@ORA10G> @gera_trigger_generica emp
create or replace trigger aud#emp
after update on emp
for each row
begin
audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO);
audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME);
audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB);
audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR);
audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE);
audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL);
audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM);
audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
end;
/

Trigger created.

Trigger criada, estamos prontos para o teste.
Abaixo, o espertinho vai aumentar o salário do KING de 10 para 50 mil e a tabela de auditoria vai mostrar mais abaixo.

ops$marcio@ORA10G> update emp set sal = 50000 where ename = 'KING';

1 row updated.

ops$marcio@ORA10G> select * from audit_tbl;

TIMESTAMP WHO TNAME CNAME OLD NEW
------------------- -------- ----- ----- ---------- ----------
27/05/2005 02:14:52 marcio EMP SAL 10000 50000

1 row selected.

Wednesday, May 25, 2005

Duvida - Parseando Campo Blob

Autor: Vander Emiro Muniz
A Dúvida
Márcio,

problema, como eu faço pra converter um blob em uma tabela ?

tenho um arquivo que foi inserido lá ... ele está delimitado o registro com
\n e o os campos com ';'

eu sei que precisa de uma procedure, mas não sei como implentar. Dá um help
...

valeu.


Já conversei em off com o Vander para entender a necessidade e o porque deles não usarem o sql loader ou external table, que seriam mais apropriados para o caso.

Ele também me enviou um export da tabela que continha o blob.
Após o import do exemplo, fiz uma procedure onde primeiro popula um array com registros delimitados por \n (chr10) e depois faz um looping varrendo esse array e quebrando mais uma vez, porém agora o delimitador é o ';', ficou mais ou menos assim.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace type array as table of varchar2 (1000);
2 /

Type created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> drop table customer purge;

Table dropped.

ops$marcio@ORA10G> create table customer
2 (
3 year number(4),
4 month number(2),
5 country_code varchar2(2),
6 customer_code number,
7 customer_name varchar2(60)
8 )
9 /

Table created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure p
2 as
3 l_string clob;
4 l_data array := array();
5 l_data_parsed array := array();
6 n number;
7
8 procedure f ( p_string in varchar2 )
9 as
10 l_str varchar2(1000);
11 begin
12 l_str := p_string || ';';
13 loop
14 exit when l_str is null;
15 n := instr( l_str, ';' );
16 l_data_parsed.extend;
17 l_data_parsed(l_data_parsed.count) :=
18 ltrim( rtrim (substr( l_str, 1, n-1 ) ) ) ;
19 l_str := substr( l_str, n+1 );
20 end loop;
21 end;
22 begin
23 select utl_raw.cast_to_varchar2(blobcolumn) || chr(10) into l_string
24 from blobtable;
25 loop
26 exit when dbms_lob.getlength(l_string) = 0;
27 n := instr( l_string, chr(10) );
28 l_data.extend;
29 l_data(l_data.count) := substr( l_string, 1, n-1 ) ;
30 l_string := substr( l_string, n+1 );
31 end loop;
32 for i in 2 .. l_data.count
33 loop
34 f( l_data(i) );
35 insert into customer
36 values ( l_data_parsed(1), l_data_parsed(2),
37 l_data_parsed(3), l_data_parsed(4), l_data_parsed(5) );
38 l_data_parsed := array();
39 end loop;
40 end;
41 /

Procedure created.

ops$marcio@ORA10G> show error
No errors.

O que veio no campo blob.

ops$marcio@ORA10G> select utl_raw.cast_to_varchar2(blobcolumn) from blobtable;

UTL_RAW.CAST_TO_VARCHAR2(BLOBCOLUMN)
-------------------------------------------------------------------------------------------------------------
year;month;country_code;customer_code;customer_name;customer_type;adress1;adress2;adress3;state_code;zip_code
ntry_name;distribution_channel;GDS_web;Countries_V2_1.country_name;resinter_code;hotel_name;SommeDenbr_rn;Som
2004;9;AR;55011283;PASSERINI VIAJES S.R.L.;AGV;AV. RAFAEL NUNEZ 4731;P. BAJA;;AR;5009;CORDOBA;ARGENTINA;Autom
;IBIS PORTO ALEGRE AEROPORTO;1;22,29119639
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;BRAZIL;2992;NOVOTEL SAO PAULO CENTER NORTE;3;187,0767494
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;GERMANY;1306;DORINT SOFITEL AM DOM CGN;3;693
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;GERMANY;5360;DORINT SOFITEL PALLAS UWE;6;1218
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;SPAIN;1320;SOFITEL MADRID PLAZA DE ESPA╤A;6;795
2004;9;AR;55500222;FIRENZE VIAJES S.R.L.;AGV;CORRIENTES NO.3247;PISO 2-LOCAL 3030;;AR;1004;BUENOS AIRES/BUENO
mations;AMA;SPAIN;1606;SOFITEL MADRID AIRPORT;1;159
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
BRAZIL;5021;IBIS GUARULHOS;1;14,95485327
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
ECUADOR;5111;GRAND HOTEL MERCURE QUITO;1;37,87573535
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
FRANCE;1561;IBIS BORDEAUX SAINT-JEAN;3;172
2004;9;AR;55500620;VIAJES CLEVELAND S.A.;AGV;CORRIENTES NO.456;PISO 6-OFICINA 65-EDIFIC.SAFICO;;AR;1366;BUENO

1 row selected.

ops$marcio@ORA10G>
ops$marcio@ORA10G> select * from customer;

no rows selected

ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p

PL/SQL procedure successfully completed.


E como ficou a tabela customer depois do processo.

ops$marcio@ORA10G> select * from customer;

YEAR MONTH CO CUSTOMER_CODE CUSTOMER_NAME
------------- ------------- -- ------------- ------------------------------------------------------------
2004 9 AR 55011283 PASSERINI VIAJES S.R.L.
2004 9 AR 55500174 SECONTUR S.R.L.
2004 9 AR 55500174 SECONTUR S.R.L.
2004 9 AR 55500174 SECONTUR S.R.L.
2004 9 AR 55500174 SECONTUR S.R.L.
2004 9 AR 55500222 FIRENZE VIAJES S.R.L.
2004 9 AR 55500432 GUSTAVO ESUSY-D.TERZE
2004 9 AR 55500432 GUSTAVO ESUSY-D.TERZE
2004 9 AR 55500432 GUSTAVO ESUSY-D.TERZE
2004 9 AR 55500620 VIAJES CLEVELAND S.A.

10 rows selected.

ops$marcio@ORA10G>
ops$marcio@ORA10G> set echo off
ops$marcio@ORA10G>

Claro que no final das contas eu não fiz para a tabela customer toda, o intento aqui é dar uma idéia de como isso pode ser feito.

Porém Vander, a melhor dica, no seu caso, é: faça o programa ASP popular diretamente uma tabela gtt já parseando como fiz no exemplo, depois voce faz uma agenda (dbms_job) para o insert definitivo, caso necessite tratar algo.

Abraços,

Tuesday, May 24, 2005

Statspack - Interpretação

Retomando depois de encontrar algum tempo, vamos analisar o report propriamente.
Bom, estamos com quase 20 páginas de relatório e agora? As próximas linhas serão minha opinião de como trato o relatório e isso é completamente empírico, é a o método que eu encontrei para a interpretação de Statspack.

Caminhando pelas seções:


STATSPACK report for

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORA10G 3848249943 ora10g 1 10.1.0.2.0 NO MARCIO807955

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 51 23-May-05 23:02:45 17 6.2
End Snap: 52 23-May-05 23:32:50 17 6.4
Elapsed: 30.08 (mins)






Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 40M Log Buffer: 256



Aqui estou interessado no tempo, algo que passe de 30 minutos ou seja menos de 15, deveria ser desprezado. Nesta seção também encontramos os caches, log buffer e tamanho do bloco. Como podem notar, trabalho em um notebook de 256m de memória, portanto tive que customizar meu Oracle 10g para executar de maneira satisfatória neste ambiente. O Oracle 10g roda sim em computador com menos de 512m de memória - vale lembrar que não é para fim comercial e sim educativo. Tenho essa instância instalada para explorar e estudar o Oracle.

Load Profile

Aqui temos noção do load através da análise da quantidade de Executes, Transactions, Logical e Physical reads por segundo e transação. Estou interessado também no Hard Parses que deve estar bem próximo de zero. Se não estiver, na grande maioria dos sistemas, eu deveria seguir para o top de sql e encontrar queries sem bind variable.

Instance Efficiency Percentage
Nesta seção procuro saber a utilização da Shared Pool, através dos Library Hit %, Execute to Parse % e Soft Parse %. Se a Library Hit % está com valor baixo, isso indica que sua shared pool pode estar subdimensionada ou sua aplicação não está fazendo uso correto das bind variables.
Soft Parse % tem grande importância, na maioria dos sistemas ela deveria estar próxima de 100. Existem algumas exceções como sistemas baseados em web e DW.
Execute to Parse % assim como Soft Parse, deveria estar proximo de 100 na maioria dos sistemas, porém segue a mesma regra de exceção que o item anterior.

Top 5 Timed Events
Quando estou analisando um statspack report venho até aqui. A partir daqui, vou especificamente para algumas seções mais abaixo para buscar mais informação, mas em resumo, venho até aqui. O Top % Timed Events mostra os 5 maiores eventos de espera, a Oracle mudou o nome de espera para tempo gasto - para mim é a mesma coisa - a novidade foi incluir o tempo da CPU junto, o tempo de CPU não poderá ser um grande problema, faça um balanço entre o tempo do snap e o tempo da CPU (n Minutos x 60 segundos x m CPUs) se o valor estiver alto, provavelmente o sistema está usando índice onde deveria usar full table scan (Já discutimos anteriormente).

Fiz um exemplo onde tinha minha PGA_AGGREGATE_TARGET = 10m. Nesse exemplo, fiz sort forçando o estouro de memória e gravação na TEMP. Veja o resultado.


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
direct path read temp 10,488 77 84.79
direct path write temp 7,464 5 5.92
db file sequential read 251 4 4.18
control file sequential read 585 2 1.79
db file parallel write 83 1 .99


Após essa execução, aumentei a pga_aggregate_target para 50m. Como cheguei a esse valor veremos em uma próxima oportunidade. Rodei outro snap e encontrei isso:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
db file sequential read 35 1 31.72
control file parallel write 15 1 29.34
control file sequential read 587 0 22.94
db file scattered read 34 0 7.69
latch free 6 0 6.96


Por que tão pouco, porque para efeito de demonstração fiz:

create or replace procedure p
as
begin
for i in 1 .. 100
loop
for x in ( select big.owner, big.object_name, big.created
from big, small
where big.id = small.id
order by 1, 2, 3 )
loop
null;
end loop;
end loop;
end;
/
show error
pause

begin
statspack.snap;
p;
statspack.snap;
end;
/


Portanto o tempo do snap, foi o tempo da execução da procedure. Como eu não tenho um sistema em minha instância, armei um exemplo simples para demonstrar o tuning com o pga_aggregate_target.

Se voce tem alguma dúvida no seu statspack, envie aqui e lhe ajudarei a interpretá-lo na medida do possível.

Thursday, May 19, 2005

Dúvida - Merge

Irei quebrar a sequência sobre statspack que estou escrevendo para responder uma dúvida que chegou. Relembrando, as dúvidas têm prioridade sobre os artigos.

Autor: Alexandre Taleb
A dúvida.


"
Eu criei uma tabela duas tabelas, uma temporária (CASE_STG_REQ_TEMP) e outra definitiva (CASE_STG_REQ_DEF) :


CREATE TABLE CASE_STG_REQ_TEMP
(
COUNTRYCODE VARCHAR2(3),
ATEN_NU_PTO NUMBER(10),
SEGM_CD NUMBER(3),
FASE_NU_SEQ NUMBER(3),
RESO_NU_SEQ NUMBER(3),
RESO_TE_REQ VARCHAR2(20),
RESO_TE_ATEN VARCHAR2(30)

)


CREATE TABLE CASE_STG_REQ_DEF
(
COUNTRYCODE VARCHAR2(3),
ATEN_NU_PTO NUMBER(10),
SEGM_CD NUMBER(3),
FASE_NU_SEQ NUMBER(3),
RESO_NU_SEQ NUMBER(3),
RESO_TE_REQ VARCHAR2(20),
RESO_TE_ATEN VARCHAR2(30), CONSTRAINT CASE_STG_REQ_TEMP PRIMARY KEY(ATEN_NU_PTO, FASE_NU_SEQ, RESO_NU_SEQ)

)


A TEMP recebe dados de um arquivo TXT e antes de ser carregada pelo SQLLOADER é executado o comando truncate para esvazia-la.
Depois de carregada deve haver um script para inserir os dados da tabela TEMP para a tabela definitiva., porém a tabela TEMP pode receber dados atualizados de registros que já estão na tabela definitiva, por exemplo o sistema fonte atualizou a descrição da requisição. (RESO_TE_REQ / Descrição da Requisição)

Tabela temporária (Nova carga)

COUNTRYCODE, ATEN_NU_PTO, SEGM_CD, FASE_NU_SEQ, RESO_NU_SEQ, RESO_TE_REQ, RESO_TE_ATEN
BR, 35, 1, 1, 1, VERIFICAR NOME VS, si


Tabela definitiva (Carga antiga)

COUNTRYCODE, ATEN_NU_PTO, SEGM_CD, FASE_NU_SEQ, RESO_NU_SEQ, RESO_TE_REQ, RESO_TE_ATEN
BR, 35, 1, 1, 1, VERIFICAR Ne VS, si

Veja que os registros chave são idênticos e o oracle não vai inserir esse registro sendo assim a descrição da requisição ficará VERIFICAR Ne VS ao invés de corrigida como VERIFICAR NOME VS.

A idéia seria verificar esse registro na tabela temporária e se a definitiva já tem esse registro, excluir da definitiva e depois inserir o novo registro da temp para a definitiva, ou seja uma atualização do registro na definitiva já que a temporária será apagada a cada nova carga.
"


Recebi ainda do autor os arquivos texto para o teste e o CTL (control do sql loader).
Ele está na versão 9ir2, portanto a opção MERGE está disponível.

Vamos ao exemplo:

ops$marcio@ORA10G> CREATE TABLE CASE_STG_REQ_TEMP
2 (
3 COUNTRYCODE VARCHAR2(3),
4 ATEN_NU_PTO NUMBER(10),
5 SEGM_CD NUMBER(3),
6 FASE_NU_SEQ NUMBER(3),
7 RESO_NU_SEQ NUMBER(3),
8 RESO_TE_REQ VARCHAR2(20),
9 RESO_TE_ATEN VARCHAR2(30)
10 )
11 /

Table created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> CREATE TABLE CASE_STG_REQ_DEF
2 (
3 COUNTRYCODE VARCHAR2(3),
4 ATEN_NU_PTO NUMBER(10),
5 SEGM_CD NUMBER(3),
6 FASE_NU_SEQ NUMBER(3),
7 RESO_NU_SEQ NUMBER(3),
8 RESO_TE_REQ VARCHAR2(20),
9 RESO_TE_ATEN VARCHAR2(30), CONSTRAINT CASE_STG_REQ_TEMP PRIMARY KEY(ATEN_NU_PTO, FASE_NU_SEQ, RESO_NU_SEQ)
10 )
11 /

Table created.

Agora, simulando sua carga de dados inicial. Não é necessário truncar a tabela como voce havia mencionado, o seu ctl já está fazendo isso para voce, veja logo abaixo que está explícito o TRUNCATE.
Se alguém estiver interessado em reproduzir o exemplo, aqui estão os arquivos de dados (dw.txt) e o control (dw.ctl)

--- dw.ctl
LOAD DATA
INFILE "C:\scripts\blog\question\DW.txt"
TRUNCATE
INTO TABLE CASE_STG_REQ_TEMP

(
countrycode Position (1:3) CHAR,
aten_nu_pto Position (4:13) INTEGER EXTERNAL,
segm_cd Position (14:16) INTEGER EXTERNAL,
fase_nu_seq Position (17:19) INTEGER EXTERNAL,
reso_nu_seq Position (20:22) integer external,
reso_te_req Position (23:42) CHAR,
reso_te_aten Position (43:149)CHAR
)
---

--- dw.txt
CO 35 1 1 1 VERIFICAR NOMBRE VS si
CO 38 1 1 1 VERIFICAR NOMBRE VS si
CO 42 1 1 1 AUTORIZACION CARDS Ssi
CO 47 1 1 1 NUMERO DE CTA A DEBI4.8743e13
CO 48 1 1 1 AUTORIZACION CARDS Ssi
CO 53 1 1 1 AUTORIZACION CARDS Ssi
CO 54 1 1 1 AUTORIZACION CARDS Ssi
CO 57 1 1 1 AUTORIZACION CARDS Ssi
CO 74 1 1 1 AUTORIZACION CARDS Sok
CO 86 1 1 1 AUTORIZACION CARDS Sok
CO 90 1 1 1 AUTORIZACION CARDS Sok
CO 95 1 1 1 VERIFICAR NOMBRE VS 1
CO 113 1 1 1 AUTORIZACION CARDS Sok
CO 122 1 1 1 AUTORIZACION CARDS Sok
CO 124 1 1 1 VERIFICAR NOMBRE VS ok
CO 125 1 1 1 AUTORIZACION CARDS Sok
CO 126 1 1 1 VERIFICAR NOMBRE VS ok
CO 128 1 1 1 AUTORIZACION CARDS Sok
CO 186 1 1 1 AUTORIZACION CARDS Sok
CO 204 1 1 1 AUTORIZACION CARDS Sok
CO 206 1 1 1 AUTORIZACION CARDS Sok
CO 228 1 1 1 VERIFICAR NOMBRE VS ok
CO 237 1 1 1 AUTORIZACION CARDS Sok
CO 239 1 1 1 AUTORIZACION CARDS Sok
CO 247 1 1 1 AUTORIZACION CARDS Sok
CO 326 1 1 1 TEST_REQUISITO_RAUL ewfewfwer
CO 327 1 1 1 Antiguedad 31 - 60 DIAS
---

ops$marcio@ORA10G> host sqlldr userid=/ control=dw.ctl data=dw.txt

SQL*Loader: Release 10.1.0.2.0 - Production on Thu May 19 02:50:21 2005

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

Commit point reached - logical record count 28

ops$marcio@ORA10G>
ops$marcio@ORA10G> select * from case_stg_req_temp;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
CO 35 1 1 1 VERIFICAR NOMBRE VS si
CO 38 1 1 1 VERIFICAR NOMBRE VS si
CO 42 1 1 1 AUTORIZACION CARDS S si
CO 47 1 1 1 NUMERO DE CTA A DEBI 4.8743e13
CO 48 1 1 1 AUTORIZACION CARDS S si
CO 53 1 1 1 AUTORIZACION CARDS S si
CO 54 1 1 1 AUTORIZACION CARDS S si
CO 57 1 1 1 AUTORIZACION CARDS S si
CO 74 1 1 1 AUTORIZACION CARDS S ok
CO 86 1 1 1 AUTORIZACION CARDS S ok
CO 90 1 1 1 AUTORIZACION CARDS S ok
CO 95 1 1 1 VERIFICAR NOMBRE VS 1
CO 113 1 1 1 AUTORIZACION CARDS S ok
CO 122 1 1 1 AUTORIZACION CARDS S ok
CO 124 1 1 1 VERIFICAR NOMBRE VS ok
CO 125 1 1 1 AUTORIZACION CARDS S ok
CO 126 1 1 1 VERIFICAR NOMBRE VS ok
CO 128 1 1 1 AUTORIZACION CARDS S ok
CO 186 1 1 1 AUTORIZACION CARDS S ok
CO 204 1 1 1 AUTORIZACION CARDS S ok
CO 206 1 1 1 AUTORIZACION CARDS S ok
CO 228 1 1 1 VERIFICAR NOMBRE VS ok
CO 237 1 1 1 AUTORIZACION CARDS S ok
CO 239 1 1 1 AUTORIZACION CARDS S ok
CO 247 1 1 1 AUTORIZACION CARDS S ok
CO 326 1 1 1 TEST_REQUISITO_RAUL ewfewfwer
CO 327 1 1 1 Antiguedad 31 - 60 DIAS

27 rows selected.

Uma vez carregada a tabela, vou criar uma procedure para o MERGE da tabela legado com a tabela transiente.

ops$marcio@ORA10G> create or replace procedure do_merge
2 as
3 begin
4 merge into case_stg_req_def def
5 using case_stg_req_temp tmp
6 on ( def.aten_nu_pto = tmp.aten_nu_pto
7 and def.FASE_NU_SEQ = tmp.FASE_NU_SEQ
8 and def.RESO_NU_SEQ = tmp.RESO_NU_SEQ )
9 when matched then
10 update set def.countrycode = tmp.countrycode
11 , def.segm_cd = tmp.segm_cd
12 , def.reso_te_req = tmp.reso_te_req
13 , def.reso_te_aten= tmp.reso_te_aten
14 when not matched then
15 insert (def.COUNTRYCODE, def.ATEN_NU_PTO, def.SEGM_CD, def.FASE_NU_SEQ,
16 def.RESO_NU_SEQ, def.RESO_TE_REQ, def.RESO_TE_ATEN)
17 values (tmp.COUNTRYCODE, tmp.ATEN_NU_PTO, tmp.SEGM_CD, tmp.FASE_NU_SEQ,
18 tmp.RESO_NU_SEQ, tmp.RESO_TE_REQ, tmp.RESO_TE_ATEN);
19 end;
20 /

Procedure created.

ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> select * from case_stg_req_def;

no rows selected

Tabela vazia.

ops$marcio@ORA10G> exec do_merge;

PL/SQL procedure successfully completed.

ops$marcio@ORA10G> select * from case_stg_req_def;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
CO 90 1 1 1 AUTORIZACION CARDS S ok
CO 327 1 1 1 Antiguedad 31 - 60 DIAS
CO 247 1 1 1 AUTORIZACION CARDS S ok
CO 54 1 1 1 AUTORIZACION CARDS S si
CO 74 1 1 1 AUTORIZACION CARDS S ok
CO 48 1 1 1 AUTORIZACION CARDS S si
CO 204 1 1 1 AUTORIZACION CARDS S ok
CO 53 1 1 1 AUTORIZACION CARDS S si
CO 42 1 1 1 AUTORIZACION CARDS S si
CO 126 1 1 1 VERIFICAR NOMBRE VS ok
CO 35 1 1 1 VERIFICAR NOMBRE VS si
CO 326 1 1 1 TEST_REQUISITO_RAUL ewfewfwer
CO 239 1 1 1 AUTORIZACION CARDS S ok
CO 57 1 1 1 AUTORIZACION CARDS S si
CO 228 1 1 1 VERIFICAR NOMBRE VS ok
CO 206 1 1 1 AUTORIZACION CARDS S ok
CO 122 1 1 1 AUTORIZACION CARDS S ok
CO 186 1 1 1 AUTORIZACION CARDS S ok
CO 38 1 1 1 VERIFICAR NOMBRE VS si
CO 86 1 1 1 AUTORIZACION CARDS S ok
CO 124 1 1 1 VERIFICAR NOMBRE VS ok
CO 47 1 1 1 NUMERO DE CTA A DEBI 4.8743e13
CO 113 1 1 1 AUTORIZACION CARDS S ok
CO 128 1 1 1 AUTORIZACION CARDS S ok
CO 125 1 1 1 AUTORIZACION CARDS S ok
CO 95 1 1 1 VERIFICAR NOMBRE VS 1
CO 237 1 1 1 AUTORIZACION CARDS S ok

27 rows selected.

Agora completa com a tabela temporária. Próximo passo, simular sua carga diária. Vamos fazer isso em 2 etapas. A primeira, carregando o registro BR 100 ... com uma descrição e o segundo, trazendo o mesmo registro (PK) porém com a alteração da descrição -- como voce colocou na dúvida.
A simulação acontece assim, criei 3 arquivos .TXT o dw0 original, dw1 com o registro novo, dw2 com o registro a ser atualizado.

ops$marcio@ORA10G> host copy dw1.txt dw.txt
1 file(s) copied.

ops$marcio@ORA10G> host sqlldr userid=/ control=dw.ctl data=dw.txt

SQL*Loader: Release 10.1.0.2.0 - Production on Thu May 19 02:50:27 2005

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

Commit point reached - logical record count 1

ops$marcio@ORA10G> select * from case_stg_req_temp;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
BR 100 1 1 1 VERIFICAR NOMBRE VS si

1 row selected.

ops$marcio@ORA10G> exec do_merge

PL/SQL procedure successfully completed.

ops$marcio@ORA10G> select * from case_stg_req_def;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
CO 90 1 1 1 AUTORIZACION CARDS S ok
CO 327 1 1 1 Antiguedad 31 - 60 DIAS
CO 247 1 1 1 AUTORIZACION CARDS S ok
CO 54 1 1 1 AUTORIZACION CARDS S si
CO 74 1 1 1 AUTORIZACION CARDS S ok
CO 48 1 1 1 AUTORIZACION CARDS S si
CO 204 1 1 1 AUTORIZACION CARDS S ok
CO 53 1 1 1 AUTORIZACION CARDS S si
CO 42 1 1 1 AUTORIZACION CARDS S si
CO 126 1 1 1 VERIFICAR NOMBRE VS ok
CO 35 1 1 1 VERIFICAR NOMBRE VS si
CO 326 1 1 1 TEST_REQUISITO_RAUL ewfewfwer
CO 239 1 1 1 AUTORIZACION CARDS S ok
CO 57 1 1 1 AUTORIZACION CARDS S si
CO 228 1 1 1 VERIFICAR NOMBRE VS ok
CO 206 1 1 1 AUTORIZACION CARDS S ok
CO 122 1 1 1 AUTORIZACION CARDS S ok
CO 186 1 1 1 AUTORIZACION CARDS S ok
CO 38 1 1 1 VERIFICAR NOMBRE VS si
CO 86 1 1 1 AUTORIZACION CARDS S ok
CO 124 1 1 1 VERIFICAR NOMBRE VS ok
CO 47 1 1 1 NUMERO DE CTA A DEBI 4.8743e13
CO 113 1 1 1 AUTORIZACION CARDS S ok
CO 128 1 1 1 AUTORIZACION CARDS S ok
CO 125 1 1 1 AUTORIZACION CARDS S ok
CO 95 1 1 1 VERIFICAR NOMBRE VS 1
CO 237 1 1 1 AUTORIZACION CARDS S ok
BR 100 1 1 1 VERIFICAR NOMBRE VS si

28 rows selected.

ops$marcio@ORA10G>
ops$marcio@ORA10G> host copy dw2.txt dw.txt
1 file(s) copied.

ops$marcio@ORA10G> host sqlldr userid=/ control=dw.ctl data=dw.txt

SQL*Loader: Release 10.1.0.2.0 - Production on Thu May 19 02:50:32 2005

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

Commit point reached - logical record count 1

ops$marcio@ORA10G> select * from case_stg_req_temp;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
BR 100 1 1 1 VERIFICAR NE VS si

1 row selected.

ops$marcio@ORA10G> exec do_merge

PL/SQL procedure successfully completed.

ops$marcio@ORA10G> select * from case_stg_req_def;

COU ATEN_NU_PTO SEGM_CD FASE_NU_SEQ RESO_NU_SEQ RESO_TE_REQ RESO_TE_ATEN
--- ------------- ------------- ------------- ------------- -------------------- ------------------------------
CO 90 1 1 1 AUTORIZACION CARDS S ok
CO 327 1 1 1 Antiguedad 31 - 60 DIAS
CO 247 1 1 1 AUTORIZACION CARDS S ok
CO 54 1 1 1 AUTORIZACION CARDS S si
CO 74 1 1 1 AUTORIZACION CARDS S ok
CO 48 1 1 1 AUTORIZACION CARDS S si
CO 204 1 1 1 AUTORIZACION CARDS S ok
CO 53 1 1 1 AUTORIZACION CARDS S si
CO 42 1 1 1 AUTORIZACION CARDS S si
CO 126 1 1 1 VERIFICAR NOMBRE VS ok
CO 35 1 1 1 VERIFICAR NOMBRE VS si
CO 326 1 1 1 TEST_REQUISITO_RAUL ewfewfwer
CO 239 1 1 1 AUTORIZACION CARDS S ok
CO 57 1 1 1 AUTORIZACION CARDS S si
CO 228 1 1 1 VERIFICAR NOMBRE VS ok
CO 206 1 1 1 AUTORIZACION CARDS S ok
CO 122 1 1 1 AUTORIZACION CARDS S ok
CO 186 1 1 1 AUTORIZACION CARDS S ok
CO 38 1 1 1 VERIFICAR NOMBRE VS si
CO 86 1 1 1 AUTORIZACION CARDS S ok
CO 124 1 1 1 VERIFICAR NOMBRE VS ok
CO 47 1 1 1 NUMERO DE CTA A DEBI 4.8743e13
CO 113 1 1 1 AUTORIZACION CARDS S ok
CO 128 1 1 1 AUTORIZACION CARDS S ok
CO 125 1 1 1 AUTORIZACION CARDS S ok
CO 95 1 1 1 VERIFICAR NOMBRE VS 1
CO 237 1 1 1 AUTORIZACION CARDS S ok
BR 100 1 1 1 VERIFICAR NE VS si

28 rows selected.

Feito, o último registro, como pode ser notado, foi atualizado corretamente.
Mais uma dica, ao invés de usar o sql loader, use o recurso da external table. Com o ctl, fica ainda mais fácil de gerar a DDL de criação da tabela basta voce pedir ao sqlldr com o parâmetro external_table=generate_only. Com isso, quando seu processo quando colocar o novo dw.txt a tabela temporária já estará pronta para o merge sem a necessidade de voce executar o loader.

ops$marcio@ORA10G> host sqlldr userid=/ control=dw.ctl external_table=generate_only

SQL*Loader: Release 10.1.0.2.0 - Production on Thu May 19 02:51:32 2005

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


ops$marcio@ORA10G> host type dw.log

SQL*Loader: Release 10.1.0.2.0 - Production on Thu May 19 02:51:32 2005

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

Control File: dw.ctl
Data File: C:\scripts\blog\question\DW.txt
Bad File: DW.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table

Table CASE_STG_REQ_TEMP, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COUNTRYCODE 1:3 3 CHARACTER
ATEN_NU_PTO 4:13 10 CHARACTER
SEGM_CD 14:16 3 CHARACTER
FASE_NU_SEQ 17:19 3 CHARACTER
RESO_NU_SEQ 20:22 3 CHARACTER
RESO_TE_REQ 23:42 20 CHARACTER
RESO_TE_ATEN 43:149 107 CHARACTER



CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'c:\scripts\blog\question'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\scripts\blog\question\'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_CASE_STG_REQ_"
(
"COUNTRYCODE" VARCHAR2(3),
"ATEN_NU_PTO" NUMBER(10),
"SEGM_CD" NUMBER(3),
"FASE_NU_SEQ" NUMBER(3),
"RESO_NU_SEQ" NUMBER(3),
"RESO_TE_REQ" VARCHAR2(20),
"RESO_TE_ATEN" VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'DW.bad'
LOGFILE 'dw.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"COUNTRYCODE" (1:3) CHAR(3),
"ATEN_NU_PTO" (4:13) INTEGER EXTERNAL(10),
"SEGM_CD" (14:16) INTEGER EXTERNAL(3),
"FASE_NU_SEQ" (17:19) INTEGER EXTERNAL(3),
"RESO_NU_SEQ" (20:22) INTEGER EXTERNAL(3),
"RESO_TE_REQ" (23:42) CHAR(20),
"RESO_TE_ATEN" (43:149) CHAR(107)
)
)
location
(
'DW.txt'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO CASE_STG_REQ_TEMP
(
COUNTRYCODE,
ATEN_NU_PTO,
SEGM_CD,
FASE_NU_SEQ,
RESO_NU_SEQ,
RESO_TE_REQ,
RESO_TE_ATEN
)
SELECT
"COUNTRYCODE",
"ATEN_NU_PTO",
"SEGM_CD",
"FASE_NU_SEQ",
"RESO_NU_SEQ",
"RESO_TE_REQ",
"RESO_TE_ATEN"
FROM "SYS_SQLLDR_X_EXT_CASE_STG_REQ_"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_CASE_STG_REQ_"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



Run began on Thu May 19 02:51:32 2005
Run ended on Thu May 19 02:51:37 2005

Elapsed time was: 00:00:05.06
CPU time was: 00:00:00.05

ops$marcio@ORA10G>

Wednesday, May 18, 2005

Statspack - Introdução

Agora que já fizemos o setup, precisamos entender o statspack. Este recurso do Oracle captura uma grande quantidade de informação, compila e apresenta em um relatório com aproximadamente 20 páginas. As principais informações apresentadas neste relatório são:

Nesta introdução, quero chamar atenção sobre o crasso erro na utilização do statspack, o período de tempo do snapshot. Ele não deveria nunca passar de 30 minutos e não menos de 15. Algo entre esses números seria o bastante.

Nesse caso, o "mais é melhor" não funciona. Exemplo: durante o dia de ontem tive que parar para atender 25 vezes o telefone. Isso atrapalhou meu trabalho ou não? Difícil de responder sem uma análise mais a fundo.

O problema com período de tempo extenso é que, as vezes, o ponto que está afetando a performance fica "diluído" no tempo. Pegando o exemplo dos telefonemas. Para definir se o número de chamadas foi o problema que impactou o trabalho, temos que analisar o número 25 vezes contra o tempo - se foi durante o dia, então 25 por 8 horas, totaliza 1 ligação a cada 19 minutos, não está mal. Agora, se o período fosse mais curto, digamos 15 minutos: Owh! 1 ligação a cada 36 segundos, agora sim, podemos afirmar que os telefonemas impactaram seriamente o trabalho, ocasionando o não cumprimento de algum cronograma.

Um outro problema encontrado no uso do statspack é a frequência com que ele é executado. Geralmente, o statspack é utilizado somente quando o sistema está indo mal. Não! Este relatório deveria ser executado todos os dias em seu sistema, para que haja histórico (instrumentação) e quando os usuários reclamarem de performance, é possível comparar os resultados de um report, quando o sistema estava bem, contra o outro, do momento que ele vai mal.

Após esta breve intrução estamos prontos para "brincar" um pouco com o statspack. No próximo artigo, vamos discutir as principais seções do statspack e como identificar issues de performance. Vou preparar alguns códigos (procedures e jobs) e tabelas para os exemplos.

Abraços,

Tuesday, May 17, 2005

Statspack - Setup

Discutindo com alguns colegas e ao longo da carreira, na maioria das vezes, vi o uso do statspack de forma ineficiente, seja pela janela de tempo que o DBA coloca, seja pela interpretação do conteúdo.

Statspacks foi introduzido oficialmente na versão 8.1.6, veio substituir os scripts UTLBSTAT e UTLESTAT. Basicamente, statspack são snapshot (fotos) de como sua instância está se comportando em dado momento, com habilidade de salvar as estatísticas em um esquema de dados. Portanto, podemos recuperar resultados, medir, fazer benchmark por alguma alteração, provar que um sistema está melhor ou pior devido um tuning ou patch, etc.

O setup do statspack necessita do sys para criar objetos e dar privilégios. No manual estão todos os passos, aqui vou resumir e comentar o que achei relevante. Fiz a instalação no 10g em um notebook com Windows XP como sistema operacional.

Primeiro criei uma tablespace exclusiva para o PERFSTAT (esquema) do statspack, achei mais fácil dessa maneira para fins administrativos. Gosto de separar aplicações por tablespace, quando possível.

Esta tablespace eu criei com 100m e deixei autoextend on, ela chegou a 132m. No script spctab.sql, a criação dos objetos têm cláusulas initial com 100k, 1m, 3m e 5m, eu não alterei o default initial dos scripts de criação dos objetos, eu poderia ter editado os scripts para tomar menos espaço, isso depende do seu sistema, como atualmente eu não tenho nenhum sistema na minha instância, deixei tudo default.

perfstat@ORA10G> @tbsfree
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------------ ------------ ------------ ------ ------------ ------------ ------
SYSAUX 122,880 88,256 34,624 71.8 28,224 307,200 40.0
SYSTEM 307,200 238,208 68,992 77.5 68,544 512,000 60.0
UNDOTBS1 204,800 18,880 185,920 9.2 185,280 512,000 40.0
USERS 15,360 768 14,592 5.0 14,464 30,720 50.0
PERFSTAT_TBS 132,928 132,928 0 100.0 0 33,554,416 .4
TEMP 20,480 20,480 0 100.0 0 512,000 4.0
------------ ------------ ------------
sum 803,648 499,520 304,128

6 rows selected.

Depois, tudo que fiz foi conectar-me como sys e executar o script $ORACLE_HOME/rdbms/admin/spcreate.sql, responder algumas perguntas e esperar ele executar a criação. O spcreate.sql é o coordenador da criação, na verdade ele chama três outros scritps nesta ordem: spcusr.sql, spctab.sql e spcpkg.sql.

spcusr.sql
- Cria o usuário (esquema) que irá abrigar os objetos de banco de dados do statspack. Este script pergunta senha do PERFSTAT, tablespace default e temporária (estamos proibidos de usar SYSTEM aqui tanto para default como para temporária). Ademais, o script cria views, synonyms e dá privilégios.

spctab.sql
- Cria os objetos (tabelas e índices) no esquema PERFSTAT para armazenar as estatísticas dos snapshots.

spcpkg.sql
- Cria os pacotes de estatística.

Após a criação do esquema, vemos algo parecido com:

perfstat@ORA10G>
perfstat@ORA10G> @dbls %

OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX STATS$BG_EVENT_SUMMARY_PK PERFSTAT_TBS
STATS$BUFFERED_QUEUES_PK PERFSTAT_TBS
STATS$BUFFERED_SUBSCRIBERS_PK PERFSTAT_TBS
STATS$BUFFER_POOL_STATS_PK PERFSTAT_TBS
STATS$CLASS_CACHE_TRANSFER_PK PERFSTAT_TBS
STATS$CR_BLOCK_SERVER_PK PERFSTAT_TBS
STATS$CURRENT_BLOCK_SERVER_PK PERFSTAT_TBS
STATS$DATABASE_INSTANCE_PK PERFSTAT_TBS
STATS$DB_CACHE_ADVICE_PK PERFSTAT_TBS
STATS$DLM_MISC_PK PERFSTAT_TBS
STATS$ENQUEUE_STATISTICS_PK PERFSTAT_TBS
STATS$EVENT_HISTOGRAM_PK PERFSTAT_TBS
STATS$FILESTATXS_PK PERFSTAT_TBS
STATS$FILE_HISTOGRAM_PK PERFSTAT_TBS
STATS$IDLE_EVENT_PK PERFSTAT_TBS
STATS$INSTANCE_RECOVERY_PK PERFSTAT_TBS
STATS$JAVA_POOL_ADVICE_PK PERFSTAT_TBS
STATS$LATCH_CHILDREN_PK PERFSTAT_TBS
STATS$LATCH_MISSES_SUMMARY_PK PERFSTAT_TBS
STATS$LATCH_PARENT_PK PERFSTAT_TBS
STATS$LATCH_PK PERFSTAT_TBS
STATS$LEVEL_DESCRIPTION_PK PERFSTAT_TBS
STATS$LIBRARYCACHE_PK PERFSTAT_TBS
STATS$OSSTAT_PK PERFSTAT_TBS
STATS$PARAMETER_PK PERFSTAT_TBS
STATS$PGA_TARGET_ADVICE_PK PERFSTAT_TBS
STATS$PROPAGATION_RECEIVER_PK PERFSTAT_TBS
STATS$PROPAGATION_SENDER_PK PERFSTAT_TBS
STATS$RESOURCE_LIMIT_PK PERFSTAT_TBS
STATS$ROLLSTAT_PK PERFSTAT_TBS
STATS$ROWCACHE_SUMMARY_PK PERFSTAT_TBS
STATS$RULE_SET_PK PERFSTAT_TBS
STATS$SEG_STAT_OBJ_PK PERFSTAT_TBS
STATS$SEG_STAT_PK PERFSTAT_TBS
STATS$SESSION_EVENT_PK PERFSTAT_TBS
STATS$SESSTAT_PK PERFSTAT_TBS
STATS$SESS_TIME_MODEL_PK PERFSTAT_TBS
STATS$SGASTAT_U PERFSTAT_TBS
STATS$SGA_PK PERFSTAT_TBS
STATS$SHARED_POOL_ADVICE_PK PERFSTAT_TBS
STATS$SNAPSHOT_PK PERFSTAT_TBS
STATS$SQLTEXT_PK PERFSTAT_TBS
STATS$SQL_PGASTAT_PK PERFSTAT_TBS
STATS$SQL_PLAN_PK PERFSTAT_TBS
STATS$SQL_PLAN_USAGE_HV PERFSTAT_TBS
STATS$SQL_PLAN_USAGE_PK PERFSTAT_TBS
STATS$SQL_STATISTICS_PK PERFSTAT_TBS
STATS$SQL_SUMMARY_PK PERFSTAT_TBS
STATS$SQL_WORKAREA_HIST_PK PERFSTAT_TBS
STATS$STATSPACK_PARAMETER_PK PERFSTAT_TBS
STATS$STREAMS_APPLY_SUM_PK PERFSTAT_TBS
STATS$STREAMS_CAPTURE_PK PERFSTAT_TBS
STATS$SYSSTAT_PK PERFSTAT_TBS
STATS$SYSTEM_EVENT_PK PERFSTAT_TBS
STATS$SYS_TIME_MODEL_PK PERFSTAT_TBS
STATS$TEMPSTATXS_PK PERFSTAT_TBS
STATS$TEMP_HISTOGRAM_PK PERFSTAT_TBS
STATS$THREAD_PK PERFSTAT_TBS
STATS$TIME_MODEL_STATNAME_PK PERFSTAT_TBS
STATS$UNDOSTAT_PK PERFSTAT_TBS
STATS$WAITSTAT_PK PERFSTAT_TBS

PACKAGE STATSPACK

PACKAGE BODY STATSPACK

SEQUENCE STATS$SNAPSHOT_ID

SYNONYM V$PROPAGATION_RECEIVER
V$PROPAGATION_SENDER

TABLE STATS$BG_EVENT_SUMMARY PERFSTAT_TBS
STATS$BUFFERED_QUEUES PERFSTAT_TBS
STATS$BUFFERED_SUBSCRIBERS PERFSTAT_TBS
STATS$BUFFER_POOL_STATISTICS PERFSTAT_TBS
STATS$CLASS_CACHE_TRANSFER PERFSTAT_TBS
STATS$CR_BLOCK_SERVER PERFSTAT_TBS
STATS$CURRENT_BLOCK_SERVER PERFSTAT_TBS
STATS$DATABASE_INSTANCE PERFSTAT_TBS
STATS$DB_CACHE_ADVICE PERFSTAT_TBS
STATS$DLM_MISC PERFSTAT_TBS
STATS$ENQUEUE_STATISTICS PERFSTAT_TBS
STATS$EVENT_HISTOGRAM PERFSTAT_TBS
STATS$FILESTATXS PERFSTAT_TBS
STATS$FILE_HISTOGRAM PERFSTAT_TBS
STATS$IDLE_EVENT PERFSTAT_TBS
STATS$INSTANCE_RECOVERY PERFSTAT_TBS
STATS$JAVA_POOL_ADVICE PERFSTAT_TBS
STATS$LATCH PERFSTAT_TBS
STATS$LATCH_CHILDREN PERFSTAT_TBS
STATS$LATCH_MISSES_SUMMARY PERFSTAT_TBS
STATS$LATCH_PARENT PERFSTAT_TBS
STATS$LEVEL_DESCRIPTION PERFSTAT_TBS
STATS$LIBRARYCACHE PERFSTAT_TBS
STATS$OSSTAT PERFSTAT_TBS
STATS$PARAMETER PERFSTAT_TBS
STATS$PGASTAT PERFSTAT_TBS
STATS$PGA_TARGET_ADVICE PERFSTAT_TBS
STATS$PROPAGATION_RECEIVER PERFSTAT_TBS
STATS$PROPAGATION_SENDER PERFSTAT_TBS
STATS$RESOURCE_LIMIT PERFSTAT_TBS
STATS$ROLLSTAT PERFSTAT_TBS
STATS$ROWCACHE_SUMMARY PERFSTAT_TBS
STATS$RULE_SET PERFSTAT_TBS
STATS$SEG_STAT PERFSTAT_TBS
STATS$SEG_STAT_OBJ PERFSTAT_TBS
STATS$SESSION_EVENT PERFSTAT_TBS
STATS$SESSTAT PERFSTAT_TBS
STATS$SESS_TIME_MODEL PERFSTAT_TBS
STATS$SGA PERFSTAT_TBS
STATS$SGASTAT PERFSTAT_TBS
STATS$SHARED_POOL_ADVICE PERFSTAT_TBS
STATS$SNAPSHOT PERFSTAT_TBS
STATS$SQLTEXT PERFSTAT_TBS
STATS$SQL_PLAN PERFSTAT_TBS
STATS$SQL_PLAN_USAGE PERFSTAT_TBS
STATS$SQL_STATISTICS PERFSTAT_TBS
STATS$SQL_SUMMARY PERFSTAT_TBS
STATS$SQL_WORKAREA_HISTOGRAM PERFSTAT_TBS
STATS$STATSPACK_PARAMETER PERFSTAT_TBS
STATS$STREAMS_APPLY_SUM PERFSTAT_TBS
STATS$STREAMS_CAPTURE PERFSTAT_TBS
STATS$SYSSTAT PERFSTAT_TBS
STATS$SYSTEM_EVENT PERFSTAT_TBS
STATS$SYS_TIME_MODEL PERFSTAT_TBS
STATS$TEMPSTATXS PERFSTAT_TBS
STATS$TEMP_HISTOGRAM PERFSTAT_TBS
STATS$THREAD PERFSTAT_TBS
STATS$TIME_MODEL_STATNAME PERFSTAT_TBS
STATS$UNDOSTAT PERFSTAT_TBS
STATS$WAITSTAT PERFSTAT_TBS


126 rows selected.


Próximos passo (artigo), vamos discutir sobre o tempo (janela) que um snapshot deveria tomar para ser eficiente, onde os resultados possam ser tangíveis, quais são os principais tópicos e como interpretá-los.

Abraço,

This page is powered by Blogger. Isn't yours?