RAC Functionality Question

We're currently running 10gR2 single instance.  The majority of the work comes from on-line activity from the students.  There are several jobs that I could class as more batch oriented jobs that run several times during the day and do have somewhat of a negative impact on response for the on-line activities.  My boss is asking if we were to implement RAC, could we then have one application server that would take all those batch type jobs and the other application servers the normal online activities in such a way that students coming in though those application servers would not be impacted by  the batch jobs?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
Possibly, although many of us would encourage you to associate "application server" with the middle tier of a three-tier system (web client, to web/app server, to back end database or file server).  But imo the benefit of the real application cluster comes by eliminating a single point of failure.  In a single instance, both batch and interactive are in high risk.  RAC is better used for a logical or physical copy of the database instance and archive logs, and I have to presume your boss isn't budgeted for additional hardware and licenses.

Were I your consultant I would start with the easy answers, the "low-hanging fruit", starting with keeping your tables/indexes statistically tuned for the cost-based optimizer to choose best paths.  Document your service-level expectations:  what conditions have been agreed upon as service-level commitments? Again to AWR, what are your starting metrics, and how do you measure their being met or exceeded?  Which, if any of the batch jobs, must be run concurrently with student access times -- move the non-critical ones to kick off at, say, 03:00.  Secondly, I'd be all over the AWR reports to find which processes are resource-bound (wait times or CPU), and focus on getting those tuned ASAP.  Look at the statement explain plans for the most-performing SQL statements.  Make friends with the DBMS_JOB_SCHEDULER.

Remember that batch and interactive user accounts can, and usually should, have distinct profiles for available resources -- with scheduled windows.  It's easy to set up a profile with extra shared pool resources, or sort area, for batch jobs firing after some set time.  Likewise, boost the appropriate quotas on interactive users at their peak window.  One solution might include setting a logged-on max time in the profile, or a sweep job to find and kill open but discarded sessions.

Identify those accounts that don't require update access, and ensure they're using a SELECT-restrained role.  Batch accounts may have a separate login.sql that specifies read-only transactions.  http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_10005.htm

The moderately ranked solutions that come to mind -- can the query/report batch jobs work just as well with non-realtime data -- a copy that's hours or days stale?  If yes, look into materialized views with scheduled refreshes. That may work for the interactive queries as well.  Can you set aside historical / archived records?  Then look into table partitioning and/or off-site storage.

Another favorite catch is that people don't bother moving their indexes to larger sized O/S blocks.  Do you prefer to be pulling back data in 256k chunks, or 16 Mb?

Hope this makes sense, and use common sense to keep your solution simple.
xoxomosAuthor Commented:
Currently we have a physical standby to satisfy the physical copy of the database instance and archived logs.  As I am interpreting my boss now assuming there are four instances, all the batch type jobs would be directed at one specific instance and processed to completion on that one instance.  The other three instances would be dedicated totally to those on-line activities of the students and not impacted at all by the processing happening on the one batch processing.
The service level agreement translates basically into 24 X 7 availability except for two and a half hours per week for a cold backup and a seventy two hour period once per quarter following final exams.
xoxomosAuthor Commented:
Coming from Oracle support, yes we can.

We are using Oracle as database behind our learning management system called Blackboard. There are certain functions we have added locally which are more batch oriented. The problem is these functions when run usually lead to some performance degradation. My boss wants to know if RAC could be implemented in such a way that say a single application server could direct these jobs to a particular instance on the RAC while other application servers direct the online activity to other instances on the RAC with the objective being the online activities would not be impacted when the batch type activities are being run.

Hi ,

Yes, you can use the service concept in RAC to route a specific connection to a particular server.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

xoxomosAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for xoxomos's comment #37834399

for the following reason:

Answer came from Oracle support.
DavidSenior Oracle Database AdministratorCommented:
I'd glad that Support was able to help.  Please understand that we are unpaid volunteers who try to help others, and that "points" are the only thanks for our service.  Well, that and a tee-shirt at certain levels of effort.  Unfortunately, your reason for closure can be misused.  Perhaps you could append the specific solution provided, after which I'd appreciate a determination from the monitor.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Thanks for bringing that to my attention and not a problem.  Some years back  I was somewhat criticized for awarding points when the response did not really provide the solution but I guess time changes things:-)
xoxomosAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.