Mundo do Banco de Dados

Tuning na Instruçao SQL LIKE no Oracle

2011-11-23T11:01:00.001-08:00

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

2011-11-21T10:13:00.001-08:00

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

2011-11-18T06:32:00.001-08:00

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 ownere 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 esseowner possui, é aconselhável utilizar um spool para receber esse conteúdo.

That's all folks

Descobrindo o DBID quando o banco esta em nomount

2011-08-24T07:42:00.000-07:00

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

2011-08-03T14:56:00.000-07:00

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

2011-08-02T13:55:00.000-07:00

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

2011-08-01T13:13:00.000-07:00

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 !

2011-07-26T12:44:00.000-07:00

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

2011-06-14T12:38:00.000-07:00

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

2011-05-19T09:17:00.001-07:00

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

2011-05-13T13:37:00.000-07:00

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

2011-04-07T13:00:00.000-07:00

HASHJOIN

Os dois conjuntos de dados processados por um Hash Join são chamados de build input e probe input. A buildinput é a esquerda, e a probe input é a entrada à direita. Como ilustrado naFigura abaixo, usando cada linha da entrada da build input, uma tabela hash namemó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 nascolunas usadas como condição de join. Uma vez que a tabela hash contém todos osdados do build input, o processamento da probe input começa.

Cada linha é testada contra a tabela hash, a fim de descobrir se elecumpre a condição de join. Obviamente, apenas registros coincidentes são retornados.

* Visãodo processo de um Hash Join

Hash Joins sãocaracterizados pelas seguintes proriedades :

· Cadaoperaçã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 aprimeira linha, somente a entrada deesquerda deve ser totalmente processado.

· Crossjoins, theta joins e partitiouned outer joins não são suportados.

Exemplo de Hash Join.

O exemplo a seguir é um simples plano de execuçãomostrando o processamento de um Hash Joins entre duas tabelas, O exemplo também mostracomo 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

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

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

| 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 deexecução pode ser resumido da seguinte forma :

· Todas aslinhas da tabela t1 são lidas através de um full tablescan, a restrição n = 19 é aplicada, e umatabela de hash é construído com as linhas resultantes. Para construir a tabela de hash, uma função hash é aplicadapara as colunas usadas na condição de join (id).

· Todas aslinhas 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 hashentra no modo probed. Se for encontrado os dados correspondentes as linhas sãoretornadas

A limitação mais importante para a operação HASH JOIN (comopara as outras operações combinadas não relacionadas) é a incapacidade de tirarproveito dos índices para aplicar nas condições de join. Istosignifica que os índices podem ser utilizados como caminhode acesso somente se as restrições estão disponíveis. Por exemplo, se a restrição n = 19 fornece boaseletividade, 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 beused. Note that the

table t1 is no longer accessed through a fulltable scan.

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

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

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

| 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

2011-01-24T05:50:00.000-08:00

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.

Manutenção em Tabelas no MySQL - Parte II

2010-08-09T14:37:00.000-07:00

Continuando o post anterior hoje voufalar das ferramentas utilizadas para a manutenção de tabelas doMySQL.

CHECK TABLE

A instruçãoCHECK TABLE executa uma verificação de integridade de conteúdo eestrutura da tabela. Ele funciona para tabelas MyISAM eInnoDB,ARCHIVE, and CSV. Para tabelas MyISAM, ele também atualiza asestatísticas de índice. Se a tabela é uma view, CHECK TABLEverifica a definição da view. Se a saída de CHECK TABLE indica queuma tabela possui problemas, a tabela deve ser reparada.

mysql> check tables action;

+---------------------+-------+----------+----------+

+---------------------+-------+----------+----------+

+---------------------+-------+----------+----------+

1 row in set (15.93 sec)

Para maiores informações consulte olink :

http://dev.mysql.com/doc/refman/5.5/en/check-table.html

REPAIR TABLE

O REPAIRTABLE corrige problemas em uma tabela que foi corrompida.

Ele só funciona para tabelasMyISAM,ARCHIVE, and CSV.

Para executa-lo é necessário terprivilégios de Select e insert sobre a tabela.

È extremamente importante antes deexecutar um repair table, que seja feito um backup dos dados.

