Mostrando postagens com marcador oracle. Mostrar todas as postagens
Mostrando postagens com marcador oracle. Mostrar todas as postagens

quarta-feira, 27 de março de 2013

Oracle In-Memory Database Cache


Nesse artigo vamos apresentar o timesten database e mostrar sua funcionalidade que é o timesten in memory cache database. Nessa primeira parte vamos instalar o timesten criar algumas estruturas realizar o cachê de algumas tabelas. No próximo artigo irei explicar melhor os conceitos apresentados aqui e um benchmarking.

Oracle TimesTen In-Memory Database (TimesTen) é utilizado para o gerenciamento de dados em tempo real na camada da aplicação; Utilizamos ele como armazenamento em cache para armazenar tabelas acessadas com frequência de um servidor de banco de dados Oracle para a camada da aplicação(cachê)  mantendo a consistência dos dados armazenados; e um componente de replicação de dados transacionais para garantir a alta disponibilidade entre camadas.

Escrevi esse artigo no portal do GPO, para continuar lendo acesse:

segunda-feira, 30 de julho de 2012

Integrando o Oracle Database com o NoSQL Database MongoDB


Integrando as soluções para problemas de relatórios em bases transacionais

Bases de dados cada vez maiores, grande número de usuários concorrendo por dados e relatórios cada vez mais complexos gerando particionamento de tabelas, visões materializadas e etc. Tem horas que é preciso escalar o banco de dados e dividir transações de relatórios e nessa hora entram em discussão as perguntas, pode? Como? Quanto?

Abraços !!

segunda-feira, 21 de maio de 2012

Muitos Índices em uma Tabela


Fala pessoal vou falar de um assunto que sempre gera alguma discussão nos ambientes em que esse assunto foi levantado

Ultimamente tenho percebido certo padrão dizendo que nenhuma tabela pode ter mais de seis índices ou sete índices em alguns clientes. Então algumas instruções SQL podem executar bem, mas algumas podem não executar tão bem assim e se precisarmos de mais índices não podemos adicionar porque já existem 6 índices então algum teria que morrer para criar mais.

Entendendo que muitas vezes alguns índices podem ser redundantes, podemos pensar no índice IDX_OMT_NAME_ID_01 em (COL1, COL2), IDX_OMT_NAME_ID_02 em (COL1, COL2,COL3), e IDX_OMT_NAME_ID_03 em (COL1, COL2,COL3,COL4).

Nesses casos é comum que alguns DBAs pensem em dropar os primeiros dois índices porque eles são redundantes, ou seja, eles têm as mesmas colunas principais e na mesma ordem, como IDX_OMT_NAME_ID_03. 

Mas dropar índices redundantes pode causar problemas com a seleção de uma tabela de condução de um join. (No post sobre métodos de join eu falo sobre tabelas de condução ou driving tables).

Quando temos muitos índices em uma tabela, não vamos obter um impacto grande em questões de desempenho isso se estamos falando de sistemas OLTP, porque apenas algumas linhas são processadas ​​uma única transação, e o impacto de vários índices de em uma atualização geralmente não é tão grande se comparado a ao desempenho de uma consulta.

Agora um número alto de índices em uma tabela pode ser extremamente prejudicial para ambientes com grande número de processos batch de atualização, ou seja com um número alto de insets, updates e deletes.


Veja um leve comparativo:
Quantidade Linhas
Quantidade de Índices
Tempo
1000
0
00:00:00.41
10000
0
00:00:02.99
10000
1
00:00:05.88
10000
3
00:00:07.82
20000
5
00:00:59.69

Da para imaginar o problema quando temos uns 8 índices de uma tabela com alguns GB de tamanho, e inserindo mais alguns GB em um processo batch.

Um workaround para esse problema é dropar on índices antes da execução da batch e recriar os mesmos após a execução . Com opções como NOLOGGING e PARALLEL é possível reconstruir um índice com mais velocidade, porém mesmo com esses recursos o trabalho de reconstruir um índice pode demorar as vezes mais que o próprio processo batch se for o caso.

Minha experiência me diz que o melhor é não criar regras em relação a um número especifico de índices que uma tabela possa ter, porque literalmente cada caso é um caso.O melhor é sempre analisar a relação de custo e beneficio de um novo índice e o Oracle nos ajuda bastante porque podemos usar recursos como o ALTER INDEX MONITORING USAGE  para monitorar e verificar a eficiência e uso dos índices.

sexta-feira, 11 de maio de 2012

Como converter Oracle DB para MongoDB

Fala Pessoal,


Tenho pesquisado e estudado bastante as soluções NoSQL depois que grandes empresas passaram a integrar essa tecnologia como solução de escalabilidade para seus problemas. Discordo totalmente da visão de alguns profissionais os quais acreditam na total substituição dos bancos relacionais pelos NoSQL, isso para mim é sonho de programador que nunca soube escreve uma linha em SQL.
Mas acredito que é uma grande solução que pode ser integrada em quase todos os negócios junto com um banco relacional e outras soluções de cache e motor de busca, gerando assim escalabilidade, exemplos de arquitetura que integram as duas tecnologias como facebook e twitter mostram isso claramente. 


Usando como base a idéia do Jean Nascimento que criou um conversor de MySQL para MongoDB.
que esta disponivel no link :


