Re: Performance issue on query doing smart scan

I used the below query to fetch the session statistics(which i had attached in the earlier post) for the query, hope it's the correct way.

select n.name, s.value

 from gv$statname n , gv$sesstat s

 where s.sid = <SID>

  and n.statistic# = s.statistic#

  and s.inst_id= n.inst_id

  and s.statistic#= n.statistic#

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

> Thank You Jonathan.
>
> Its bit difficult to run exact query manually as it involves global temp
> table but I was able to capture the session stats when the query was
> running from java side in real time and perhaps that session was already
> endup executing multiple execution of same query, but i captured both
> session stats and the sql monitor when it was doing one execution and till
> that point it endup scanning ~169 partitions. Attached are the sql monitor
> and the session stats and it does show quite a lot of activity logged
> against EHCC compression/decompression. I am not able to find the exact CPU
> seconds associated with that activity though. Not sure how to conclude if
> we are endup doing double decompression but is there any way to get rid of
> that additional work?
>
> Regarding the index , as we are really avoiding any global index because
> of maintenance issues and we have to scan all the partitions data, so are
> you pointing towards trying a composite local index on ANUM, DID ?
>
> I think you are correct Jonathan, yet to see any latest Doc but , I see in
> page 16 and 17 of the below article by Tanel poder, it states that, for the
> cell server 11.2.3.3.0+ versions , even cell_flash_cache default objects
> can be smart scanned from flash cache. And it appears that this is
> irrespective of whether it's compressed or uncompressed. And I think we
> must be saving some time/resources due to that and changing that to NONE at
> the partition level is going to add some more time to the query execution
> time.
>
> https://www.eiseverywhere.com/docs/1757/132265
>
>
> Regards
> Lok
>
> On Sat, Jan 23, 2021 at 6:53 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Looking at the SQL Monitor there are 4 key details to consider.
>>
>> a) Almost all the time is spent in the table scan (181 partitions
>> apparently - Execs columns). 500 seconds smart scan, 400 second CPU.
>> That's a lot of CPU and it would be nice to know if it was used in applying
>> predicates or in decompressing storage units
>>
>> b) You got 95% cell offload for the tablescan (but, strangely, 99.67% in
>> the summary) Either number suggests that predicates pushed to storage may
>> have been quite effective. The contradiction in the two percentages (and
>> the 450GB vs. 6TB for read bytes) - makes me wonder whether you've been
>> caught in the "double-decompression trap some of the time, meaning the
>> decompressed data to be sent back to the server it larger than the 1MB
>> limit so the CU is decompressed at the cell, then sent compressed anyway
>> and decompressed again at the datbase. The session stats would give you a
>> clue about that.
>>
>> c) (Estim)rows on the table is 18 Billion, (Actual) rows is 5 Million -
>> which suggests the Bloom filter was quite effective
>>
>> d) (Most important of the 4) - you join 34 rows to 5M rows and produce
>> 415 rows -- (Actual) column.
>>
>>
>> That last observation means that if the inputs and data sizes for this
>> query are typical then a nested loop join using a perfect index into the
>> partitioned table might have to decompress 415 (query high) to find the
>> 415 relevant rows - which would be a tiny amount of I/O and CPU compared to
>> the current load . It's not quite that nice, though, because with the best
>> local index it looks as if you'd have to probe all 181 partitions of the
>> index for each of the 34 driving rows (and you might decide that you don't
>> want to create the index).
>>
>> 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>
>> <#m_-1919722941050030159_m_-296521073788472678_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>
>> On Fri, 22 Jan 2021 at 18:53, 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