Re: Question on IO consideration

From: Lok P <loknath.73_at_gmail.com>
Date: Wed, 22 Sep 2021 15:50:31 +0530
Message-ID: <CAKna9VZ4LF2AJ64dbsxzQpTpHH1Qiij6H-8UQb0FUp+M4+6+mw_at_mail.gmail.com>



Thank You so much Ghassan.

When tanel mentioned '*Exadata storage cells (starting from cellsrv v12.1.2.1.0 / Jan 2015) can use fully columnar flash cache for greatly speeding up reads (the cache is fully columnar, not hybrid like the datafile storage).' *My understanding was that this option of 'columnar flash cache' is default. But it seems that's not correct and as you said it atlast needs HCC to be in place first for a table. And ofcourse adding HCC to a table , will have some impact to DML/Inserts also so we need to carefully plan that may impact application. Correct me if I'm wrong.

And as you mentioned *"You'll see columnar flashcache if you have tables that use HCC. And HCC is not automatic, you have to tell the DB to do it". *Say we have a database with version 11.2.0.4 and have many big partition tables having historical partitions compressed as 'query high' means we are using HCC and its having cell server version 19.2+ (Its X8 machine) . So in this case should not we expect to see the 'cell physical IO bytes saved by columnar cache' in the AWR? Why are we not seeing it?

Regarding the other option as you mentioned the 'in memory flash cache' is closely related to the database in memory option( available for DB version 12.1+) to be opted which is extra licensed and needs inmemory_size parameter to be non zero. Is it okay to just see the behavior by setting this 'inmemory_size' to some non zero value on one of 12.1+ version DB(of course considering we have the required licenses in place) or it can have negative impact and thus will it need significant planning and other stuff?.

Regards
Lok

On Wed, Sep 22, 2021 at 2:12 PM Ghassan Salem <salem.ghassan_at_gmail.com> wrote:

> Lok,
> You'll see columnar flashcache if you have tables that use HCC. And HCC is
> not automatic, you have to tell the DB to do it.
>
> As for the in-memory flashcache, it requires that you have the in-memory
> option, and that you just set inmemory_size to an acceptable value
> (depending on your sga...). This will let the storage software use the
> inmemory format and libraries but using the flashcache instead of the db
> node ram. There's no need for you to modify the tables to tell it to do
> that. The first versions of this required HCC tables, but the latest ones
> do not.
>
> So these features depend on the db version, as well as storage software
> version.
>
>
> On Wed, Sep 22, 2021 at 10:19 AM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Thank You All. I was trying to query a few databases. And the below query
>> gives Zero for most. But I checked all of them having the cell server
>> version is 19.2+. Few of the databases are in 11.2 though. We do have big
>> partition tables with compressed historical partitions as 'query high'. So
>> I wanted to understand, as we are talking mainly three separate things
>>
>> 1)Database in memory option which needs additional license and also it
>> will need inmemory_size parameter to be set as nonzero (which we don't have
>> set currently). And required a minimum 12.1+ Db version and is independent
>> of the cell server version. And I think it will also need specific changes
>> to the table/column definition to make it in memory. Correct me if I'm
>> wrong.
>>
>> 2)Columnar flash cache store format, which I believe is default for all
>> 12.1+ cell server versions irrespective of database version. And oracle
>> will do it automatically based on OLTP vs warehousing type load. So why am
>> I seeing the results out of the below query as zero in some of our
>> databases? And also 'cell physical IO bytes saved by columnar cache' as
>> zero. Is there any other parameter that needs to be tweaked to use this
>> feature?
>>
>> 3)And _at_Ghassan , as you mentioned just now, is 'in memory flash cache' .
>> Is it different from the above two and is dependent on specific DB and cell
>> server version or parameter change?
>>
>> Apology if I am mixing up multiple things and interpreting them wrong.
>>
>>
>> select
>> xmlcast(xmlquery('/cell_stats/stats/stats/stat[_at_name="columnar_cache_size"]'
>> passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) )
>> "COLUMNAR CACHE SIZE"
>> from v$cell_state
>> where xmlexists('/cell_stats/stats/stats[_at_type="columnarcache"]' passing
>> xmltype(STATISTICS_VALUE));
>>
>> On Wed, Sep 22, 2021 at 1:22 PM Ghassan Salem <salem.ghassan_at_gmail.com>
>> wrote:
>>
>>> Mladen,
>>> The feature Tanel is mentioning is free, not to be confused with
>>> in-memory flashcache, which requires the inmemory option.
>>>
>>> Regards
>>>
>>> On Wed, Sep 22, 2021 at 1:59 AM Mladen Gogala <gogala.mladen_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> On 9/21/21 17:41, Tanel Poder wrote:
>>>> > Exadata storage cells (starting from cellsrv v12.1.2.1.0 / Jan 2015)
>>>> > can use fully columnar flash cache for greatly speeding up reads (the
>>>> > cache is fully columnar, not hybrid like the datafile storage).
>>>>
>>>>
>>>> And all they want in exchange for that capability is your first-born.
>>>> Bargain price, indeed. BTW, MariaDB can also do that, a bit cheaper.
>>>>
>>>> --
>>>> Mladen Gogala
>>>> Database Consultant
>>>> Tel: (347) 321-1217
>>>> https://dbwhisperer.wordpress.com
>>>>
>>>> --
>>>> http://www.freelists.org/webpage/oracle-l
>>>>
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 22 2021 - 12:20:31 CEST

Original text of this message