http://imasters.com.br/artigo/17078/mongodb/como-converter-mysql-para-mongodb

Tive a idéia de criar um conversor de Oracle para MongoDb utilizando o PHP.


O MongoDB é uma aplicação de código aberto, de alta performance, sem esquemas, orientado a documentos. Foi escrito na linguagem de programaçãoC++. Além de orientado a documentos, é formado por um conjunto de documentosJSON. Muitas aplicações podem, dessa forma, modelar informações de modo muito mais natural, pois os dados podem ser aninhados em complexas hierarquias e continuar a ser indexáveis e fáceis de buscar. (Font Wikipedia)

Eu já vinha testando o MongoDB ha algum tempo e realmente é rápido e simples, não vejo sentido em benchmarks com um bancos relacionais pois acredito que em focos diferenciados. Acredito que o MongoDB é excelente para tudo o que não precisa ser relacional.


No link do Jean Nascimento acima tem a explicação para quem deseja instalar o MongoDB.


Segue o Link do conversor:


http://code.google.com/p/sql-hudson/downloads/list


segunda-feira, 21 de novembro de 2011

Habilitar DDL no alert.log Oracle

Fala pessoal, o 11g tem um recurso interessante que é habilitar o log de comandos DDLs no alert.log, para habilitar é bem simples basta alterar o parâmetro ENABLE_DDL_LOGGING para true, segue abaixo um pequeno step by step que altera o parâmetro e cria alguns objetos para teste.


COLUMN DESCRIPTION FORMAT A30
COLUMN VALUE FORMAT A30
SET LINESIZE 120

SQL> select value,isdefault,isses_modifiable, issys_modifiable,isbasic,description  
from v$parameter  where name='enable_ddl_logging';

VALUE  ISDEFAULT ISSES ISSYS_MOD ISBAS DESCRIPTION
------ --------- ----- --------- ----- -------------------
FALSE   TRUE    TRUE  IMMEDIATE FALSE enable ddl logging

SQL> alter system set ENABLE_DDL_LOGGING=true ;

System altered.

SQL> conn hudson/c402d92

Connected

SQL> create table t_hudson as select * from user_tables;

Table created.

SQL> grant select on t_hudson to public;

Grant succeeded.

SQL> alter table t_hudson read only;

Table altered.

SQL> drop table t_hudson ;

Table dropped.

Após a alteração do parâmetro vamos verificar se foi gerado o log no alert.log:


[oracle@rjhud trace]$ tail -f alert_centro.log

Mon Nov 21 15:58:43 2011
SMCO started with pid=31, OS id=5981
Mon Nov 21 16:08:37 2011
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Mon Nov 21 16:09:41 2011
create table t_hudson as select * from user_tables
Mon Nov 21 16:10:09 2011
alter table t_hudson read only
drop table t_hudson


Para maiores informações sobre o parâmetro é só consultar o link:

 http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/initparams078.htm

sexta-feira, 18 de novembro de 2011

Exportar linhas sem dump ou backup no Oracle

Muitas vezes é necessário obter um número pequenos de linhas de algumas ou todas tabelas de um ambiente de produção, mas nem sempre um dump ou um backup esta disponível ou ainda o tempo de demora pode atrasar muito uma atuação, quem trabalha em consultorias, fabricas e qualquer lugar onde ambiente de produção é um lugar onde você não pode entrar em 1000 seguranças do seu lado sabe do que eu estou falando.
Então um meio muito utilizado é gerar "Inserts" concatenando via selects os famosos :

SELECT 'INSER INTO ..... '  || COLUMNS  .....
Dessa forma é possível obter os dados da tabela de uma forma simples e rápida, o problema é o tempo gasto na hora de gerar esses selects, principalmente se estamos falando de uma tabela com muitas colunas, ou se você precisa de muitas tabelas.
Pensando nisso eu fiz uma package que gera esses selects dinamicamente , recebendo como argumentos o nome de uma tabela com o número de linhas que deseja, ou passando diretamente um onwer e as linhas que precisa.
Óbvio que esse método só é útil quando precisa de um número pequeno de linhas e não tem em mãos outras ferramentas.

Abaixo o código de criação das packages :


CREATE OR REPLACE PACKAGE PKG_DUMP AS

  PROCEDURE P_DUMP(V_OWNER VARCHAR2,V_ROWS INT);
  FUNCTION V_DUMP(v_table_name VARCHAR2,V_ROWS INT) RETURN VARCHAR2;

END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DUMP AS

PROCEDURE P_DUMP(V_OWNER VARCHAR2,V_ROWS INT)
IS
  V_SMT VARCHAR2(3000);
  V_TABLE_NAME VARCHAR2(100);
  CURSOR trip_cursor IS SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=V_OWNER AND TABLE_NAME IN 
  ('ACTION','ROLE','ROLE_ACTION','URL','MENU');
BEGIN
  FOR trip_record IN trip_cursor LOOP
  SELECT V_DUMP(trip_record.TABLE_NAME,V_ROWS) INTO V_SMT FROM DUAL;
  V_TABLE_NAME := trip_record.TABLE_NAME;
  dbms_output.new_line();
  dbms_output.new_line();
  DBMS_OUTPUT.PUT_LINE('/************* ' || V_TABLE_NAME || '***************/');
  dbms_output.new_line();
  DBMS_OUTPUT.PUT_LINE(V_SMT);
  END LOOP;
