Atendendo a pedidos…

 

logotipia-nerv-pequeno

Atendendo a literalmente centenas de pedidos, continuaremos com Treinamentos em 2017.

A agenda 2017 está aberta. Como sempre, são poucas vagas: http://nervinformatica.com.br/agenda.php

Mas as outras atividades impedem que realizemos tantas turmas quanto são feitas atualmente. Portanto, serão poucas turmas. Se você quer se especializar em Oracle e MySQL, sugerimos que não postergue seus planos para vir (ou voltar) ao Centro de Treinamentos referência no assunto.

Agradeço de coração aberto o reconhecimento e preferência, e as bonitas palavras que recebemos.

Banco de Dados Limpo: Comando CREATE DATABASE para non-CDB

Estes são os comandos que uso para criação de um banco de dados non-CDB (em 12c, mas creio que nada mudaria para 11g, por exemplo).
Nada de novo aqui, este tipo de criação já deve ser conhecido para a maioria dos DBAs.
Só estou guardando aqui meus próprios comandos. 🙂

$ mkdir -p /u01/app/oracle/audit/ORCLEE
$ mkdir -p /u01/app/oracle/oradata/EE01
$ cat $ORACLE_HOME/dbs/initEE01.ora
	*.audit_file_dest='/u01/app/oracle/audit/ORCLEE/'
	*.audit_trail='NONE'
	*.compatible='12.1.0.2.0'
	*.control_files='/u01/app/oracle/oradata/EE01/control01.dbf','/u01/app/oracle/oradata/EE01/control02.dbf'
	*.db_block_size=8192
	*.db_name='EE01'
	*.diagnostic_dest='/u01/app/oracle'
	*.log_archive_format='%t_%s_%r.dbf'
	*.nls_language='BRAZILIAN PORTUGUESE'
	*.nls_territory='BRAZIL'
	*.open_cursors=300
	*.pga_aggregate_target=512m
	*.processes=300
	*.remote_login_passwordfile='EXCLUSIVE'
	*.sga_target=1024m
	*.undo_tablespace='UNDOTBS1'
	*.CONTROL_MANAGEMENT_PACK_ACCESS='NONE'
	*.PARALLEL_MAX_SERVERS=0
	*.RESOURCE_LIMIT='FALSE'
	*.DEFERRED_SEGMENT_CREATION='FALSE'
	*.OPTIMIZER_USE_SQL_PLAN_BASELINES='FALSE'
	*.OPTIMIZER_ADAPTIVE_FEATURES='FALSE'