Se o servidorfalhar durante a execução do REPAIR TABLE, é essencial depois dereiniciá-lo de que você execute imediatamente outro REPAIR TABLE natabela antes de executar quaisquer outras operações sobre ela. Napior das hipóteses, você pode ter um novo arquivo de índice limposem informações sobre o arquivo de dados e, em seguida, a próximaoperação de executar poderia substituir o arquivo de dados. Este éum cenário improvável, mas possível que ressalta o valor de fazerum backup primeiro.

Obs: O Comando Repair table por defaultescreve no log binario e se for um servidor master de uma replicação,vai replicar a instrução para todos os slaves, então nesse caso énecessario que se rode a opção NO_WRITE_TO_BINLOG.

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/repair-table.html

ANALYZE TABLE

O ANALYZE TABLE atualiza a tabela comas informações sobre a distribuição das chaves da tabela. Essasinformações são usadas pelo optmizador para fazer melhoresescolhas sobre os planos de execução das consultas SQL.

Para executa-lo é necessário terprivilégios de Select e insert sobre a tabela.

Obs: O Analyze table por defaultescreve no log binario e se for um servidor master de uma replicação,vai replicar a instrução para todos os slaves, então nesse caso énecessario que se rode a opção NO_WRITE_TO_BINLOG.

O MySQLutiliza a distribuição de chaves para decidir a ordem em que astabelas devem ser unidas quando você executar um join em algumacoisa que não seja uma constante. Além disso, a distribuição daschaves podem ser usadas para decidir quais os índices de uma tabela será utilizado dentro de uma consulta.

mysql> analyze table action;

+---------------------+---------+----------+----------+

+---------------------+---------+----------+----------+

+---------------------+---------+----------+----------+

1 row in set (0.00 sec)

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html

OPTIMIZE TABLE

A instrução optimize table efetua adesfragmentação de uma tabela MyISAM. Ela recupera o espaço nãoutilizado, causado pela fragmentação das operações delete eupdate. Optimize table table classifica as paginas e as estatisticasdo indices.

Optimize table também funciona emtabelas innodb, porém não efetua a desfragmentação das tabelas,para isso é necessario que se execute um ALTER TABLE nome_da_tabelaengine=innodb;

È interessante ressaltar que essaoperação é extremamente custosa, bloqueia as tabelas, e se forfeita em produção, é necessario que seja realizado em um horárioque que não seja o de pico.

Obs: O optimize Table por defaultescreve no log binario e se for um servidor master de uma replicação,vai replicar a instrução para todos os slaves, então nesse caso énecessario que se rode a opção NO_WRITE_TO_BINLOG.

mysql> OPTIMIZE TABLE action;

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

1 row in set (0.04 sec)

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

Agora que falei das ferramentas voumostrar uma procedure para automatizar esses recursos:

A procedure abaixo realiza um optimizeem todas as tabelas do banco que você especificar, e dependendo dasua demanda você pode transforma-la em um evento do mysql, ou aindacriar um script para chama-la de tempo em tempo.

DELIMITER &&

CREATE PROCEDURE OPTIMIZE_TABLES()

BEGIN

DECLARE v_nome_table varchar(60);

DECLARE done INT DEFAULT 0;

DECLARE cur1 CURSOR FOR selecttable_name from information_schema.columns wheretable_schema='NOME_DO_BANCO' ;

DECLARE CONTINUE HANDLER FOR NOT FOUNDSET done = 1;

OPEN cur1;

REPEAT

FETCH cur1 INTO v_nome_table;

IF NOT done THEN

SET @v_comando := concat("OPTIMIZETABLES nome_do_banco.", v_nome_table, ";");

PREPARE stmt FROM @v_comando;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

end if;

UNTIL done END REPEAT;

CLOSE cur1;

END

DELIMITER ;

Manutenção em Tabelas no MySQL - Parte I

2010-08-03T08:24:00.000-07:00

Hoje eu vou falar sobre operações que fazem parte da vida de um DBA MySQL, que são as manutenções de tabelas, que é um processo extremamente necessário para o desempenho e integridade do seu banco de dados, dividi esse assunto em dois posts no qual vou explicar cada processo de manutenção com suas suas devidas observações e scripts para automatizar esse processo.

