Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 23 de maio de 2017

Monitorando alternâncias do redo log online no Oracle (Redo Log Switch Frequency)

Por Eduardo Legatti

Olá,

Uma situação que pode acontecer em um ambiente de banco de dados Oracle é o aumento expressivo no número de dados de "redo" gerados pelo aumento do número de transações ao longo do tempo ou até mesmo por alguma operação isolada. A consequência imediata no número de transações que afetam grandes quantidades de dados é o número excessivo de "redo log switches" ou alternâncias de log gerados um curto espaço de tempo. Em ambientes Oracle que operam em modo ARCHIVELOG a geração excessiva de archive logs por conta de várias operações de alternâncias de log em um curto espaço de tempo pode ocasionar problemas como: espaço livre em disco, tempo de backup, entre outros. Portanto, é importante monitorar a quantidade de redo log switches ocorridas na última hora. Vale a pena salientar que o tamanho do arquivo de redo log online influencia na quantidade de alternâncias de log realizadas.

O cenário abaixo ocorreu em um ambiente de banco de dados com redo log de tamanho de 50 MB no qual o padrão era gerar em média 23 alternância de log por dia, ou seja, cerca de uma alternância por hora. No entanto, foi detectado que a partir do dia 09/05 às 11:00 hs, conforme resultado da consulta abaixo, as operações de redo log switches aumentaram drasticamente gerando cerca de 770 alternâncias por dia. O impacto disso foi um aumento significativo de archive logs gerados, consumindo espaço extra na flash recovery area. Uma vez detectado o problema, foi averiguado que a aplicação tinha sido configurada para fazer mais coisas do que deveria e que, a partir do dia 21/05 às 16:00 hs, as operações de alternância de redo log foram reduzidas significativamente.

SQL> select to_char(first_time,'DD/MM/YYYY') day,
  2  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  3  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  4  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  5  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  6  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  7  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  8  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  9  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
 10  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
 11  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
 12  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
 13  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
 14  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
 15  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
 16  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
 17  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
 18  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
 19  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
 20  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
 21  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
 22  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
 23  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
 24  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
 25  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
 26  sum(1) "TOTAL_IN_DAY"
 27  from v$log_history
 28  group by to_char(first_time,'DD/MM/YYYY')
 29  order by to_date(day) desc;

DAY        00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   TOTAL_IN_DAY
---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------------
21/05/2017   32   32   33   32   34   33   34   32   33   32   32   32   33   31   32   33    5    4    2    0    0    0    0    0          531
20/05/2017   32   32   32   33   33   33   34   33   32   32   35   32   32   33   32   33   32   33   32   32   32   32   33   32          781
19/05/2017   33   32   33   32   32   32   34   33   32   32   34   32   32   32   31   33   32   32   32   34   33   32   33   32          779
18/05/2017   33   32   32   32   32   32   34   33   32   32   33   33   32   33   33   31   32   32   32   33   33   32   32   32          777
17/05/2017   33   32   33   32   33   32   34   32   32   33   32   32   33   32   32   33   32   32   33   32   32   32   33   32          778
16/05/2017   32   32   31   32   32   32   33   32   32   31   32   32   31   32   32   32   32   32   32   33   33   32   33   32          769
15/05/2017   31   32   30   32   33   32   34   31   32   31   33   31   31   32   31   33   32   32   31   32   32   31   34   31          764
14/05/2017   31   32   32   31   33   31   33   31   31   31   33   31   32   31   32   31   32   32   32   32   31   31   33   32          761
13/05/2017   31   31   31   32   31   32   33   32   31   32   32   32   32   31   31   32   32   30   32   31   32   32   33   31          759
12/05/2017   31   31   32   32   31   32   33   31   32   32   32   31   31   32   30   31   32   32   31   33   31   32   32   32          759
11/05/2017   32   31   32   31   32   31   35   31   31   31   34   31   30   31   33   31   32   31   32   32   31   32   33   31          761
10/05/2017   31   31   31   31   34   31   33   32   31   31   32   31   32   31   31   31   31   32   32   32   31   32   32   32          758
09/05/2017    0    1    0    1    2    1    2    1    1    0    2   30   32   32   32   32   32   31   32   32   31   32   34   31          424
08/05/2017    0    1    1    0    3    1    3    0    1    1    1    1    1    1    1    1    1    1    1    2    1    1    2    1           27
07/05/2017    0    1    1    0    3    1    3    0    1    1    1    1    1    1    1    1    1    1    1    2    0    1    2    1           26
06/05/2017    0    1    0    1    1    1    3    0    1    1    2    1    1    1    1    1    1    1    1    2    1    1    2    1           26
05/05/2017    0    1    0    1    0    1    3    1    0    1    2    0    1    0    1    0    1    1    0    3    0    1    1    2           21
04/05/2017    1    0    1    0    1    1    4    1    0    1    2    1    0    1    0    1    1    0    1    2    0    1    2    1           23
03/05/2017    0    1    1    0    3    1    3    1    0    1    2    1    1    1    1    1    1    1    0    2    1    1    2    1           27
02/05/2017    1    1    0    1    3    1    2    1    1    1    2    1    1    1    1    1    2    1    1    1    1    1    2    1           29
01/05/2017    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1    2    1            5

