Numeração Contínua com "Sequences" usando NOCACHE ?

Vamos lá: os objetos do tipo "sequence" em Oracle não garantem sequências numéricas sem "gaps". Nunca. Não existe nenhum parâmetro que possa ser setado, nehuma variação na sintaxe do comando CREATE SEQUENCE, nada que possa garantir isso.

_

Por quê ? Ora, porque existe uma coisa chamada "rollback", que acontece automaticamente quando ocorre um erro qualquer dentro de uma transação. Quando você pega um número da sequence (nextval), isso não tem volta. É como se fosse uma transação autônoma. A grosso modo, se você tem um processo qualquer que usa uma sequence para preencher um campo, você terá um buraco na sequence sempre que esse processo der erro.

_

Às vezes encontramos por aí sequences configuradas como NOCACHE, numa tentativa de conseguir esse efeito de numeração contínua. Isso não apenas não resolve um problema, mas cria outro pior. Sequences com NOCACHE prejudicam muito o desempenho, especialmente em processos batch e mais especialmente ainda em ambientes clusterizados (RAC). Se você tiver problemas de desempenho relacionados ao evento de wait "SQ - Sequence Number Enqueue", procure por sequences definidas como NOCACHE.

Duas Dicas Sobre Privilégios

Primeira dica: Uma procedure/function/package roda sempre* com os privilégios do owner, não do executor. Isso significa que, se o usuário SCOTT precisa rodar uma package do usuário FINANC que acessa 300 tabelas de FINANC, apenas o grant de EXECUTE na package para SCOTT é suficiente. Dentro da execução, valem os privilégios de FINANC.

_

Segunda dica: Dentro de uma procedure/function/package, grants recebidos através de uma role não valem. Se através da role ABC você consegue abrir um SQL*Plus e fazer select na tabela X, isso não lhe dá o direito de fazer o mesmo select dentro de uma procedure sua. Para uso dentro de procedures, o grant tem que ser direto e não via role.

_

Tenha sempre isso em mente quando estiver investigando problemas ligados a grants de privilégios. Você poupará muito esforço inútil.

_

* Exceto quando explicitamente definido pelo pragma AUTHID_CURRENT_USER

Instance Caging: Limitando Recursos

Em ambientes de desenvolvimento e de homologação, é comum termos várias instâncias e vários bancos de dados compartilhando uma mesma máquina. Como uma instância Oracle é composta por vários processos no sistema operacional e cada sessão de usuário é também atrelada a um ou mais processos, pode ser bastante difícil impedir que um processo problemático em uma instância "afogue" a máquina e prejudique todas as outras que residem no mesmo hardware.

_

Para endereçar este problema, temos no Oracle 11g release 2 uma nova funcionalidade chamada "Instance Caging" (literalmente, "encarceramento"), que permite definir exatamente o número de CPUs às quais uma instância terá acesso.

_

A ativação é muito simples. Basta habilitar um "resource plan" e ajustar o número de CPUs. Suponhamos uma máquina com 16 CPUs e uma instância onde queremos que apenas duas dessas CPUs sejam utilizadas:

_

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DEFAULT_PLAN;

_

ALTER SYSTEM SET CPU_COUNT=2;

_

Pronto. Instância devidamente enjaulada.

_

Veja também: 
_
Cuidado com estes parâmetros no Oracle 11g
Starvation ou "Meu Trace Sofreu um Lapso de Tempo"

Livro: Expert Oracle Exadata

Excelente livro para quem precisa começar a lidar com a "Oracle Exadata Database Machine". Ao contrário do que aparenta, a Exadata não é apenas uma caixa preta de 7 milhões de dólares que você pode comprar e colocar no data center. Existem diversos sabores, diversas opções de configuração e dezenas de parâmetros exclusivos. Embora a Exadata rode exatamente o mesmo software Oracle 11g Release 2, existem coisas que só funcionam nela. E são coisas incríveis. Comprei na Amazon. Tem versão para o Kindle.

Mudança no comportamento das roles "default"

