Tuning na Instruçao SQL LIKE no Oracle

Fala pessoal, vou postar aqui um dica simples, que é quando se precisa de um tunning rápido nas declarações "LIKE"  que o CBO naturalmente ignora os índices existentes nas colunas utilizadas pelo LIKE para uso de um FTS. Quando ocorre isso temos o recurso de utilizar um hint forçando a utilização do índice.

No Blog do Laurent Schneider tem um demo bem legal.
Abaixo a demonstração.

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 || ' '
 || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true);

SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5044 |   123K| 22123   (1)| 00:04:26 |
|*  1 |  TABLE ACCESS FULL| LSC_T |  5044 |   123K| 22123   (1)| 00:04:26 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
  379  recursive calls
    0  db block gets
  100090  consistent gets
  100016  physical reads
    0  redo size
    2066  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    5  sorts (memory)
    0  sorts (disk)
   35  rows processed

SWLQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  5044 |   123K|  3574   (1)| 00:00:43 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LSC_T |  5044 |   123K|  3574   (1)| 00:00:43 |
|*  2 |   INDEX FULL SCAN           | LSC_I |  5000 |       |   220   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
    1  recursive calls
    0  db block gets
  360  consistent gets
  322  physical reads
    0  redo size
  142849  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
   35  rows processed

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

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 

Descobrindo o DBID quando o banco esta em nomount

Fala pessoal, uma situação chata mas que pode acontecer é recuperar os controlfiles depois de alguma perda. Se você não usa a FRA para uma recuperação automática dos controlfiles com o rman, você vai precisar setar o DBID e o control-file autobackup location manualmente.
Mas ai que mora a questão, se seu banco não esta aberto e você não tem o DBID anotado, como descobrir ele ??
Então vamos a boa dica :

Primeira coisa vamos logar no rman:

[oracle@rjhud ~]$ rlwrap rman target=sys/c402d92@centro

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 24 11:46:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN>


Agora que começa o legal da história , precisamos descobrir o DBID e como o banco não esta montado não iremos conseguir acessar a V$DATABASE então o que vamos fazer, vamos no diretório onde os backups estão armazenados ou no diretório dos datafiles e vamos fazer a seguinte busca atraves do comando strings do sistema operacional.
Os comandos vão variar para o tipo de backup que você vai pesquisar, se é um backup do datafile system ou undo ou um backupset.

strings file_name |grep MAXVALUE, (No caso do SYSTEM datafile or FULL Backup)
strings file_name |grep MAXVALUE (No caso do UNDO datafile )

Exemplos :

[root@rjhud backup2]# ls -lh

-rw-r----- 1 oracle oinstall 972K Aug 23 16:42 01mknoqp_1_1_TAGBACKUP_FULL
strings 01mknoqp_1_1_TAGBACKUP_FULL |grep MAXVALUE,

1287652985, MAXVALUE,

Esse é o meu DBID :1287652985

Agora é só seguir a sequencia para restaurar o controlfile e da próxima vez mantenha o seu DBID guardado em uma planilha ou repositório qualquer.

That's all folks

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

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 !!

Recuperando Variável Bind com a V$SQL_BIND_CAPTURE

Segue abaixo uma forma bem simples de pegar o valor de uma variável bind utilizando a view V$SQL_BIND_CAPTURE . Precisei recentemente pegar os valores de uma consulta que uma sessão estava realizando e o trace não estava ajudando por "n" fatores e utilizando o velho dicionario de dados foi bem mais simples nesse caso.

1_ Buscando o valor o sql_id na V$SQL :

SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%all_tab_columns%';

SQL_TEXT                                  SQL_ID

----------------------------------------  -------------

select col.*, com.Comments from sys.all_  032n4avhdnaz3

tab_columns col,      sys.all_col_commen

WHERE o.owner LIKE :1 ESCAPE '/'

AND o.object_name LIKE 2: ESCAPE '/'

Atenção a identificação das binds , essa mesma posição vai ser recuperada logo a frente.

2_ Olhando a V$SQL_BIND_CAPTURE

SQL>

 desc V$SQL_BIND_CAPTURE

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 ADDRESS                                                                    RAW(8)

 HASH_VALUE                                                                 NUMBER

 SQL_ID                                                                     VARCHAR2(13)

 CHILD_ADDRESS                                                              RAW(8)

 CHILD_NUMBER                                                               NUMBER

 NAME                                                                       VARCHAR2(30)

 POSITION                                                                   NUMBER

 DUP_POSITION                                                               NUMBER

 DATATYPE                                                                   NUMBER

 DATATYPE_STRING                                                            VARCHAR2(15)

 CHARACTER_SID                                                              NUMBER

 PRECISION                                                                  NUMBER

 SCALE                                                                      NUMBER

 MAX_LENGTH                                                                 NUMBER

 WAS_CAPTURED                                                               VARCHAR2(3)

 LAST_CAPTURED                                                              DATE

 VALUE_STRING                                                               VARCHAR2(4000)

 VALUE_ANYDATA                                                              ANYDATA

3_Buscando o valor da bind desejada :

SQL_ SELECT NAME,TO_CHAR(LAST_CAPTURED,'DD/MM/YYYY HH24:MI:SS'),VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='0prhvnya3f97z' ;

