Oracle Database Performance Management & Tuning Eğitimi

Bu eğitim, performans iyileştirmesi (tuning) gerektiren bilinmeyen bir veritabanı ile başlar. Dersler, bir DBA’in problemli alanları tespit etmek için ihtiyaç duyduğu bilgileri toplama, yaygın problemleri teşhis etme ve bu problemleri giderme adımlarını izler. Uygulamalarda kullanılan metodoloji ağırlıklı olarak reaktiftir (problemi gör → teşhis et → çöz).

İzleme (monitoring) araçları yapılandırılıp mevcut raporlar incelendikten sonra, öğrenciye Oracle mimarisi; SELECT ve DML SQL ifadelerinin işlenme süreçleri üzerinden aktarılır.

SQL tuning bölümünde, DBA’in kod üzerinde çok az veya hiç değişiklik yapamadığı varsayılır. Bu nedenle DBA, SQL performansını mevcut araçlarla iyileştirmeye odaklanır. Performans iyileştirmesi gerektiren SQL ifadelerinin tespit edilmesi ve performansı değiştirecek yöntemlerin belirlenmesi için gerekli teşhis araçları ele alınır. Bu kapsamda: optimizer’ı etkilemek için istatistikler ve SQL profile kullanımı, indeks ekleme, indeks yeniden oluşturma, SQL Advisor araçlarından yararlanma gibi yöntemler işlenir.

DBA’in temel görevlerinden biri, değişiklikler sonrası SQL performansını sürdürülebilir şekilde korumaktır. Bu eğitimde, değişikliklerin etkisini test etmek ve minimize etmek için:

Types of Tuning
Tuning Methodology
Effective Tuning Goals
General Tuning Session

Performance Tuning Features and Tools
Top Timed Events
DB Time
CPU and Wait Time Tuning Dimensions
Time Model
Statistic Levels
Wait Events
Alert Log and Trace Files

Automatic Workload Repository Data
AWR Administration
AWR in a Multitenant Architecture Database
Snapshots
Reports
Compare Periods
PDB-Level Snapshot Views

Determining Tuning Priorities
Top SQL Repors
Common Tuning Problems
Tuning During the Life Cycle
ADDM Tuning Session
Performance Versus Business Requirements
Performance Tuning Resources

Typical Delta Tools
Oracle Database Metrics
Statistic Histograms
Server-Generated Alerts
Alert Usage Model
Setting Thresholds

Comparative Performance Analysis with AWR Baselines
Types of Baselines
Creating AWR Baselines
Managing Baselines by Using the DBMS_WORKLOAD_REPOSITORY PL/SQL Package
Performance Monitoring and Baselines
Defining Alert Thresholds Using a Static Baseline
Configuring a Basic Set of Thresholds

Automatic Maintenance Tasks
ADDM Performance Monitoring
AWR and ADDM Behavior in a Multitenant Architecture Database
Using Compare Periods ADDM
Active Session History
Emergency Monitoring
Real-time ADDM

Scope of a Composite Database Operation
Database Operation Concepts
Identifying a Database Operation
Enabling Monitoring of Database Operations
Monitoring Load Database Operations
Reporting Database Operations by Using Functions
Database Operation Tuning

Creating Services
Using Services with Client Applications
Using Services with the Resource Manager
Using Enterprise Manager to Manage Consumer Group Mappings
Using Services with Metric Thresholds
Service Aggregation and Tracing
Client Identifier Aggregation and Tracing
trcsess Utility

SQL Statement Processing Phases
Role of the Oracle Optimizer
Identifying Bad SQL
Uses of Execution Plans
Reading an Execution Plan
SQL Trace Facility
Generate an Optimizer Trace

Functions of the Query Optimizer
Selectivity,Cardinality and Cost
Optimizer Statistics
Extended Statistics
Adaptive Execution Plans
Using Hints
Access Paths
Join Operations and Sort Operations

Index Maintenance
SQL Access Advisor
Table Reorganization Methods
Space Management
Extent Management
Migration and Chaining
Shrinking Segments
Data Compression

Capturing the SQL Workload
Creating a SQL Performance Analyzer Task
Comparison Repor
Tuning Regressing Statements
SQL Tuning Recommendations
Preventing Regressions
Tuning Regressed SQL Statements

Maintaining Optimizer Statistics
Setting Statistic Preferences
Using the Optimizer Statistics Advisor
Deferred Statistics Publishing
Automatic SQL Tuning
SQL Tuning Advisor
Using the SQL Access Advisor
SQL Plan Management

Capture Considerations
Replay Considerations
Replay Analysis
Database Replay Packages
Calibrating Replay Clients

Shared Pool Architecture
The Library Cache
Latch and Mutex
Diagnostic Tools for Tuning the Shared Pool
Sizing the Shared Pool
Avoiding Fragmentation
Data Dictionary Cache
SQL Query Result Cache

Database Buffers
Symptoms of a Buffer Cache Issue
Solutions for Buffer Cache Issues
Memoptimized Rowstore
Automatically Tuned Multiblock Reads
Database Smart Flash Cache
Force Full Database Caching
Flushing the Buffer Cache

SQL memory usage
SQL Memory Manager
Configuring Automatic PGA Memory
Managing PGA for PDBs
PGA Target Advice Statistics and Histograms
Temporary Tablespace Management
Temporary Tablespace Group
Shrinking a Temporary Tablespace

Dynamic SGA
Memory Advisories
Automatic Shared Memory Management
Memory Broker Architecture
Memory Management for CDBs
Managing SGA for PDBs
SGA Advisor
Automatic Memory Management

Automatic Checkpoint Tuning
Sizing the Redo Log Buffer
Sizing Redo Log Files
Increasing the Performance of Archiving
General Tablespace and Undo Tablespace Best Practices
SQL Execution Related Waits
Internal Fragmentation Considerations
I/O Modes