RAC Functionality Question

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.

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.

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.

Thanks,
Sanjay

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.

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

Thank you.