Skip to Main Content
  • Questions

  • Recommendation required for best combination of large_pages, AMM and ASMM for dealing with heavy workloads during load testing

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashish.

Asked: May 02, 2019 - 11:15 am UTC

Last updated: May 13, 2019 - 3:27 am UTC

Version: Oracle Database 11g SE1 (11.2.0.4)

Viewed 1000+ times

You Asked

Hi,

We are currently conducted load testing on our application that is JAVA and Oracle based. Major processing occurs at database end. Application is a web-based lending platform. Below are details:

1. Application Server - Jboss 6.1.0 running on AWS EC2 VM with 16 Cores/64GB RAM
2. DB Server - Oracle 11g SE1 (11.2.0.4) hosted on AWS RDS with 16 Cores/64GB RAM
3. Load testing using apache jmeter 3.3 with 400 virtual users using and performing activities on application concurrently

We have been performing this exercise since last 2 months and based multiple ADDM and AWR reports, we have done appropriate tuning at code level, post which we saw improvements in agr. response times and also queries are not being ADDM/AWR reports.

We reached a stage where any sort of code level tuning was't impacting avg. response times obtained in load tests. Thus, we started playing with DB parameters one by one. Till date, altering the below parameters has resulting in good performance improvement:

1. cursor_sharing set to FORCE
2. use_large_pages enabled
3. sga_target increased from 25GB to 30GB
4. sga_max_size increased from 25GB to 30GB
5. memory_target increased from 37GB to 40GB

memory_max_target is 48GB by default. pga_aggregate_target is unset(0)

The problem that we have started facing now is, as we increase SGA to any value > 30GB, huge performance degrade is observed in forms of increase avg. response times in load test reports. Major wait events reported in ADDM and AWR reports are:

1. Shared Pool Latches - library cache: mutex X, library cache lock, latch: row cache objects
2. Hard Parse
3. Hard Parse Due to Invalidations

Assistance is required in terms of recommending apt. values for above parameters so that we can make the most of the hardware platform given and remove these waits.

Thanks in advance.

and Connor said...

Since you are focusing on application performance then if

cursor_sharing set to FORCE


is providing benefits, then you really should tackle that first, ie, your application is not using bind variables. That is an overhead right there in terms of parsing and SGA access.

For larger systems, we generally recommend *against* full memory managment, so you want to set:

sga_target
pga_aggregate_target

and *not* set the memory_target parameter.

Similarly, for large session counts and large SGA's, then huge pages is a no brainer.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Issues with cursor sharing=FORCE

Ashish Srivastava, May 07, 2019 - 5:17 am UTC

Thank you for the useful response regarding memory related parameters.

Now the thing is, setting cursor_sharing to FORCE is improving performance tremendously for services with huge avg. response time. But, it is also degrading the performance of some other existing services. Kindly let us know, is there any other DB parameter that we can tune to reduce hard parsing, library cache lock and library cache: mutex X apart from:

1. Setting cursor_sharing=FORCE
2. Using bind variables

The point is - for using bind variables, we would have to put huge effort. But, we would definitely do it to scale our application in case there is no way out.

Thanks in advance!
Connor McDonald
May 13, 2019 - 3:27 am UTC

It doesn't have to a "big bang" (ie, tackle EVERY query).

You could start with something like:

select FORCE_MATCHING_SIGNATURE, sum(execution_count) 
from   v$sql
group by FORCE_MATCHING_SIGNATURE


which gives you the highest execution count SQL - you could target them initially to get the biggest "bang for your buck". Convert those to (say) PLSQL to get all the benefits of lower parsing, cursor reuse etc.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database