--EXECUTE IMMEDIATE V_SMT ;
EXCEPTION
    WHEN OTHERS THEN
    PKG_ERR.PRC_HANDLE(-20001, 'Problems in P_DUMP  ' || V_TABLE_NAME );
END P_DUMP;

FUNCTION V_DUMP(v_table_name VARCHAR2,V_ROWS INT) 
RETURN VARCHAR2
IS

  v_column_name VARCHAR2(5000);
  v_column_name2 VARCHAR2(5000);
  v_column_name3 VARCHAR2(5000);
  v_column_name4 VARCHAR2(5000);
  v_column_name5 VARCHAR2(5000);
  V_SMT VARCHAR2(5000);
   
  CURSOR trip_cursor IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=v_table_name ORDER BY COLUMN_ID;

BEGIN

  FOR trip_record IN trip_cursor LOOP
  v_column_name2 := trip_record.column_name ;
  v_column_name := v_column_name || ',' || v_column_name2;

  END LOOP;
  SELECT REGEXP_REPLACE(v_column_name, ',' , '' ,1,1)  into v_column_name3 FROM DUAL;
  --SELECT REGEXP_REPLACE(v_column_name, ',' , '|| '',''||' ,2) into v_column_name4 FROM DUAL;
  SELECT REGEXP_REPLACE(v_column_name, ',' , '|| '''''''' || '',''||'''''''' ||' ,2) into v_column_name4 FROM DUAL;
  SELECT REGEXP_REPLACE(v_column_name4, ',' , ',''''||''''''''||' ,1,1)  into v_column_name5 FROM DUAL;
  --DBMS_OUTPUT.PUT_LINE(v_column_name);
  V_SMT:= ' SELECT ''INSERT INTO ' || v_table_name || ' ( ' || v_column_name3 || ' ) VALUES (''' || v_column_name5
  || '||'''''''' ||'');''  FROM ' || v_table_name || ' WHERE ROWNUM < ' || V_ROWS || ';';
  --EXECUTE IMMEDIATE V_SMT ;
  --DBMS_OUTPUT.PUT_LINE(V_SMT);
  RETURN V_SMT;
EXCEPTION
    WHEN OTHERS THEN
    PKG_ERR.PRC_HANDLE(-20001, 'Problems in V_DUMP  ' || v_table_name );
END V_DUMP;
END PKG_DUMP;
/
Agora as formas de Utilizar a package, a primeira forma é gerar o insert de apenas uma tabela. Primeiro executando a function PKG_DUMP.V_DUMP passando o nome da tabela e depois o número de linhas.
new@ORACLE> SELECT PKG_DUMP.V_DUMP('TESTE',29) FROM DUAL;
 SELECT 'INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (',''||''''||X|| '''' || ','||'''' ||C|| '''' || ','||'''' ||H|| '''' || ','||'''' ||G|| '''' || ','||'''' ||HG||'''' ||');'  FROM TESTE WHERE ROWNUM <
 29;
Após o resultado executa o select gerado e já se tem o insert pronto.
new@ORACLE>  SELECT 'INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (',''||''''||X|| '''' || ','||'''' ||C|| '''' || ','||'''' ||H|| '''' || ','||'''' ||G|| '''' || ','||'''' ||HG||'''' ||');'  FROM TESTE WHERE ROWNUM <
 29;  2
INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'1','2','1','1','A');

INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'4','5','1','1','A');

INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'4','5','1','1','A');
A outra forma é tendo todas as tabelas, executando a procedure PKG_DUMP.P_DUMP passando o owner e o número de linhas:
new@ORACLE> set pagesize 0
set long 90000
set linesize 200
set trimspool ON
set feedback off
set echo off 

new@ORACLE>EXECUTE PKG_DUMP.P_DUMP('NEW',100);
Ele vai gerar o output de todos os selects para geraçao de inserts de todas as tabelas que esse owner possui, é aconselhável utilizar um spool para receber esse conteúdo.

That's all folks 

quarta-feira, 3 de agosto de 2011

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Estava agora configurando uma base nova, antes de passar ela para ARCHIVELOG no momento que estava configurando o diretório dos archives recebi o famoso:


SQL> Alter system set log_archive_dest_1='/u01/app/oracle/archives/iraja';

ORA - 16179 : incremental changes to "log_archive_dest_1" not allowed with

Meu cenário era um Oracle Database 11G R2 com um Red Hat 5.5.

A Solução é bem simples galera e eu achei no metalink que tem o Doc ID: 194494.1.
Só adicionar a palavra LOCATION antes do caminho do diretório.


SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archives/iraja' ;

System altered.


That's all folks

terça-feira, 2 de agosto de 2011

Problema com DISPLAY no VirtualBox com Oracle

Post simples e com muito conteúdo na Internet, mas que de vez em quando me dava trabalho e eu não vi/não achei nada tão simples na internet.
Problema :

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Cenário:
VirtualBox 4.4 Instalando o Oracle Database 11G R2

Soluçao :
Como root executar :
xhost +localhost

Depois loga com o oracle na mesma sessão e zaz(Como diria o chaves).

That's all folks !!

quinta-feira, 19 de maio de 2011

Matar job do Datapump Oracle

