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


segunda-feira, 16 de abril de 2018

Abordando a instalação e configuração do MySQL no Linux

Por Eduardo Legatti

Olá,



Neste artigo irei abordar a instalação e configuração do MySQL (5.7.18 x86-64) em um Linux Centos 7.3 (64 bits). O primeiro passo é realizar o download dos pacotes de instalação para o servidor. Para isso, irei usar o utilitário wget, conforme a seguir.

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 335516 (328K) [application/x-redhat-package-manager]
Salvando em: “mysql-community-common-5.7.18-1.el7.x86_64.rpm”

100%[==============================================================================================>] 335.516     1,21M/s   em 0,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 2103888 (2,0M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-libs-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 2.103.888   3,40M/s   em 0,6s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 23039984 (22M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-client-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 23.039.984  2,86M/s   em 7,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 156322552 (149M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-server-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 156.322.552  967K/s   em 3m 4s

[root@linux1 ~]# ls -lh *.rpm
-rw-r--r-- 1 root root 328K Mar 20 06:40 mysql-community-common-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2,1M Mar 20 06:40 mysql-community-libs-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root  22M Mar 20 06:40 mysql-community-client-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 150M Mar 20 06:41 mysql-community-server-5.7.18-1.el7.x86_64.rpm

Após a realização do download dos pacotes de instalação, irei realizar a instalação dos mesmos usando o utilitário rpm. Vale a pena salientar que a ordem de instalação dos pacotes a seguir é importante.

[root@linux1 ~]# rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-common-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-common ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-libs-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-libs   ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-client-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-client ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-server-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-server ########################################### [100%]

Pronto. Podemos checar pelo comando abaixo que os pacotes do MySQL foram instalados com sucesso.

[root@linux1 ~]# rpm -qa | grep mysql
mysql-community-server-5.7.18-1.el7.x86_64
mysql-community-common-5.7.18-1.el7.x86_64
mysql-community-libs-5.7.18-1.el7.x86_64
mysql-community-client-5.7.18-1.el7.x86_64

Se quisermos prosseguir com a desinstalação do MySQL, basta utilizarmos os comandos abaixo.

[root@linux1 ~]# rpm -e mysql-community-server-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-common-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-libs-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-client-5.7.18-1.el7.x86_64 –nodeps
[root@linux1 ~]# rm -rf /var/lib/mysql/

Uma vez que o MySQL já se encontra está instalado, poderemos iniciar o seu serviço e começar a realizar a configuração inicial, conforme demonstrado abaixo.

[root@linux1 ~]# systemctl start mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

Após a inicialização do serviço da instância do MySQL, poderemos conectar no mesmo após obter uma senha temporária.

[root@linux1]# grep 'temporary password' /var/log/mysqld.log
2018-04-16T12:30 [Note] A temporary password is generated for root@localhost: 8V*vubKtFQp(

[root@linux1 ~]# mysql -uroot –p'8V*vubKtFQp('
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 Server version: 5.7.18

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Pronto. Após conectarmos na instância com o usuário root, somos forçados a alterar a senha por questões de segurança

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Agora irei realizar o procedimento de alteração da senha que deve conter pelo menos uma letra maiúscula, um caractere especial, além de números, como também alterar a política de segurança de senhas de MEDIUM para LOW.

mysql> alter user root@localhost identified by 'Asdjer3jk4kjd4@#';
Query OK, 0 rows affected (0,00 sec)

mysql> show variables like '%password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+
1 row in set (0,07 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0,03 sec)

Após diminuir o nível de segurança da senha, é possível perceber que ainda não é possível utilizar uma senha mais simples. Apesar de não precisar de letras maiúsculas, e caracteres especiais, a mesma precisa ter pelo menos um tamanho de 8 caracteres conforme demonstrado abaixo.

mysql> alter user root@localhost identified by 'admin';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> alter user root@localhost identified by '12345678';
Query OK, 0 rows affected (0,01 sec)

Para desativar esta obrigatoriedade, bastará desinstalar o plugin validate_password.

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0,01 sec)

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0,07 sec)

Uma vez desinstalado, poderemos usar uma senha mais simples.

mysql> alter user root@localhost identified by 'admin';
Query OK, 0 rows affected (0,00 sec)

Após a alteração da senha, poderemos instalar novamente o plugin validate_password, caso seja necessário.

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0,03 sec)

Para realizarmos qualquer operação no MySQL, fazemos uso do utilitário mysql.

[root@linux1]# whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

Para iniciar, parar ou checar o status da instância do MySQL, poderemos fazer uso do comando systemctl. No Linux 6.x e anteriores, fazíamos uso do comando service.

[root@linux1 ~]# systemctl start mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

[root@linux1 ~]# systemctl stop mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2017-05-02 13:47:00 BST; 904ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (code=exited, status=0/SUCCESS)

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.
Abr 16 13:46:59 linux1 systemd[1]: Stopping MySQL Server...
Abr 16 13:47:00 linux1 systemd[1]: Stopped MySQL Server.

Em relação a possibilidade de conexão remota à instância do MySQL, podemos checar que qualquer computar poderá ter acesso ao mesmo. Caso queiramos que a conectividade fuique restrita ao servidor, bastara apenas setar o parâmetro bind_address para 127.0.0.1.

mysql> show variables like '%bind%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+

[root@linux1 ~]# netstat -na | grep 3306
tcp        0      0 :::3306       :::*          LISTEN

Para finalizar, segue abaixo umas informações do arquivo de configuração da instância do MySQL. O mesmo pode ser encotrado em /etc/my.cnf, mas dependendo da distribuição do Linux, ele poderá estar em outro diretório.

[root@linux1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

segunda-feira, 26 de março de 2018

Oracle Blog agora é Database Blog

Por Eduardo Legatti

Olá,

Após a enquete realizada entre os meses de Agosto a Dezembro/2017, abordarei artigos técnicos não somente de Oracle, mas também MySQL, SQL Server e MongoDB.


quarta-feira, 14 de março de 2018

Retomando o processo de Export/Import Datapump no Oracle

Por Eduardo Legatti

Olá,

Quando realizamos tarefas de exportação ou importação através do Oracle Datapump, temos a opção de utilizar o modo interativo. Por exemplo, podemos abandonar o utilitário e deixar a parte "server" executando. Segue abaixo uma simulação usando o utilitário Import Datapump (impdp).

$ impdp system/manager dumpfile=BD01.dmp schemas=SCOTT nologfile=y

Import: Release 11.2.0.3.0 - Production on Seg Mar 12 15:06:41 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SYSTEM"."SYS_IMPORT_SCHEMA_01" carregada/descarregada com sucesso
Iniciando "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=BD01.dmp schemas=SCOTT nologfile=y
Processando o tipo de objeto SCHEMA_EXPORT/USER
^C

Acima, após iniciar a importação, executei [CTRL +C] e digitei a palavra help. Dentre os comandos abaixo que podemos utilizar, irei fazer o teste saindo do utilitário impdp e depois retomando o modo interativo do mesmo.

Import> help
----------------------------------------------------------------------------
Os seguintes comandos sao validos quando estao no modo interativo.
Observacão: sao permitidas abreviaturas.

CONTINUE_CLIENT
Retorna ao modo de log. O job ser reiniciado se estiver inativo.

EXIT_CLIENT
Encerra a sessao do cliente e deixa o job sendo executado.

HELP
Resume comandos interativos.

KILL_JOB
Desassocia e deleta o job.

PARALLEL
Altera o nmero de workers ativos para o job atual.

START_JOB
Inicia ou retoma o job atual.
As palavras-chave validas sao: SKIP_CURRENT.

STATUS
A frequencia (segundos) com que o status do job ser monitorado, em que
O default [0] mostrar o novo status quando disponivel.

STOP_JOB
Faz shutdown de forma ordenada da execuão do job e sai do cliente.
As palavras-chave validas sao: IMMEDIATE.

Import> exit_client

Após sair do utilitário impdp através do comando exit_client, irei verificar no banco de dados o nome da sessão que iremos utilizar para retomar ao modo não interativo.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Sessão alterada.

SQL> SELECT created, object_name
  2      FROM dba_objects
  3     WHERE owner = 'SYSTEM' AND object_name LIKE '%IMPORT%'
  4  ORDER BY 1;

CREATED             OBJECT_NAME
------------------- --------------------
12/03/2018 15:07:27 SYS_IMPORT_SCHEMA_01

SQL> select owner_name,job_name,operation from dba_datapump_jobs;
 
OWNER_NAME      JOB_NAME                       OPERATION
---------------- ------------------------------ --------------
SYSTEM           SYS_IMPORT_SCHEMA_01           IMPORT

Sabemos que o nome do job que está realizando a importação se chama SYS_IMPORT_SCHEMA_01 e será o nome deste job que iremos utilizar para retomar o processo de importação, como demonstrado abaixo.

$ impdp system/manager attach=SYS_IMPORT_SCHEMA_01

Import: Release 11.2.0.3.0 - Production on Seg Mar 12 15:10:37 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_01
  Proprietrio: SYSTEM
  Operaão: IMPORT
  Privs. do Criador: TRUE
  GUID: 673C01A018EC5943E055000000000001
  Horrio de Incio: Segunda-Feira, 12 Marco, 2018 15:07:27
  Modo: SCHEMA
  Instncia: BLN18
  Paralelismo Mx: 1
  EXPORT Parmetros do Job:
     CLIENT_COMMAND        system/******** dumpfile=BD01.dmp schemas=SCOTT compression=all nologfile=y
     COMPRESSION           ALL
  IMPORT Parmetros do Job:
  Nome do Parmetro      Valor do Parmetro:
     CLIENT_COMMAND        system/******** dumpfile=BD01.dmp schemas=SCOTT nologfile=y
  Estado: EXECUTING
  Bytes Processados: 0
  Paralelismo Atual: 1
  Contagem de Erros do Job: 0
  Arquivo de Dump: /tmp/BD01.dmp

Worker 1 Status:
  Nome do Processo: DW00
  Estado: EXECUTING
  Esquema de Objeto: SCOTT
  Nome do Objeto: SCOTT
  Tipo de Objeto: SCHEMA_EXPORT/USER
  Paralelismo do Worker: 1

É possível perceber pelo resultado acima que é mostrado tanto o comando que que está executando a importação quanto o comando que foi utilizado para realizar a exportação. Para verificar em que estágio a importação está, poderemos utilizar o comando status conforme seguir.

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operaão: IMPORT
  Modo: SCHEMA
  Estado: EXECUTING
  Bytes Processados: 0
  Paralelismo Atual: 1
  Contagem de Erros do Job: 0
  Arquivo de Dump: /tmp/BD01.dmp

Worker 1 Status:
  Nome do Processo: DW00
  Estado: EXECUTING
  Esquema de Objeto: SCOTT
  Nome do Objeto: FK_T2_T1
  Tipo de Objeto: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  Objetos Concludos: 110
  Paralelismo do Worker: 1

Por fim, se quisermos continuar com a saída padrão da importação podemos utilizar comando abaixo.

Import> continue_client

terça-feira, 20 de fevereiro de 2018

Verificando a versão, features e options instaladas no banco de dados Oracle

Por Eduardo Legatti

Olá,

Quando instalamos o Oracle e configuramos uma instância do banco de dados Oracle, podemos verificar posteriormente a sua versão, quais "options" e "features" do banco de dados foram instaladas e usadas. Segue abaixo 4 views do dicionário de dados que geralmente utilizo para verificar tais informações em um banco de dados Oracle.

  • V$VERSION: Lista a versão e edição do banco de dados Oracle.
  • V$OPTION: Lista as options e features instaladas no banco de dados. Geralmente as options são licenciadas separadamente e as features costumam já vir no produto que foi instalado (Standard Edition, Enterprise Edition).
  • DBA_REGISTRY: Lista os componentes que foram instalados no banco de dados.
  • DBA_FEATURE_USAGE_STATISTICS: Lista as features e as estatísticas de uso. Essas estatísticas são geradas aptravés de uma procedure não documentada do Oracle.
Segue abaixo um exemplo de saída das views acima executadas em um banco de dados Oracle Standard Edition e um Oracle Enterprise Edition.

-- --------------------------
-- Oracle Standard Edtion  --
-- --------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Active Data Guard                                                FALSE
Advanced Compression                                             FALSE
Advanced replication                                             FALSE
Application Role                                                 FALSE
Automatic Storage Management                                     FALSE
Backup Encryption                                                FALSE
Basic Compression                                                FALSE
Bit-mapped indexes                                               FALSE
Block Change Tracking                                            FALSE
Block Media Recovery                                             FALSE
Change Data Capture                                              FALSE
Data Mining                                                      FALSE
Data Redaction                                                   FALSE
Database resource manager                                        FALSE
Deferred Segment Creation                                        FALSE
Duplexed backups                                                 FALSE
Enterprise User Security                                         FALSE
Export transportable tablespaces                                 FALSE
Fast-Start Fault Recovery                                        FALSE
File Mapping                                                     FALSE
Fine-grained access control                                      FALSE
Fine-grained Auditing                                            FALSE
Flashback Database                                               FALSE
Flashback Table                                                  FALSE
Join index                                                       FALSE
Managed Standby                                                  FALSE
Materialized view rewrite                                        FALSE
OLAP                                                             FALSE
Online Index Build                                               FALSE
Online Redefinition                                              FALSE
Oracle Data Guard                                                FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Parallel backup and recovery                                     FALSE
Parallel execution                                               FALSE
Partitioning                                                     FALSE
Point-in-time tablespace recovery                                FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Result Cache                                                     FALSE
SecureFiles Encryption                                           FALSE
Server Flash Cache                                               FALSE
Spatial                                                          FALSE
SQL Plan Management                                              FALSE
Streams Capture                                                  FALSE
Transparent Data Encryption                                      FALSE
Trial Recovery                                                   FALSE
Unused Block Compression                                         FALSE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
DICOM                                                            TRUE
Flashback Data Archive                                           TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Parallel load                                                    TRUE
Plan Stability                                                   TRUE
Proxy authentication/authorization                               TRUE
Sample Scan                                                      TRUE
Transparent Application Failover                                 TRUE
XStream                                                          TRUE

65 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                               VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------
Automatic Memory Tuning                            11.2.0.4.0                      0 Automatic Memory Tuning is enabled.
Automatic SGA Tuning                               11.2.0.4.0                     25 Automatic SGA Tuning is enabled.
Backup ZLIB Compression                            11.2.0.4.0                      0 ZLIB compressed backups are being used.
Character Semantics                                11.2.0.4.0                      0 Character length semantics is used in Oracle Database
Character Set                                      11.2.0.4.0                     25 Character set is used in Oracle Database
Client Identifier                                  11.2.0.4.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                    11.2.0.4.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                              11.2.0.4.0                      0 Crossedition triggers is being used.
CSSCAN                                             11.2.0.4.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                         11.2.0.4.0                      0 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                        11.2.0.4.0                      0 There exist Oracle Data Mining models in the database.
Database Migration Assistant for Unicode           11.2.0.4.0                      0 Database Migration Assistant for Unicode has been used.
Deferred Segment Creation                          11.2.0.4.0                     25 Deferred Segment Creation is being used
Dynamic SGA                                        11.2.0.4.0                      0 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                   11.2.0.4.0                      0 Editioning views is being used.
Editions                                           11.2.0.4.0                      0 Editions is being used.
EM Database Control                                11.2.0.4.0                      0 EM Database Control Home Page has been visited at least once.
EM Grid Control                                    11.2.0.4.0                      0 EM Grid Control Database Home Page has been visited at least once.
EM Performance Page                                11.2.0.4.0                      0 EM Performance Page has been visited at least once.
Encrypted Tablespaces                              11.2.0.4.0                      0 Encrypted Tablespaces is enabled.
File Mapping                                       11.2.0.4.0                      0 File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Data Archive                             11.2.0.4.0                      0 Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Flashback Database                                 11.2.0.4.0                      0 Flashback Database, a rewind button for the database, is enabled
Internode Parallel Execution                       11.2.0.4.0                      0 Internode Parallel Execution is being used.
Label Security                                     11.2.0.4.0                      0 Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Locally Managed Tablespaces (system)               11.2.0.4.0                     25 There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user)                 11.2.0.4.0                     25 There exists user tablespaces that are locally managed in the database.
Messaging Gateway                                  11.2.0.4.0                      0 Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
MTTR Advisor                                       11.2.0.4.0                      0 Mean Time to Recover Advisor is enabled.
Multiple Block Sizes                               11.2.0.4.0                      0 Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces                         11.2.0.4.0                      0 OLAP - the analytic workspaces stored in the database.
OLAP - Cubes                                       11.2.0.4.0                      0 OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Database Vault                              11.2.0.4.0                      0 Oracle Database Vault is being used
Oracle Managed Files                               11.2.0.4.0                      0 Database files are being managed by Oracle.
Oracle Secure Backup                               11.2.0.4.0                      0 Oracle Secure Backup is used for backups to tertiary storage.
Oracle Text                                        11.2.0.4.0                      0 Oracle Text is being used - there is at least one oracle text index
Parallel SQL DDL Execution                         11.2.0.4.0                      0 Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution                         11.2.0.4.0                      0 Parallel SQL DML Execution is being used.
Parallel SQL Query Execution                       11.2.0.4.0                      0 Parallel SQL Query Execution is being used.
Partitioning (system)                              11.2.0.4.0                     25 Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user)                                11.2.0.4.0                      0 Oracle Partitioning option is being used - there is at least one user partitioned object created.
PL/SQL Native Compilation                          11.2.0.4.0                      0 PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Quality of Service Management                      11.2.0.4.0                      0 Quality of Service Management has been used.
Real Application Clusters (RAC)                    11.2.0.4.0                      0 Real Application Clusters (RAC) is configured.
Recovery Area                                      11.2.0.4.0                      0 The recovery area is configured.
Recovery Manager (RMAN)                            11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup                                 11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup                                 11.2.0.4.0                      0 Recovery Manager (RMAN) is being used to backup the database to tape.
SQL Monitoring and Tuning pages                    11.2.0.4.0                      0 EM SQL Monitoring and Tuning pages has been visited at least once.
Very Large Memory                                  11.2.0.4.0                      0 Very Large Memory is enabled.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
CONTEXT                        Oracle Text                                        11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 CTXSYS                         VALIDATE_CONTEXT
EM                             Oracle Enterprise Manager                          11.2.0.4.0                     VALID       12-JAN-2017 10:24:21 SYSMAN
OWM                            Oracle Workspace Manager                           11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 WMSYS                          VALIDATE_OWM
XDB                            Oracle XML Database                                11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 XDB                            DBMS_REGXDB.VALIDATEXDB

6 linhas selecionadas.


-- ----------------------------
-- Oracle Enterprise Edtion  --
-- ----------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Automatic Storage Management                                     FALSE
Data Mining                                                      FALSE
OLAP                                                             FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Active Data Guard                                                TRUE
Advanced Compression                                             TRUE
Advanced replication                                             TRUE
Application Role                                                 TRUE
Backup Encryption                                                TRUE
Basic Compression                                                TRUE
Bit-mapped indexes                                               TRUE
Block Change Tracking                                            TRUE
Block Media Recovery                                             TRUE
Change Data Capture                                              TRUE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Database resource manager                                        TRUE
Deferred Segment Creation                                        TRUE
DICOM                                                            TRUE
Duplexed backups                                                 TRUE
Enterprise User Security                                         TRUE
Export transportable tablespaces                                 TRUE
Fast-Start Fault Recovery                                        TRUE
File Mapping                                                     TRUE
Fine-grained access control                                      TRUE
Fine-grained Auditing                                            TRUE
Flashback Data Archive                                           TRUE
Flashback Database                                               TRUE
Flashback Table                                                  TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Join index                                                       TRUE
Managed Standby                                                  TRUE
Materialized view rewrite                                        TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Online Index Build                                               TRUE
Online Redefinition                                              TRUE
Oracle Data Guard                                                TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Partitioning                                                     TRUE
Plan Stability                                                   TRUE
Point-in-time tablespace recovery                                TRUE
Proxy authentication/authorization                               TRUE
Result Cache                                                     TRUE
Sample Scan                                                      TRUE
SecureFiles Encryption                                           TRUE
Server Flash Cache                                               TRUE
Spatial                                                          TRUE
SQL Plan Management                                              TRUE
Streams Capture                                                  TRUE
Transparent Application Failover                                 TRUE
Transparent Data Encryption                                      TRUE
Trial Recovery                                                   TRUE
Unused Block Compression                                         TRUE
XStream                                                          TRUE

64 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                                                             VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------
Active Data Guard - Real-Time Query on Physical Standby                          11.2.0.3.0                      0 Active Data Guard real-time query is enabled on a physical standby
ADDM                                                                             11.2.0.3.0                     11 ADDM has been used.
Advanced Replication                                                             11.2.0.3.0                      0 Advanced Replication has been enabled.
ASO native encryption and checksumming                                           11.2.0.3.0                      0 ASO network native encryption and checksumming is being used.
Audit Options                                                                    11.2.0.3.0                    281 Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering                           11.2.0.3.0                    247 Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor                                            11.2.0.3.0                     52 Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor                                       11.2.0.3.0                    210 Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system)                                      11.2.0.3.0                    281 Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user)                                        11.2.0.3.0                    281 Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory                                                   11.2.0.3.0                    281 Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Management                                                     11.2.0.3.0                      0 Automatic Storage Management has been enabled
Automatic Undo Management                                                        11.2.0.3.0                    281 Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository                                                    11.2.0.3.0                      0 A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline                                                                     11.2.0.3.0                      0 At least one AWR Baseline has been created by the user
AWR Baseline Template                                                            11.2.0.3.0                      0 At least one AWR Baseline Template has been created by the user
AWR Report                                                                       11.2.0.3.0                      9 At least one Workload Repository Report has been created by the user
Backup Encryption                                                                11.2.0.3.0                      0 Encrypted backups are being used.
Backup Rollforward                                                               11.2.0.3.0                      0 Backup Rollforward strategy is being used to backup the database.
Baseline Adaptive Thresholds                                                     11.2.0.3.0                      0 Adaptive Thresholds have been configured.
Baseline Static Computations                                                     11.2.0.3.0                      0 Static baseline statistics have been computed.
Change-Aware Incremental Backup                                                  11.2.0.3.0                    120 Track blocks that have changed in the database.
Character Semantics                                                              11.2.0.3.0                      0 Character length semantics is used in Oracle Database
Character Set                                                                    11.2.0.3.0                    281 Character set is used in Oracle Database
Client Identifier                                                                11.2.0.3.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                                                  11.2.0.3.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                                                            11.2.0.3.0                      0 Crossedition triggers is being used.
CSSCAN                                                                           11.2.0.3.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                                                       11.2.0.3.0                    278 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                                                      11.2.0.3.0                      0 There exist Oracle Data Mining models in the database.
Data Recovery Advisor                                                            11.2.0.3.0                      0 Data Recovery Advisor (DRA) is being used to repair the database.
Database Migration Assistant for Unicode                                         11.2.0.3.0                      0 Database Migration Assistant for Unicode has been used.
Direct NFS                                                                       11.2.0.3.0                      0 Direct NFS is being used to connect to an NFS server
Dynamic SGA                                                                      11.2.0.3.0                      2 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                                                 11.2.0.3.0                      0 Editioning views is being used.
Editions                                                                         11.2.0.3.0                      0 Editions is being used.
EM Database Control                                                              11.2.0.3.0                      0 EM Database Control Home Page has been visited at least once.
Instance Caging                                                                  11.2.0.3.0                      0 Instance Caging is being used to limit the CPU usage by the database instance.
Locator                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Locator index metadata table.
Resource Manager                                                                 11.2.0.3.0                      0 Oracle Database Resource Manager is being used to manage database resources.
Server Flash Cache                                                               11.2.0.3.0                      0 Server Flash Cache is being used with this database.
Server Parameter File                                                            11.2.0.3.0                    281 The server parameter file (SPFILE) was used to startup the database.
Shared Server                                                                    11.2.0.3.0                      0 The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Spatial index metadata table.
SQL Access Advisor                                                               11.2.0.3.0                      0 SQL Access Advisor has been used.
SQL Performance Analyzer                                                         11.2.0.3.0                      0 SQL Performance Analyzer has been used.
SQL Tuning Advisor                                                               11.2.0.3.0                     16 SQL Tuning Advisor has been used.
SQL Workload Manager                                                             11.2.0.3.0                      0 SQL Workload Manager has been used.
Tune MView                                                                       11.2.0.3.0                      0 Tune MView has been used.
Undo Advisor                                                                     11.2.0.3.0                      0 Undo Advisor has been used.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM                            Oracle Workspace Manager                           11.2.0.3.0                     VALID       03-OUT-2012 22:48:14 WMSYS                          VALIDATE_OWM

segunda-feira, 29 de janeiro de 2018

SQL*Plus 12c - SET FEEDBACK ONLY

Por Eduardo Legatti

Olá,

No SQL*Plus do Oracle 12c, especificamente na variável de sistema FEEDBACK foi introduzida uma novo parâmetro na qual podemos executar uma instrução SELECT em uma tabela de forma que apenas o resultado do número de linhas seja retornado. Neste caso, seria o equivalente a executar um SELECT COUNT(*) na tabela.



C:\>sqlplus scott/tiger@ORCL11

SQL*Plus: Release 12.2.0.1.0 Production on Seg Jan 29 12:43:35 2018

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

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

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 select rownum rn from dual connect by rownum <=10;

SQL> commit;

Commit concluído.

SQL> show feedback;
FEEDBACK ON for 6 or more rows

SQL> select rowid,id from t1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10

10 linhas selecionadas.

SQL> set feedback ONLY;
SQL> show feedback;
feedback ONLY

SQL> select rowid,id from t1;

10 linhas selecionadas.

quinta-feira, 28 de dezembro de 2017

Enquete - 2017 (Agosto/Dezembro)

Por Eduardo Legatti

Olá,

Sobre a enquete realizada entre os meses de Agosto a Dezembro/2017, segue o resultado abaixo.
Estarei analisando as possibilidades!!!

Feliz Ano Novo!!!!

Legatti

terça-feira, 21 de novembro de 2017

Otimização: Cursor Sharing, Histogramas, Cursores no Oracle

Por Eduardo Legatti

Olá,

O objetivo deste artigo é demonstrar através de um exemplo prático como um histograma pode ajudar o otimizador do Oracle a encontrar um plano de execução melhor para instruções SQL que acessam tabelas que possuem registros distribuídos de forma não uniforme (skewed distribution) em uma ou mais colunas. Algumas variáveis podem influenciar como o Oracle enxerga uma instrução SQL, tais como o valor atual do parâmetro cursor_sharing, Configurações de NLS da sessão, se a instrução SQL utiliza literal ou bind variables, além de o otimizador fazer uso do Adaptive Cursosr Sharing de forma a avaliar se existe um plano de execução melhor de acordo com os valores das bind variables utilizadas. Essas variações serão tratadas em artigos futuros. Abaixo foi criada uma tabela T1 com cerca de 17 milhões de registros. É possível notar que a coluna ID possui 3 valores distintos (1, 2 e 3) e que os mesmos estão distribuídos de forma não uniforme. Vale a pena salientar que a coluna ID está indexada. Irei coletar estatísticas da tabela T1 sem coletar histogramas para as colunas conforme abaixo.

SQL> select id,count(*) from t1 group by id order by 1;

        ID   COUNT(*)
---------- ----------
         1   16777216
         2     262144
         3         64

SQL> exec dbms_stats.gather_table_stats(
 2   ownname=>'SCOTT',
 3   tabname=>'T1',
 4   cascade => true,
 5   METHOD_OPT => 'for all columns size 1');

Procedimento PL/SQL concluído com sucesso.

Abaixo, podemos ver que o parâmetro cursor_sharing está configurado para EXACT (default) o que significa que somente instruções SQL com textos idênticos serão compartilhadas para reutilização.

SQL> show parameter cursor_sharing;

NAME                         TYPE          VALUE
--------------------------- ------------- ---------------------
cursor_sharing              string        EXACT

Após coletar estatísticas da tabela (sem histogramas), irei executar três consultas abaixo. Vale a pena salientar que as instruções se diferenciam textualmente pelo valor da coluna ID que é passada na cláusula WHERE, ou seja, elas não são idênticas.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Consultando a view dinâmica de desempenho V$SQLAREA que mostra os PARENT CURSORS de todas as instruções SQL executadas, podemos ver que cada instrução SQL possui um SQL_ID/HASH_VALUE diferentes, exatamente porque os literais passados na cláusula WHERE na coluna ID são diferentes. Podemos ver também que a coluna VERSION_COUNT mostra o valor 1  o que significa que cada PARENT CURSOR criou apenas um CHILD CURSOR para cada consulta. É importante lembrar que cada PARENT CURSOR (V$SQLAREA) deverá sempre ter no mínimo 1 CHILD CURSOR (V$SQL).

SQL> SELECT sql_id,
  2         hash_value,
  3         version_count,
  4         executions,
  5         parsing_schema_name,
  6         module,
  7         last_active_time,
  8         is_bind_sensitive,
  9         is_bind_aware,
 10         sql_profile,
 11         sql_text
 12    FROM V$SQLAREA
 13   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 14         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE VERSION_COUNT EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025             1          1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Consultando a view dinâmica de desempenho V$SQL que mostra os CHILD CURSORS de todas as instruções SQL executadas, podemos ver que o Oracle gerou o mesmo plano de execução (PLAN_HASH_VALUE) para as 3 consultas.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 0000000092188610      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 000000009F85B2D8      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 00000000944BC930      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Irei gerar um explain plan da consulta abaixo de forma que verificar qual plano de execução foi gerado para as consultas SQL.

SQL> explain plan for select count(object_name) from t1 where id=1;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 | 55703   (1)| 00:11:09 |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5679K|    54M| 55703   (1)| 00:11:09 |
---------------------------------------------------------------------------

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

   2 - filter("ID"=1)

14 linhas selecionadas.

Pelo plano de execução mostrado acima, podemos ver que o otimizador resolveu fazer um Full Table Scan na tabela T1. Para o valor de ID=1 está ótimo, porque este valor está em 98% dos registros da tabela. No entanto, esse plano de execução é horrível para os valores 2 e 3 que teria melhor performance se fizesse uso do índice criado na coluna ID. A questão é saber porque o Oracle não utilizou o índice? A resposta é porque o Oracle não sabe a distribuição dos valores na coluna ID. Por exemplo, ele não sabe que 98% dos valores são 1 e que os outros 2% são 1 e 2. Pelo resultado da consulta abaixo é possível perceber que o valor da coluna DENSITY é 33%, ou seja, o Oracle sabe que existem 3 valores distintos na coluna ID (NUM_DISTINCT=3), no entanto, ele enxerga de forma uniforme a distribuição dos valores na coluna, ou seja, cerca de 5,6 milhões de linhas para cada ID.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               1              3
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               0              1

Agora irei criar um histograma para a coluna ID de forma que o otimizador possa enxergar a não uniformidade dos valores armazenadas na coluna.

SQL> exec dbms_stats.gather_table_stats(
  2  ownname=>'SCOTT',
  3  tabname=>'T1',
  4  cascade => true,
  5  METHOD_OPT => 'for columns ID');

Procedimento PL/SQL concluído com sucesso.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5262              1
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5343              3
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5342              2

Após executada a coleta de estatísticas incluindo a criação de histograma (FREQUENCY), irei executar novamente as 3 consultas SQL.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Fazendo novamente a consulta na view V$SQL, é possível observar que o plano de execução (PLAN_HASH_VALUE) das consultas que utilizam os IDs 2 e 3 mudaram.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 000000009F8DED10      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 0000000098978A30      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:22 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 000000009F8CA890      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:14 N N            select count(object_name) from t1 where id=1

Para finalizar, irei gerar um novo explain plan da consulta que utiliza o ID=3 de forma a verificar qual plano de execução foi gerado pelo otimizador do Oracle.

SQL> explain plan for select count(object_name) from t1 where id=3;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1284813898

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    10 |    52   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |  3189 | 31890 |    52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_ID |  3189 |       |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("ID"=3)

15 linhas selecionadas.

Pronto. Pelo resultado acima podemos ver que o otimizador escolheu o acessar os dados através do índice IDX_T1_ID, ou seja, o histograma foi responsável por ajudar o otimizador a verificar que os valores da coluna ID não estavam com a distribuição uniforme e encontrou um plano de acesso mais performático para a instrução SQL com ID=3.


Postagens populares