21 linhas selecionadas.

Durante a análise, foi detectado que havia uma sessão no banco de dados que estava gerando mais dados de UNDO/REDO que as demais, conforme resultado da consulta abaixo.

SQL>   SELECT s.sid,
  2           s.serial#,
  3           s.username,
  4           s.program,
  5           t.used_ublk,
  6           t.used_urec
  7      FROM v$session s, v$transaction t
  8     WHERE s.taddr = t.addr
  9  ORDER BY 5 DESC, 6 DESC, 1, 2, 3, 4;

       SID    SERIAL# USERNAME            PROGRAM                USED_UBLK  USED_UREC
---------- ---------- ------------------- --------------------- ---------- ----------
       930      49433 SCOTT               JDBC Thin Client              88       6177
       633      53173 SCOTT               JDBC Thin Client              10        693
       250      58501 ADAM                JDBC Thin Client               9        692
        99      11223 ADAM                JDBC Thin Client               5        340
       851      51835 SCOTT               JDBC Thin Client               0          0

5 linhas selecionadas.

Outra consulta bastante útil é verificar a quantidade de "redo size" gerada para cada sessão no banco de dados.

SQL> SELECT s.sid,
  2         sn.SERIAL#,
  3         sn.username,
  4         n.name,
  5         ROUND (VALUE / 1024 / 1024, 2) redo_mb,
  6         sn.status,
  7         sn.TYPE
  8    FROM v$sesstat s
  9         JOIN v$statname n ON n.statistic# = s.statistic#
 10         JOIN v$session sn ON sn.sid = s.sid
 11   WHERE n.name LIKE 'redo size' AND s.VALUE <> 0
 12  ORDER BY redo_mb DESC;

       SID    SERIAL# USERNAME            NAME                    REDO_MB STATUS   TYPE
---------- ---------- ------------------- -------------------- ---------- -------- ----------
       848          1                     redo size               4397,06 ACTIVE   BACKGROUND
       771          1                     redo size               4367,24 ACTIVE   BACKGROUND
      1079          1                     redo size               2689,53 ACTIVE   BACKGROUND
       930      49433 SCOTT               redo size                838,87 INACTIVE USER
         8      18777 SCOTT               redo size                115,77 INACTIVE USER
       166      59825 SCOTT               redo size                172,59 INACTIVE USER
       707      52285 ADAM                redo size                141,95 INACTIVE USER
       622      34283 ADAM                redo size                139,28 INACTIVE USER
      1004      43893 ADAM                redo size                112,61 INACTIVE USER
      1009      39717 SCOTT               redo size                111,41 INACTIVE USER
       249      10097 ADAM                redo size                106,07 INACTIVE USER
       237      44045 SCOTT               redo size                181,37 INACTIVE USER
       699      40115 ADAM                redo size                174,25 INACTIVE USER
       391      58243 ADAM                redo size                154,91 INACTIVE USER

