Re: IORM Setup for Oracle Exadata

Thank You.

Yes, we don't have exact volume/configuration on lower , but i think we will be able to reproduce the scenario by doing concurrent cell smart scan from multiple sessions and see the impact on index reads with default objective "BASIC" vs "AUTO" without any other modification to DB resource manager etc. Will try to replicate/test it on dev. Thank you so much.

On Fri, Nov 20, 2020 at 6:24 PM Shane Borden <sborden76_at_yahoo.com> wrote:

> In my opinion, you are overthinking this for the first change. All of the
> things you mention would be evaluated after you initially change the IORM
> plan to auto. If within AUTO, you need to set limits or tweak based on
> cell metrics, you would do individual “dbplans”.
>
> Try to keep things as simple as possible for your first iteration.
> Something like this might work:
>
> dcli -g ~/cell_grp -l root cellcli -e "list iormplan detail”
> dcli -g ~/cell_grp -l root cellcli -e "ALTER IORMPLAN OBJECTIVE = AUTO”
> dcli -g ~/cell_grp -l root cellcli -e "list iormplan detail”
>
> On MOS, Oracle has a great script to confirm how things are working after
> you change the IORM plan called “metric_iorm.pl”. I forget which note
> its attached to, but you can certainly search for it.
>
> Of course you should do this in your test environment first before moving
> it to any other environment, because your experience may not match mine.
> Hopefully that goes without saying.
>
>
> ---
>
> Thanks,
>
>
> Shane Borden
> sborden76_at_yahoo.com
>
> On Nov 20, 2020, at 5:04 AM, Lok P <loknath.73_at_gmail.com> wrote:
>
>
> Additionally I see, In the book "Expert Oracle Exadata" , I see below
> definitions for the IORM objective. For "AUTO", its saying, "*If 75% or
> more of the I/O operations from a consumer group are small I/O (less than
> 128K), it is considered to be a latency-oriented consumer group and is
> managed accordingly.*" , So , in the first method i.e. just with the
> Alter command ("alter iormplan objective=auto") without defining "consumer
> group" , will the algorithm work in the same way or will it have to have
> "consumer group" defined for "AUTO" to work?
>
> *IORM Objective:-*
>
> *low_latency: *This setting provides optimization for applications that
> are extremely sensitive to I/O latency. It provides the lowest possible
> I/O latencies by significantly limiting disk utilization. In other words,
> throughput-hungry applications will be significantly (negatively)
> impacted by this optimization objective.
>
> *high_throughput:* This setting provides the best possible throughput for
> DWtransactions by attempting to fully utilize the I/O capacity of the
> storage cells. It is the opposite of low_latency and, as such, it will
> significantly (negatively) impactdisk I/O latency.
>
> *Balanced:* This setting attempts to strike a balance between low latency
> and high throughput. This is done by limiting disk utilization for large
> I/O operations to a lesser degree than the low_latency objective
> described above. Use this objective when workloads are mixed and you have
> no applications that require extremely low latency.
>
> *Auto:* This setting allows IORM to determine the best optimization
> objective for your workload. Cellsrv continuously monitors the
> large/small I/O requests and applies the optimization method on a
> best-fit basis. If 75% or more of the I/O operations from a consumer
> group are small I/O (less than 128K), it is considered to be a
> latency-oriented consumer group and is managed accordingly.
>
> *Basic: *This setting is the default after installation. This objective
> means the storage server does limited optimization for low latency, but
> does not throttle, which the other objectives do.
>
>
>
> On Fri, Nov 20, 2020 at 1:35 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>>
>> 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_at_gmail.com> 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_at_yahoo.com> 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_at_yahoo.com
>>>> 407-963-4883
>>>>
>>>> On Nov 19, 2020, at 3:53 PM, Lok P <loknath.73_at_gmail.com> 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_at_yahoo.com> 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_at_yahoo.com
>>>>> 407-963-4883
>>>>>
>>>>> On Nov 19, 2020, at 3:34 PM, Lok P <loknath.73_at_gmail.com> 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_at_yahoo.com>
>>>>> 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_at_yahoo.com
>>>>>> 407-963-4883
>>>>>>
>>>>>> On Nov 19, 2020, at 2:58 PM, Lok P <loknath.73_at_gmail.com> 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_at_gmail.com> 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?
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>

--
http://www.freelists.org/webpage/oracle-l