Pessoal vou passar um dica, quando se precisa matar um job do datapump. Quando você esta executando o export ou import do datapump, nao basta dar Control-C para parar esse job, quando se faz isso apenas pausa o job, em alguns casos nem isso. Vou listar o Procedimento para eliminar realmente o Job.

 1) Verifique as informações para eliminação do job.
SELECT OWNER_NAME,JOB_NAME FROM DBA_DATAPUMP_JOBS;
2) Saia do sqlplus e entre no datapump novamente com o commando abaixo:
impdp username/password@database attach=nome_do_job
3) Elimine o job com o commando :
$ Import>kill_job
Are you sure you wish to stop this job (y/n): y
4) Verifique se o job realmente foi eliminado
SELECT OWNER,JOB_NAME FROM DBA_DATAPUMP_JOBS;

sexta-feira, 13 de maio de 2011

Gerar Schema (DDL) no Oracle sem Datapump ou Exp


Vou disponibilizar um script que fiz, para situações que voce precisa exportar um schema só com ddl e sem os dados.
Ok, o datapump e o exp podem fazer isso perfeitamente, mas existem situações onde você não tem acesso a essas ferramentas e/ou nao pode esperar o DBA do cliente disponibilizar isso para voce.
Além disso esse script não traz nenhuma definição de tablespace, armazenamento,ele separa a criação de constraints para o final, podendo ser executado novamente para a criação do schema depois direto, sem se preocupar com a ordem da criação dos objetos em relação a integridade.
Ele também exporta procedures,triggers,sequences,views .
Enfim uma mão na roda quando não se tem acesso a quase nada e precisa de informações em tempo rápido.
Para acessar o link, va no meu outro blog, lá o código esta formatado .
-- Comments and Script Documentation
-- Script Name   : extract_schema_ddl.sql
-- Requirements  : User calling script must be owner the objects, and have privileges the package
     DBMS_METADATA. 
-- Author        : HUDSON SANTOS
-- Created Date  : 05/05/2011


set pagesize 0
set long 90000
set linesize 32767
set trimspool ON
set feedback off
set echo off 
column txt format a121 word_wrapped

BEGIN
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); 
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
END;
/


spool schema.sql 

-- TABELAS

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) txt
     FROM USER_TABLES u ;

-- CONSTRAINTS R1

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',u.constraint_name) txt
     FROM USER_constraints u WHERE CONSTRAINT_TYPE IN ('P','C');

-- CONSTRAINTS R2

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',u.constraint_name) txt
     FROM USER_constraints u WHERE CONSTRAINT_TYPE IN ('R');

-- INDICES 
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) txt
     FROM USER_INDEXES u ;

-- TRIGGERS

SELECT dbms_metadata.get_ddl('TRIGGER', u.trigger_name) txt
 FROM USER_TRIGGERS u;

-- PROCEDURES

SELECT dbms_metadata.get_ddl('PROCEDURE', u.object_name) txt
 FROM USER_objects u WHERE OBJECT_TYPE='PROCEDURE' ;

-- FUNCTIONS

SELECT dbms_metadata.get_ddl('FUNCTION', u.object_name) txt
 FROM USER_objects u WHERE OBJECT_TYPE='FUNCTION' ;

-- SEQUENCES

SELECT dbms_metadata.get_ddl('SEQUENCE',u.SEQUENCE_NAME) txt
 FROM USER_SEQUENCES u;

-- VIEWS

SELECT dbms_metadata.get_ddl('VIEW',u.VIEW_NAME) txt
 FROM USER_VIEWS u;

-- VIEW_MATERIALIZED

SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW',u.MVIEW_NAME) txt
 FROM USER_MVIEWS u;

--PACKAGE

SELECT dbms_metadata.get_ddl('PACKAGE',u.object_name) txt
 FROM USER_OBJECTS u WHERE OBJECT_TYPE='PACKAGE';

--PACKAGE_BODY

SELECT dbms_metadata.get_ddl('PACKAGE_BODY',u.object_name) txt
 FROM USER_OBJECTS u WHERE OBJECT_TYPE='PACKAGE_BODY';


spool off;


quinta-feira, 7 de abril de 2011

Métodos de Joins no Oracle Parte III


HASH JOIN
Os dois conjuntos de dados processados por um Hash Join são chamados de build input e probe input. A build input é a esquerda, e a probe input é a entrada à direita. Como ilustrado na Figura abaixo, usando cada linha da entrada da build input, uma tabela hash na memória (ou espaço temporário, se não há memória suficiente está disponível) é construída.
Observe que a chave de hash usada para esse fim é calculada com base nas colunas usadas como condição de join. Uma vez que a tabela hash contém todos os dados do build input, o processamento da probe input começa.
Cada linha é testada contra a tabela hash, a fim de descobrir se ele cumpre a condição de join. Obviamente, apenas registros coincidentes são retornados.


                * Visão do processo de um Hash Join

Hash Joins são caracterizados pelas seguintes proriedades :
·         Cada operação Filha só é executada uma vez
·         A tabela hash é construída sobre a entrada da esquerda. Por conseqüência , é geralmente construída na menor entrada.
·         Antes de retornar a primeira linha, somente a entrada deesquerda deve ser totalmente processado.
·         Cross joins, theta joins e partitiouned outer joins não são suportados.
Exemplo de Hash Join.
O exemplo a seguir é um simples plano de execução mostrando o processamento de um Hash Joins entre duas tabelas, O exemplo também mostra como forçar uma hash join por meio das dos hints leading  e use_hash.