14 linhas selecionadas.

Por fim, segue abaixo uma consulta que retorna a porcentagem utilizada do arquivo de redo log online corrente antes de realizar uma operação de alternância de log.

SQL> SELECT le.lenum "Group#",
  2       le.leseq "Current log sequence No",
  3       ROUND (100 * cp.cpodr_bno / (le.lesiz - 28770), 2) "Percent Full",
  4       cp.cpodr_bno "Current Block No",
  5       le.lesiz * le.lebsz / 1024 / 1024 "Size of Log in MB"
  6  FROM x$kcccp cp, x$kccle le
  7  WHERE le.leseq = CP.cpodr_seq AND BITAND (le.leflg, 24) = 8;

    Group# Current log sequence No Percent Full Current Block No Size of Log in MB
---------- ----------------------- ------------ ---------------- -----------------
        13                  253996         37,4           372194                50

1 linha selecionada.

No mais, é recomendável que uma operação de alternância de log ocorra no mínimo a cada 20 ou 25 minutos, ou seja de 4 a 5 redo log switches por hora, caso contrário talvez seja necessário rever o tamanho dos arquivos de redo log online.


terça-feira, 18 de abril de 2017

Oracle Application Express (APEX)

Por Eduardo Legatti

Olá,

Para quem estiver interessado em criar aplicações ou até mesmo acessar um banco de dados Oracle online através de um navegador da internet, basta acessar o Oracle Application Express que é um ambiente para desenvolvimento de softwares  baseado no banco de dados Oracle. O cadastro é gratuito através do link https://apex.oracle.com/ e sua documentação pode ser acessada neste link.



https://apex.oracle.com/






sexta-feira, 3 de março de 2017

Oracle 12c Release 2 disponível para download

Por Eduardo Legatti

Olá,

Neste mês de Março/2017, a Oracle disponibilizou para download o Oracle 12c R2. Muitas características foram adicionadas desde o lançamento do Oracle 12c R1 conforme a documentação. Dentre algumas new features do Oracle 12c R2, podemos citar, por exemplo, o aumento da capacidade de criação de pluggable databases de 252 na Release 1 para 4096 na Release 2 e o aumento do tamanho máximo dos nomes de vários tipos de objetos e identificadores de 30 bytes para 128 bytes


 

Segue algumas outras características interessantes dessa nova release.

  • Shared Undo e Local Undo
  • Local TEMP Tablespaces
  • SQL*Plus Command History
  • Online Table Move where tables can be moved as an online operation without blocking any concurrent DML operations
  • Oracle Database Sharding
  • PDB Character Set
  • Flashback Pluggable Database
  • Near Zero Downtime PDB Relocation
  • PDB Hot Cloning
  • Online conversion of a nonpartitioned table to a partitioned table
  • Application Containers

quarta-feira, 15 de fevereiro de 2017

Tuning de intruções SQL: Prestar atenção ao plano de execução é o primeiro passo

Por Eduardo Legatti

Olá,

Em algum momento, todo DBA vai passar por aquela experiência de tentar melhorar a performance de uma instrução SQL e acreditar que o que foi feito até então vai resolver o problema de performance. Mas aí, após fazer as melhorias e executar o SQL, percebe-se que o mesmo plano de execução de baixa performance continua sendo utilizado. O objetivo desse artigo é chamar a atenção para que prestemos realmente mais atenção ao plano de execução gerado por uma instrução SQL antes de quebrar a cabeça com outras tentativas.

Avaliando a consulta abaixo na qual a performance não estava aceitável, é possível perceber alguns filtros e um JOIN entre as tabelas T1 e T2.

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND B.VALUE = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

Analisando estruturalmente as tabelas, verifiquei que as colunas que estão fazendo JOIN (B.VALUE = A.EMP_CODE)  são de tipos de dados e tamanhos diferentes.

SQL> desc T1
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 EMP_CODE                         NOT NULL NUMBER(15)
 NAME                                      VARCHAR2(255)
 HASH                                      VARCHAR2(32)