Até a versão 10.2.0.4 do Oracle Database, era possível o seguinte cenário: 
_
Uma determinada role "ROLE_XX", embora protegida por senha, era ativada automaticamente para usuários que a têm como role default. 
_
Em outras palavras: se foi dado apenas o grant da role para o usuário SCOTT ("grant ROLE_XX to SCOTT") e a role é protegida por senha, então SCOTT precisava rodar o seguinte comando para ter os privilégios dados pela role:
_
set role ROLE_XX identified by SENHA_DA_ROLE_XX;
_
Entretanto, se a role foi também associada como "role default" ("alter user SCOTT default role ROLE_XX"), não havia necessidade do "set role". Ao efetuar o login, SCOTT já ganhava os privilégios da role, mesmo sem saber a senha.
_
Isso mudou na versão 10.2.0.5 e posteriores. Mesmo roles associadas como "default" precisam do comando "set role" com a senha correta para serem ativadas.
_
É importante notar que a Oracle considerou o comportamento anterior um "bug" de segurança, portanto não disponibilizou nenhum parâmetro ou "workaround". Aplicações que se beneficiavam das roles default para, por exemplo, dar privilégios a um usuário de execução de processos batch, simplesmente param de funcionar.
_
O documento oficial sobre a mudança pode ser encontrado no documento número 745407.1 do Suporte Oracle.

Mensagens de Erro Intrigantes

Algumas mensagens de erro Oracle são no mínimo estranhas. Compilei essa lista já faz um bom tempo e já sei quem é essa tal diana, mas ainda me pergunto que tipo de problema retornaria o erro "ORA-02846 - Servidor imortal" :-) 
_
Deixei a lista em inglês porque as mensagens são mais engraçadas. Quem quiser saber a versão em português, basta usar o código ao final do post e digitar o número do erro quando solicitado.
_

ORA-00982 missing plus sign

ORA-00996 the concatenate operator is ||, not |

ORA-01072 cannot stop ORACLE; ORACLE not running

ORA-01169 DATAFILE number 1 not found. Must be present

ORA-01177 data file does not match dictionary - probably old incarnation

ORA-01241 an external cache has died

ORA-01645 previous attempt to make read write is half complete

ORA-01712 you cannot grant a privilege which you do not have

ORA-01744 inappropriate INTO

ORA-01842 quarter must be between 1 and 4

ORA-01869 reserved for future use

ORA-02309 atomic NULL violation

ORA-02841 Server died on start up

ORA-02846 Unkillable server

ORA-03002 operator not implemented

ORA-03007 obsolete feature

ORA-03129 the next piece to be inserted is required

ORA-04012 object is not a sequence

ORA-04028 cannot generate diana for object

ORA-04046 results of compilation are too large to support

ORA-08114 can not alter a fake index

declare

   s varchar2(200);

   i pls_integer;

begin

   i:= utl_lms.get_message(&CODIGO, 'rdbms', 'ora', 'brazilian portuguese', s);

   dbms_output.put_line(s);

end;

/ 

Starvation ou "Meu Trace Sofreu um Lapso de Tempo"

Durante a análise de arquivos trace do Oracle, às vezes nos deparamos com a seguinte situação: o tempo de CPU foi X, o tempo decorrido (elapsed) foi bem maior que X, mas não aparece nenhum evento de espera que justifique a diferença. Por exemplo:
_
********************************************************************************
SQL ID: fcqbkkryrx6xh Plan Hash: 2798344373
_
SELECT MIN(OBJECT_ID)
FROM
 DBA_OBJECTS WHERE OBJECT_ID > :B1
_
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   7088      0.83       2.51          0          0          0           0
Fetch     7088   1396.10    4853.38          0   17149867          0        7088
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    14177   1396.94    4855.90          0   17149867          0        7088
_
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                    28176        0.00          0.06
  latch: cache buffers chains                     7        0.07          0.24
  latch: row cache objects                        1        0.05          0.05
  latch free                                      1        0.06          0.06
********************************************************************************
_
Neste caso, a query executou durante 4856 segundos, mas gastou apenas 1397 deles em CPU. Isso é comum, porque processos de banco de dados geralmente passam um bom tempo esperando blocos serem trazidos do cache ou do disco. Mas vejam que não houve espera alguma por disco ("db file sequential read" ou "db file scattered read") nem por qualquer outro recurso (Soma de "Total Waited" não dá nem 1s). Então pra onde foram os outros 3459 segundos ?

Resposta: eles foram gastos esperando por CPU. Durante o tempo de execução, outros processos estavam rodando na mesma máquina, e nosso processo só teve acesso aos ciclos da CPU durante 29% do tempo em que rodou. A espera por CPU não é registrada pelo trace do Oracle como um evento de "wait", portanto não aparece.

