Managing Oracle System Statistics


Managing Oracle System Statistics

Description

2016 till now Oracle, Outsourcing

Resolving system performance issues and improving the overall database performance.

The US national leader in customized insurance, claims and patient safety & risk solutions for physicians, surgeons, dentists and other healthcare professionals, as well as hospitals, senior care and other healthcare facilities.

Challenge

It was noticed that one SQL-query was performing poorly and required optimization. The task was to find out the reasons which had a negative impact on the system performance and eliminate them.

Solution

The problem was revealed during system performance monitoring. While building the query execution plan, the optimizer was more likely to choose a full table scan instead of more efficient use of existing indexes. We also noticed a whole class of the queries having the same behavior.

At the next step, we conducted a root cause analysis of the issue, which showed that the system statistics weren’t valid and they failed to represent the real performance of the system.

The elaborated solution to this system issue contained the following:

  • Identifying the time intervals for gathering statistics;
  • Gathering and validating system statistics values for the database;
  • Finding the balance, since the Database serves both batch (DSS) and online transaction processing (OLTP);
  • Gathering  daytime, night-time and 24-hour system statistics for the databases, when the workload is expected to be typical of the system;
  • Analyzing the gathered values;
  • Publishing new system statistics.

As the result, the number of total direct path read events decreased twice. Therefore, some of the bottleneck processes speeded up manifold – from 3 hours run time in average to 10-15 minutes.



Technologies:

  • Oracle
  • Oracle SQL Trace

Some detailed information not disclosed due to NDA restrictions