SQL_IRAJA  > SELECT /*+ leading(t1) use_hash(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  1180 |    30   (4)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    10 |  1180 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    57 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   100 |  6100 |    24   (0)| 00:00:01 |
---------------------------------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=19)

 O processamento do plano de execução pode ser resumido da seguinte forma  :

·         Todas as linhas da tabela t1 são lidas através de um full table scan, a restrição n = 19 é aplicada, e uma tabela de hash é construído com as linhas resultantes.  Para construir a tabela de hash, uma função hash é aplicada para as colunas usadas na condição de join (id).
·         Todas as linhas da tabela T2 são ligas através de um full table scan, uma função hash é aplicada para as colunas usadas na condição de join (t2_id) e a tabela de hash entra no modo probed. Se for encontrado os dados correspondentes as linhas são retornadas

A limitação mais importante para a operação HASH JOIN (como para as outras operações combinadas não relacionadas) é a incapacidade de tirar proveito dos índices para aplicar nas condições de join. Isto significa que os índices podem ser utilizados como caminho de acesso somente se as restrições estão disponíveis. Por exemplo, se a restrição n = 19 fornece boa seletividade, pode ser útil para criar um índice para aplicá-la.

SQL_IRAJA > CREATE INDEX t1_n ON t1 (n)

In fact, with this index in place, the following execution plan might be used. Note that the
table t1 is no longer accessed through a full table scan.

Na verdade, com esse índice em vigor, o plano de execução a seguir pode ser usado. Observe que a tabela t1 já não é mas acessada através de um full table scan.

-------------------------------------------------------------------------------------                                                                                                                                                          
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                          
-------------------------------------------------------------------------------------                                                                                                                                                          
|   0 | SELECT STATEMENT             |      |    10 |  1180 |    27   (4)| 00:00:01 |                                                                                                                                                           
|*  1 |  HASH JOIN                   |      |    10 |  1180 |    27   (4)| 00:00:01 |                                                                                                                                                           
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    57 |     2   (0)| 00:00:01 |                                                                                                                                                           
|*  3 |    INDEX RANGE SCAN          | T1_N |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                           
|   4 |   TABLE ACCESS FULL          | T2   |   100 |  6100 |    24   (0)| 00:00:01 |                                                                                                                                                           
-------------------------------------------------------------------------------------                                                                                                                                                           
                                                                                                                                                
   1 - access("T1"."ID"="T2"."T1_ID")                                                                                                                                                                                                          
   3 - access("T1"."N"=19)    









segunda-feira, 24 de janeiro de 2011

Métodos de Joins no Oracle Parte I


Depois muito tempo sem freqüentar e atualizar o blog , neste ano pretendo ser mais ativo na comunidade ,tendo tirado o ano passado para novas experiências profissionais infelizmente não tive tempo de aparecer por aqui, porém agora podem esperar novas atualizações.
E volto falando de um assunto que sempre me perguntam que é como o oracle realiza joins com mais de uma tabela.

NESTED LOOPS (Loops aninhados)
Definição
Os registros da primeira tabela são recuperados, e para cada registro recuperado, o acesso é realizado na segunda tabela.
NESTED LOOPS são úteis quando pequenos subconjuntos de dados estão sendo juntados, e se a condição de join é uma forma eficiente de acesso à segunda tabela.
O acesso da segunda tabela é feito através de um acesso baseado em índice (index-based access).

Um hint é usado para recomendar um acesso baseado em índice da tabela Livros no exemplo abaixo:

select /*+ INDEX(Livros) */
Livros.Publisher,
Livros_autor.AuthorName
from Livros, Livros_autor
where Livros.Title = Livros_autor.Title;

A coluna title da tabela Livros é utilizada como parte da condição de join na consulta, o índice de chave primária pode resolver esse join.
Quando a consulta é executada, uma operação de NESTED LOOP pode ser usada para executar esse join.
Para executar um join de NESTED LOOP, o otimizador deve primeiro selecionar uma tabela de condução da junção, que é a tabela que será lida primeiro (geralmente através de uma operação de TABLE ACCES FULL, embora index scans sejam muito utilizados). Para cada registro na tabela de condução, a segunda tabela na junção será consultada. A consulta de exemplo junta as tabelas Livros_autor e Livros, com base nos valores da coluna de title. Durante a execução de NESTED LOOPS, uma operação irá selecionar todos os registros da tabela Livros_autor. O índice de chave primária da tabela Livros será investigado para determinar se ele contém uma entrada para o valor do registro atual da tabela Livros_autor. Se uma correspondência for encontrada, então o valor de Title será retornado da chave primária de Livros. Se outras colunas são necessárias para Livros, a linha será selecionada da tabela Livros através de uma operação de TABLE ACCESS BY INDEX ROWID.

Então podemos resumir que um NESTED LOOP envolve as seguintes etapas:

1_ O otimizador determina a tabela de condução e designa-o como uma tabela externa.
2_ A outra tabela é designada como a tabela interna.
Para cada linha na tabela externa, o Oracle permite acesso a todas as linhas na tabela interna.

O outer loop é para cada linha na tabela externa e o inner loop é para todas as linhas na tabela interna.
O outer loop aparece antes do inner no plano de execução, como segue:

