Re: Performance issue on query doing smart scan

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