No exemplo acima eu sei que foi exatamente isso que aconteceu, porque foi um teste controlado: em uma máquina com 4 CPUs foram disparados 4 processos Oracle que executavam operações pesadas, mas praticamente sem acesso a disco. Em seguida, disparei um quinto processo idêntico, mas este sofreu um rebaixamento de prioridade no sistema operacional (comando "renice" em Linux). O trecho acima vem deste último processo.

Então em resumo é isso: quando cair em suas mãos um trace com lapsos de tempo, é porque outra(s) coisa(s) estava(m) rodando junto com seu processo no período registrado pelo trace. Para investigar mais a fundo um caso como esse, é necessário utilizar ferramentas que vejam a instância Oracle como um todo (e.g. AWR Report) ou até ferramentas de análise de carga do servidor, já que os processos que consumiram a CPU podem nem ter sido do Oracle.

Cuidado com estes parâmetros no Oracle 11g

Para você que está migrando agora para o Oracle 11g, segue uma lista com alguns parâmetros que me causaram surpresas desagradáveis. Claro, o correto seria ter lido a documentação completa e o "Upgrade Guide" da nova versão, mas eu não tive tempo e duvido que você tenha, então vamos lá:
_
1. SEC_CASE_SENSITIVE_LOGON { TRUE | FALSE }
_
Este parâmetro torna "case sensitive" as senhas do Oracle, ou seja, passa a haver diferenciação de letras maiúsculas e minúsculas. Como o valor padrão é "TRUE", quaisquer sistemas ou interfaces que não façam esta diferenciação podem parar de funcionar por conta de erro na autenticação. Para evitar isso, ou para contornar o problema até que os sistemas sejam adaptados, altere o valor do parâmetro para FALSE.
_
2. JOB_QUEUE_PROCESSES { Integer }
_
Este parâmetro limita o número de jobs que podem executar simultaneamente. Jobs são processos agendados para execução imediata ou posterior, através das packages DBMS_JOB ou DBMS_SCHEDULER. Como muitas vezes os jobs são utilizados para execução de um grande número de tarefas em lote, é importante que este parâmetro seja definido com um número razoável para o ambiente. O problema é que o valor padrão no Oracle 10g era "10", mas passou para "1000" na versão 11g. "10" é um número de processos que a maioria dos servidores pode suportar, mas "1000" é coisa para uma Exadata. Ao migrar para 11g, configure este parâmetro de acordo com a capacidade do seu ambiente.
_
3. _PARTITION_LARGE_EXTENTS { TRUE | FALSE }
_
Quando este parâmetro está com o valor "TRUE", que é o padrão no 11g, quaisquer novas partições são criadas com tamanho de 8 MB, enquanto na versão anterior este valor era de 64 kB. Em sistemas com muitas tabelas particionadas, esta diferença pode causar uma ocupação de disco muito maior no 11g do que na versão anterior, sem razão aparente. Para evitar este problema, configure o parâmetro com o valor "FALSE", pelo menos durante a criação das partições.  

Descendentes de uma Tabela

O script SQL abaixo retorna, para uma tabela qualquer, todas as descendentes, ou seja, todas as tabelas que possuem constraints de integridade referencial (FKs) que apontem para ela em qualquer nível. É útil por exemplo em atividades de saneamento de dados, quando você precisa remover a pessoa com ID = 186413 mas existem dezenas de outras tabelas onde este ID é referenciado. Claro que o script só funciona se as FKs realmente existem na base de dados e estão ativas.

select nivel, parent_table_name, child_table_name, fkey_constraint

from   ( select max(level) nivel,

                parent_table_name,

                child_table_name,

                fkey_constraint

         from   ( select pk.table_name         parent_table_name,

                         pk.constraint_name    pkey_constraint,

                         fk.table_name         child_table_name,

                         fk.constraint_name    fkey_constraint,

                         fk.r_constraint_name

                  from   all_constraints pk,

                         all_constraints fk

                  where  pk.constraint_name = fk.r_constraint_name

                    and  pk.constraint_type in ('P','U')

                    and  fk.status = 'ENABLED'

                    and  fk.constraint_type = 'R'

                )

         start with parent_table_name = upper('&TABELA')

         connect by nocycle prior child_table_name = parent_table_name

         group by parent_table_name, child_table_name, fkey_constraint

       )

