Blog Flávio Soares
Oracle, Database, Data Guard, RAC, Performance, Exadata, ASM, Goldengate, Troubleshooting
December 12, 2012
Só nós DBA’s sabem as inúmeras funções, sintaxes e comandos que precisamos decorar no nosso dia a dia e isso não é uma tarefa fácil, além do que, as soluções sempre precisam ser rapidamente propostas. Por isso que gosto de facilitar as coisas sempre que posso, assim como esse script, que além de simples ele realiza é uma formarápida os passos que nem sempre são lembrados, isso evita aquele tempão gasto procurando na documentação a sintaxe exata de tal procedimento. Qual DBA que nunca passou por isso que atire a primeira pedra!
O objetivo principal do script é facilitar a execução do Oracle Segment Advisor, de uma forma bem simples, apenas três argumentos e nada mais e o melhor de tudo: não é preciso criar qualquer tipo de objeto no banco de dados, todo trabalho é executado através de um bloco anônimo de PLSQL e o único requisito aqui é o bom e velho SQL*Plus.
Você pode conferir a primeira versão (sim, próximas poderão surgir) você pode fazer aqui.
Como usar Segment Advisor Script?
SYNTAX: { @run_sa “Owner Segment” “Segment Name” “Segment Type” }
Ele não precisa nada mais além do que três argumento:
1. “Owner Segment” : O primeiro representa o nome do owner do segmento.
2. “Segment Name”: O segundo representa o nome do segmento.
3. “Segment Type”: O terceiro representa o tipo do segmento. Que pode ser um TABLE, INDEX etc …
Usando o Segment Advisor Script
Para explicar melhor vamos a um teste prático.
Primeiro de tudo, precisamos de uma tabela grande favorável para o teste que vamos fazer, para isso criei a tabela chamada TBIG dentro do schema FSOARES.
FSOARES@dbtst> create table tbig as select * from dba_source; Table created. FSOARES@dbtst> insert into tbig (select * from tbig); 633054 rows created. FSOARES@dbtst> / 1266108 rows created. FSOARES@dbtst> / 2532216 rows created. ... FSOARES@dbtst> commit; FSOARES@dbtst> @size tbig SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ --------------- TBIG TABLE 5,244.00
Pronto, temos agora uma tabela de 5G que está perfeita para o nosso teste. Vamos ver quantos registros temos:
FSOARES@dbtst> set timing on
FSOARES@dbtst> select count(*) from tbig;
COUNT(*)
----------
40515456
1 row selected.
Elapsed: 00:04:08.21
Ok, temos cerca de 40515456 registros e levamos cerca de 4 minutos para sabermos isso. Vamos agora apagar uns 98% dessa tabela deixando apenas alguns milhares de registro. Para fazer essa operação mais racional, quero descobrir quantos registros tenho por usuário nessa tabela, afim de deixar apenas os menores owners:
FSOARES@dbtst> select count(*), owner from tbig group by owner order by 1;
COUNT(*) OWNER
---------- ------------------------------
576 IX
576 OUTLN
1088 PM
2176 FLOWS_FILES
2176 HR
2880 SYSTEM
13696 OE
14912 ORDPLUGINS
19392 WMSYS
70464 EXFSYS
183808 ORACLE_OCM
230528 DBSNMP
231552 ORDSYS
548288 XDB
731264 OLAPSYS
1258816 CTXSYS
1340416 MDSYS
2624256 APEX_030200
9466112 SYS
23772480 SYSMAN
20 rows selected.
Elapsed: 00:01:14.06
FSOARES@dbtst> delete tbig where owner NOT IN ('IX', 'OUTLN');
40514304 rows deleted.
Elapsed: 00:29:57.41
FSOARES@dbtst> commit;
Commit complete.
Elapsed: 00:00:00.01
Aqui, a nossa tabela TBIG está somente com os dados usuário IX e OUTLN, o resto dos outros usuário foi simplesmente apagado. Bom como temos agora apenas alguns registros vamos realizar aquele mesmo count para ver o a quantidade de registro, vamos ver agora o quanto tempo levará. Primeiro é claro, vamos remover a consulta do nosso cache.
FSOARES@dbtst> alter system flush shared_pool;
System altered.
FSOARES@dbtst> alter system flush buffer_cache;
System altered.
FSOARES@dbtst> select count(*) tbig
COUNT(*)
----------
1152
1 row selected.
Elapsed: 00:03:53.93
Temos agora cerca de mil registros e levamos quase o mesmo tempo para realizar o count da tabela quando ela estava com mais de 40 milhões de registro!? Como isso pode ser? Tivemos o mesmo tempo para contar de 0 a 1152 e de 0 a 40 milhões?
Bom, sem dúvidas há alguma coisa de errado com nosso segmento de tabela TBIG. É aí que entra o Segment Advisor, que vai nos aconselhar o que fazer com esse segmento.
Veja como é simples:
FSOARES@dbtst> @run_sa fsoares tbig table --------------------------------------------------------------------------------- -- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com ) Running the Segment Advisor for Segment Owner : FSOARES Segment Name: TBIG Segment Type: TABLE Segment Advisor successfuly completed For delete the task TaskName_FSOARES_cxdnLahXMf run: SQL> exec dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf'); -- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig" TABLESPACE_NAME : USERS SEGMENT_OWNER : FSOARES SEGMENT_NAME : TBIG SEGMENT_TYPE : TABLE PARTITION_NAME : ALLOC_MB : 5,244.00 RECLAIM_MB : 4,567.54 USED_MB : 676.46 PCT_SAVE : 87 % RECOMMENDATIONS : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes. SOLUTION 1 : alter table "FSOARES"."TBIG" shrink space SOLUTION 2 : alter table "FSOARES"."TBIG" shrink space COMPACT SOLUTION 3 : alter table "FSOARES"."TBIG" enable row movement ---------------------------------------------------------------------------------
Observe a recomendação, ele sugere realizar um shrink na tabela que ganharemos com isso cerca de 87% de espaço que hoje não está sendo utilizado. Opa! … é 87% é um bom ganho, então vamos aplicar as recomendações sugeridas.
FSOARES@dbtst> alter table "FSOARES"."TBIG" enable row movement; Table altered. FSOARES@dbtst> alter table "FSOARES"."TBIG" shrink space; Table altered.
Após as recomendações aplicadas, vamos agora executar novamente o count de encontro a tabela TBIG e observar o tempo:
FSOARES@dbtst> alter system flush shared_pool;
System altered.
FSOARES@dbtst> alter system flush buffer_cache;
System altered.
FSOARES@dbtst> select count(*) from tbig;
COUNT(*)
----------
1152
1 row selected.
Elapsed: 00:00:00.01
Depois da recomendação aplicada, o tempo simplesmente caiu para 0.01 segundos.
Viu como ficou bem mais simples utilizar o Segment Advisor agora com o run_sa.sql 
Com apenas três argumentos e já temos nossas recomendações.
FSOARES@dbtst> @run_sa fsoares tbig table --------------------------------------------------------------------------------- -- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com ) Running the Segment Advisor for Segment Owner : FSOARES Segment Name: TBIG Segment Type: TABLE Segment Advisor successfuly completed For delete the task TaskName_FSOARES_cxdnLahXMf run: SQL> exec dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf'); -- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig" TABLESPACE_NAME : USERS SEGMENT_OWNER : FSOARES SEGMENT_NAME : TBIG SEGMENT_TYPE : TABLE PARTITION_NAME : ALLOC_MB : 5,244.00 RECLAIM_MB : 4,567.54 USED_MB : 676.46 PCT_SAVE : 87 % RECOMMENDATIONS : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes. SOLUTION 1 : alter table "FSOARES"."TBIG" shrink space SOLUTION 2 : alter table "FSOARES"."TBIG" shrink space COMPACT SOLUTION 3 : alter table "FSOARES"."TBIG" enable row movement ---------------------------------------------------------------------------------
Dúvidas, melhorias, bugs, recomendações serão muito bem vindas!
Um abraço e aproveitem!
October 17, 2012
O erro ORA-00845 é disparado sempre no startup de uma instância Oracle 11g, falhando com a seguinte mensagem de erro:
ORA-845: MEMORY_TARGET not supported on this system
O parâmetro MEMORY_TARGET define o tamanho a área que o Automatic Memory Management (AMM) irá poder trabalhar. O AMM permite o banco de dados Oracle realizar a redução e aumento das áreas SGA ou PGA como necessário, fazendo assim um tuning online do seu banco de dados.
Como esse artigo não se destina a explicar o que é AMM, você pode conferir mais detalhes na própria documentação Oracle: http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory003.htm
O erro ORA-845 acontece quando você define um valor para o MEMORY_TARGET além do que o sistema consegue gerênciar. Vamos a um exemplo para ficar mais fácil
Em uma máquina de teste, tenho 5G de memória RAM:
[oracle@oracle11g ~]$ grep MemTotal /proc/meminfo MemTotal: 5079856 kB
Meu banco de dados, está com o MEMORY_TARGET para 1984M (quase 2G).
[oracle@oracle11g ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 23:13:59 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter memory NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 1984M memory_target big integer 1984M shared_memory_address integer 0
Uma outra informação importante (você vai entender logo mais), o meu espaço em disco:
[oracle@oracle11g ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 19G 9.0G 8.5G 52% / tmpfs 2.5G 1.2G 1.3G 48% /dev/shm
Quero agora aumentar 500M da minha AMM, vamo mudarentão o parâmetro MEMORY_TARGET para 2500M:
SQL> alter system set memory_max_target=2500M scope=spfile; System altered. SQL> alter system set memory_target=2500M scope=spfile; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup ORA-00845: MEMORY_TARGET not supported on this system SQL>
A primeira vez que vi o erro não consegui entender muito bem do que se tratava. Já que tenho memória livre de sobra, como pode 500M interferir no startup da minha instância?
Tudo ficou ainda mais confuso, quando li uma note no site do supporte da Oracle:
ORA-00845 When Starting Up An 11g Instance With AMM Configured. [ID 460506.1]
A nota diz claramente que o valor do parâmetro memory_target está relacionado com o tamanho da partição /dev/shm, e que para resolver o problema tenho que aumentar a partição /dev/shm para um valor maior:
# mount -t tmpfs shmfs -o size=7G /dev/shm
Feito isso minha instância subiu sem problema algum.
Ok … Perfeito, tudo funcionando …
Porém …
Surge agora o objetivo desse post, explicar o que o AMM tem q ver com a partição /dev/shm?!
A dúvida inicial era, por onde começar?
Bom … Existe um utilitário chamado sysresv sobre o diretório $ORACLE_HOME/bin para obter status da instância. Algumas vezes quando uma instância Oracle é abortada ou simplesmente um crash acontece, existe alguns “semaphores” ou “shared memory” que continuam ativados mesmo sem a instância no ar. Quando esses lixos (vamos dizer assim) ficam na memória a instância não consegui iniciar.
Para resolver esse problema a partir do 8i, a Oracle começou a fornecer o sysresv como um utilitário que limpa shared memory e sempahores além de visualizar processos IPC alocados, que é o que queremos. Mais detalhes veja a nota: Semaphores and Shared Memory – An Overview [ID 153961.1]
Em nosso ambiente:
[oracle@oracle11g ~]$ sysresv IPC Resources for ORACLE_SID "dbtst" : Shared Memory: ID KEY 3506180 0x3c8e282c Semaphores: ID KEY 688129 0x00fadcd4 Oracle Instance alive for sid "dbtst"
Ok, já temos a informação do Oracle que contém somente um segmento de memory shared definida pelo ID: 3506180
E no Linux? Como ele pode me falar?
No Linux assim como no Unix, é possível de obter detalhes das shared memories através do comando ipcs:
[oracle@oracle11g ~]$ ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 3080192 root 644 80 2 0x00000000 3112961 root 644 16384 2 0x00000000 3145730 root 644 280 2 0x3c8e282c 3506180 oracle 660 4096 0
Opa! Uma informação importante, o segmento 0x3c8e282c está sendo utilizado … porém existe somente um segmento de 4k (4096 bytes), valor muito pequeno comparado com outras versões.
Na versão 9i por exemplo esse valor é bem maior bem maior veja:
[oracle@oracle9i ~]$ ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x90129508 65537 oracle 640 337641472 9
Com isso, percebemos que o segmento de memória do 11g, tornou-se muito menor em relação as outras versões. Mais isso nos leva a uma pergunta: Porque ele mudou isso?
A unica conclusão que cheguei foi que ele precisava desalocar e alocar rapidamente (função do AMM) e com segmentos menores isso se torna mais rápido. Precisava confirmar isso, e iniciei uma busca de como obter mais detalhes como tamanho e endereço dos segmentos de memória.
Em busca disso encontrei a resposta no “mapeamento dos segmentos de memória” que é visualizada através do comando pmap.
oracle@oracle11g ~]$ pmap `pgrep -f lgwr` 5601: ora_lgwr_dbtst 0000000000400000 180232K r-x-- /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle 000000000b602000 1820K rwx-- /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle 000000000b7c9000 300K rwx-- [ anon ] 000000000d775000 276K rwx-- [ anon ] 0000000060000000 4K r-xs- /dev/shm/ora_dbtst_3506180_0 0000000060001000 16380K rwxs- /dev/shm/ora_dbtst_3506180_0 0000000061000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_1 0000000062000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_2 ... 00000000da000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_122 00000000db000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_123 00000000dc000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_124 00000000dd000000 16384K rwxs- /dev/shm/ora_dbtst_3506180_125 00007f962c052000 1088K rwx-- /dev/zero 00007f962c162000 1088K rwx-- /dev/zero 00007f962c272000 1920K rwx-- /dev/zero 00007f962c452000 1088K rwx-- /dev/zero 00007f962c562000 1088K rwx-- /dev/zero ... 00007f962cfe0000 128K rwx-- /dev/zero 00007f962d000000 8K rwx-- /dev/zero 00007f962d142000 40K r-x-- /lib64/libnss_files-2.5.so 00007f962d14c000 2044K ----- /lib64/libnss_files-2.5.so 00007f962d34b000 4K r-x-- /lib64/libnss_files-2.5.so 00007f962d34c000 4K rwx-- /lib64/libnss_files-2.5.so 00007f962e062000 2048K ----- /lib64/libdl-2.5.so (deleted) 00007f962e262000 4K r-x-- /lib64/libdl-2.5.so (deleted) 00007f962e263000 4K rwx-- /lib64/libdl-2.5.so (deleted) ... 00007f962feec000 4K rwx-- /lib64/ld-2.5.so (deleted) 00007fff02cdf000 84K rwx-- [ stack ] 00007fff02d8d000 4K r-x-- [ anon ] ffffffffff600000 4K r-x-- [ anon ]
A saída do comando pmap para o processo LGWR mostra que o Oracle usa o /dev/shm para o compartilhamento de memória. De fato, os arquivos realmente existem na partição /dev/shm
oracle@oracle11g ~]$ ls -ltr /dev/shm/ total 1210004 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_125 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_107 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_108 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_109 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_110 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_105 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_104 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_106 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_9 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_8 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_7 ... -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_13 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_12 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_11 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_10 -rw-r----- 1 oracle oinstall 0 Oct 16 23:38 ora_dbtst_3506180_1 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:38 ora_dbtst_3506180_116 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:43 ora_dbtst_3506180_92 -rw-r----- 1 oracle oinstall 16777216 Oct 16 23:43 ora_dbtst_3506180_87 .. -rw-r----- 1 oracle oinstall 16777216 Oct 17 00:14 ora_dbtst_3506180_120 -rw-r----- 1 oracle oinstall 16777216 Oct 17 00:14 ora_dbtst_3506180_0 -rw-r----- 1 oracle oinstall 16777216 Oct 17 00:14 ora_dbtst_3506180_124
Existem aí vários arquivos de 16M de tamanho, o que responde nossa primeira dúvida. O Oracle utiliza desses pequenos arquivos para armazenar os dados de segmentos compartilhados, isso é feito devida a implementação POSIX onde tudo, inclusive um “segmento de memória compartilhada” é um arquivo.
A grande sacada do AMM é permitir deslocar memória compartilhada da SGA para a memória privada da PGA e com esses pequenos segmentos de 16M torna a operação rápida e tudo isso acontece na partição /dev/shm.
Na medida que aumento ou diminuo o tamanho da PGA através do parâmetro PGA_AGGREGATE_TARGET, os arquivos dentro da partição /dev/shm vão se movimentando de acordo com o valor passado, mostrando assim o deslocamento das memórias compartilhadas e privadas.
Um outro exemplo interessante acontece quando eu baixo a instância:
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle11g ~]$ ll /dev/shm/ total 0
Como era de imaginar, nenhum arquivo existe mais na /dev/shm
Ficou mais claro agora, porque do erro ORA-00845?
July 18, 2012
A feature já diz tudo!
A Criação Adiada de Segmentos (Deferred Segment Creation) realmente faz o que o nome diz, somente é criado um segmento quando uma linha é inserida e não quando criamos o objeto, funcionalidade essa habilitada por DEFAULT a partir do Oracle 11G R2 através do parâmetro de inicialização DEFERRED_SEGMENT_CREATION.
SQL> show parameter deferred NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE
Veja aqui que quando crio a tabela T1, nenhum segmento é criado com ela:
SQL> create table T (id number); Table created. SQL> select * from user_segments; no rows selected
Somente então quando insiro a primeira linha na tabela que o segmento é criado:
SQL> insert into T values(1); 1 row created. SQL> commit; Commit complete. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- T SQL> select table_name,segment_created from user_tables; TABLE_NAME SEG ------------------------------ --- T YES
Ok, Você deve estar se perguntando agora … quais as reais vantagens dessa feature? Vamos lá …
Economia de espaço. Lembra aquelas tabelas que são criadas mais nunca populadas? Pois então, uma grande quantidade de dados pode ser salvada para as centenas ou milhares de tabelas criadas pela aplicação que nunca foram usadas.
Quando se vai instalar uma aplicação, o tempo da criação das DDL pode ser reduzidas drasticamente apenas evitando a criação do segmento em disco. Quando estou preste a fazer a carga inicial do GoldenGate em que a estrutura das tabelas de destinos devem estar iguais com as tabelas da origem, sempre antes de realizar a importação da estrutura das tabelas verifico o parâmetro DEFERRED_SEGMENT_CREATION, o tempo gasto para criar os segmentos é muito grande é um tempo desnecessário, já que ele será criado logo após o primeiro INSERT.
Como esse parâmetro é dinâmico, pode ser alterado a qualquer:
SQL> show parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> alter system set deferred_segment_creation=FALSE; System altered. SQL> show parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean FALSE SQL> create table T1 (id number); Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- T1
E também é possível a alteração desse comportamento a nível de sessão, apenas com um alter session:
SQL> alter session set deferred_segment_creation=true;
Caso você não queira alterar a nível de sistema ou de sessão, a Oracle pensou em você. É capaz também de alterar esse comportamento no momento da criação da tabela:
SQL> select * from user_segments; no rows selected SQL> CREATE TABLE TABELA_SEGMENTO1 (id number) SEGMENT CREATION IMMEDIATE; Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TABELA_SEGMENTO1 SQL>CREATE TABLE TABELA_SEGMENTO2 (C1 number, C2 number) SEGMENT CREATION DEFERRED; Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TABELA_SEGMENTO1