Agora vamos mostrar o plano de execução desta consulta para esclarecer a sua mente.

EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 165 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 165 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 123 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 48 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 25 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 14 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"<103)
6 - access("E"."JOB_ID"="J"."JOB_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Neste outro exemplo, o outer loop recupera todas as linhas da tabela de employees.
Para cada linha da tabela employees recuperada pelo outer loop, o inner loop recupera a linha associada na tabela jobs.

Outer loop
Nesse exemplo a consulta abaixo seria equivalente ao que o outer loop executa com uma tabela de conducao.
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.employee_id < 103;

Inner loop
O plano de execução mostra o inner loop sendo iterado para cada linha obtida
a partir do outer loop, como segue:
SELECT j.job_title
FROM jobs j
WHERE e.job_id = j.job_id ;

É muito importante garantir que a tabela interna é conduzida a partir da da tabela externa.
Se o caminho de acesso a tabela interna é independente da tabela externa e em seguida as mesmas linhas são recuperadas para cada iteração de outer loop o desempenho vai cair consideravelmente.
Em tais casos, operaçoes de *hash joins juntam duas fontes independentes com um melhor desempenho.

*Hash join será abordado brevemente

Em operações de acesso a pelo menos duas fontes de dados que estão envolvidos joins com um NESTED LOOPS, vai ocorrer um acesso na tabela de condução(tabela principal) e um acesso normalmente baseado em índice da tabela dirigida .
Os métodos de acesso de dados mais comuns usados são o TABLE ACCESS FULL, TABLE ACCESS BY INDEX ROWID e INDEX-SCAN eles retornam os registros para às operações sucessivas logo que um registro for encontrado, eles não esperam por todo o conjunto de registros selecionados. Porque essas operações podem fornecer o primeiro conjunto de linhas rapidamente para os usuários, Nested Loops são comumente usados para joins que são freqüentemente executados por usuários online.
Ao implementar joins com NESTED LOOPS, você precisa considerar o tamanho da tabela de condução. Se a tabela de condução é grande e é lida através de um FULL TABLE SCAN, então o FULL TABLE SCAN realizado pode afetar negativamente o desempenho da consulta. Se os índices estão disponíveis em ambos os lados do join o Oracle vai selecionar uma tabela de condução para a consulta e o otimizador irá verificar a estatísticas do tamanho das tabelas e a *seletividade dos índices para escolher o caminho com o menor custo global.

* A seletividade é um valor entre 0 e 1 que representa a fração de linhas filtradas por uma operação.
Por exemplo, se uma operação de leitura lê 120 linhas de uma tabela e depois de aplicar um filtro(where) retorna 18 linhas, a seletividade é de 0,15 (18/120). A seletividade também pode ser expressa como um percentual de 0,15 que é expresso como 15 por cento.

Ao juntar três tabelas, o Oracle realiza dois joins distintos: O join de duas tabelas para gerar um conjunto de registros e em seguida um join entre o conjunto de registros e a terceira tabela.
Se NESTED LOOP são utilizados a ordem na qual as tabelas são unidas é crítica.
A saída do primeiro join gera um conjunto de registros e esse conjunto de registros é usado como uma tabela de condução da segunda junção.
O Tamanho do conjunto de registros retornado pelo primeiro join impacta no desempenho do segundo join e assim tem um impacto significativo sobre o desempenho da consulta geral. Você deve tentar unir as tabelas mais seletivas primeiro para que o impacto desses proximos joins sejam insignificantes.
Se tabelas grandes sao unidas no primeir join de uma consulta multijoin, o tamanho das tabelas terá um impacto sucessivos no join e irá impactar negativamente o desempenho global da consulta.
O otimizador deve escolher o caminho adequado para executar o join ,você pode confirmar os custos e o caminho gerado pelo plano de execução.
NESTED LOOPS são úteis quando se criam joins com tabelas de tamanhos diferente assim você pode usar a menor tabela como a tabela de condução e selecione na tabela maior com um acesso baseados em índices.
Quanto mais seletivo o índice, mais rápido a consulta será concluída.

Um join com quatro Tabelas

O plano de execução que se segue é um exemplo de uma árvore típica de profundidade à esquerda, implementado com NESTED LOOP .
Observe como cada tabela é acessada por meio de índices.
O exemplo também mostra como forçar um loop aninhado usando hints ordered e use_nl.
O exemplo especifica o acesso as tabelas na mesma ordem em que aparecem na cláusula FROM.
O último especifica qual método join é usado para juntar as outras tabelas para a primeira tabela .

SELECT /*+ ordered use_nl(t2 t3 t4) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 1 | NESTED LOOPS | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 2 | NESTED LOOPS | | 100 | 18000 | 116 (0)| 00:00:02 |
| 3 | NESTED LOOPS | | 10 | 1180 | 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 57 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 610 | 10 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T2_T1_ID | 10 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 | 10 | 620 | 11 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T3_T2_ID | 10 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | T4 | 10 | 640 | 11 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | T4_T3_ID | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=19)
7 - access("T1"."ID"="T2"."T1_ID")
9 - access("T2"."ID"="T3"."T2_ID")
11 - access("T3"."ID"="T4"."T3_ID")

O Processamento deste tipo de plano de execução pode ser resumido da seguinte forma

1.A primeira linha é obtida , não quando a consulta é analisada ou executada, o processamento começa por obter a primeira linha que atende o filtro t1.n = 19 da tabela t1.
2.Com base nos dados encontrados na tabela t1, a tabela t2 é procurada. Perceba que o Oracle aproveita a condição do join t1.id = t2.t1_id para acessar a tabela t2. Na verdade, nenhuma restrição é aplicada a essa tabela. Apenas a primeira linha que atende a condição do join é restituída à operação principal.
3.Com base nos dados encontrados na tabela t2, a tabela t3 é procurada. Também neste caso, o oracle tira proveito de uma condição de join, t2.id = t3.t2_id, para acessar a tabela T3. Apenas a primeira linha que preenche a condição de join é devolvida para a operação pai.
4.Com base nos dados encontrados na tabela t3, a tabela t4 é procurada. Aqui novamente o Oracle se aproveita de uma condição de join, t3.id = t4.t3_id, para acessar a tabela T4. A primeira linha que cumpre a condição de join é imediatamente devolvida ao cliente.
5.Quando as linhas subseqüentes são buscadas, as mesmas ações são realizadas para as buscar as seguintes.

Obviamente, o processo é reiniciado a partir da posição do ultimo resultado (que poderia ser a segunda linha que corresponde na tabela t4, se houvesse). É essencial destacar que os dados são devolvidos assim que uma linha que atende à solicitação é encontrada. Em outras palavras, não é necessária à plena execução do join antes de retornar a primeira linha.
No próximo post eu vou abordar como os MERGE JOIN são realizados.

quarta-feira, 23 de junho de 2010

A Luz no fim do túnel ... no Oracle

Perdeu os archives ???
Perdeu o backup ???
Não tem mais jeito ???
Demissão ???

È pessoal pode ser que ainda exista a última esperança.

_allow_resetlogs_corruption = true

Com esse parametro no spfile, pode ser que  o banco abra resetando os logs , ai você recupera os dados e reza, mas raza muito pra ninguém descobrir o que realmente aconteceu.

Bem não preciso nem dizer que esse é um parâmetro não documentado, que a chance de dar errado é grande, e se você ferrou seu ambiente de produção pra testar se ele funciona, boa demissão!!!

Sem brincadeiras, já testei o parâmetro em um ambiente de testes, fiz o backup, deletei todos os archives e exclui alguns datafiles, acrescentei ele no spfile e dei um alter database open resetlogs e ele subiu.

Já presenciei outra situação na qual ele também funcionou, mas espero que nem eu nem vocês nunca tenham que usá-lo pra valer.
Abraços!!

domingo, 13 de junho de 2010

Cache Hit Ratio Oracle

A Buffer Cache é um recurso compartilhado, acessível por todos os usuários.
Quando um usuário emite uma consulta , O Oracle antes de ir nos datafiles buscar os blocos necessários para retornar o resultado, primeiro olha para os blocos de dados na buffer cache.
Se os dados colocados no cache são retornados para o solicitante imediatamente .
Ocorre o famoso Hit Ratio .
Quando os dados não forem encontrados, um cache miss ocorre e o processo do usuário irá lêr os dados do disco para um buffer disponível no cache.
A taxa de acerto do cache é a percentagem total de solicitações de dados que são servidos diretamente a partir da buffer cache.
No Oracle, o buffer cache hit ratio normalmente é calculado utilizando a seguinte fórmula:
Cache Hit Ratio = 100 * (1 - leituras físicas / lógicas leituras)
Nesta fórmula, "leituras físicas", corresponde à falta de cache e 'leituras lógicas "corresponde ao total de solicitações de dados.
Ajustando o buffer cache para optimizar o desempenho normalmente ocorre a adição de buffers no cache até que a taxa de acerto foi maximizada.
O número de buffers no cache é especificado pelo parâmetro de inicialização DB_BLOCK_BUFFERS.
Abraços !!

quarta-feira, 2 de junho de 2010

Tempo de Recuperação e outros parâmetros no Oracle

Fala Pessoal,
A visão V$Instance_Recovery merece destaque nesse assunto, as principais colunas são:
recovery_estumated_ios - > O número de operações de i/o nos datafiles que seriam necessárias para a   recuperação se a instância falhasse.
actual_redo_blocks - > O número de blocos de redo do sistema operacional que se precisariam ser aplicados aos datafiles para a recuperaçõ se a intância falhasse agora.
estimated_mttr - > O número de segundos necessários para abrir o banco de dados caso ele falhasse agora.
writes_mttr -> O número de vezes que o DBWn teve de gravar, além das gravações que ele normalmente faria, para atingir a meta do mttr.
Enfim uma mão na roda......

quarta-feira, 12 de maio de 2010

Tempo médio de recuperação de uma Instância - MTTR- Oracle

Fala Pessoal,
Hoje eu vou falar sobre falha e recuperação de uma instância.
Dizemos que um banco de dados esta corrompido quando o banco é finalizado por uma falha ou um shutdown abort , armazenando transações sem commit. Quando ocorre isso na hora da inicialização da instância o oracle irá aplicar o conteudo dos Redo logs para recuperar a instância, vai usar o conteúdo dos arquivos de redo log para reconstruir o cache do banco de dados para o estado que ele estava antes da falha.
O MTTR (mean time to recover)  - Tempo médio de recuperação
A Recuperação da instância garante corrupção zero, mas até todas as alterações dos arquivos de Redo tenham sido aplicadas nos blocos de undo e nos blocos de dados, vamos ter um custo de I/O referente aos arquivos de dados a medida que o redo é aplicado. Esses fator pode ser controlado pelo checkpoint.
O Checkpoint , vai garantir que apartir de uma certa hora, as alterações dos dados, que compõem um SCN específico tenham sido aplicadas nos datafiles pelo DBWn. Quanto mais atualizada esta a posição do checkpoint, mais rápida vai ser a recuperação da instância, porque não há necessidade de aplicar o redo nas transações que sofreram commit antes de uma falha.
Avançamos a posição do checkpoint quando gravamos gravamos os blocos alterados no disco, o que gera I/O . Porém se DBWn ficar muito atrasado de uma forma com que caso ocorra um falha SMOn tenha que fazer um I/O muito grande nos datafiles para aplicar as alterações do redo,isso vai gerar um tempo de recuperação da instância muito grande, ou seja o MTTR vai subir.
Para ajudar a controlar essa questão temos o parâmetro FAST_START_MTTR_TARGET. Esse parâmetro é especificado  em segundos e  ajuda a controlar o tempo de recuperação de uma instância. Esse parâmetro vem configurado como default 0, caso esse valor seja modificado , vai ser ativado o autoajuste do checkpoint, o que força uma analise das estatísticas sobre os recursos e utilização da máquina, e caso houver capacidade extra , esta será usada para gravar os buffers sujos adicionais do database buffer cache, fazendo assim a posição do checkpoint subir.

sexta-feira, 7 de maio de 2010

O que um DBA deve saber ....

Certa vez lendo algum artigo na internet achei essas recomendações ....
Para que o DBA possa exercer adequadamente o seu papel e cumprir com suas responsabilidades é importante que ele detenha algumas competências:
*Conhecimentos em Sistemas Operacionais: Um banco de dados é totalmente dependente do sistema operacional onde está instalado.
É fundamental que o DBA conheça conceitos ligados ao sistema operacional (processos, threads, gerenciamento de memória, paginação, sistema de arquivos, etc)
para poder adequar o máximo possível o sistema operacional ao banco de dados utilizado.
*Conhecimentos em Redes: É desejável que o DBA conheça características da rede (capacidade de tráfego, protocolos, etc).
*Compreensão em arquitetura em banco de dados: Entender como funciona um banco de dados é um pouco mais do que conhecer uma tecnologia específica
(ORACLE, DB2, SQL Server, etc).
Entender alguns dos andamentos de banco de dados (algoritmos de indexação, concorrência, transações, etc) podem ser tão valiosos quanto conhecer
as implementações de um produto específico.
*Noções do sistema de armazenamento: É importante ao DBA ter conhecimento dos princípios que são utilizados nos sistemas de armazenamento (RAID, SAN, etc).
Esse conhecimento pode ajudar o DBA a utilizar a infra-estrutura de armazenamento para um projeto físico eficiente.
*Manuseio do XML: Esse padrão já possui uma forte aceitação e cada vez mais os profissionais de banco de dados têm de conviver com ele.
É importante que o DBA conheça XML e suas tecnologias correlatas (XSD, Web Services, etc).
*Noções de ETL (Extract, Transform, Load*.*): Com o crescente mercado de Business Inteligence é esperado o aumento de bases multidimensionais utilizando diversas tecnologias
de banco de dados (XML, Flat Files, banco de dados relacionais, banco de dados orientados a objeto, etc).
É importante ao DBA conhecer meios para dar suporte a rotinas de extração, transformação e carga no projeto de Data Warehouse além de monitoramento das consultas.

segunda-feira, 3 de maio de 2010

O Processo de Commit

Fala Pessoal,estava lendo um artigo sobre performance o qual explicava sobre o processo de commit e explicava o conceito do termo SCN, achei interessante e resolvi publicar aqui no blog.
O Banco de Dados Oracle usa um rapido mecanismo de de commit o qual garante que as mudanças efetivadas possam ser recuperadas em caso de falhas da instância.

 SCN (System Change Number) Número de mudança do Sistema.

 Sempre que uma transação sofre commit, o banco de dados Oracle atribui SCN para a transação. O SCN é constantemente incrementado e é único dentro do banco de dados. Ele é usado pelo banco de dados Oracle como um carimbo de tempo interno para sincronizar os dados e fornecer consistência de leitura quando os dados são obtidos a partir dos arquivos de dados. Usando o SCN habilitamos o banco de dados oracle para executar verificações de consistência sem depender da data e da hora do sistema operacional.
Quando o commit é emitido, as seguintes etapas são realizadas:
  1. Processo do servidor coloca um registro de commit, com o SCN, no buffer redo log.
  2. O Processo de background Log Writer (LGWR) realiza uma constante escrita de todo Redo Log buffer até e inclusive o registro de commit para os arquivos de Redo Log .Após esse ponto o banco de dados já pode garantir que as alterações não serão perdidas mesmo se houver uma falha na instancia.
  3. O Processo do servidor fornece um retorno ao usuário sobre o processo de realização da transação.
 Em Algumas vezes o Processo de background DBWR  escreve as reais mudanças de volta para o disco baseado  em um próprio sistema de temporalização interno.
Fonte :Oracle Database 10g :SQL Tuning - Priya Vennapusa