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


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 e 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.


quinta-feira, 26 de outubro de 2017

Obtendo o tamanho dos segmentos por schemas de banco de dados: (Tabelas, Índices, LOBs)

Por Eduardo Legatti

Olá,

No artigo de Maio/2011 eu compartilhei uma instrução SQL que retorna o tamanho de todos os segmentos (Tabelas, Índices, LOBs, etc.) pertencentes a um um schema (o schema atual), através das views de dicionário de dados USER_*. O objetivo dessa instrução SQL é mostrar não só o tamanho de uma tabela como também o tamanho dos objetos dependentes dela como índices e LOBs. Agora irei compartilhar uma consulta SQL que utiliza as views DBA_* de forma a retornar o tamanho dos segmentos de tabelas, índices e LOBs por schema de banco de dados, conforme a seguir.
 
SQL> SELECT owner,
  2         data_mb,
  3         indx_mb,
  4         lob_mb,
  5         total_mb
  6      FROM (SELECT data.owner,
  7                   NVL(data_mb,0) data_mb,
  8                   NVL(indx_mb,0) indx_mb,
  9                   NVL(lob_mb,0) lob_mb,
 10                   NVL(data_mb,0) + NVL(indx_mb,0) + NVL(lob_mb,0) total_mb
 11              FROM (  SELECT owner,
 12                             ROUND(SUM(data_mb),2) data_mb
 13                        FROM (SELECT owner, data_mb
 14                                FROM (SELECT a.owner,
 15                                             b.bytes/1024/1024 AS data_mb
 16                                        FROM dba_tables a, dba_segments b
 17                                       WHERE a.owner = b.owner and a.table_name = b.segment_name))
 18                    GROUP BY owner) data,
 19                   (  SELECT a.owner,
 20                             ROUND(SUM(b.bytes/1024/1024),2) AS indx_mb
 21                        FROM dba_indexes a, dba_segments b
 22                       WHERE a.owner = b.owner and a.index_name = b.segment_name
 23                    GROUP BY a.owner) indx,
 24                   (  SELECT a.owner,
 25                             ROUND(SUM(b.bytes/1024/1024),2) AS lob_mb
 26                        FROM dba_lobs a, dba_segments b
 27                       WHERE a.owner = b.owner and a.segment_name = b.segment_name
 28                    GROUP BY a.owner) lob
 29             WHERE
 30             data.owner = indx.owner(+)
 31             AND data.owner = lob.owner(+))
 32  WHERE owner in ('SCHEMA01',
 33                  'SCHEMA02',
 34                  'SCHEMA03',
 35                  'SCHEMA04',
 36                  'SCHEMA05',
 37                  'SCHEMA06',
 38                  'SCHEMA07')
 39  ORDER BY owner;

OWNER                       DATA_MB    INDX_MB     LOB_MB   TOTAL_MB
------------------------ ---------- ---------- ---------- ----------
SCHEMA01                   16069.38   58428.25  174381.44  248879.07
SCHEMA02                      11618   43081.31    9064.94   63764.25
SCHEMA03                   93944.63     529311  206722.38  829978.01
SCHEMA04                       78.5     107.63      98.56     284.69
SCHEMA05                    2814.25     9761.5    14573.5   27149.25
SCHEMA06                    3211.88   13447.94     555.75   17215.57
SCHEMA07                    9777.44   41685.13  258100.44  309563.01

7 rows selected.

segunda-feira, 25 de setembro de 2017

Analisando o RMAN no que se refere à varredura dos arquivos de dados em backups incrementais

Por Eduardo Legatti


Olá,

Com o lançamento do recurso de backups incrementais  à partir do RMAN Oracle 9i, é possível ter uma economia grande de espaço no que se refere ao tamanhos dos backups já que um backup incremental é gerado à partir dos blocos alterados desde o último backup base, seja ele cumulativo ou diferencial.
 
Mas, independente do tipo de backup incremental realizado, seja ele cumulativo ou diferencial, uma maneira de melhorar o desempenho dos mesmos é ativar o rastreamento de alterações de blocos (Block Change Tracking). Em um backup incremental tradicional, o RMAN tem que inspecionar cada bloco do arquivo de dados no qual será feito o backup para verificar se o mesmo foi alterado desde o último backup efetuado. Dependendo do tamanho do banco de dados, isso poderá levar muito tempo. Portanto, habilitando o BCT o mesmo irá registrar em um arquivo especial os blocos que mudaram e, durante a realização do backup incremental NIVEL-1, apenas estes blocos serão lidos, ou seja, o RMAN não precisará varrer todos os blocos do arquivo de dados.

Vale a pena salientar que o BCT é uma feature do Oracle Enterprise Edition, ou seja, a versão Standard não possui este recurso.
 
Na imagem abaixo é possível observar backups incrementais NIVEL-0 ocorrendo nos dias 10/09 e 17/09. A linha azul mostra os dados lidos para os backup sets e a linha vermelha mostra esses mesmos dados  já com a compressão utilizada pelo RMAN. A linha verde mostra o tempo gasto na realização do backup. Nos dias de semana é possível perceber uma diminuição do tamanho e do tempo dos backups, pelo fato de estar sendo realizado o backup incremental NIVEL-1.


A imagem baixo mostra eventos de backups incrementais em um Oracle Standard Edition.O importante a se notar é a linha azul se mantém igual durante a execução dos backups, independente se o backup é do NIVEL-0 ou NIVEL-1.




Segue abaixo o gráfico mostrando os dados em uma janela de tempo menor.




Por fim, posso concluir que as informações de input_bytes e output_bytes mostradas pelo RMAN através da view  V$RMAN_BACKUP_JOB_DETAILS estão atreladas ao que o RMAN está lendo no momento da varredura dos arquivos de dados, e não dos blocos que apenas sofreram modificações como eu imaginava.

segunda-feira, 21 de agosto de 2017

Resolvendo o erro ORA-08104 com a procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN ao criar um índice ONLINE

Por Eduardo Legatti

Olá,

Durante a criação de um índice ONLINE em um banco de dados Oracle, ocorreu um problema de conectividade fazendo com que o Oracle emitisse o erro ORA-08104 ao retomar a atividade de rebuild do índice. Não era possível dropar ou recriar o índice.

SQL> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;

ORA-03113: end-of-file on communication channel

SQL> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;
alter index idx_emp_name rebuild online
*
ERRO na linha 1:
ORA-08104: este objeto de índice 248352 está sendo construído ou reconstruído on-line

SQL> alter index idx_emp_name rebuild online;
alter index idx_emp_name rebuild online
*
ERRO na linha 1:
ORA-08104: este objeto de índice 248352 está sendo construído ou reconstruído on-line

Para resolver o problema, utilizei a procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN encontrada no site psoug.org conforme demonstrado abaixo.


Segue abaixo a execução da procedure.

SQL> set timing on
SQL> DECLARE
  2   isClean BOOLEAN;
  3  BEGIN
  4    isClean := FALSE;
  5    WHILE isClean=FALSE
  6    LOOP
  7      isClean := dbms_repair.online_index_clean(
  8      dbms_repair.all_index_id, dbms_repair.lock_wait);
  9      dbms_lock.sleep(10);
 10    END LOOP;
 11  END;
 12  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:00.01

sql> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;

Índice criado.

Decorrido: 00:00:03.08

No mais, este procedimento também está disponível no documento Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1] no My Oracle Support (Metalink).

Postagens populares