Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 26BA91002D8A46 for ; Wed, 22 Sep 2021 12:33:44 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 6724742534; Wed, 22 Sep 2021 10:33:42 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 51AF040737; Wed, 22 Sep 2021 10:33:42 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1632306822; bh=XR4qxSVaopq16IPy1jQkyr0CQ+tSqEbd16aEAtbzQ4M=; h=From:Sender:Sender:From; b=Fo4AidPPUmnlJvpyImx01JOBK/7KhJb4fAo5nGkuC0hIwhiA409JW3ukfU11c8dfC KxObcLVieeAHHeY7EIpWnJRFvOKmCF4QNf1w+vaM5GOOU4K7T48OhPvRcT8TERgS8T wNqNSZwuBa2ofiKfUORJXo9RDIKSVsSRAB21+ATo= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id q0LEc3WKzSue; Wed, 22 Sep 2021 10:33:42 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 7A7574073E; Wed, 22 Sep 2021 10:33:39 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1632306820; bh=XR4qxSVaopq16IPy1jQkyr0CQ+tSqEbd16aEAtbzQ4M=; h=From:Sender:Sender:From; b=Ea3hCMD43IDaIT1kgqX240H9EszOzUggQLS70JGcs7SeqZ99zK5p/ZiSoPGpbQ8Pl 1wRS/hQq8I7HXx2t9dQpvQg8gLPeHabElqetbgzI8z3RDbdMcDAQEf4Hf8J7NkUeE3 GbPbOScXelqc5OPsai7h/YTAxH3uOJ2c3Wa2m8yI= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 22 Sep 2021 10:33:38 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E615240737 for ; Wed, 22 Sep 2021 10:33:37 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=mwHtscay; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0kAXVdS7MBhO for ; Wed, 22 Sep 2021 10:33:37 +0000 (UTC) Received: from mail-qk1-f173.google.com (mail-qk1-f173.google.com [209.85.222.173]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id D987540706 for ; Wed, 22 Sep 2021 10:33:37 +0000 (UTC) Received: by mail-qk1-f173.google.com with SMTP id b65so7579920qkc.13 for ; Wed, 22 Sep 2021 03:33:37 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=6ywfW35hRKnMYR6/ZTUDXBudKCg1lvkq7/+juvClx3c=; b=enrOD3HsubBVQYwDPhsELBOSF2IiovwaLp04NSTYzoFjTQhq6h8GuCVjY28CWA2AIf MhoPjiEeh5WRJYe1T5uwoHD1yBt4sJpDCZ1Ep2M85P9ibJF6wGeYaLX6W+jnTNN2HNqB ThirURRq00CY4eRRYMwntx9Wxafo8hnMZz4kALsOTrTDHC1sU480TBA8AGDBX+vLM2um e8dN39eZqokswVv328OQCftSFd4t9H2ezJLrFdDOSEBxZFPvTRXcMX4oPg/aGdw1oxp/ lBiaYR1lmySWeUMUpxyt3IgDddq7I2dlVAMijGcCKcm/IRpYdGxEPlkWj5+8NGt7dyFd Qi7Q== X-Gm-Message-State: AOAM532GPLNZULucSfwuSCTUnq9UvWRXvR9DvOsPhk2QdPweOlSavp0p XAIh9LyqRzTgpb9i2jkmwcHNgPtJi5K+F99YlfPggPyMm8RULA== X-Google-Smtp-Source: ABdhPJxHecCQTP2jgnx/jnnnHOWw221ueFqR2N8qtgsWya89QuNQGlM59dtoYuXaGn0yhBGG9MLDNLgQLCJr34lMWqk= X-Received: by 2002:a25:ea51:: with SMTP id o17mr41677333ybe.192.1632306817357; Wed, 22 Sep 2021 03:33:37 -0700 (PDT) MIME-Version: 1.0 References: <89961f65-7365-75cb-f682-9060e055dd34@gmail.com> In-Reply-To: From: Lok P Date: Wed, 22 Sep 2021 16:03:24 +0530 Message-ID: Subject: Re: Question on IO consideration To: Ghassan Salem Cc: Mladen Gogala , oracle-l Content-Type: multipart/alternative; boundary="0000000000000359da05cc930cfb" X-archive-position: 80971 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: loknath.73@gmail.com Precedence: normal Reply-To: loknath.73@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000000359da05cc930cfb Content-Type: text/plain; charset="UTF-8" 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 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 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 >> 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 >>> 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 >>>> >>>> >>>> --0000000000000359da05cc930cfb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
When you said, "The first versions of this require= d HCC tables, but the latest ones do not." Do you mean the HCC tab= le is the minimum requirement for columnar flash cache storage in absence o= f a inmemory=C2=A0option. But with the in memory=C2=A0option, it really doe= sn't=C2=A0matter whether it's HCC enabled or not. It can go for col= umnar flash cache storage even HCC not enabled in this case. And the inmemo= ry=C2=A0size parameter doesn't=C2=A0have any relation with the amount o= f flash cache memory used for columnar=C2=A0storage by oracle. Correct me i= f=C2=A0 I'm wrong.


On Wed, Sep 22, 2021 at 2:12 PM Gh= assan Salem <salem.ghassan@gm= ail.com> wrote:
Lok,
You'll see columnar flashca= che 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 flashca= che, 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 th= e flashcache instead of the db node ram. There's no need for you to mod= ify the tables to tell it to do that. The first versions of this required H= CC tables, but the latest ones do not.

So these fe= atures depend on the db version, as well as storage software version.
=


On Wed, Sep 22, 2021 at 10:19 AM Lok P <loknath.73@gmail.com> wrot= e:
Thank You All. I was trying to query a few databases. And the below que= ry gives Zero for=C2=A0most. But I checked all of them having=C2=A0the cell= server version is 19.2+. Few of the databases are in 11.2 though. We do ha= ve big partition tables with compressed historical partitions as 'query= high'. So I wanted to understand, as we are talking mainly three separ= ate things=C2=A0

1)Database in memory option which needs addit= ional license=C2=A0and 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)Colum= nar 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 au= tomatically based on OLTP vs warehousing type load. So why am I seeing the = results=C2=A0out of the below query as zero in some of our databases? And a= lso 'cell physical IO bytes saved by columnar cache' as zero. Is th= ere any other parameter that needs to be tweaked to use this feature?=C2=A0=

3)And @Ghassan , as you mentioned just now, is &#= 39;in memory=C2=A0flash cache' . Is it different from the above two and= is dependent=C2=A0on specific DB and cell server version=C2=A0or parameter= change?

Apology=C2=A0if I am mixing up multiple t= hings and interpreting them wrong.


select xmlcast(xmlquery('/cell_stats/stats/s= tats/stat[@name=3D"columnar_cache_size"]' passing xmltype(STA= TISTICS_VALUE) returning content) as varchar2(200) ) "COLUMNAR CACHE S= IZE"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@typ= e=3D"columnarcache"]' passing xmltype(STATISTICS_VALUE));=C2=A0

On Wed, Sep 22, 2021 at 1:22 PM Ghassan S= alem <salem= .ghassan@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@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) <= br> > 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


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