Busting Bottlenecks
Retailers improve customer relationships based on the nuggets of information found in each transaction. This data often traverses multiple divisions of the organization – many of which are supported by disparate databases. For Schnuck Markets, a database management system that monitors transactions also supports business analysis and ensures optimal applications performance.
Database applications have steadily expanded and improved as computing systems have evolved, helping keep retailers abreast of the relationship between consumer data and applications’ end-user performance.
Due to this expansion of database applications and retailers’ multi-channel operations, corporate databases have evolved into treasure chests of transaction and customer information pertaining to staying on top of daily consumer trends and seasonal business cycles.
There is a downside, however: Retail chains continue to struggle with exactly where this data resides and how to access it in an efficient manner. Some organizations operate siloed applications and data storage, a configuration that can be costly to support and from which extracting data can be difficult.
St. Louis-based Schnuck Markets relies on its internal data to run regular reports on merchandise forecasts and sales, as well as to analyze results from customer surveys. “These reports could take us hours, even days, to complete, due to performance issues,” says Lisa Hu, the retailer’s database administrator leader.
Rather than reconfigure operations to consolidate data and streamline data flow, it had added solutions that merely improved visibility into siloed data. This caused two issues for Schnucks.
First, it could take as long as four hours to create reports. “We needed a way to identify performance or pinpoint when a bottleneck is occurring,” Hu says. “We had no baseline for application or database performance. We would know performance was declining, but had nothing to compare it to.” Worse, this caused the chain to be reactionary when addressing performance issues of its computing and storage systems.
Schnucks operates a UNIX box that supports multiple databases. It was outfitted with the Oracle Enterprise Manager solution, an application management system that monitors the health and performance of application processes and components, including applications, middleware and databases.
While the solution provides insight into how the company’s systems are operating, a lack of historical data made it difficult to determine the source of performance-related issues.
“It became apparent we needed to find a way to pinpoint problems and pro-actively find resolutions and fix problems,” Hu says.
Overall, what the chain needed was a database and application management solution that could improve the application development process – a foundation that would allow Schnucks to support operations, best serve shoppers and build profits. More specifically, the ideal solution had to provide insight into historical data and previous performance activities and issues.
“We didn’t want to write a lot of code to remediate solutions,” Hu says. “That would be horrible from a cost and labor standpoint. We wanted an out-of-the-box solution that could easily find indexes for poorly-written SQL (structured query language).” Schnucks chose to add a database and application performance management system from Redwood Shores, Calif.-based Precise Software.
Drills down into data
Using Precise’s Transaction Performance Management solution, the grocer is now able to stay abreast of potential technology breakdowns before they occur, as well as understand their business impact. Precise’s TPM tool drills down into data to provide visibility across all application environments and capture performance metrics from all system components.
The solution is being deployed to monitor and detect bottlenecks occurring in the company’s Oracle databases. By managing its technology’s performance, Schnucks can now correlate end-user performance problems to specific databases.
The TPM solution monitors transactions from their origination (whether at store-level POS or online) through every application tier through which the data passes. It breaks down where the data spends the most time, and how that time compares with the highest recorded performance level within each application.
The software then analyzes information and serves up reports in graphs and pictures, giving users insight into potential problems. The format also allows users to drill down into data and understand the root cause of bottlenecks.
24-hour reporting
On a daily basis, Hu logs into the application through a web-based portal and receives a first-hand view of all transactions that ran through each database over the previous 24 hours. “I focus on the highest user times, sort by environment — in this case, Oracle databases — and look at time transactions spent on this particular SQL,” she says. “If the average is two hours, then I look at higher anomalies.”
Hu then drills down to see what might be causing the blockage. Once a problem is revealed, she is able to share the performance report with Schnucks’ IT team, “before performance really turns sour.”
The solution allows the chain to set alerts to monitor specific trends, as well as any applications or servers that are on the verge of violating preset thresholds. This affords the chain ample time to resolve the issue or ramp up memory before the system crashes.
TPM has allowed Schnucks to slash the many hours it spent creating reports to a mere 10 minutes, a factor in helping the retailer save labor among its taxed DBAs.
While Schnuck Markets is currently using the solution to monitor SQL for customer surveys and all customer transactions entering through POS and customer web interfaces, it hopes to expand the solution to manage data back-up and audits.
