Re: Query running slow because cell offload discrepancy

Can someone guide here, how to proceed to get the root cause of this issue?

On Mon, 17 Jun, 2024, 9:53 am Pap, <oracle.developer35_at_gmail.com> wrote:

> Hi All,
> This is version 19.21 of oracle Exadata. It's a two node RAC database. We
> are suddenly seeing the same query which is doing cell smartscan in one
> node(Node-1) but is not doing on another(node-2). The node in which it does
> cell smart scan finishes in milliseconds vs in another one it takes 20
> seconds.
>
> By running the query i checked the v$sessstats and get the difference in
> figures as below when the query runs on node-1 vs on node-2 respectively:-
>
> cell physical IO bytes eligible for predicate offload- 16523788288 on
> Node-1 vs 281649152 on node-2
> cell physical IO bytes saved by storage index - 16045342720 on Node-1
> vs no data on node-2
> physical read bytes- 16523788288 on Node-1 vs 281673728 on node-2
> logical read bytes from cache- 29507584 on Node-1 vs 16271810560 on
> node-2
> physical read total bytes optimized- 16523788288 on Node-1 vs
> 281673728 on node-2
>
> Then I thought , it might be happening because the decision of "cell
> offloading" depends on VLOT which in turn depends on the size of the buffer
> cache. But then I noticed in v$parameter, the db_cache_size is set as zero
> in both the instances. So wondering what must be the cause for this
> difference in performance then?
>
> sga_max_size - 55GB
> sga_target - 44GB
> db_cache_size - 0
>
> Below is the details sql monitors for the queries in github link:-
>
> https://gist.github.com/databasetech0073/b0e6e44a04dff7a2250f681bce454b2e
>
>
> Global Stats
>
> =================================================================================================================================================
> | Elapsed | Cpu | IO | Application | Other | Fetch | Buffer |
> Read | Read | Uncompressed | Offload | Offload | Cell |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
> Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
>
> =================================================================================================================================================
> | 0.32 | 0.10 | 0.19 | 0.03 | 0.00 | 56 | 2M |
> 15809 | 15GB | 456MB | 15GB | 289KB | 100.00% |
>
> =================================================================================================================================================
>
> VS
>
> Global Stats
>
> =================================================================================================================================================
> | Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer |
> Read | Read | Uncompressed | Offload | Offload | Cell |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
> Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
>
> =================================================================================================================================================
> | 21 | 9.49 | 11 | 0.02 | 0.00 | 2 | 2M |
> 37561 | 598MB | 269MB | 269MB | 211KB | 99.92% |
>
> =================================================================================================================================================
>
> Regards
> Pap
>

--
http://www.freelists.org/webpage/oracle-l