RAC Functionality Question

Posted on 2012-04-06
Last Modified: 2012-07-03
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?
Question by:xoxomos
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 23

Expert Comment

ID: 37817640
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.

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.

Author Comment

ID: 37817696
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.

Author Comment

ID: 37834399
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.

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


Author Comment

ID: 37834833
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.
LVL 23

Accepted Solution

David earned 250 total points
ID: 37834834
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.


Author Comment

ID: 37835219
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:-)

Author Closing Comment

ID: 38151448
Thank you.

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question