segunda-feira, 5 de maio de 2014

O que precisamos saber sobre incidências de "FTS" ( Full-Table-Scan) em nosso Banco de Dados

INTRODUÇÃO :

Olá á todos? Como estão? Bem, eu estava arrumando alguns livros de minha coleção quando me deparei com um assunto que eu particularmente sempre vivenciei nas empresas por onde passei.

As ocorrências cotidianas de "full-table-scan", eram e são situações muito atuais em qualquer base de dados transacional (OLTP) ou até mesmo em bases DW ( DataWarehousing).

Tudo isso se deve ao fato de que os FTS ( Full-Table-Scans), não são erros encontrados no banco de dados, nem tão pouco tratam-se sempre de vilões em nossas bases, é preciso compreeder e analisar os aspectos que envolvem um evento de full table scan antes de diagnostica-lo como problema da sua query ou até mesmo pelo nível elevado de incidências no seu banco de dados, um problema para sua Base.

Mas antes de prosseguirmos é primordial um breve conceito então, sobre o que viria a ser um FTS?

O QUE É UM FULL-TABLE-SCAN?

Conceito : Trata-se de um método de leitura dos dados das tabelas, que também é conhecido como leitura SEQUENCIAL, onde ocorre uma validação por parte do otimizador do banco de dados ao ler o objeto do tipo tabela de maneira completa, analisando linha por linha e coluna por coluna para tal. É uma operação muito custosa para o banco de dados em si, pois demanda uma enorme quantidade de I/O para ser executada, e com isso acarretam como consequência principal em muitos casos, a queda na performance de algumas consultas. A ausência de indices adequados, bem como a presença de joins entre duas ou mais tabelas, pode sim acarretar essa situação como reflexo, por isso é necessário a analíse minuciosa do problema.

DEVO EVITAR OS FTS's EM MEU BANCO DE DADOS?

Sim e Não. Positivamente, é necessário que todo DBA tenha uma administração pró-ativa de sua base de dados, seja através de ferramentas de monitoramento próprietárias, scripts manuais ou qualquer outro tipo de controle de atividades das bases de dados, sobretudo no que diz respeito a consumo, sejam eles de CPU, I/O ou Memória. Negativamente é impossível que se evite os FTS's em 100% em uma base de dados, claro que um modelo bem delineado, com uma intervenção minima em termos dde alterações de estrutura de tabelas ajudaria muito, mais sabemos que por conta da real necessidade de inovação das aplicações, isso é HUMANAMENTE impossível de ter.

O QUE DEVEMOS FAZER ENTÃO?

Pimeiramente instruir a sua equipe de desenvolvimento, em conjunto com um AD ( Administrador de Dados), a criar uma politica interna de discussão sobre as alterações e não faze-las a qualquer tempo e de qualquer forma. É preciso que haja uma análise de impacto dessas alterações realizadas no banco de dados.

Relacionei baseando-se no que já vi por ai nas empresas, aspectos que julgo serem importantes para esse tipo de situação :

- planos de execuções;
- analise da volumetria da tabela;
- analíse sob a possiblidade da criação de um indice;
- analíse sobre a criação de um cluster ou tabela em cache de acordo com o tamanho;

Essas pequenas medidas, por si só já ajudariam em muito, é claro que como citei acima, um modelo bem elaborado por si só é muito eficiente. Entretando após o advento do ASM ( Automatic Storage Management) e também do nosso mais novo aliado as máquinas EXADATA Database Machine, com seus Database Storages, que possuem inteligencia própria para trabalhar as estruturas menóres do banco ( blocos de dados) tudo isso ficou meio que de lado. É raro ver um DBA hoje em dia usar o TKPROF para analisar o que quer que seja, a primeira opção em caso de encontrar um FTS's é ir logo ou rodando um ANALYZE TABLE/DBMS_STATS ou então sair criando indices nos campos para tentar melhorar.

Ambas ações acima descritas não estão de todo equivocadas, mas devem serem realizadas com cautela, usando um relatório de AWR por exemplo para identificação com precisão das potênciais query's com problemas, ou até mesmo utilizar a fantastica ferramenta do Oracle Cloud Control 12c e até o bom e velho amigo Oracle GRID CONTROL.

