“You say you don’t think it will solve all the problems, so what problems
will it definitely solve and what problems will remain?
This move would help in having more resources, but having more resources
would not solve concurrency related problems (Fetching same data at the
same time). Instead of correcting the poor application design, decision
makers (Developer turned Architects) are expecting the scaling to solve
these problems.”
You mean two sessions trying to read the same row? What’s the problem
there? If two sessions try to read the same block, one will do the physical
IO (if necessary), the other will wait on read by other session (for the
same amount of time), this isn’t slower for either session. You might get
buffer busy waits but that’s going to be a tiny amount of time per block.
So where are you getting these ideas from? If a process spends a
considerable amount of time waiting on buffer busy/read by other then it’s
probably reading a ton of data , changing the archetecture isn’t going to
help that, changing the amount of data needed (tuning the SQL will).
I suggest you get all the facts together, provide the alternative point of
view - the effort it would take to enact and the performance rewards it
would entail. Cary Millsap’s Optimizing Oracle For Performance can be
considered a step by step guide, it even suggests sharing the first few
chapters with your decision makers so they can really appreciate just how
easy it can be.
If you really need to separate your reporting from your OLTP then Mladen’s
suggestions are good. Don’t forget good ol’ resource manager though to
prevent unimportant processes getting more than their fair share of cpu
time (if that’s what’s going on!)
Compare your alternative solution(s) with the effort included in changing
everything (on a guess). Present your findings to whoever is making these
decisions. Changing RDBMS is no quick process, It’s no surprised they
consider us locked in ;)
Regards,
Andrew