Manutenção nas tabelas do mysql são necessarias para identificar e corrigir problemas de integridade, ocasionados por diversos fatores como bugs, problemas no disco e no servidor e também para fornecer mais velocidade ao MySQL, veremos o porquê, a seguir os tipos de manutenção que podemos realizar:

A Ferramenta check realiza uma verificação de integridade para se certificar de que a estrutura da tabela e o conteúdo não têm problemas. Esta operação poderá ser realizada em tabelas MyISAM e InnoDB.

A Ferramenta repair corrige problemas de integridade para restaurar a tabela para um estado conhecido utilizável. Esse comando só tem suporte para tabelas MyISAM.

A Ferramenta analyze, atualiza as estatísticas sobre a distribuição dos valores de índice de chave. Esta é uma informação que o otimizador pode usar para gerar melhores planos de execução para consultas sobre as tabelas .Esse comando pode ser feito tanto em tabelas MyISAM como em tabelas InnoDB.

A Ferramenta optimize reorganiza uma tabela para que seu conteúdo pode ser acessado de forma mais eficiente. Esse comando pode ser feito em tabelas MyISAM ,InnoDB e Archive, mas com limitações que variam entre essas storages engines.

As Ferramentas analyze e optimize são operações que você pode executar periodicamente para manter suas tabelas com sua melhor performance:

Quando o MySQL analisa uma tabela MyISAM ou InnoDB, ele atualiza as estatísticas de índice. O otimizador usa essas estatísticas ao processar consultas para tomar melhores decisões sobre a melhor forma de procurar registros na tabela e na ordem em que ele precisa ler tabelas em um join.
Quando o MySQL Otimiza uma tabela MyISAM, ele desfragmenta o datafile para recuperar o espaço não utilizado, classifica os índices e atualiza as estatísticas de índice. Periódicamente a desfragmentação é necessária para acelerar o acesso as tabelas que contêm colunas de comprimento variável como VARCHAR, VARBINARY, BLOB, ou TEXT. Inserções e exclusões podem resultar em muitas lacunas em tais tabelas, particularmente aqueles que são modificados com freqüência. Desfragmentando elimina essas lacunas.

No próximo post vou falar detalhadamente das ferramentas, com exemplos e scripts de automação.

Configurando Replicação Semi – Sincronizada no Mysql

2010-07-19T12:05:00.000-07:00

    Hoje vou explicar como configurar uma replicação semi sincornizada no mysql, no post Replicação Semi Sincornizada eu expliquei o que era essa nova feature do mysql , e as vantagens dela em relação a replicação normal contida na versão 5.1 e anteriores.
    Na realidade demorei para publicar esse post, porque não podia falar dela sem testa-la, eu precisei configura-la em uma ambiente de produção web, e após 2 meses a replicação se encontra totalmente estavél. Com a segurança de um ambiente de produção de um grande portal, com mais de 300 consultas por segundo, vou explicar agora como configurar a replicação semi sincronizada
utilizando o mysql 5.5.3.

Meu cenário foram de 3 servidores com o mysql 5.5.3 em uma rede Gigabyte, utilizando o CentOS 5.5.

A Primeira coisa a ser realizada é a instalação dos plugins necessários.

Os plugins da replicação semi-síncronizada já estão incluídos com a distribuição MySQL. Atualmente, os plugins só estão disponíveis para Linux. Em outras plataformas são ainda não é suportado.

No master instale o seguinte plugin:

semisync_master.so

mysql >; INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

No Slave instale o seguinte plugin:

semisync_slave.so

mysql >; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Caso na hora da instalação do plugin , ocorra o erro abaixo :

ERROR 1126 (HY000): Can't open shared library
'/usr/local/mysql/lib/plugin/semisync_master.so' (errno: 22 libimf.so: cannot open
shared object file: No such file or directory)

Será necessário o download da libmf em :
http://dev.mysql.com/downloads/os-linux.html.

Para verficar os plugins instalados você pode dar um show plugins ou ainda efetuar a consulta SELECT * FROM INFORMATION_SCHEMA.PLUGINS.

Após a instalação os plugin são ativados por default, é importante a instalação dos plugins tanto no master quanto no slave, caso contrario a replicação será assincrona.

