Solved

RAC Functionality Question

Posted on 2012-04-06
8
243 Views
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?
0
Comment
Question by:xoxomos
  • 5
  • 2
8 Comments
 
LVL 23

Expert Comment

by:David
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.  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.
0
 

Author Comment

by:xoxomos
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.
0
 

Author Comment

by:xoxomos
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.

Thanks,
Sanjay
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:xoxomos
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.
0
 
LVL 23

Accepted Solution

by:
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.

Regards,
Dave
0
 

Author Comment

by:xoxomos
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:-)
0
 

Author Closing Comment

by:xoxomos
ID: 38151448
Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now