Solved

RAC Functionality Question

Posted on 2012-04-06
8
248 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 88
add more rows to hierarchy 3 26
Fill Null values 5 29
minium over 4 numeric columns for each row in oracle 2 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

820 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