Database Performance Tuning: Critical Deployment Problems Solved at Canadian Telecommunications Company
Rogers Communications, Inc. is a leading provider of wireless, cable, high speed Internet and telephone service for residences and businesses across Canada.
It was in the final stage of rolling out a customized version of ClickSoftware’s ServiceOptimization suite for scheduling customer appointments, intended to optimise support schedules and make the best use of technician’s time. The business was under pressure to deploy the new system, but was being held back by poor performance and severe response time problems.
The Oracle database that supported the system was already exceeding the CPU load that had been estimated for its full deployment, with fewer than half of the company’s technicians in the mix. The Greater Toronto Area (GTA), the company’s largest region, would account for 50% of the total load, and still had to be brought on line. Clearly, the system was not working as anticipated, and the project was stalled until the underlying issues could be resolved.
CPT Global was invited to examine the application and investigate these performance issues in order to reduce the CPU requirements and build in enough headroom to support the GTA rollout.
CPT Global initially analysed the Rogers systems remotely using its proprietary Perfmaster tool (a CPT expert system designed for Oracle analysis). This step determined the scope of on-site analysis required. It then placed a single consultant at Rogers’ facility in Brampton, Ontario, for two weeks. Additional remote expertise was available from other CPT specialists as required.
The on-site consultant worked cooperatively with Rogers’ staff to identify specific symptoms and trace the root causes of the excessive consumption problem. The consultant’s work identified a number of underlying issues, and led to a concise set of recommendations for technical and management consideration.
These recommendations included the development of two new indexes, and the implementation of an Oracle patch to overcome a rarely-encountered CPU-spin bug. No changes were required in the application code, which greatly simplified testing and implementation.
CPT’s detailed recommendations and subsequent testing allowed Rogers to validate the improvements and implement the fix. Several changes were implemented concurrently so the Toronto roll-out could proceed on-schedule.
The results were impressive. Overnight, the demand for backend CPU was reduced by more than 60%. End-user performance problems dropped to virtually zero, and the GTA deployment went forward without incident.