order by nivel

/

Paralelismo Automático no Oracle 11g Release 2

Embora algum tipo de processamento paralelo esteja presente nos bancos de dados Oracle desde a versão 6.2, apenas agora na versão 11.2 este processamento pode ser configurado para atuar de forma automática, sem necessidade de "hints" ou de alterações no grau de paralelismo nas tabelas (campo "DEGREE" em "USER_TABLES"). 
_
Na versão 11.2, basta definir o valor de dois parâmetros dinâmicos e o otimizador passará a utilizar, se julgar necessário, planos de acesso com operações paralelas. Note que nem sempre o otimizador toma a decisão correta, então é necessário realizar testes antes de implementar esta técnica em sistemas de produção. Vamos aos parâmetros.
_
1. PARALLEL_SERVERS_TARGET { INTEGER }

_

Define o número de processos paralelos que se deseja ter executando simultaneamente. Deve ser configurado com um número que seja razoável para o hardware onde estamos executando. Por exemplo, para um servidor Intel com 2 CPUS quad-core, este valor poderia ser 8:

_

ALTER SYSTEM SET PARALLEL_SERVERS_TARGET = 8;

_

2. PARALLEL_DEGREE_POLICY { MANUAL | LIMITED | AUTO }
_
Define a política de paralelismo. Qualquer valor diferente de "MANUAL" ativará o paralelismo automático:
_

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = AUTO;

Geração de Trace sem Mestre

Na análise de problemas de desempenho em processos Oracle, ainda é comum a necessidade de geração e análise de "trace files", arquivos que registram todas as ações de uma sessão no banco de dados. O problema é que muitas vezes a investigação tem que ser feita diretamente no ambiente de produção, onde geralmente existe todo um processo até que um DBA identifique a sessão, ative a geração de trace e finalmente retorne o arquivo processado. 

Felizmente, existe uma maneira de ligar a geração de TRACE já no script, sem necessidade de terceiros. Vejam o código abaixo. Depois de terminada a execução, existirá um arquivo de trace no servidor de banco de dados com a string “TRACE_DO_MEU_PROCESSO” no nome do arquivo.

Além de ser bem mais simples do que pedir a alguém para encontrar sua sessão, ligar o trace, esperar acabar, desligar o trace e processar a saída, este método ainda permite que você trabalhe em lote: execute todos os processos necessários e posteriormente solicite aos responsáveis os arquivos resultantes: “Favor executar o comando TKPROF em todos os arquivos de trace gerados ontem com 'PROC_FATURAMENTO' no nome do arquivo e enviar para o e-mail X”.

declare

  str varchar2(127);

begin

  --

  -- Ligando o Trace: coloque um texto no "identifier" para que seu arquivo seja encontrado depois:

  --

  str := 'alter session set tracefile_identifier=''TRACE_DO_MEU_PROCESSO''';

  execute immediate str;

  str := 'alter session set events ''10046 trace name context forever, level 8''';

  execute immediate str;

  --

  -- Fazendo o que eu tenho que fazer: aqui você chama o seu processo

  --

  for i in ( select * from user_objects ) loop

      null;

  end loop;

  --

  -- Desligando o Trace

  --

  str:= 'alter session set events ''10046 trace name context off''';

  execute immediate str;

  -- 

end;

/

Posteriormente, seus arquivos serão facilmente identificados no servidor:

[oracle@dbserver01 udump]$ ls -l

total 51672

-rw-r----- 1 oracle oinstall      874 Nov 25 20:11 perf01_ora_27175.trc

-rw-r----- 1 oracle oinstall     1136 Dec  4 19:15 perf01_ora_30466.trc

-rw-r----- 1 oracle oinstall    44883 Dec  4 19:37 perf01_ora_31275_TRACE_DO_MEU_PROCESSO.trc

-rw-r----- 1 oracle oinstall     1100 Dec  1 18:41 perf01_ora_32359.trc

-rw-r----- 1 oracle oinstall      621 Dec  1 18:41 perf01_ora_32365.trc

-rw-r----- 1 oracle oinstall      874 Dec  1 18:41 perf01_ora_32393.trc

-rw-r----- 1 oracle oinstall     4864 Dec  1 19:17 perf01_ora_32449.trc