Advanced Queueing and Resource Manager
In 2008, Oracle Certified Master Administrator John Watson used his knowledge of Oracle Database features to create a clever solution to a performance problem.
The client was maxed at 48,000 transactions per hour; John used Advanced Queuing and Resource manager to quadruple that. Read on...
SMSs make money - if you can process them fast enough
The volume of SMS traffic is unbelievable. This isn't just people voting for a Simon Cowell show: it is requests for ringtones, wallpapers, jokes, games, who knows what else. Millions of SMSs,
and they all generate income. Or they should.
At this client (a cellphone network operator in Africa) they were processing maximum 48000 requests per hour.
More than that could not go through the database: the sessions just hanged. When the SMS aggregators were throwing messages at peak rate to the application servers, the queues would shoot
out, and eventually the application servers would start dropping messages. Bad, very bad: that meant not just losing a sale, but probably losing a customer.
What is the answer to a scalability problem? RAC, of course. Well, not always. We investigated the transactions, and the problem was enqueue contention: the business model relied on a pyramid hierarchical
sales structure, so concurrent transactions at a low level would block each other at a higher level.
We had to explain: RAC can't help with TX enqueues, and might make things worse. The answer: re-design the transactions to use Advanced Queueing.
The low level updates put a message on a queue, and commit. Fast. Then de-queueing every few seconds would process a group of a several hunded updates as
one logical transaction. Problem solved: now processing up to 160000 requests per hour.
But they wanted more. Rolling out a banking application for transferring funds from one subscriber account to another, all with SMSs, looked like doubling the request rate.
And the box was already running at around 90% utilization. It was trime to look at processing priorities, and what else was running on that box.
The "what else" was reports. Such as analyzing the effect on sales of a radio advert - in real time. This is important and the information needs to be timely, but does it really matter if
the analysis take three minutes instead of thirty seconds? This is where the Resource Manager comes into its own.
Not widely used and understood (a lazy DBA can just throw more hardware and Oracle licences at the problem) but in this case perfect.
By setting up appropriate priorities, every sales request would respond fast no matter what the workload, and at times the reports would degrade.
Just another example of how enabling a couple of advanced features can save the need for more hardware and more licences.