Configuração no Master

No banco master é necessário a configuração das seguintes variaveis no arquivo de configuração do Mysql (Quando instalado em distribuições rpm, fica localizado em /etc/my.cnf)

Entre os servidores que compoem o esquema de replicação, cada servidor deve possuir um id único, o id do servidor é estabelecido pelo parâmetro :

--server-id=x ( Onde x é o id do servidor)

Caso ocorra duplicação desse valor entre os servidores, ocorrera problemas na replicação.

Habilite a Replicação Semi-Sincronizada com as variaveis:

rpl_semi_sync_master_enabled=1
Essa variavel determina o tempo de resposta do slave ( Maiores informações leia meu post sobre replicação semi sincronizada)

rpl_semi_sync_master_timeout=10
O Log binário deve estar habilitado no master porque o log binário é a base para o envio de dados das alterações do master para seus escravos. Se o log binário não estiver habilitado, a replicação não será possível.

Exemplo
log-bin    =     /var/lib/mysql/mysql-bin-database-01.log
log-bin-index = /var/lib/mysql/mysql-bin-database-01.log.index

Para uma maior durabilidade e consistência possível em uma configuração de replicação usando InnoDB com transações, configure as seguintes variaveis:

usar innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Para estabelecer que a replicação ocorra para um unico banco utilize a varivavel a seguir:

binlog-do-db = nome_do_banco

Para estabelecer o numero de dias de vida dos logs binários utilize a seguinte variavel :

expire_logs_days = (número de dias)

Configuração no Slave

Configure o id do servidor com :

--server-id=x ( Onde x é o id do servidor)

Habilite a replicação semi sincronizada
rpl_semi_sync_slave_enabled=1

Configure o Relay Log

O relay log consiste em ler os eventos do log binário do master e escrito pela thread de I/O do Slave. Eventos no relay log são executadas no slave, como parte da linha SQL.

Exemplo:
relay-log=relay-bin-server2

Obs: Após as alterações no arquivo de configuração my.cnf é necessário o restart do mysql.

Crie um usuário para a Replicação

È necessário que você crie um usuário no servidor master, com permissão de acesso das outros servidores presentes na arquitetura de replicação
Esse usuário precisa de grant de replicação.

Use esse comando para conceder grant ao usuário.

GRANT REPLICATION SLAVE ON *.* TO 'user'@'%';

Verifique a necessidade de segurança do seu banco de dados, para verificar os privilégios adequado a esse usuário.

Ativando a Replicação.

Se você seguiu todos os passos corretamente nesse momento o master já esta registrando gerando as informações no log binário, então agora é o momento de ligar a replicação.
Existe duas formas de ligar a replicação, uma é atraves de variaveis do my.cnf, outra é com o comando change master to, por considerar o comando change master to mais seguro escolhi essa opção:

No master execute o comando:

Show master status;
Você deve ter uma resposta parecida com essa:
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000005 | 153469228 | qb3_migracao |                  |
+------------------+-----------+--------------+------------------+

No slave execute o seguinte comando :

use as informações obtidas no comando show master status;

CHANGE MASTER TO
         MASTER_HOST='nome_do_servidor_master',
         MASTER_USER='usuario_para_replicacao',
         MASTER_PASSWORD='senha_do_usuario',
         MASTER_LOG_FILE='nome_do_log_binario',
         MASTER_LOG_POS=0;
Agora execute o comando:

mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

Pronto , você acabou de configurar uma replicação semi sincronizada no Mysql.

Para verificar o status execute o comando:

show slave status\G;

Verifique no log do servidor slave as seguintes linhas:

[Note] Slave I/O thread: Start semi-sync replication to master 'slave-01@qb-database-main-01:3306' in log 'mysql-bin.000005' at position 001

Embora a versão 5.5 do mysql ainda não seja uma versão Generally Available (GA) ,
hoje tenho servidores em produção com essa versão e tenho obtido ganhos relavantes a versão 5.1, e a feature replicação semi sincronizada , é uma das que mais tem me agradado, é mais segura , eu acompanho via log qualquer problema que possa ocorrer.

Nos proximos posts , irei abordar sobre as novas features do innodb.

Abraços !

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

2010-06-23T12:04:00.000-07:00

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

