Lesson 1

This module introduces you to the components of the environment external to the Oracle database. These are the components that you must focus on as you learn to monitor, identify, tune, and report external performance issues. Each subsequent module of the course expands on the material in this first module. We will start with an overview of the basic processes of each component in the external environment. As the course progresses, you will learn to tune each one for maximum performance.
By the end of this module you should be able to:

1. List OS components that can be tuned
2. Describe OS tuning roles
3. Explain architecture-related performance factors
4. Explain the importance of disk I/O to Oracle database performance
5. Describe how OS memory is managed
6. Describe how Oracle manages CPU usage

The performance of an Oracle 19c database can be influenced by several factors spanning hardware, software configurations, application design, and database management. Below are the key areas that impact performance:

How to Monitor and Troubleshoot Performance?

1. Database Configuration and Instance Tuning
   • Memory Allocation: Improper allocation of SGA (System Global Area) and PGA (Program Global Area) can cause performance issues.
   • Optimizer Statistics: Outdated or missing statistics can lead to suboptimal execution plans.
   • Redo Log Configuration: Insufficient redo logs or frequent log switches can degrade performance.
   • Undo Management: Poorly sized UNDO tablespace can slow down transactions.
   • Control Files and Multiplexing: Suboptimal control file multiplexing may cause failures or bottlenecks.
2. Hardware and System Resources
   • CPU Bottlenecks: High CPU utilization due to inefficient queries or parallel processing.
   • Memory Pressure: If the database doesn't have enough RAM, excessive paging and swapping will occur.
   • Disk I/O Performance: Slow disk read/write speeds can degrade database response time.
   • Network Latency: High latency between application servers and the database can increase response time.
3. Storage and I/O Performance
   • Tablespace Fragmentation: Frequent updates/deletes can cause fragmentation, increasing I/O operations.
   • ASM Configuration: Poorly tuned Oracle Automatic Storage Management (ASM) can impact I/O throughput.
   • Disk Contention: Multiple databases or applications sharing the same disk can cause contention.
   • Data Block Contention: Hot blocks with high concurrent access can slow down performance.
4. SQL Query Performance
   • Inefficient Query Execution Plans: Bad execution plans due to outdated optimizer statistics.
   • Lack of Indexing or Improper Index Usage: Missing indexes can cause full table scans, increasing I/O load.
   • Excessive Joins and Nested Loops: Poorly designed queries with too many joins can be slow.
   • Use of Unnecessary SELECT *: Fetching unnecessary columns increases memory and I/O usage.
5. Parallel Execution and Concurrency Issues
   • Locking and Blocking: Excessive row locking can slow down concurrent transactions.
   • Deadlocks: Poor transaction management can cause deadlocks, halting execution.
   • Latch and Mutex Contention: High contention on shared resources can degrade performance.
   • Parallel Query Execution: Improper use of parallelism can overwhelm CPU and memory.
6. Background Processes and Workload Management
   • LGWR (Log Writer) Performance: Slow writes to redo logs can impact transaction commit speed.
   • DBWR (Database Writer) Performance: Inefficient writing to datafiles can cause performance bottlenecks.
   • Archiver (ARCH) Performance: Sluggish archiving of redo logs can slow down transactions.
   • Resource Manager Configuration: Improper setup can cause CPU hogging by specific workloads.
7. Patching and Version Upgrades
   • Outdated Patch Levels: Running an older Oracle 19c release without patch updates can lead to performance issues.
   • Compatibility Mode: Running in legacy compatibility mode can restrict optimizations.
8. Workload Management and Resource Allocation
   • High Concurrent User Load: Unexpected spikes in user activity can degrade performance.
   • Poorly Configured Connection Pooling: Inefficient connection management can overwhelm resources.
   • Oracle Resource Manager Not Enabled: Without proper limits, some queries may consume excessive CPU or memory.
9. Application and Code Design Issues
   • PL/SQL Bottlenecks: Poorly written stored procedures or triggers can slow down transactions.
   • Excessive Context Switching: Switching between SQL and PL/SQL frequently can degrade performance.
   • Application Logic Issues: Poorly written business logic can increase database load.
10. Data Volume and Growth
    • Large Tables without Partitioning: Querying large unpartitioned tables can be slow.
    • Bloat Due to Frequent DML Operations: Large number of inserts, updates, and deletes can cause row chaining and migration.
    • Table and Index Growth: If not managed, growing indexes and tables can impact performance.
11. Network and Client-Side Factors
    • High Latency Between Client and Database: If the application is far from the database server, performance degrades.
    • Incorrect Oracle Net Services Configuration: Misconfigured SQL*Net settings can slow query execution.

The next lesson begins our discussion with an overview of the OS environment.