Controlling load
From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Wed, 22 Feb 2023 01:48:18 +0530
Message-ID: <CAEzWdqeSPT065T5cMf10H3f_nCRwvL6rjYiUeJ5G+6-BjOQFGA_at_mail.gmail.com>
Hello Listers,
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.
Date: Wed, 22 Feb 2023 01:48:18 +0530
Message-ID: <CAEzWdqeSPT065T5cMf10H3f_nCRwvL6rjYiUeJ5G+6-BjOQFGA_at_mail.gmail.com>
Hello Listers,
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.
Regards
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 21 2023 - 21:18:18 CET