Cache Hit Ratio Oracle

2010-06-13T07:52:00.000-07:00

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 nosdatafiles 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 oprocesso do usuário irá lêr os dados do disco para um buffer disponívelno 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 normalmenteocorre a adição de buffers no cache até que a taxa de acerto foimaximizada.
O número de buffers no cache é especificado pelo parâmetro de inicialização DB_BLOCK_BUFFERS.
Abraços !!

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

2010-06-02T07:30:00.001-07:00

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

Replicação Semi Sincronizada no Mysql 5.5

2010-05-30T16:08:00.000-07:00

Fala Pessoal, estava meio afastado mas hoje vou falar das novas caracteristicas de replicação no Mysql 5.5

MySQL 5.5 suporta uma interface de replicação semi-síncronas, além da embutida replicação assíncrona.

A replicação do MySQL, por padrão é assíncrona. O mestre grava eventos em seu log binário, mas não sabe se ou quando um escravo tem recuperado e transformado-los. Com a replicação assíncrona se o máster falhar, as operações de que tenham sido comitadas não poderiam ser transmitidas a um escravo. Consequentemente esse failover de um mestre a escravo neste caso pode resultar em failover para um servidor que está faltando operações relativas ao mestre.

A Replicação semi-síncronas pode ser usada como uma alternativa para a replicação assíncrona:

· Um Escravo indica se ele esta semi sincronizado quando se conecta ao master

· Se a replicação semi-síncronas está habilitada, no lado mestre e há pelo menos um escravo semi-síncronizado , uma thread que executa uma operação de commit em um master cria um bloqueio após o commit ser realizado ,então o master espera até que pelo menos um escravo semi-síncronizado reconheça que tenha recebido todos os eventos da operação, ou até um tempo limite .

· O escravo reconhece a recepção de eventos de uma transação somente após que esses eventos foram gravados em seu relay log e sofrerem flush para o disco.

· Se um tempo limite ocorre sem que qualquer escravo tenha reconhecido a operação, o master volta a replicação assíncrona. Quando pelo menos um escravo alcança o nivel semi-síncronizado, o mestre retorna à replicação semi-síncrona..

· A Replicação semi-síncronas deve ser ativada em ambos os lados e dominar a escrava. se a replicaçãosemi-síncronas está desabilitado no master, ou habilitado no master mas não escravos, o mestre usa replicação assíncrona.

Enquanto o master está bloqueando (Esperando confirmação de um escravo, após ter efetuado um commit), ele não irá retornar para a sessão que realizou a transação. Quando o bloqueio termina, o máster retorna à sessão, que pode então proceder para executar outras instruções. Neste ponto, a transação foi comittada no lado mestre, e a recepção de seus eventos foram reconhecidas por pelo menos um escravo.

O bloqueio também ocorre após rollbacks que são escritas no log binário, que ocorrem quando uma operação que modifica as tabelas não transacionais é revertida. A transação rolled-back é registrada, mesmo que não tem nenhum efeito para tabelas transacionais porque as modificações nas tabelas não transacionais não podem ser revertidas e devem ser enviados para os escravos.

As instruções que não ocorrem em contexto transacional (ou seja, quando nenhuma transação tenha sido iniciado com START TRANSACTION ou SET autocommit = 0), O autocommit é ativado e cada declaração compromete implicitamente. Com a replicação semi-síncronas, O master é bloqueado após o commit da instrução, da mesma forma que ocorreria para uma transação com um commit explicito.

Para entender o que o "semi" "da replicação semi-síncrona" significa, vamso compar a replicação assíncrona e totalmente sincronizada:

·         Com a replicação assíncrona, o master grava eventos em seu log binário e escravos requisitam quando eles estiverem prontos. Não há garantia de que qualquer evento jamais chegar a qualquer escravo.

·         Com a replicação totalmente sincronizada, quando um master efetua um commit de uma transação, todos os escravos terão o commit da transação antes do retorno do mestre para a sessão que realizou a transação. A desvantagem desta situação é que pode haver muita demora para completar uma transação.