SQL> desc T2
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 DEP_CODE                        NOT NULL NUMBER(20)
 VALUE                                    VARCHAR2(4000)

Verificando os dados da tabela T2, verifiquei que não seria possível criar um índice na coluna VALUE pois o erro "ORA-01450: maximum key length (string) exceeded" seria emitido devido a limitação existente no Oracle do tamanho máximo de valores indexados de acordo com o tamanho do bloco de dados usado pela tablespace da tabela (8 KB neste caso). Bom, a idéia então foi criar um índice baseado em função na qual a mesma limitaria o tamanho da coluna VARCHAR2(4000) para o tamanho da coluna numérica da tabela T1 (EMP_CODE). Como o tamanho da coluna EMP_CODE da tabela T1 é NUMBER(15), irei criar um índice de função SUBSTR(VALUE,1,15) na tabela T2. Como na instrução SQL existem outros filtros, irei criar 2 índices conforme demonstrado abaixo.

SQL> create index idx_name_hash on t1 (name,hash) tablespace tbs_indx noparallel;
SQL> create index idx_substr_value_depcode on t2 (substr(value,1,15),dep_code) tablespace tbs_indx noparallel;

Após criados os índices e alterada a instrução SQL para utilizar a função SUBSTR, segue abaixo o plano de execução gerado:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926

11 linhas selecionadas.

Decorrido: 00:00:42

Plano de Execução
----------------------------------------------------------
Plan hash value: 540149683

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   1 |  COUNT                        |                            |       |       |            |          |
|*  2 |   HASH JOIN                   |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                         |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH              |     3 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T2                         |  5251K|    88M|   103K  (1)| 00:20:42 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."EMP_CODE"=TO_NUMBER(SUBSTR("VALUE",1,15)))
   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   5 - filter("B"."DEP_CODE"=13)

Foi possível verificar acima que a instrução SQL executou em 42 segundos, o que é péssimo, e que o índice IDX_NAME_HASH foi utilizado. No entanto, o índice de função criado na tabela T2 não foi usado e por isso está sendo realizado uma operação de TABLE ACCESS FULL na mesma em cerca de 5 milhões de linhas ao realizar uma operação de HASH JOIN. A questão é: porque o otimizador não utilizou o índice de função? Após alguns testes utilizando HINTS para forçar o uso do índice entre outras tentativas, consegui a tempo perceber que a resposta estava no próprio plano de execução na seção "Predicate Information".

É possível perceber que o Oracle implicitamente converteu o tipo de dado utilizando a função TO_NUMBER de modo a fazer o JOIN com a coluna EMP_CODE que é NUMBER(15), o que é compreensível. Neste caso, o problema pode ser resolvido com 2 opções: criar o índice como TO_NUMBER(SUBSTR(VALUE,1,15)) na tabela T1 ou utilizar a função TO_CHAR na coluna EMP_CODE como demonstrado abaixo:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = TO_CHAR(A.EMP_CODE)
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169

11 linhas selecionadas.

Decorrido: 00:00:00.09

Plano de Execução
----------------------------------------------------------
Plan hash value: 424040904

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   1 |  COUNT                        |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                           |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH                |     3 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2                           |     1 |    27 |     5   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_SUBSTR_VALUE_DEPCODE     |     4 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   6 - access(SUBSTR("VALUE",1,15)=TO_CHAR("A"."EMP_CODE") AND "B"."DEP_CODE"=13)


Pronto. Agora a instrução SQL executou em menos de 1 segundo com a utilização do índice IDX_SUBSTR_VALUE_DEPCODE.

sexta-feira, 13 de janeiro de 2017

Funções analíticas no Oracle: RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD

Por Eduardo Legatti

Olá,

Neste artigo irei abordar de forma simples e direta exemplos de uso de algumas funções analíticas que podemos utilizar nas instruções SQL com o Oracle. Ás vezes muitas dessas funções são ignoradas por quem está construindo uma instrução SQL. O uso de funções analíticas podem ajudar muito a tornar uma instrução SQL que até então é complexa em uma versão muito mais simples. Dentre os exemplos de funções analíticas que irei abordar estão RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD.

