Re: Controlling load
Date: Wed, 22 Feb 2023 09:21:20 +0000
Message-ID: <DBAPR02MB6470D69DEDA1E77005233E46A1AA9_at_DBAPR02MB6470.eurprd02.prod.outlook.com>
This sounds like old-fashioned tuning by ratios.
I wouldn’t worry too much about that red line on AAS. You can handle a lot more than a fleeting 1:1 ratio of active sessions to CPU. Finger in the air - think 10x but sustained - but depends.
If this red line was not there what problems did you actually experience?
Your CPU utilisation is telling you you have no CPU shortage.
AAS reflects sessions actively working or actively waiting. Hence why a jump in AAS does not necessarily represent a jump in CPU.
You should investigate these spikes because they are telling you something about your application.
But whether batch interferes with online is impossible to tell from just the description,
I would think your batch work involves something which gets invalidated, maybe stats or partition related, probably some parallel sessions and there is this brief invalidation/revalidation spike. Just guessing.
Worth investigating etc but sounds like a slightly wrong initial perspective?
Cheers
Sent from my iPhone
On 22 Feb 2023, at 08:44, yudhi s <learnerdatabase99_at_gmail.com> wrote:
As i mentioned we have 48 core CPU with 2 socket each for a node, so Total ~96 core per node. And the OEM also its showing showing the red line bar on AAS as 96 as Max limit. But during three different issue period we see the below AAS shoot ups above 96 and the wait distribution,
Basically its combination of wait event from classes like concurrency, configuration and others but CPU part is minimum.
But i think you are correct putting CAP on CPU using DB resource manager may not going to help us in above situation as cpu portion is less than 10%. However, i was thinking if any possible way to put cap on the AAS or the number of session those were spawning from the program prog_batch as because they are the one causing the chaos by submitting lot many session simultaneously. So is that possible with the DB resource manager configuration or by tweaking the existing services in anyway?
Regards
On Wed, 22 Feb, 2023, 12:54 pm Ilmar Kerm, <ilmar.kerm_at_gmail.com<mailto:ilmar.kerm_at_gmail.com>> wrote:
Resource manager can help you if you are seeing resource shortage (CPU shortage) during these periods and CPU shortage is causing the Concurrency waits.
If you are not seeing CPU shortage, then you need traditional database troubleshooting methods and resource manager will not help you.
For example if you are behind in 19c patching, then for example before 19.15 there was bug 32898678, 33541865 (fixed in 19.17) and multiple others that caused block change tracking (BCT) to freeze the database or just high BCT buffer space waits.
On Wed, Feb 22, 2023 at 6:08 AM yudhi s <learnerdatabase99_at_gmail.com<mailto:learnerdatabase99_at_gmail.com>> wrote:
Thank you Andy.
I have never used the DB resource consumer group in our databases though. But going through the doc, it seems it can be set for many number of input parameters and also on many resources. Yet to test it , however In most cases I see it's set on amount of cpu. So in our case will it be good idea to determine the cpu percentage and define the plan or we can do it on number of average active sessions which will be again hardcoded value only.
We need to set it for multiple input program values in our case. If you can pass on reference to some sample doc with its exact code implementation, that would be great.
On Wed, 22 Feb, 2023, 2:11 am Andy Sayer, <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote:
You can set up resource manager to map different programs to different resource consumer groups. You’ll need to make sure you order the priority of mapping to make sure program is checked before service name (if that’s not the default). It’s all in dbms_resource_manager
Thanks,
On Tue, Feb 21, 2023 at 12:19 PM, yudhi s <learnerdatabase99_at_gmail.com<mailto:learnerdatabase99_at_gmail.com>> wrote:
Hello Listers,
Regards
--
Dominic
Thank you for the response.
Yudhi
Andy
On a normal day, we see sessions/AAS per ~1 minute interval as ~50 in this database. and they are mainly from two specific 'programs' (say e.g. prog_batch, prog_online). But during certain periods of the day we saw a large number of sessions with programs as 'prog_batch' (i.e. with AAS>~500) flooding the database and we are seeing large concurrency waits, bct buffer space waits all over the database. And its impacting critical sessions of other programs i.e. prog_online. So considering 'prog_batch' is meant for batch kind of processes and it's okay for those sessions to queue-up and run a few minutes longer, but at the same time we can't afford to impact the sessions from prog_online, as those are latency sensitive online users. But we found both of these programs are pointing to the same database services and running on the same node-1. So , apart from controlling the number of sessions/connections from the application end, can we do some easy fixes with regards to the service config level, which will better control the incoming load? Or say putting some cap on the incoming sessions from prog_batch?
This is a 19C database with 2 node RAC and its an Exadata machine. Each node is a 48 core, 2 socket. Both the programs were using the same service and having preferred nodes as node-1.
Yudhi
Ilmar Kerm
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 22 2023 - 10:21:20 CET