·          A Replicação semi-síncrona cai entre replicação assíncrona e totalmente sincronizada. O Master aguarda após o commit apenas até, pelo menos, um escravo tem recebido e registrado os eventos. Ele não espera que todos os escravos confirmem a recepção, e ele exige apenas a recepção, não que os eventos tenham sido integralmente executados e empenhados no lado escravo.

Comparado a replicação assíncrona, a replicação semi-síncronas proporciona maior integridade dos dados. Quando um commit é retornado com sucesso, sabe-se que os dados existem, em pelo menos, dois lugares (no mestre e pelo menos em um escravo). Mas se ocorre um crash no master enquanto o ele está esperando a confirmação de um escravo, é possível que a operação possa não ter alcançado nenhum escravo.( Ai Chora!!! )

A Replicação semi-síncronas tem algum impacto no desempenho porque os commits são mais lentos devido à necessidade de esperar pelo os escravos. O dilema para a aumentar a integridade dos dados é o tempo de resposta do TCP / IP para enviar a confirmação para o escravo e aguardar a confirmação da recepção pelo escravo. Isto significa que a replicação semi-síncronas funciona melhor para os servidores que fecham comunicação através de redes rápidas, e o pior para os servidores distantes se comunicando por redes lentas.

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

2010-05-12T12:47:00.000-07:00

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.

O que um DBA deve saber ....

2010-05-07T07:18:00.000-07:00

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.

O Processo de Commit

2010-05-03T09:22:00.001-07:00

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:

Processo do servidor coloca um registro de commit, com o SCN, no buffer redo log.
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.
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

Processamento das Instruções SQL no Oracle

2010-05-01T16:05:00.000-07:00

Fala Pessoal,

Um Bom entendimento de como as instruções SQL são processadas no banco é fundamental para uma criação otimizada de instruções SQL. No Processamento de Instruções SQL, existem quatro fases Importantes as quais vou relatar ao longo dos meus posts e que são elas:
Parsing,binding,Executing and Fetching. Nesse post irei começar falando da fase de parse.

Fase de Parse (Análise)
A Fase de Parsing é um dos estágios que ocorre no processamento das instruções SQL. Quando uma aplicação emite uma instrução SQL, , ela na realidade emite uma chamada para o banco de dados e durante essa chamada para o banco analisar essa instrução, ou seja verificar se realmente é possível extrair resultados dela, e nessa fase de analise, o Oracle:

Verifica a instrução sql, sua sintaxe e valida a sua semântica

Determina se o processo de emissão da instrução SQL tem a devida permissão para executá-la.

Aloca uma área privada para a instrução SQL

O Oracle primeiramente verifica se já existe uma analise (parse) dessa instrução SQL no cachê de biblioteca. Se já existir uma analise dessa instrução no cachê de biblioteca, essa instrução é executada imediatamente porém se a mesma não existir, o Oracle irá gerar uma analise dessa instrução e o processo do usuário ira alocar uma área de SQL compartilhada para a instrução no cachê de biblioteca após esse procedimento a instrução será analisada nesta área.
A Operação de Parse (análise) do Oracle sempre aloca uma área compartilhada para uma instrução SQL. Depois dessa área compartilhada ter sido alocada para essa instrução, a mesma já pode ser executada novamente repetitivamente(varias vezes) sem precisar ser analisada , ou seja sem precisar passar pela fase de Parse. O processo de Analise pode ser "caro" em relação a execução, pode ter um alto custo dos requisitos disponíveis, por isso essa etapa deve ser minimizada sempre que possível. O Ideal é que a instrução deva ser analisada uma vez e executada varias vezes sem a necessidade de uma nova análise para cada execução.
Existem dois Tipos de Operação de Parse (Analise):

Hard Parsing : Dizemos que existe uma operação de Hard Parse quando uma instrução Sql é executada pela primeira vez e ela não se encontrada na shared pool. Esse tipo de parse é a fase que mais consome recurso intensivamente do banco de dados, porque quando isso ocorre é necessario executar todas as operações necessárias da fase de parse.
Soft Parsing: Dizemos que existe uma operação de Soft Parse quando uma instrução executada já existe na shared pool..Quando uma instrução sql já esta armazenada no cache de biblioteca temos um ganho de performance.No entando, até mesmo na fase de parse temos ainda um consumo de recursos, pois mesmo a instrução já estando armazenada o banco ainda precisa verificar os parâmetros de segunça e a sintaxe da instrução e isso consome recursos do sistema.

