Scale Oracle DBs¶

The solution of optimizing Oracle DBs to reduce query fetch time (read mode).

1. Recognize Hot-Spots¶

Use Oracle’s Built-In Monitoring Tools: Utilize tools like Automatic Workload Repository (AWR) and Active Session History (ASH) to capture detailed performance data. These tools help you see which queries are the most resource-intensive and how often certain queries are executed.
Query Execution Statistics: Monitor views such as SQL Queries to identify SQL statements with high execution counts or long cumulative execution times.
Oracle Enterprise Manager (OEM): If available, OEM provides a graphical and historical analysis of system performance. It can automatically highlight SQL statements and database objects that are consistently under heavy read load.
Dashboard Query Logs: For environments where tools like Metabase interface with the database, analyze the queries generated by your dashboard. Identify patterns where the same heavy queries are executed repeatedly.

2. Database Indexing¶

Database indexing in Oracle is a powerful tool to enhance query performance by minimizing unnecessary full table scans.

An index in Oracle is similar to an index in a book—it provides pointers that help locate specific data quickly. Instead of reading every row in a table to find a match for a query, Oracle can traverse the index structure to jump directly to the relevant rows. This is particularly beneficial in large tables where full table scans would be resource-intensive.

Oracle typically employs B-tree structures for standard indexes. A B-tree index organizes data in a balanced tree format, facilitating fast lookups, range scans, and ordered retrieval.

-- Add indexes for frequently queried columns
CREATE INDEX idx_user_age ON users(age);
CREATE INDEX idx_user_email ON users(email);

-- Composite index for multiple columns
CREATE INDEX idx_user_age_city ON users(age, city);

3. Caching Implementation¶

Integrating Caching

Enable and configure query result caching through Oracle’s initialization parameters.

A. Database-Level Caching¶

Oracle Query Result Cache: Oracle supports query result caching, which stores the outcome of a query in memory. When the same query is executed and the underlying data hasn't changed, Oracle returns the cached result instead of re-executing the query.

B. Application-Level Caching¶

Use systems like Redis or Memcached to cache query results outside the database.

C. Combined Caching Strategies¶

Combining database-level caching with application-level caching can further optimize performance.

4. Database Partitioning¶

Definition¶

Partitioning divides a large database table or index into distinct segments. Each segment (or partition) stores a subset of the data based on a partitioning key—such as a date, range of values, or list of categories—allowing Oracle to operate more efficiently.

Objective¶

The primary goals are to improve query performance by reducing the amount of data scanned (through partition pruning), simplify maintenance activities (e.g., archiving or purging old data), and boost manageability for large tables.

Benefits of Partitioning¶

Improved Query Performance
Enhanced Maintenance and Manageability
Optimized Resource Usage
Scalability

Types of Partitioning in Oracle¶

Replications¶

Definition¶

Database replication is the process of copying and distributing data from one database (the source) to one or more additional databases (the replicas).

Objectives¶

Enhance Availability and High Availability (HA)
Disaster Recovery
Continuous Operation
Improve Performance and Load Distribution
Read Scaling
Reduced Query Latency

5. Normalizing¶

If necessary

Difinition¶

Normalization is a database design process that organizes data into structured, related tables to minimize data redundancy and dependency.

Goals¶

Reduce Data Redundancy
Eliminate Duplication
Improves query performance in many cases
Improve Data Integrity and Consistency
Enhance Maintainability
Optimize Storage Utilization and Efficient Use of Storage