Performance issue on query doing smart scan
Date: Fri, 22 Jan 2021 23:45:15 +0530
Message-ID: <CAKna9VYfit64QskJCDGhUnK=S_hydbbrPy3-E9yJMWrOR87=pg_at_mail.gmail.com>
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
Regards
Lok
--http://www.freelists.org/webpage/oracle-l Received on Fri Jan 22 2021 - 19:15:15 CET