Re: Performance issue on query doing smart scan

Two problems with that query.
a) You've joined the gv$ (global / RAC) views. Since you could have the same SID in use on every node you need to eliminate the redundant set(s) of results. Stick with the v$ if you're running a query on a single node, or include a predicate to identify the correct node.

b) There are about 700 statistics - and in the report you posted there were about 600 with zeros. It's unlikely that anyway will look at one of these zeros and say "that's odd, that value isn't often zero". Eliminate value=0 in the query - and if you don't report the query when you report the results do say that you've explicitly eliminated zeros to pre-empt anyone asking about "missing" data.

On the plus side, you didn't do anything silly like sorting the values in descending order by value, or ascending order alphabetically.

Regards
Jonathan Lewis

On Sat, 23 Jan 2021 at 19:03, Lok P <loknath.73_at_gmail.com> wrote:

> 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_-757165797777012933_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