Query running slow because cell offload discrepancy
Date: Mon, 17 Jun 2024 09:53:54 +0530
Message-ID: <CAEjw_fgr5uahHkNXjyDorFyzNRj-shcSAgHo=VU_W5kvPDaAbQ_at_mail.gmail.com>
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-lReceived on Mon Jun 17 2024 - 06:23:54 CEST