Check the v$sesstat metrics, there's nowadays plenty of feedback from
storage cells telling how "deep" the offload processing actually was or if
there was pushback/passthrough caused
Common causes for smart scan "hiccups" with single block reads:
1. chained rows
2. migrated rows for some operations (update, maybe select for update
etc)
3. uncommitted transactions in blocks (or not trivially cleanable blocks
in storage)
But v$sesstat metrics will tell you more.
Here's an article from 12 years ago, where chained rows caused problems for
a smart scan.
-
http://tech.e2sn.com/oracle/exadata/performance-troubleshooting/exadata-smart-scan-performance
Things have somewhat improved by now, but back then the "cell chained
rows%" metrics didn't even exist, had to reason by looking at other
metrics, like the "table fetch continued row" and the difference of
"processing depth" at cache & txn layer vs data layer in the storage cells:
869, DWH , STAT, table fetch continued row ,
3660, 732,
869, DWH , STAT, cell blocks processed by cache layer ,
5428, 1.09k,
869, DWH , STAT, cell blocks processed by txn layer ,
5428, 1.09k,
869, DWH , STAT, cell blocks processed by data layer ,
3625, 725,
--
Tanel Poder
https://learn.tanelpoder.com
On Tue, Jun 14, 2022 at 6:26 PM Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi all
I have a strange situation where a very simple query (no join, a single
FROM table) sometimes is fast (seconds) and sometimes slow (hours). After
digging a bit It seems that one of PQ slave instead of accessing the table
using cell smart table scan is accessing by cell single blocks.
This is 19.10, has anyone observed or faced such a problem?
Thanks