QUANDO OCORRE UM FTS's?

De acordo com a Oracle, uma tabela que retorne 40% de suas linhas em caso de serem ordenadas ou 7% de suas linhas em caso de não-ordenadas, levando em conta a chave do indice existente descrita na coluna clustering_factor da visão dba_indexes, esta consulta em si poderá ser re-organizada para um uso eficiente d eum indice novo ou já existente ao invés de usar um full-table-scan.


ALL_INDEXES

ALL_INDEXES describes the indexes on the tables accessible to the current user. To gather statistics for this view and the related views DBA_INDEXES andUSER_INDEXES, use the SQL ANALYZE statement.
Related Views
  • DBA_INDEXES describes all indexes in the database.
  • USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.
Note:
Column names followed by an asterisk are populated only if you collect statistics on the index using the ANALYZE statement or the DBMS_STATS package.
ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the index
INDEX_NAMEVARCHAR2(30)NOT NULLName of the index
INDEX_TYPEVARCHAR2(27) Type of the index:
  • NORMAL
  • BITMAP
  • FUNCTION-BASED NORMAL
  • FUNCTION-BASED BITMAP
  • DOMAIN
TABLE_OWNERVARCHAR2(30)NOT NULLOwner of the indexed object
TABLE_NAMEVARCHAR2(30)NOT NULLName of the indexed object
TABLE_TYPECHAR(5) Type of the indexed object (for example, TABLECLUSTER)
UNIQUENESSVARCHAR2(9) Indicates whether the index is UNIQUE or NONUNIQUE
COMPRESSIONVARCHAR2(8) Indicates whether index compression is enabled (ENABLED) or not (DISABLED)
PREFIX_LENGTHNUMBER Number of columns in the prefix of the compression key
TABLESPACE_NAMEVARCHAR2(30) Name of the tablespace containing the index
INI_TRANSNUMBER Initial number of transactions
MAX_TRANSNUMBER Maximum number of transactions
INITIAL_EXTENTNUMBER Size of the initial extent
NEXT_EXTENTNUMBER Size of secondary extents
MIN_EXTENTSNUMBER Minimum number of extents allowed in the segment
MAX_EXTENTSNUMBER Maximum number of extents allowed in the segment
PCT_INCREASENUMBER Percentage increase in extent size
PCT_THRESHOLDNUMBER Threshold percentage of block space allowed per index entry
INCLUDE_COLUMNNUMBER Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to theCOLUMN_ID column of the *_TAB_COLUMNS data dictionary views.
FREELISTSNUMBER Number of process freelists allocated to this segment
FREELIST_GROUPSNUMBER Number of freelist groups allocated to this segment
PCT_FREENUMBER Minimum percentage of free space in a block
LOGGINGVARCHAR2(3) Logging information
BLEVEL*NUMBER B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
LEAF_BLOCKS*NUMBER Number of leaf blocks in the index
DISTINCT_KEYS*NUMBER Number of distinct indexed values. For indexes that enforce UNIQUEand PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)
AVG_LEAF_BLOCKS_PER_KEY*NUMBER Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always1.
AVG_DATA_BLOCKS_PER_KEY*NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.
CLUSTERING_FACTOR*NUMBER Indicates the amount of order of the rows in the table based on the values of the index.
  • If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
  • If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
For bitmap indexes, this column is not applicable and is not used.
STATUSVARCHAR2(8) Indicates whether a nonpartitioned index is VALID or UNUSABLE
NUM_ROWSNUMBER Number of rows in the index
SAMPLE_SIZENUMBER Size of the sample used to analyze the index
LAST_ANALYZEDDATE Date on which this index was most recently analyzed
DEGREEVARCHAR2(40) Number of threads per instance for scanning the index
INSTANCESVARCHAR2(40) Number of instances across which the indexes to be scanned
PARTITIONEDVARCHAR2(3) Indicates whether the index is partitioned (YES) or not (NO)
TEMPORARYVARCHAR2(1) Indicates whether the index is on a temporary table
GENERATEDVARCHAR2(1) Indicates whether the name of the index is system generated (Y) or not (N)
SECONDARYVARCHAR2(1) Indicates whether the index is a secondary object created by theODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N)
BUFFER_POOLVARCHAR2(7) Name of the default buffer pool to be used for the index blocks
USER_STATSVARCHAR2(3) Indicates whether statistics were entered directly by the user (YES) or not (NO)
DURATIONVARCHAR2(15) Indicates the duration of a temporary table:
  • SYS$SESSION - Rows are preserved for the duration of the session
  • SYS$TRANSACTION - Rows are deleted after COMMIT
