Re: Parallel Query change from smart scan to cell single block access

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)

   -
   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_at_gmail.com> 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

>

>


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