Hi all,
Ghassan has done a great job explaining the Database In-Memory and cell memory
on Exadata. One caution, setting inmemory_force=cellmemory will require a
license for Database In-Memory (see the Database Reference and License Guide
for details). Also, I think part of the confusion with columnar formats in
Exadata Flash is that there are two distinct types, one tied to Database
In-Memory and one not. You can review this blog
post<https://blogs.oracle.com/in-memory/post/database-in-memory-columnar-format-in-exadata-flash>
as a starting point in explaining how this works.
Regards,
Andy
On Sep 22, 2021, at 11:34 AM, Pap
<oracle.developer35@xxxxxxxxx<mailto:oracle.developer35@xxxxxxxxx>> wrote:
Thanks a lot.
So if I get it correct , without spending much time on finding object/segment
to move to inmemory. If we just start by setting inmemory_size as 0 (which we
already have) and additionally setting inmemory_force=cellmemory should be
enough to get started and see the effect of columnar data store in flash. And
oracle will automatically put the eligible ones in flash memory and maintain
them accordingly as per inbuild algorithm. Thank You.
Regards
Pap
On Wed, Sep 22, 2021 at 9:20 PM Ghassan Salem
<salem.ghassan@xxxxxxxxx<mailto:salem.ghassan@xxxxxxxxx>> wrote:
cellmemory does not need the table to be inmemory. It is automatic, but can be
enforced/inhibited by using the cellmemory attribute for a segment.
the inmemory area is not in the buffer cache, but a separate area in the sga.
cellmemory is managed by the storage software, and there are limits on the
space it takes in the flash, so that it does not affect other types of usage
(e.g. oltp operations,....)
Also, the format of cellmemory is different from that of columnar flashcache,
the later uses the HCC format, while cellmemory uses the inmemory format, and
also enables usage of the same SIMD and other algorithms implemented in the
inmemory option on db nodes.
I don't think that there's an easy way to see what tables need to go there.
Best way is to enable it and see the results. If you ask your Sales Rep for
authorization to do so, say for a month, he'll probably accept. If you have an
ExaCS or ExaCC, then it is inclmuded and you can try it.
On Wed, Sep 22, 2021 at 4:19 PM Pap
<oracle.developer35@xxxxxxxxx<mailto:oracle.developer35@xxxxxxxxx>> wrote:
Great Learning.
Lok, So i think what Ghassan is trying to point out that, previously(Pre 18.1
cell server version) HCC enabled tables/partitions can only be stored as
columnar format in flash cache , but in recent versions even non HCC tables can
be stored in columnar format in flash cache but it has to be inmemory option
enabled (i.e. Create table/alter table TAB1 inmemory.. etc). And the question
on why you don't see statistics ' 'cell physical IO bytes saved by columnar
cache' even with HCC enabled table/partitions, i believe the DB version has to
be at least 12.1 for this to work, but yours is 11.2.0.4.
Ghassan, I had some related questions , with regards to normal buffer cache if
we use inmemory feature and enable that at table level (using alter table ..
inmemory..) that will try to store the data in columnar format in a separate
memory area(of size as defined by inmemory_size parameter) within buffer cache
itself. As you mentioned, just keeping INMEMORY_FORCE=CELLMEMORY_LEVEL, and
leaving inmemory_size =0 will be enough for objects to be stored in cellmemory
in columnar format. So in this case , 1)Isn't it required at all to define the
table/partiton as inmemory in the first place?
2)Is there a risk if we define a big table (say ~30TB+) in size as in memory
enabled(inmemory_size =0) , will that be going to create issues/saturate flash
or will it be handled automatically by Oracle exadata software? Basically how
safe is it to turn on the inmemory feature and play with different tables to
pin in/out as inmemory?
3) As Lothar pointed out, how can we query dba_hist_active_sess_history for a
sql_ids to see nd interpret if inmemory or columnar format is really going to
benefit us by reducing IO usage?
Regards
Pap
On Wed, Sep 22, 2021 at 5:32 PM Ghassan Salem
<salem.ghassan@xxxxxxxxx<mailto:salem.ghassan@xxxxxxxxx>> wrote:
Lok,
As I said, columnar flashcache requires tables to be HCC compressed. As you're
using version 19 of the storage software, that should be ok. For the inmemory
on flashcache (cellmemory), you'll need inmemory option, plus 19c DB.Staring
19.8, you can set INMEMORY_FORCE=CELLMEMORY_LEVEL, and leave inmemory_size =0.
And yes, inmemory_size has no relation to the amount of flashcache that can be
used by this option.
The first version of this required HCC compressed tables, this was removed in
more recent versions.
On Wed, Sep 22, 2021 at 12:33 PM Lok P
<loknath.73@xxxxxxxxx<mailto:loknath.73@xxxxxxxxx>> wrote:
When you said, "The first versions of this required HCC tables, but the latest
ones do not." Do you mean the HCC table is the minimum requirement for columnar
flash cache storage in absence of a inmemory option. But with the in memory
option, it really doesn't matter whether it's HCC enabled or not. It can go for
columnar flash cache storage even HCC not enabled in this case. And the
inmemory size parameter doesn't have any relation with the amount of flash
cache memory used for columnar storage by oracle. Correct me if I'm wrong.
On Wed, Sep 22, 2021 at 2:12 PM Ghassan Salem
<salem.ghassan@xxxxxxxxx<mailto:salem.ghassan@xxxxxxxxx>> 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@xxxxxxxxx<mailto:loknath.73@xxxxxxxxx>> 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 @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[@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[@type="columnarcache"]' passing
xmltype(STATISTICS_VALUE));
On Wed, Sep 22, 2021 at 1:22 PM Ghassan Salem
<salem.ghassan@xxxxxxxxx<mailto:salem.ghassan@xxxxxxxxx>> 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@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>> 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).
