Re: IORM Setup for Oracle Exadata

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