$ export ORACLE_SID=EE01
$ rlwrap sqlplus / AS SYSDBA
SQL> STARTUP NOMOUNT;
SQL> CREATE DATABASE EE01
   USER SYS IDENTIFIED BY Nerv2016
   USER SYSTEM IDENTIFIED BY Nerv2016
   LOGFILE
	GROUP 1 ('/u01/app/oracle/oradata/EE01/redo01a.log','/u01/app/oracle/oradata/EE01/redo01b.log') SIZE 100M BLOCKSIZE 512,
	GROUP 2 ('/u01/app/oracle/oradata/EE01/redo02a.log','/u01/app/oracle/oradata/EE01/redo02b.log') SIZE 100M BLOCKSIZE 512,
	GROUP 3 ('/u01/app/oracle/oradata/EE01/redo03a.log','/u01/app/oracle/oradata/EE01/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/EE01/system01.dbf'
	SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/EE01/sysaux01.dbf'
	SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/EE01/users01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/EE01/temp01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/EE01/undotbs01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/sqlplus/admin/pupbld.sql

Comando CREATE DATABASE para CDB

Um banco de dados CDB também pode ser criado sem o DBCA.

O DBCA tem o inconveniente abaixo em bancos CDB. Mesmo com o Template Custom, não é possível remover funcionalidades que não serão utilizadas. E tratando-se de bancos de dados, menos é mais.

DBCA-CDB-Custom

Fui então atrás dos comandos para criá-lo manualmente. Mas, mesmo criando um banco de dados manualmente – via comando CREATE DATABASE – para CDBs, tudo isso é instalado. Desconfiei disso pela demora na execução do último comando, o catcbd.sql. Depois fui olhar na v$OPTION, e lá estavam o Multimedia, OLAP, Spatial…

Enfim, para criar um banco de dados CDB, o pacote abaixo (OEL 7 / RHEL 7 / CentOS 7) é deve ser instalado para executar o comando final de criação do dicionário de dados. Isto não é necessário para bancos non-CDB.

$ sudo yum -y install perl-TermReadKey.x86_64

Abaixo está a criação dos diretórios que serão utilizados.

$ mkdir -p /u01/app/oracle/audit/EECDB01
$ mkdir -p /u01/app/oracle/oradata/EECDB01
$ mkdir -p /u01/app/oracle/oradata/EECDB01/pdbseed/

E abaixo está o PFILE que será utilizado. Atenção para o parâmetro que habilita o uso de PDBs (em negrito), e os parâmetros que desabilitam funcionalidades da Enterprise Edition (em itálico).

$ cat $ORACLE_HOME/dbs/initEECDB01.ora
*.audit_file_dest='/u01/app/oracle/audit/EECDB01/'
*.audit_trail='NONE'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/EECDB01/control01.dbf','/u01/app/oracle/oradata/EECDB01/control02.dbf'
*.db_block_size=8192
*.db_name='EECDB01'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='BRAZILIAN PORTUGUESE'
*.nls_territory='BRAZIL'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'
*.CONTROL_MANAGEMENT_PACK_ACCESS='NONE'
*.PARALLEL_MAX_SERVERS=0
*.RESOURCE_LIMIT='FALSE'
*.DEFERRED_SEGMENT_CREATION='FALSE'
*.OPTIMIZER_USE_SQL_PLAN_BASELINES='FALSE'
*.OPTIMIZER_ADAPTIVE_FEATURES='FALSE'

Em seguida, iniciar a instância em NOMOUNT.

$ export ORACLE_SID=EECDB01
$ rlwrap sqlplus / AS SYSDBA
SQL> STARTUP NOMOUNT;

Em seguida, executar o comando CREATE DATABASE, adequado à sua necessidade.

SQL> CREATE DATABASE EECDB01
USER SYS IDENTIFIED BY Nerv2016
USER SYSTEM IDENTIFIED BY Nerv2016
LOGFILE
	GROUP 1 ('/u01/app/oracle/oradata/EECDB01/redo01a.log','/u01/app/oracle/oradata/EECDB01/redo01b.log') SIZE 100M BLOCKSIZE 512,
	GROUP 2 ('/u01/app/oracle/oradata/EECDB01/redo02a.log','/u01/app/oracle/oradata/EECDB01/redo02b.log') SIZE 100M BLOCKSIZE 512,
	GROUP 3 ('/u01/app/oracle/oradata/EECDB01/redo03a.log','/u01/app/oracle/oradata/EECDB01/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/EECDB01/system01.dbf'
	SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/EECDB01/sysaux01.dbf'
	SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/EECDB01/users01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/EECDB01/temp01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/EECDB01/undotbs01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
	FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/EECDB01/', '/u01/app/oracle/oradata/EECDB01/pdbseed/')
	SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
	SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

O script abaixo cria o dicionário de dados, e substitui os antigos catalog.sql, catproc.sql e utlrp.sql. Os scrips antigos ainda devem ser utilizados em criação de bancos sem CDB.

SQL> @?/rdbms/admin/catcdb.sql

Oracle Tuning – SQL: Os Falsos Nulos

Falsos Nulos ocorrem quando ao invés de NULL, é utilizado um valor que nunca seria utilizado normalmente, como “Bruce Wayne” para uma columa NOME_EMPREGADO, “000.000.000.000-00” para uma coluna CPF, ou “31/12/4000” para uma coluna DATA_NASCIMENTO.

É muito comum uma modelagem utilizar Falsos Nulos (propositalmente ou não), para “evitar problemas”. Geralmente estes problemas são comportamentos não esperados – não esperados pelo desenvolvedor, e não pelo Oracle – com valores NULL, e manter a portabilidade da aplicação para outro banco de dados (que de qualquer forma não seria fácil mesmo).

O problema dos Falsos Nulos é que ao computar a seletividade de um SELECT, o Oracle os considera valores normais.

Por exemplo, em uma coluna de data, se é utilizada um Falso Nulo com um valor muito longe da maioria dos outros valores (como “31/12/4000”), o Oracle (mais especificamente, o CBO – Cost Based Optimizer) considera que há valores “de 1998 a 4000”, incluindo nos anos 3999, 3998, e assim por diante.

Vejam a tabela abaixo:

SQL> SELECT COUNT(*) FROM T92;

  COUNT(*)
----------
    182700

SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO = TO_DATE('31/12/4000', 'DD/MM/YYYY');

  COUNT(*)
----------
       183

SQL>

Como é mostrado acima, apenas uma pequena parte das linhas possui o Falso Nulo, mas vemos abaixo a consequência – os valores reais estão mais “diluídos”, e o CBO considera que há apenas 4 linhas na consulta dos pedidos de um ano, quando na verdade há 3097.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO BETWEEN TO_DATE('01/12/2010','DD/MM/YYYY') AND TO_DATE('31/12/2010','DD/MM/YYYY');

  COUNT(*)
----------
      3097


Execution Plan
----------------------------------------------------------
Plan hash value: 103883946

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    93   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T92  |     4 |    36 |    93   (3)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Um Histograma corrigiria essa situação, mas colunas de data geralmente tem uma grande quantidade de valores distintos (10 anos são 3650 dias distintos, que nem os histogramas do 12c comportam), o que inviabiliza um bom histograma.

Mas o melhor é fazer o certo: se removermos os falsos nulos, este problema é resolvido.

SQL> SET AUTOTRACE OFF
SQL> DELETE FROM T92 WHERE DATA_PEDIDO = TO_DATE('31-Dec-4000');

183 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO BETWEEN TO_DATE('01/12/2010','DD/MM/YYYY') AND TO_DATE('31/12/2010','DD/MM/YYYY');

  COUNT(*)
----------
      3097


Execution Plan
----------------------------------------------------------
Plan hash value: 103883946

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    91   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T92  |  3097 | 27873 |    91   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Lendas de Índices: Tabelas Pequenas

Há uma lenda que diz que em tabelas pequenas índices não são utilizados.

Isto não é verdade, como por exemplo quando só é necessária a informação que está indexada – não há acesso a colunas não indexadas da tabela.

SQL> CREATE TABLE T (C1 NUMBER);

Table created.

SQL> CREATE INDEX IDX_T ON T(C1);

Index created.

SQL> INSERT INTO T VALUES (1);

1 row created.

SQL> INSERT INTO T VALUES (2);

1 row created.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT C1 FROM T WHERE C1 = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL>