Sobre o certo e o errado
Nosso especialista em tecnologia verifica a documentação e testa as respostas.
Tenho uma pergunta sobre SPFILEs e alterações de parâmetros. Se eu fizer uma alteração em um parâmetro durante a execução de uma instância (usando o script alter system set <parameter>. . .), a mudança será aplicada na instância em execução ou será aplicada somente quando o banco de dados for reiniciado? Estou perguntando porque meus colegas dizem que as modificações feitas enquanto um arquivo de parâmetros armazenado (SPFILE) está em uso são aplicadas somente quando o banco de dados é reiniciado.
Seus colegas estão errados; eles devem verificar a documentação ou pelo menos experimentar o procedimento antes de dispensá-lo. Invariavelmente verifico ou testo, pois sempre que respondo alguma dúvida em “Pergunte ao Tom” e não comprovo o que digo, acabo me dando mal!
Quanto ao momento de efetivação da alteração, existe um parâmetro SCOPE na instrução ALTER SYSTEM que permite especificar quando a alteração entra em vigor. O parâmetro SCOPE pode ser definido como MEMORY, SPFILE ou BOTH. Se você definir SCOPE como MEMORY, a instrução ALTER SYSTEM mudará a instância (se o parâmetro for alterável sem reinicialização). Se o parâmetro SCOPE for definido como SPFILE, a instrução ALTER SYSTEM atualizará somente o arquivo de parâmetros e a mudança entrará em vigor quando o BD for reiniciado. Se você usar BOTH (disponível somente se o parâmetro for alterável com o banco de dados em execução), a instrução ALTER SYSTEM mudará a instância e o arquivo de parâmetros armazenado.
Mostre aos seus colegas o conteúdo do Listing 1.
Code Listing 1: Como alterar um parâmetro da instância atual em execução
SQL> show parameter user_dump_dest
NAME TYPE VALUE
--------------- ------ ------------------------
user_dump_dest string /home/ora10gr2/rdbms/log
SQL> alter session set sql_trace=true;
Session altered.
SQL> select c.value || '/' ||
2 d.instance_name ||
3 '_ora_' ||
4 a.spid ||
5 '.trc' trace
6 from v$process a, v$session b,
7 v$parameter c, v$instance d
8 where a.addr = b.paddr
9 and b.audsid = userenv('sessionid')
10 and c.name = 'user_dump_dest'
11 /
TRACE
------------------------------
/home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc
SQL> !ls -l /home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc
-rw-rw---- 1 ora10gr2 ora10gr2 286874 Oct 29 10:21
/home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc
...so trace files are going to /home/ora10gr2/rdbms/
log/ora10gr2_ora_18556.trc...
SQL> alter system set user_dump_dest = '/tmp' scope=both;
System altered.
SQL> connect /
Connected.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select c.value || '/' ||
2 d.instance_name ||
3 '_ora_' ||
4 a.spid ||
5 '.trc' trace
6 from v$process a, v$session b,
7 v$parameter c, v$instance d
8 where a.addr = b.paddr
9 and b.audsid = userenv('sessionid')
10 and c.name = 'user_dump_dest'
11 /
TRACE
------------------------------
/tmp/ora10gr2_ora_18562.trc
SQL> !ls -l /tmp/ora10gr2_ora_18562.trc
-rw-rw---- 1 ora10gr2 ora10gr2 4014 Oct 29 10:22
/tmp/ora10gr2_ora_18562.trc
. . . and now they are not.
Nem todo parâmetro pode ser modificado quando a instância está em execução; os parâmetros são divididos em três categorias gerais:
1. Não alterável on-line. O Oracle Database Reference descreve, entre outras coisas, os parâmetros de inicialização e respectivas propriedades. A documentação inclui uma propriedade “modifiable” para cada parâmetro e se um deles não for alterável, não poderá ser modificado on-line. AUDIT_TRAIL, por exemplo, não é modifiable (não pode ser alterado on-line).
2. Alterável on-line, mas somente para sessões futuras. A alteração não afetará a sessão em andamento, mas modificará todas as novas sessões criadas após a execução da instrução ALTER SYSTEM. Por exemplo, SORT_AREA_SIZE pode ser modificado on-line, mas a mudança entrará em vigor somente nas sessões futuras:
SQL> alter system
2 set sort_area_size =32765
3 deferred scope=memory;
System altered.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------- ------- -----
sort_area_size integer 65536
SQL> connect /
Connected.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------- ------- -----
sort_area_size integer 32765
3. Alterável on-line e as modificações são refletidas imediatamente em todas as sessões. A alteração é propagada para todas as sessões conectadas no momento. Por exemplo, USER_DUMP_DEST é alterável on-line e refletido imediatamente em todas as sessões:
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------- ------- -----
user_dump_dest string /tmp
SQL> alter system
set user_dump_dest =
'/home/ora10gr2/rdbms/log';
System altered.
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------- ------- -----
user_dump_dest string /home/ora10...
A próxima pergunta que normalmente surge é, “tudo bem, definimos um valor, mas gostaríamos de ‘cancelá-lo’”. Em outras palavras, não queremos mais o parâmetro definido em nosso arquivo SPFILE e resolvemos removê-lo. Já que não é possível editar o arquivo usando um editor de texto, como podemos fazer a exclusão?” Esse procedimento também é feito via ALTER SYSTEM, mas com uma cláusula RESET:
alter system
reset parameter
<scope=memory|spfile|both> sid='sid|*'
Portanto, por exemplo, se você quiser remover o parâmetro SORT_AREA_SIZE para que ele assuma o valor padrão cancelado anteriormente, faça o seguinte:
SQL> alter system reset
sort_area_size scope=spfile sid='*';
System altered.
O parâmetro SORT_AREA_SIZE é removido do arquivo SPFILE. Para verificar o procedimento, emita o seguinte:
SQL> create pfile='/tmp/pfile.tst'
from spfile;
File created.
Em seguida, você poderá revisar o conteúdo do arquivo /tmp/pfile.tst que será gerado no servidor de banco de dados e verá que SORT_AREA_SIZE não existe mais nos arquivos de parâmetros.
Armazenamento amplo de carga
Estou trabalhando no site de um cliente que tem um data warehouse com tabelas de fatos particionadas com cerca de 400 colunas. O desempenho não é satisfatório e estou tentando otimizar um pouco o sistema.
O cliente disse que a maior parte das colunas é consultada raramente, ou nunca, mas ele quer armazenar os dados por precaução. Então, minha ideia de acelerar a inevitável varredura de tabela completa, derrubando todas as colunas nunca utilizadas, foi rejeitada. Obviamente, pretendo armazenar um conjunto redundante de tabelas mais enxutas e comprimi-las para reduzi-las ainda mais, mas o problema é o espaço (sabe aquela história de “não podemos investir em mais capacidade de armazenamento”?) e os usuários teriam de saber em qual tabela consultar uma determinada coluna. O cliente quer apenas a lógica ETL [extrair, transformar e carregar] no banco de dados, sem nenhuma lógica de consulta, pois “é para isso que temos nossas ferramentas de BI”. Você tem alguma sugestão de como ajustar um banco de dados como esse?
“Não podemos investir mais em espaço de armazenamento”: Bem, se quiserem melhor desempenho, é interessante reconsiderar o assunto. Às vezes é preciso aumentar o espaço de armazenamento. Os índices consumem espaço extra, visões materializadas idem, e ambos são vitais para o desempenho do data warehouse.
Entretanto, talvez você consiga usar particionamento vertical neste caso e adicionar uma nova restrição de chave primária, que resultará em um índice (mas esse índice é desejável para reunir os dados novamente!).
Se você informar o banco de dados Oracle que há uma relação um-para-um obrigatória entre as duas tabelas, poderá incluir uma eliminação de tabela no plano. Para a relação um-para-um ser obrigatória, deverá haver uma chave primária na coluna de junção, além de chaves estrangeiras recíprocas que poderão ser validadas ou não se você carregar os dados limpos — basta dizer que eles existem. O código na Listing 2 faz isso informando o banco de dados da existência de uma chave estrangeira.
Code Listing 2: Como criar restrições e tabelas T1 e T2
SQL> create table t1 as select
2 OBJECT_ID,
3 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
4 DATA_OBJECT_ID, OBJECT_TYPE
5 from all_objects where 1=0;
Table created.
SQL> alter table t1
2 add constraint t1_pk
3 primary key(object_id)
4 rely;
Table altered.
SQL> create table t2 as select
2 OBJECT_ID,
3 CREATED, LAST_DDL_TIME, TIMESTAMP,
4 STATUS, TEMPORARY, GENERATED, SECONDARY
5 from all_objects where 1=0;
Table created.
SQL> alter table t2
2 add constraint t2_pk
3 primary key(object_id)
4 rely;
Table altered.
Usaremos o pacote DBMS_STATS para dizer ao otimizador que T1 e T2 são tabelas grandes, como seriam na vida real:
SQL> begin
2 dbms_stats.set_table_stats
3 ( user, 'T1',
4 numrows => 100000000,
5 numblks => 1000000 );
6 dbms_stats.set_table_stats
7 ( user, 'T2',
8 numrows => 100000000,
9 numblks => 1000000 );
10 end;
11 /
PL/SQL procedure successfully completed.
Em seguida, os aplicativos usarão a exibição VW, mostrada na Listing 3, que oculta a existência de duas tabelas embaixo.
Code Listing 3: Como criar restrições e uma exibição VW
SQL> create or replace view vw
2 as
3 select
4 t1.OBJECT_ID,
5 t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
6 t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
7 t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
8 t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
9 from t1, t2
10 where t1.object_id = t2.object_id;
View created.
SQL> alter table t2
2 add constraint t2_fk_t1
3 foreign key(object_id)
4 references t1(object_id)
5 rely disable novalidate;
Table altered.
SQL> alter table t1
2 add constraint t1_fk_t2
3 foreign key(object_id)
4 references t2(object_id)
5 rely disable novalidate;
Table altered.
Quando precisamos de colunas das duas tabelas, o desempenho é prejudicado, pois temos de uni-las novamente. Será usado um índice se a consulta retornar um pequeno conjunto de linhas ou um grande hash join (junção hash) se houver muitas linhas no resultado, como mostra o Listing 4.
Code Listing 4: Consulta em exibição VW que requer hash join e varredura de tabela completa
SQL> set autotrace traceonly explain
SQL> select * from vw;
Execution Plan
-----------------------------------------
Plan hash value: 2959412835
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100M| 13G| | 1490K (3)|
|* 1 | HASH JOIN | | 100M| 13G| 6198M| 1490K (3)|
| 2 | TABLE ACCESS FULL | T2 | 100M| 5054M| | 317K (4)|
| 3 | TABLE ACCESS FULL | T1 | 100M| 8392M| | 316K (4)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Mas se quase nenhuma consulta acessar as colunas da segunda tabela (T2), você poderá removê-la totalmente do plano de consulta. Tal resultado é obtido de modo transparente. Como mostra a Listing 5, você não terá de fazer nada para que isso aconteça.
Code Listing 5: Como remover a T2 de modo transparente do plano de consultas
SQL> select OWNER, OBJECT_NAME,
2 SUBOBJECT_NAME,
3 DATA_OBJECT_ID,
4 OBJECT_TYPE
5 from vw;
Execution Plan
----------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte | Cost (%CPU) | Time
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100M| 8392M| 316K (4)| 00:25:03
| 1 | TABLE ACCESS FULL | T1 | 100M| 8392M| 316K (4)| 00:25:03
--------------------------------------------------------------------------
Observe que a capacidade de eliminar tabelas desnecessárias é uma novidade do Oracle Database 10g Release 2. E, finalmente, sim, você pode usar a instrução COMPRESS nas tabelas!
Uma solução alternativa seria usar um índice para criar uma versão enxuta dos dados consultados com frequência. Quando possível, o otimizador usa uma varredura completa rápida de índice (um caminho de execução do tipo varredura completa usando uma entrada/saída multiblocos como uma varredura completa de tabelas) para ler o subconjunto de colunas e dar acesso eficiente a todos os dados na tabela sempre que necessário. Por exemplo, com base no exemplo anterior, imagine que determinadas seis colunas sejam as mais consultadas. Nesse caso, você criaria uma versão enxuta dos dados como mostra o Listing 6.
Code Listing 6: Como criar uma versão enxuta dos dados
SQL> create table t1 as
2 select *
3 from all_objects
4 where 1=0;
Table created.
SQL> create index t1_idx on
2 t1( OBJECT_ID, OWNER,
3 OBJECT_NAME, SUBOBJECT_NAME,
4 DATA_OBJECT_ID, OBJECT_TYPE );
Index created.
SQL> begin
2 dbms_stats.set_table_stats
3 ( user, 'T1',
4 numrows => 100000000,
5 numblks => 1000000 );
6 dbms_stats.set_index_stats
7 ( user, 'T1_IDX',
8 numrows => 100000000,
9 numlblks => 1000000/2 );
10 end;
11 /
PL/SQL procedure successfully completed.
Agora, o otimizador pode acessar duas estruturas — a tabela (T1) ou o índice (T1_IDX) — para recuperar essas seis colunas.
Observe que essas estruturas contam com pelo menos um dos atributos no índice definido como NOT NULL. Se todas as colunas no índice forem anuláveis, esta abordagem não funcionará, pois entradas totalmente nulas não são colocadas no índice. Desse modo, nem todas as linhas serão indexadas. Neste exemplo, a função OBJECT_ID é NOT NULL na tabela e, portanto, o índice apontará para todas as linhas na tabela.
Quando consultamos todas as colunas (ou pelo menos uma coluna não indexada), obtemos uma varredura completa da tabela única (T1), como mostra o Listing 7.
Code Listing 7: Como consultar usando a varredura completa da tabela T1
SQL> set autotrace traceonly explain
SQL> select * from t1;
Execution Plan
----------------
Plan hash value: 3617692013
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100M| 9536M| 320K (5)| 00:25:25 |
| 1 | TABLE ACCESS FULL | T1 | 100M| 9536M| 320K (5)| 00:25:25 |
-------------------------------------------------------------------------
Quando consultamos apenas as colunas indexadas, obtemos uma varredura completa rápida de índice, como mostra o Listing 8. Observe que, como usamos DBMS_STATS para informar o otimizador de que o índice equivale a cerca da metade da tabela, o tamanho da varredura completa do índice (157K) é perto da metade do tamanho da varredura completa da tabela (320K) e o tempo de execução da varredura completa do índice (12:31) equivale a aproximadamente a metade do tempo de execução da varredura completa da tabela (25:25).
Code Listing 8: Como consultar usando a varredura completa rápida de índice T1_IDX T1_IDX
SQL> select OWNER, OBJECT_NAME,
2 SUBOBJECT_NAME,
3 DATA_OBJECT_ID,
4 OBJECT_TYPE
5 from t1;
Execution Plan
--------------------------
Plan hash value: 1294651092
------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes|Cost(%CPU)| Time |
------------------------------------------------------------------
|0 |SELECT STATEMENT | | 100M | 152M | 157K (4) |00:12:31|
|1 |INDEX FAST FULL SCAN|T1_IDX | 100M |7152M | 157K (4) |00:12:31|
------------------------------------------------------------------