Significant Performance Boost
Extract process runtime reduced from 3 hours to just 10-15 minutes, accelerating financial reporting and operational workflows.
Technologies used
Oracle
Oracle SQL Trace
Motivation
The customer, a US leader in patient safety, healthcare insurance, claims, and risk solutions, faced severe performance issues when extracting large volumes of financial data from their general ledger. Inefficient SQL execution, invalid system statistics, and overloaded memory caused extract jobs to run for hours, slowing analytics, reporting, and operational workflows. The objective was to identify the root causes, improve query efficiency, optimize system statistics, and reduce extraction time to support timely decision-making and high system availability.
Main challenges
Challenge 01
Memory and Query Bottlenecks
Large datasets triggered memory overload, inefficient execution paths, and slow-running queries. As data volume grew, extraction times increased dramatically, impacting downstream analytics and general ledger reconciliation.
#1
Challenge 02
Invalid and Outdated System Statistics
System statistics did not reflect actual performance conditions, causing the optimizer to choose suboptimal execution plans and perform unnecessary full table scans instead of using available indexes.
#2
Challenge 03
Inefficient Index Utilization
Due to outdated statistics and poor query structure, indexes were not used effectively. This resulted in long-running operations, excessive I/O, and increased CPU consumption during extraction.
#3
Challenge 04
Mixed OLTP and DSS Load Conditions
The database served both transactional (OLTP) and batch (DSS) processing, requiring a flexible statistics collection strategy to maintain optimal performance under varying load patterns.
#4
Our approach
Performance Monitoring
Used SQL Trace and Oracle performance monitoring tools to identify inefficient execution paths and slow operations.
Root Cause Analysis
Detected that a class of queries consistently triggered full table scans due to invalid or non-representative system statistics.
Statistics Validation and Adjustment
Gathered and validated new system statistics, ensuring they accurately represented real workload behavior.
Processing Type Balancing
Balanced statistics collection across OLTP and DSS workloads to avoid skewing performance in either processing type.
Time-Based Statistics Collection
Collected daytime, nighttime, and 24-hour statistics to capture full system behavior under varying load.
Optimization through Statistics Publishing
Analyzed gathered statistics and published updated values to improve query execution plans and eliminate bottlenecks.
Facing the same challenge?
Bring your complexity. We'll bring the plan. Tell us about your project and we'll get back within one business day.
Inquire for more infoSolution
01
Performance Monitoring & Diagnostics
A monitoring framework used to detect inefficient query patterns, identify full table scans, and capture performance bottlenecks through SQL Trace analysis.
Key capabilities:
- Continuous performance tracking
- Detection of inefficient execution plans
- Identification of memory and I/O bottlenecks
- SQL Trace-based diagnostics
02
Root Cause Analysis Engine
A systematic analysis module designed to uncover issues caused by invalid system statistics and poor query design, enabling the team to isolate the key factors slowing down the extract process.
Key capabilities:
- Deep analysis of query behavior
- Identification of invalid or outdated system statistics
- Detection of suboptimal index usage
- Automated generation of tuning recommendations
03
Statistics Optimization Framework
A structured process for gathering, validating, and publishing accurate system statistics to ensure the optimizer generates efficient execution plans.
Key capabilities:
- Time-based statistics collection (day, night, 24-hour)
- Balancing OLTP vs DSS workloads
- Index utilization improvement
- Publishing optimized statistics for query performance
04
Query & System Performance Tuning
A set of optimization enhancements applied to SQL queries and system parameters to reduce extract runtime and eliminate system bottlenecks.
Key capabilities:
- Reduction of extract runtime from 3 hours to 10-15 minutes
- Improved memory utilization
- Elimination of redundant full table scans
- Acceleration of high-volume data processing
Results & business impact
Reduced I/O Load
Total direct path read events decreased by 50%, lowering system stress and improving stability.
Higher System Efficiency
Optimized statistics and query execution increased throughput and reduced resource usage across the system.