Null for a permanent table
PCT_DIRECT_ACCESSNUMBER For a secondary index on an index-organized table, the percentage of rows with VALID guess
ITYP_OWNERVARCHAR2(30) For a domain index, the owner of the indextype
ITYP_NAMEVARCHAR2(30) For a domain index, the name of the indextype
PARAMETERSVARCHAR2(1000) For a domain index, the parameter string
GLOBAL_STATSVARCHAR2(3) For partitioned indexes, indicates whether statistics were collected by analyzing the index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO)
DOMIDX_STATUSVARCHAR2(12) Status of the domain index:
  • NULL - Index is not a domain index
  • VALID - Index is a valid domain index
  • IDXTYP_INVLD - Indextype of the domain index is invalid
DOMIDX_OPSTATUSVARCHAR2(6) Status of the operation on the domain index:
  • NULL - Index is not a domain index
  • VALID - Operation performed without errors
  • FAILED - Operation failed with an error
FUNCIDX_STATUSVARCHAR2(8) Status of a function-based index:
  • NULL - Index is not a function-based index
  • ENABLED - Function-based index is enabled
  • DISABLED - Function-based index is disabled
JOIN_INDEXVARCHAR2(3) Indicates whether the index is a join index (YES) or not (NO)
IOT_REDUNDANT_PKEY_ELIMVARCHAR2(3) Indicates whether redundant primary key columns are eliminated from secondary indexes on index-organized tables (YES) or not (NO)
DROPPEDVARCHAR2(3) Indicates whether the index has been dropped and is in the recycle bin (YES) or not (NO); null for partitioned tables
Mas não são somente esses fatores que impactam neste caso, a analíse sobre essa situação deve ser bem mais criteriosa, a criação de indices ajudara? SIM, e muito, principalmente se eles forem baseados em funções que são utilizadas na query principal em algum momento. Devemos ter muito cuidado ao lidar com FT's, só a criação de novos indices não basta, após isso é primordial que façamos um estudo correlacionando a quantidade de I/O Lógicos contra as Leituras Consistentes ( Consistents Gets) e cruzando com o custo utilizado para as situações de Full Table Scan.

Notadamente todos nós sabemos que uma leitura de uma tabela em CACHE é muito mais eficiente e mais rápida do que um acesso á disco, deixemos de lado por enquanto a questão do software de inteligência do EXADATA Storage Server, e foquemos nas bases de dados em hardwares normais que não possuem essa arquitetura. Se nós conseguirmos colocar a tabela em questão em memória, seja "pinando" em memória ou até mesmo alocando-a em um POOL de Memória mais robusto (16k,32k ou 64k) em nosso Buffer_Pool, com toda certeza teremos uma eficiencia tamanha neste tempo de resposta, e ainda podemos contar também com o SSD em alguns casos.

Uma solução abordada por alguns DBA's, é a utilização do Oracle Parallel Query, para dar uma espécie de fôlego a mais para as tarefas de Scans, mais isso também implica no aumento do LOAD da CPU do ambiente onde é utilizado, é necessário ficar de olho.

CONCLUSÃO : é importante que todo DBA fique atento as periodicidades de execuções de FTs's em suas bases de dados, assim evitam-se maiores dores de cabeças com essa problemática. É preciso também que haja um trabalho conjunto de desenvolvimento e banco de dados neste ponto, para que a modelagem dos dados e das estruturas e co-relacionamentos não sejam prejudicados nem em suas raizes quanto em suas dependências. Vale lembrar que não adianta apenas apelar para as novas tecnologias existentes, elas são extremamente uteis sim, mais uma analise clinica de um bom DBA ainda é muito mais válida do que scripts ou correções pré-formatadas.

Espero que tenham gostado, é um artigo pequeno apenas para criar uma discussão sobre um assunto muito pertinente no nosso cotidiano.

SUCESSO Á TODOS!!!...