Oracle Database Full Table Scan Basics & Internals Express Training (PT-BR)

Se você sempre quis saber como o Full Table Scan funciona no Oracle Database, como o Oracle decide por usar FTS ou leitura usando índice então esse treinamento é pra você.

Vamos explorar o básico do FTS entendendo a arquitetura do banco de dados para então ir a fundo nos processos internos a fim de compreender intensamente o funcionamento dessa poderosa operação de leitura.

Ementa:

1. Introduction to Full Table Scans (FTS)

• Definition and scenarios where FTS occurs.

• Comparison with other access methods like index scans.

2. Oracle Database Architecture Overview

• Understanding database block size and its impact on FTS.

• Role of the buffer cache during FTS operations.

3. Mechanics of Full Table Scans

• How Oracle reads data blocks during FTS.

• Impact of table size and structure on scan performance.

4. Direct Path Reads and Buffer Cache Interaction

• Criteria determining the use of direct path reads versus buffered reads.

• Influence of parameters like _small_table_threshold and _direct_read_decision_statistics_driven on FTS behavior.

5. Parallel Execution and Full Table Scans

• Behavior of parallel execution slaves concerning direct path and buffered reads.

• Performance implications of parallel FTS.

6. Exadata and Smart Scans

• Introduction to Exadata’s Smart Scan feature.

• Conditions required for Smart Scans to occur during FTS.

• Challenges in forcing Smart Scans and considerations for production environments.

7. Monitoring and Tuning Full Table Scans

• Tools and techniques to detect FTS in execution plans.

• Strategies to optimize or avoid FTS, such as indexing and query rewriting.