Todos os exemplos das funções analíticas terão como base a tabela T1 abaixo na qual existem 3 grupos de ID (10, 20 e 30) com seus respectivos valores na coluna VALUE. Vale a pena salientar que a tabela possui uma linha duplicada intencionalmente (ID: 20 e VALUE: 202).

SQL> select * from t1 order by 1,2;

        ID      VALUE
---------- ----------
        10        101
        10        102
        10        103
        20        201
        20        202
        20        202
        20        203
        30        301
        30        302
        30        303

10 linhas selecionadas.

RANK


A função analítica RANK tem como objetivo retornar a classificação de cada linha de um conjunto de resultados. Por exemplo, abaixo irei classificar ou criar um rank para as linhas da tabela T1 de acordo com os valores da coluna VALUE ordenados de forma ascendente. Vale a pena salientar que valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 6 foi perdida.

SQL> select id,
  2         value,
  3         rank() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          5
        20        203          7
        30        301          8
        30        302          9
        30        303         10

10 linhas selecionadas.

Caso a intenção seja o de gerar a mesma classificação de acordo com os valores da coluna VALUE ordenados de forma ascendente, mas agora agrupando por ID, bastará apenas utilizar a palavra chave PARTITION BY. Como dito anteriormente, valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 3 foi perdida.

SQL> select id,
  2         value,
  3         rank() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          2
        20        203          4
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.


DENSE_RANK

A função analítica DENSE_RANK age da mesma forma que a função RANK, porém com a diferença nos valores de classificação do rank. Os valores gerados serão consecutivos, mas os valores duplicados ainda continuarão com rank repetidos.
 
SQL> select id,
  2         value,
  3         dense_rank() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          5
        20        203          6
        30        301          7
        30        302          8
        30        303          9

10 linhas selecionadas.

SQL> select id,
  2         value,
  3         dense_rank() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          2
        20        203          3
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.


ROW_NUMBER

A função analítica ROW_NUMBER tem como objetivo gerar um valor único para a linha retornada da mesma forma que a pseudo coluna ROWNUM faz. Neste caso os valores da classificação serão sempre consecutivos.

SQL> select rownum from dual connect by level <=10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.


SQL> select id,
  2         value,
  3         row_number() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          6
        20        203          7
        30        301          8
        30        302          9
        30        303         10

10 linhas selecionadas.


SQL> select id,
  2         value,
  3         row_number() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          3
        20        203          4
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.

Um uso muito comum para uso da função analítica ROW_NUMBER é utilizá-la em instruções SQL que precisam obter valores de um conjunto de dados no qual precisam ser retornados os N maiores ou menores valores de cada grupo. Por exemplo, o SQL abaixo irá retornar as duas linhas com os maiores valores (VALUE) de cada grupo (ID).

SQL> select *
  2   from (select id,
  3                value,
  4                row_number () over (partition by ID order by VALUE) rank
  5           from t1)
  6  where rank <= 2;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        20        201          1
        20        202          2
        30        301          1
        30        302          2

6 linhas selecionadas.


FIRST_VALUE

A função analítica FIRST_VALUE irá retornar o primeiro valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o primeiro valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o menor valor (primeiro) de cada grupo de ID será retornado para cada linha.

SQL> select id,
  2         value,
  3         first_value(value) over (partition by ID order by VALUE range
  4                                  between unbounded preceding and unbounded following) rank
  5  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101        101
        10        102        101
        10        103        101
        20        201        201
        20        202        201
        20        202        201
        20        203        201
        30        301        301
        30        302        301
        30        303        301

10 linhas selecionadas.


LAST_VALUE


A função analítica LAST_VALUE irá retornar o último valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o último valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o maior valor (último) de cada grupo de ID será retornado para cada linha.
 
