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.
On Wed, Sep 22, 2021 at 9:20 PM Ghassan Salem <salem.ghassan@xxxxxxxxx>
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
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> wrote:
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
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?
On Wed, Sep 22, 2021 at 5:32 PM Ghassan Salem <salem.ghassan@xxxxxxxxx>
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> 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>
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
So these features depend on the db version, as well as storage
On Wed, Sep 22, 2021 at 10:19 AM Lok P <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
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'.
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
will do it automatically based on OLTP vs warehousing type load. So why
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
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
and cell server version or parameter change?
Apology if I am mixing up multiple things and interpreting them wrong.
passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) )
"COLUMNAR CACHE SIZE"
On Wed, Sep 22, 2021 at 1:22 PM Ghassan Salem <
The feature Tanel is mentioning is free, not to be confused with
in-memory flashcache, which requires the inmemory option.
On Wed, Sep 22, 2021 at 1:59 AM Mladen Gogala <
On 9/21/21 17:41, Tanel Poder wrote:
Exadata storage cells (starting from cellsrv v188.8.131.52.0 / Jan2015)
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
Bargain price, indeed. BTW, MariaDB can also do that, a bit cheaper.
Tel: (347) 321-1217