Re: Performance issue on query doing smart scan

Do you have any references that are more up to date ? One of the two you've given is dated 2013, the other 2015 (and probably by/in association with) the same author. It's quite possible that if you're running Exadata with the 19c image that things have moved on from anything written 5 - 7 years ago.

If you're trying to work where the work is being done and can re-run the query a few times then it's a good idea to collect the session activity (v$mystat, or v$sesstat for the session) and total wait events (v$session_wait for the session). The instance activity is the more important since the SQL Monitor gives you a good statistical sample of the waits.

Regards
Jonathan Lewis

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, 22 Jan 2021 at 18:15, 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