Oracle Database Performance Tuning

single image
Client  
         :  Revitas

Industry       :  Hi-Tech

Practice Area  :  Product Engineering Services


Background:

Revitas Inc., a leader in Enterprise Revenue Dynamics, delivers integrated solutions for contract, pricing and compliance that drive higher profitability and lower risk. Revitas’ integrated solutions offer multiple products running on the Oracle database. Most of the processes in the application layer are calculations using complex formula, reports and other operations which rely heavily on the database.

The performance of the Revitas applications are only as good as the performance of the database. Database objects (tables, views, procedures), and performance objects (indices on tables, SQL execution profiles), must be perfectly tuned for optimal application performance. Many of Revitas’ reports and calculations were taking days, even weeks to complete. Business users were not happy. Estuate made database design changes, rewrote application code, removed unused indices and recommend creation of new indices. We also addressed SQL query efficiency, Oracle database enterprise edition configuration features and trained Revitas’ developers on how to better code their applications.

The Opportunity:

  • Evaluation of both the application layer and the underlying database shed light on key tuning areas- design changes, object reevaluation, workflow changes and SQL level tuning required
  • Execution of Automatic Workload Repository (AWR) reports
  • Enhancement of tuning by recommending database partitioning, parallel execution, index hints, converting insert statements to merge and append statements, sequence cache, result cache, and “WITH clause” features
  • Identification of sub-optimal SQL code, index fragmentation, row chaining and the need for database object reevaluation

The Estuate Solution:

  • Performance tuning services included complete application and database object evaluation, pain point identification, and recommendations for moving forward.
  • All our processes are transparent, properly documented, demonstrated in test environments, and presented to the client development team for approval and deployment.
  • Our recommendations are explained and prioritized. If needed, we also train developers on high performance application and query design.

Business Impact:

  • Application algorithms, queries, and report performance dramatically improved. One report that used to take 2+ days to run, now runs in less than 30 minutes. Similar gains were made with other queries and application algorithms.
  • Revitas DBAs and developers came away from the project, and Estuate’s training, understanding best practices for performance-sensitive database and application development.
  • Revitas’ IT management now has a roadmap for future tuning work and understands which aspects of their system would get the most benefit from further tuning efforts.