Quando as variáveis bind (ligação) são usadas de modo correto, a operação de sof parsing ocorre mais vezes no banco da dados, reduzindo assim a operação de hard parsing e mantendo as instruções analisadas na cachê de biblioteca por um tempo maior.

Concorrência e Consistência dos Dados (Locks) Oracle

2010-04-30T09:12:00.001-07:00

Olá
Nesse primeiro Post, tenho a intenção de mostrar de forma resumida, prática e simples o conceito de concorrência e consistência de dados, enfim os nossos locks de cada dia.
Se os banco de dados tivessem apenas um único usuário, muitos dos nossos problemas seriam resolvidos (Tudo bem que criaríamos muitos outros, mas isso não vem ao caso agora), os dados poderiam sofrer modificações em diversas horas e até ao mesmo tempo. Porém como dois corpos não ocupam o mesmo lugar no espaço, em bancos de dados "multi usuário", as transações podem tentar atualizar os mesmos dados ao mesmo tempo. Então por isso os bancos de dados devem ter e possuem um controle de concorrência afim de manter a consistência dos dados, para os outros usuários.
Concorrência de Dados quer dizer que os usuários(vários) podem acessar os mesmos dados ao mesmo tempo. Já Consistência de dados quer dizer que os usuários podem ter uma visão consistente sólida e real dos dados, incluindo as alterações que o próprio usuário esta realizando e as alterações feitas por outros usuários.
Help_1: Caso algum iniciante tenha a seguinte dúvida:
-         Se algum usuário estiver atualizando uma tabela, eu vou ver os valores antigos ou os alterados ?
Resposta : Em primeiro lugar, caso você tenha essa dúvida é sinal que está na hora de aprender sobre segmentos de UNDO, mas ~~clareando~~ respondendo a dúvida, sempre quando vamos atualizar,deletar ou inserir algum dado em alguma tabela, o Oracle usa os segmentos de UNDO para armazenar os blocos de dados antigos e como resultado, fornecem os valores anteriores das linhas para qualquer usuário que emite uma instrução select antes de a transação sofrer commit.Então se alguém estiver fazendo alguma modificação, quando outra pessoa executar uma instrução select, essa somente verá os dados armazenados no segmento de UNDO ou seja os antigos, até que o commit, seja executado.( Vale a Pena pesquisar sobre o assunto).
Mecanismo de Locks (Ou bloqueio)
Os SGBDs tem por default um mecanismo de lock(bloqueio) para resolver os problemas realcionados a integridade, consistência e concorrência de dados. Os Locks são mecanismos que impedem que transações atualizem os mesmos dados ao mesmo tempo.
-         Controlam o acesso concorrente ao banco de dados
-         Impede atualizações simultâneas do mesmo dado
-         È liberado ao final de cada transação (COMMIT,ROLLBACK)

Tempo de Duração

Todos os bloqueios adquiridos pelas declarações dentro de uma transação são mantidos para a duração da operação, impedindo a interferência destrutiva, incluindo leituras sujas e perda de atualizações. As alterações introduzidas pelas instruções SQL de uma transação só se tornam visíveis para outras transações que começam após que a primeira transação seja confirmada (Commit).
O Oracle libera todos os bloqueios adquiridos pelas declarações dentro de uma transação quando você confirmar ou desfazer a transação. A Oracle também libera os bloqueios adquiridos após um ponto de salvamento(savepoint) quando existe reversão para o ponto de salvamento.

DEADLOCK

