Re: Performance issue on query doing smart scan

Run the sample query which does smartscan on a Compressed partition VS non compressed partition of TAB1 and then execute below query with input sessionid to see which one is using flash cache and which one not.

select sid, name, value from gv$sesstat ss , gv$statname st

where ss.sid = <in_sid> and ss.inst_id= st.inst_id and ss.statistic#= st.statistic#

and st.name in (

'cell flash cache read hits',

'cell overwrites in flash cache',

'cell partial writes in flash cache',

'cell writes to flash cache')

order by name;

On Sat, Jan 23, 2021 at 12:30 PM Lok P <loknath.73_at_gmail.com> wrote:

> I am guessing the compressed partitions are the ones , which is causing
> the cell smart scan on those to pass through flash cache(which should not
> happen in an ideal case), is it okay to have those ~150 compressed table
> partition storage flash_cache options changed to NONE as below? And is
> there any easy way to find/establish that out from the session stats by
> manually executing the SELECT query on those compressed partitions to see
> if it's really going through flash cache as opposed to a smart scan on
> uncompressed partitions?
>
> ALTER TABLE TAB1 MODIFY PARTITION PART_DAY_01202021 STORAGE
> (CELL_FLASH_CACHE NONE) ;
>
> Which means we still want all the index reads to go through flash cache
> only and non compressed table partitions to be treated in BAU fashion , so
> we will not change those properties, and anyway the index partitions are
> not compressed explicitly in our case here.
>
> alter index IDX1 modify partition PART_DAY_01202021 storage
> (cell_flash_cache DEFAULT);
>
> Regards
> Lok
>
>
> On Sat, Jan 23, 2021 at 12:22 AM Lok P <loknath.73_at_gmail.com> wrote:
>
>> One correction, it's actually scanning ~180 partitions out of which 150
>> are compressed for "query high". Does compression be the cause of the smart
>> scan being served from flash cache?
>>
>> Attached is the same query and its sql monitor. It does show TAB1 is
>> doing cell smart scans. Its range is partitioned on column part_dt daily
>> holding ~56billion rows. Want to know, if there is any way I can make the
>> query finish in the same time without doing a smart scan?
>>
>> Here table, TAB1 is daily range partitioned on column PART_DT.
>> Plan_line_id- 21 is where it spends most of the time and its reading a lot
>> of data over in that step. A bloom filter is applied on column AANUM and
>> DID for table TAB1 on that step. Out of which NUM_DISTICNT for column AANUM
>> is -173million and num_distinct for column DID is 333K. There exist two
>> different local composite indexes with column AANUM and DID being leading
>> in them individually. But no such index exists having AANUM and DID both
>> columns as composite keys. But again considering the size of the table and
>> its exposed to heavy DML, so not sure if it would be a good idea to create
>> a new index on (AANUM, DID) and whether that would really be beneficial?
>>
>>
>> Regards
>> Lok
>>
>> On Fri, Jan 22, 2021 at 11:45 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Hi All, I found in many articles(two of the samples is below) that the
>>> smart scan wont use the flash cache by default. But in our case, we are
>>> seeing a query doing the smart scan using flash cache heavily and making it
>>> reach flash IOPS to ~200K and response time around 15-20ms. Are there any
>>> bugs around that which we are hitting? or is there any other setting
>>> driving this or my understanding is wrong here?
>>>
>>> Actually in our case it's exadata X5 machine with image version 19.2,
>>> half RAC with ~40TB of flash cache and its hosting single database on it
>>> which is on version 11.2.0.4. One of the query which does cell smart scan
>>> on a big partitioned table(TAB1 , daily range partitioned on column
>>> PART_DT) and it scans through all the ~500+ partition(which is as per the
>>> business requirement), the flash disk utilization went up to ~80% reaching
>>> ~200K IOPS with response time of ~15-20ms. And at the same time the hard
>>> disk utilization and IOPS stays below ~40%. The flash disk is showing high
>>> large reads during that interval and so index read/small reads are getting
>>> impacted. I verified the FLASH_CACHE and DEFAULT_FLASH_CACHE values in
>>> dba_tab_partitions both are DEFAULT for this object. Want to understand why
>>> it's happening that way?
>>>
>>> https://www.informit.com/articles/article.aspx?p=2418151&seqNum=3
>>>
>>>
>>> http://guyharrison.squarespace.com/blog/2013/12/30/can-the-exadata-smart-flash-cache-slow-smart-scans.html
>>>
>>> Regards
>>>
>>> Lok
>>>
>>

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