Hi Shane, I have one doubt here, As I see while defining the IORM we have
two inputs , either "catPlan" or "dbPlan". In cases where we have multiple
databases , we have to define the "dbPlan '' something as below. But as in
our case we have only one database, so we don't need the "dbPlan" to be
defined. So as you mentioned should we only do the online alter like "alter
iormplan objective=auto" in all the ~7 cell server,
OR
We should define DBRM without any plan/plan directive, which means we are
not capping any CPU resources at DB level, but providing just a name to the
IORM to work based on these resource manager categories using catPlan
parameter.
What should be the correct strategy here? Should we first try "alter
iormplan objective=auto" and let Oracle manage its own? If that doesn't
work then we should go for "catPlan '' method ? or "catplan" method is the
way AUTO objective works and by simply making it AUTO(using oneline ALTER
command) will not work?
*******************Using "dbPlan" *******************************
alter iormplan dbplan=((name=database1, level=1, allocation=60), ->
(name=database2, level=2, allocation=80), -> (name=other, level=3,
allocation=100))
list iormplan detailname: enkcel04_IORMPLAN
catPlan:
dbPlan: name=database1,level=1,allocation=60
name=database2,level=2,allocation=80
name=other,level=3,allocation=100
objective: auto
status: active
********************* Using "catPlan" **********************************
And in our scenario, like we have only one database, so "Dbplan" is not
required, so we can opt for "catPlan", something as below using Db resource
manager, without creating any plan/plan directives in DB level.
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
-- Create Categories --
dbms_resource_manager.create_category(
category => 'OLTP_CATEGORY',
comment => 'Category for low latency queries mostly small reads or
index access');
dbms_resource_manager.create_category(
category => 'BATCH_CATEGORY',
comment => 'Category for low latency queries mostly large reads or
full table scan');
-- Assign Consumer Groups to Categories --
dbms_resource_manager.update_consumer_group(
consumer_group => 'OLTP',
new_category => 'OLTP_CATEGORY');
dbms_resource_manager.update_consumer_group(
consumer_group => 'BATCH',
new_category => 'BATCH_CATEGORY');
dbms_resource_manager.update_consumer_group(
consumer_group => 'MAINTENANCE',
new_category => 'BATCH_CATEGORY');
dbms_resource_manager.submit_pending_area();
END;
/
alter iormplan catplan=((name=OLTP_CATEGORY, level=1, allocation=70), ->
(name=BATCH_CATEGORY, level=1, allocation=30), -> (name=OTHER, level=2,
allocation=100))
list iormplan detailname: enkcel04_IORMPLAN
catPlan: name=OLTP_CATEGORY,level=1,allocation=70
name=BATCH_CATEGORY,level=1,allocation=30
name=OTHER,level=2,allocation=100
dbPlan:
objective: auto
status: active
On Fri, Nov 20, 2020 at 2:48 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank you so much. We will try to switch from Basic to Auto objective and
monitor the behaviour.
I hope this alter iorm (from Basic to Auto)can be done online without
downtime in each storage cell level, so we can change it on the fly and
revert back in case things goes bad.
On Fri, 20 Nov 2020, 2:40 am Shane Borden, <sborden76@xxxxxxxxx> wrote:
AUTO is supposed to take a balanced approach to all databases on the
rack., whether there is 1 or 100.
---
Thanks,
Shane Borden
sborden76@xxxxxxxxx
407-963-4883
On Nov 19, 2020, at 3:53 PM, Lok P <loknath.73@xxxxxxxxx> wrote:
Yes, we have this one on exadata - X5 and image version 19.2 and thus
having rightback flash cache enabled too.
I am not seeing any documents clearly stating this. So my question was if
AUTO iorm objective works on single database(i.e intra database) or it only
works when multiple databases hosted on the same exadata machine(i.e. inter
database Io load)?
On Fri, 20 Nov 2020, 2:08 am Shane Borden, <sborden76@xxxxxxxxx> wrote:
I dont know your workload, but AUTO did what we needed it to on a few
Exas now…..
Also depending on how old the Exa is, make sure you have WriteBack
FlashCache turned on. Default is on now, but for older versions, it didn’t
get turned on by default.
---
Thanks,
Shane Borden
sborden76@xxxxxxxxx
407-963-4883
On Nov 19, 2020, at 3:34 PM, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You Shane.
Currently we have a default IORM objective set as "BASIC". In our case
it's one database in that exadata half rack box and it has a mixed workload
running in it. We suffered mainly when , a large smart scan was running ,
scanning thousands of partitions and thus doing lot of large reads, and at
same time we have OLTP workload came to picture and they were started
slowing down(mostly because they were getting served from hard disk
rather flash as flash was occupied by the large reads smart scan query).
And, I read in a few blogs and also our DBA's suggesting AUTO will only
work or manage the IO in case multiple databases are hosted on the
exadata machine.
So my doubt was, will AUTO objective help us here giving priorities to
low latency queries in the intra database workload? In other words, How
"AUTO" objective decides which workload it will give priorities to?
Or
Should we dynamically switch between two different objective
"Low latency" VS "BASIC" each day. And is it possible to do online through
automated script ,without impacting anything else?
On Fri, Nov 20, 2020 at 1:53 AM Shane Borden <sborden76@xxxxxxxxx>
wrote:
I use AUTO all the time and it is my go to IORM plan if I want to
manage I/O and allow the system to try to do it first. If that doesn’t
produce the desired result, then I will start to make my own plans, but it
becomes very difficult to get it right.
---
Thanks,
Shane Borden
sborden76@xxxxxxxxx
407-963-4883
On Nov 19, 2020, at 2:58 PM, Lok P <loknath.73@xxxxxxxxx> wrote:
It looks to me as if DB resource managers are purely made for CPU
management, and with that respect , I doubt it will be effective in
managing the storage cell I/O, and that is why IORM(IO resource manager)
has come into picture. But sadly it's not documented properly. And in most
of the cases it says it manages inter database workload only. But we are
facing issues with intra database workload management. I got below blog
stating how AUTO objectives for IORM fixed the issue of IO management. So
has anybody used AUTO objective for managing INTRA databases workload?
https://weidongzhou.wordpress.com/2013/08/23/awr-is-not-enough-to-track-down-io-problem-on-exadata/
On Thu, Nov 19, 2020 at 2:29 AM Lok P <loknath.73@xxxxxxxxx> wrote:
We have one database with version 11.2.0.4 Exadata-X5 machine. We have
IORM objective kept as BASIC(which is default) and it's only one database
in that half RACK(~7 cell server) exadata cluster. We encountered
performance issue twice , where a ETL query doing FULL table/partition
scan
repetitively for longer duration, causing the flash cache to be flooded
with large reads and the other low latency work load or small reads/index
reads getting suffered because of that, and so we had to kill the ETL
query
to let the low latency workload perform in its normal speed.
I saw in a few blogs stating the IORM should be kept as AUTO and is
recommended. Wanted to understand from experts, if AUTO IORM setup is
going
to help in such a situation, where we have only one database residing in
the exadata machine having multiple types of workloads(both OLTP and BATCH
types at same time) running in the same database? I am not seeing a clear
difference , how different is IORM objective AUTO from the BASIC incase of
single database IORM management?