Um deadlock ocorre quando dois ou mais segmentos do controle estão bloqueadas, cada um à espera de um recurso mantido por outro segmento.Elas ficam aguardando que uma libere um determinado recurso para que outra o utilize. Existe outras fontes na internet os quais detalham melhor esse conceito e os tipos de Lock.
( http://profissionaloracle.com.br/blogs/drbs/page/2/)

A Nível de Teste vamos forçar o acontecimento de Locks.

1_Vamos Criar e popular a tabela HT_LOCK.
SQL> CREATE TABLE HT_LOCK (
ID_LOCK VARCHAR2(1),
COD_LOCK VARCHAR2(1));
Table created.
SQL> INSERT INTO HT_LOCK VALUES (1,'A');
SQL> INSERT INTO HT_LOCK VALUES (2, 'B');
SQL> INSERT INTO HT_LOCK VALUES (3, 'C');
SQL> INSERT INTO HT_LOCK VALUES (4, 'D');
4 row created.
SQL> SELECT * FROM HT_LOCK;
ID_LOCK COD_LOCK
---                ---
1                  A
2                  B
3                  C
4                  D
4 rows selected.
SQL> commit ;
Commit complete.
2_Vamos efetuar um UPDATE nessa Tabela com usuários diferentes;
SQL>UPDATE HT_LOCK SET ID_LOCK='0' WHERE ID_LOCK='4';
1 linha atualizada.
SQL> UPDATE HUDSON.HT_LOCK SET ID_LOCK='9' WHERE ID_LOCK='2';
1 linha atualizada.
3_Agora vamos verificar quais usuários estão gerando algum tipo de lock.

SQL> SELECT OBJECT_ID,SESSION_ID,ORACLE_USERNAME FROM V$LOCKED_OBJECT;
OBJECT_ID SESSION_ID ORACLE_USERNAME
---------- ---------- ------------------------------
19996           14                    HUDSON
19996           21                     SYSTEM
2 linhas selecionadas.
Help_2 A Visão V$LOCKED_OBJECT listas todos os bloqueios adquiridos por todas as transações no sistema. Isso mostra que as sessões estão mantendo bloqueios DML em que objetos e de que modo.
Então Temos dois Usuários com locks em um objeto de mesmo ID, logo se trata do mesmo objeto..
Agora vamos descobrir que objeto é esse ;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE,OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_ID = 19996;
OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID
------------------------------ ------------------- ------         ----
HT_LOCK                        TABLE                             19996
Agora sabemos que esse lock é realizado na Tabela HT_LOCK .
Eu mostrei como localizar quais são os usuários os quais estão gerando lock em alguma tabela e quais tabelas são essas de forma separada para melhor entendimento, mas isso também poderia ser feito de uma forma só com o select abaixo :
SQL>SELECT A.OBJECT_ID, A.SESSION_ID, A.ORACLE_USERNAME, B.OBJECT_NAME , B.OBJECT_TYPE FROM v$locked_object A , DBA_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID;
OBJECT_ID      SESSION_ID ORACLE_USERNAME            OBJECT_NAME
--------------- --------------- ------------------------------ ----------------------------------
19996              21            SYSTEM                                            HT_LOCK
19996              14            HUDSON                                           HT_LOCK
Agora se um sessão de um usuário gerar um lock em alguma tabela e se a mesma for mal finalizada ela ira ficar com o status de inativa no banco de dados, e esse lock somente irá sumir quando o processo de segundo plano ou background PMON aplicar rollback na transação, e assim remover os locks nas linhas afetadas.
Mas caso por algum problema PMON demorar para aplicar o rollback e remover os locks, você pode matar essa sessão pendente e assim retirar o lock, da seguinte forma.
SQL> SELECT SID,SERIAL#,USERNAME,STATUS FROM V$SESSION WHERE SID = 14 --(ESSE VALOR É EQUIVALENTE AO VALOR DO CAMPO SESSION_ID DA VISÃO v$locked_object)
SID    SERIAL# USERNAME             STATUS
-------- ---------- ------------------------------ --------
14         61          HUDSON                  INACTIVE
Após obter o SID, e o SERIAL# de sessão vamos forçar a sua finalização com o comando :
ALTER SYSTEM KILL SESSION '301,9797' IMMEDIATE;
A Minha intenção nesse Post foi mostrar como ocorre um lock e como entender o processo de identificação e finalização :
Porém existem scripts que podem fazer tudo, ou andar um bom caminho segue esse script do Rodrigo Almeida publicado em 06 novembro 2005 ás 15:03 no link :
http://forum.imasters.uol.com.br/index.php?/topic/153638-monitoracao-de-objetos-em-lock/
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM   dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
Bibliografia utilizada :
-         Oracle Database 11g /Bob Byla e Kevin Loney
-         Dominando o Oracle 9i Damaris Fanderuff
-    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i5704