NAME                           TO_CHAR(LAST_CAPTUR VALUE_STRING

------------------------------ ------------------- ------------------------------

:1                             01/08/2011 16:24:39 REPORT_SERVER_AALTAMIRANO

:2                             01/08/2011 16:24:39 ROLE

Pronto, de forma simples foi recuperado os valores passados no filtro da consulta SQL.

GUOB Tech Day 2011 - Minha Visão !

Revigorante, essa é a palavra que pra mim traduziu o GUOB Tech Day 11. Sabe quando você precisa respirar novos ares, conhecer novas tendências e buscar inspiração mesmo para seguir na nossa “batalha” diária ? Então o GUOB forneceu esse conteúdo para todos os presentes.

As Palestras foram de alto nível, a começar pela parte da manhã com as palestras internacionais, dentre todas destaco a palestra do Pablo Ciccarello que apresentou a OTN de uma forma que muitos não conheciam com um ótimo conteúdo técnico e espaço para divulgação, e as palestras do Arup Nanda e a do Graham Wood nas quais ambos falaram de Exadata, o Graham Wood conseguiu mostrar em minutos o porquê que o Exadata tem essa fama, vou usar a frase do Rodrigo Almeida para descrever o que ele fez:

“...1 hora de palestra e uma VPNzinha consegue montar um Data Warehouse de 1 TB ao vivo, direto de uma Oracle Exadata X2-2 e carregar 8 bilhões de registros ...”

E na palestra do Arup Nanda ele simplesmente desmitificou o Exadata e deu o caminho das pedras para qualquer DBA conseguir se tornar um DMA (Database Machine Administrator).

Na parte da tarde eu assisti as duas palestras do Kai yu na qual ele deu um show em boas praticas para RAC e deu um belo overview sobre virtualização de RAC utilizando a Oracle VM, me deu motivações suficiente para testar a Oracle VM e talvez largar o VMWare.

Assisti também a concorrida palestra do Rodrigo Mulafani sobre certificações e a do Thomas Glufke sobre EBS.

E pra finalizar assisti a uma das melhores palestras do dia do Arup Nada sobre Boas praticas para DBAs.

O Evento pelo seu conteúdo técnico foi um dos melhores que eu já fui, e falando de Oracle com certeza o melhor, como disse o Ricardo Portilho não tenho saco para o Oracle Open World com seu conteúdo quase que todo para Gerentes.

O Evento foi ótimo para fazer, fortalecer e ampliar o networking, ele contou com presença dos maiores Blogueiros de Oracle do País tais como Rodrigo Almeida, Ricardo Portilho ( Que eu já conhecia porque fiz o ótimo curso de RAC na nerv com ele), Eduardo Legatti, David Ricardo e quase todos donos de blogs que com certeza já te ajudaram ou que você achou algum conteúdo interessante em algum momento. Nesse ponto eu falhei por ter ido sozinho para o evento e falando no popular, “dei mole” nesse requisito, mas fica a dica pro próximo: Conheça e fale com todo mundo e não vá sozinho, mas e for não tem problema.

Pra finalizar acho mais que justo agradecer ao Eduardo Hahn, presidente do GUOB, que com certeza deve ter ficado bem orgulhoso do resultado final do evento e parabenizar todas as pessoas que ajudaram pra realizar esse Evento.

Abaixo tem a cobertura de outros blogs, é legal quem não foi ler para observar a visão das pessoas que foram e no Final tem o link do GUOB para o download das palestras, tem que realizar um cadastro e fazer a associação ao grupo (Relaxa é Free) e se eu você fosse eu com certeza faria o download das palestras.

Cobertura

Ricado Portilho:

http://itweb.com.br/blogs/guob-tech-day-2011-eu-fui/

Rodrigo Almeida:

http://www.rodrigoalmeida.net/blog/resumo-guob-tech-day-2011

Mauro Pichiliani:

imasters.com.br/artigo/21478/outros/cobertura-do-guob-tech-day-2011

Alessandro Guimarães:

http://aguimaraes.wordpress.com/2011/07/23/guob-2011-comentarios-feedbacks-e-impressoes/

Eduardo Legatti:

http://eduardolegatti.blogspot.com/2011/07/minha-experiencia-no-guob-tech-day-2011.html

William do Carmo:

http://aprenderoracle.com/2011/07/review-do-evento-guob-2011/

Paulo Henrique:

http://phpdba.wordpress.com/2011/07/22/guob-tech-day-2011-attendant-feedback/

Fernando Franquini:

http://franquini.wordpress.com/2011/07/17/guob-tech-day-2011/

Leandro Lana:

http://leandrolana.blogspot.com/2011/07/guob-tech-day.html

Rodrigo Almeida:

http://www.rodrigoalmeida.net/blog/resumo-guob-tech-day-2011

Palestras:

http://www.guob.com.br

GUOB Tech Day 2011

Agende-se para mais este grande evento organizado pelo GUOB com apoio do LAOUC e OTN. Acontecerá no dia 16/07/2011,sábado, em São Paulo no Hotel Blue Tree Morumbi, a edição 2011 do grande encontro de usuários de tecnologia Oracle do Brasil com a participação de palestrantes internacionais e nacionais.
Este ano contamos com a presença de Arup Nanda, Graham Wood, Debra Lilley, Kay Yu, Hans Forbrich e Francisco Munoz.
Aproveite esta grande oportunidade de estar próximo de grande autores e referências em tecnologia Oracle no mundo.
Faça sua associação gratuita ao GUOB através da opção no menu ASSOCIE-SE em nosso site. Você poderá ser o ganhador de um dos 3 convites para associados ao GUOB para um almoço com os palestrantes durante o GUOB TECH DAY 2011.
Participem e divulguem o evento.
Acesse nosso site para maiores informações. GUOB TECH DAY 2011.

GUOB - Grupo de Usuários de Tecnologia Oracle do Brasil

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;

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;

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)    

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.