SQL> select id,
  2         value,
  3         last_value(value) over (partition by ID order by VALUE range
  4                                 between unbounded preceding and unbounded following) rank
  5  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101        103
        10        102        103
        10        103        103
        20        201        203
        20        202        203
        20        202        203
        20        203        203
        30        301        303
        30        302        303
        30        303        303

10 linhas selecionadas.


LAG

A função analítica LAG tem como objetivo acessar os dados de uma linha anterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas anteriores da coluna VALUE  (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 303 mostrou na coluna VALUE_PREVIOUS_1 o valor 302 que é exatamente o valor da linha anterior ao valor 303. Já a coluna VALUE_PREVIOUS_3 mostrou o valor 203 que é exatamente o valor das 3 linhas anteriores ao valor 303. Já a coluna VALUE_PREVIOUS_9 mostrou o valor 101 que é exatamente o valor das 9 linhas anteriores ao valor 303.

SQL> select id,
  2         value,
  3         lag(value,1,0) over (order by value) AS value_previous_1,
  4         lag(value,3,0) over (order by value) AS value_previous_3,
  5         lag(value,9,0) over (order by value) AS value_previous_9
  6  from   t1;

        ID      VALUE VALUE_PREVIOUS_1 VALUE_PREVIOUS_3 VALUE_PREVIOUS_9
---------- ---------- ---------------- ---------------- ----------------
        10        101                0                0                0
        10        102              101                0                0
        10        103              102                0                0
        20        201              103              101                0
        20        202              201              102                0
        20        202              202              103                0
        20        203              202              201                0
        30        301              203              202                0
        30        302              301              202                0
        30        303              302              203               101

10 linhas selecionadas.


LEAD

A função analítica LEAD tem como objetivo acessar os dados de uma linha posterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas posteriores da coluna VALUE  (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 101 mostrou na coluna VALUE_NEXT_1 o valor 102 que é exatamente o valor da linha posterior ao valor 101. Já a coluna VALUE_NEXT_3 mostrou o valor 201 que é exatamente o valor das 3 linhas posteriores ao 101. Já a coluna VALUE_NEXT_9 mostrou o valor 303 que é exatamente o valor das 9 linhas posteriores ao valor 101.

SQL> select id,
  2         value,
  3         lead(value,1,0) over (order by value) AS value_next_1,
  4         lead(value,3,0) over (order by value) AS value_next_3,
  5         lead(value,9,0) over (order by value) AS value_next_9
  6  from   t1;

        ID      VALUE VALUE_NEXT_1 VALUE_NEXT_3 VALUE_NEXT_9
---------- ---------- ------------ ------------ ------------
        10        101          102          201          303
        10        102          103          202            0
        10        103          201          202            0
        20        201          202          203            0
        20        202          202          301            0
        20        202          203          302            0
        20        203          301          303            0
        30        301          302            0            0
        30        302          303            0            0
        30        303            0            0            0

10 linhas selecionadas.

segunda-feira, 5 de dezembro de 2016

Tamanho máximo de um arquivo de dados no Oracle (Physical Database Limits)

Por Eduardo Legatti

Olá,

No artigo de Novembro/2016 eu abordei sobre o erro "ORA-00059: maximum number of DB_FILES exceeded". Existe um outro erro ORA- muito conhecido entre os DBAs Oracle que é o erro "ORA-01144 File Size exceeds maximum of 4194303 Blocks" que significa que o DBA tentou redimensionar o tamanho do datafile além do limite máximo. Lendo a documentação do Oracle 11g R2 e do Oracle 12c R1, é possível determinar que esses limites não se alteraram na nova versão (12c), ou seja, um arquivo de dados (datafile) em um banco de dados Oracle pode conter no máximo cerca de 4 milhões de blocos (2^22 que dá exatamente 4194303 blocos). Vale a pena salientar que à partir do Oracle 10g foi criada uma tablespace do tipo BIGFILE, ou seja, ela é um tipo especial de tablespace que pode conter apenas um único datafile. No entanto, este datafile pode conter no máximo cerca de 4 bilhões de blocos (2^32 que dá exatamente 4294967295 blocos). Como o tamanho de um datafile é definido pelo tamanho do bloco de dados (block size) utilizado pelo banco de dados ou pela tablespace, segue abaixo um quadro comparativo contendo o tamanho máximo que um datafile pode ter de acordo com o tamanho do bloco de dados utilizado.


No mais, vale a pena salientar que o número máximo de datafiles que um banco de dados Oracle pode conter é 65533. O número máximo de tablespaces não pode exceder 65536 e o número máximo de datafiles por tablespace geralmente é 1022.

segunda-feira, 7 de novembro de 2016

Abordando o erro ORA-00059: maximum number of DB_FILES exceeded

Por Eduardo Legatti

Olá,

Por padrão, quando criamos um banco de dados no Oracle, o número máximo de arquivos de dados (datafiles) que o mesmo pode suportar é 100. Essa informação fica armazenada no arquivo de controle (control file). Até o Oracle 8 quando esse limite era atingido o erro "ORA-1118: cannot add any more data files: limit of % exceeded" era emitido informando que não era mais possível adicionar novos datafiles. Para corrigir esse problema o control file deveria ser recriado de forma que o valor MAXDATAFILES fosse incrementado. A partir do Oracle 8i foi eliminado a necessidade de recriação do control file, bastando apenas setar o parâmetro de inicialização DB_FILES (valor padrão é 200) com um novo valor. Neste caso, o control file se expandirá automaticamente para acomodar novos registros dentro de suas seções. Vale a pena salientar que este parâmetro não é dinâmico, ou seja, o banco de dados precisa ser reinicializado após a alteração no mesmo. Caso o valor de DB_FILES seja atingido, o erro "ORA-00059: maximum number of DB_FILES exceeded" será emitido. Para simular o erro ORA-00059, irei recriar o control file setando MAXDATAFILES com um valor menor, bem como o parâmetro DB_FILES. Portanto, segue abaixo uma simulação.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 10:06:11 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning option

SQL> alter database backup controlfile to trace as '/tmp/controlfile.sql';

Database altered.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "BD01" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 5
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/BD01/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/BD01/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/BD01/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/oradata/BD01/system01.dbf',
 13    '/oradata/BD01/sysaux01.dbf',
 14    '/oradata/BD01/undotbs01.dbf',
 15    '/oradata/BD01/users01.dbf'
 16  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/BD01/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Como demonstrado acima, eu recriei o control file com o valor de MAXDATAFILES igual a 5. Logo abaixo irei setar o valor de DB_FILES para 6.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_files                             integer     6

Realizando uma consulta na view dinâmica de desempenho V$CONTROLFILE_RECORD_SECTION abaixo é possível notar que a seção DATAFILE comporta no máximo 5 registros e que atualmente existem 4 registros em uso. Este valor bate com o número de datafiles existentes no banco de dados conforme demonstrado pela view DBA_DATA_FILES.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520             5            4

SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
         4

Agora irei simular a criação de arquivos de dados no banco de dados conforme a seguir.       

SQL> create tablespace tbs01 datafile '/oradata/BD01/tbs01.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs02 datafile '/oradata/BD01/tbs02.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

É possível perceber que na criação da tablespace TBS03 o erro ORA-00059 foi emitido, ou seja, o control file tentou se expandir para acomodar o novo datafile, mas foi impedido pela limitação do parâmetro DB_FILES que está setado para 6. Para resolver o problema, irei aumentar o valor do mesmo e reinicializar a instância.

SQL> alter system set db_files=200 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
Database mounted.
Database opened.

Após a alteração do parâmetro DB_FILES e reinicialização da instância, irei tentar criar novamente a tablespace TBS03.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;

Tablespace created.

Pronto. É possível verificar que o control file foi expandido para acomodar mais registros.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520            35            7

Para finalizar, segue abaixo um trecho do arquivo de alerta da instância mostrando que a seção 4 do control file foi expandida para acomodar novos registros.

Mon Nov 7 10:21:51 2016
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
Expanded controlfile section 4 from 5 to 35 records
Requested to grow by 30 records; added 1 blocks of records
Completed: create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M

Postagens populares