Re: Direct read stopped suddenly

Hi Pap,

ntcache is a number of blocks of that object in the buffer cache. Since there are almost all blocks in the cache, oracle decides to do not use direct reads.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

вт, 24 мая 2022 г., 22:29 Pap <oracle.developer35_at_gmail.com>:

> Hello Listers, We have one legacy system on version 11.2.0.4 and it is
> planned to be migrated to 19C. It's a four node database. We have a select
> query suddenly stopped doing smartscan from one node and the response time
> goes from <1 seconds to >5seconds. And the surprising thing is , when we
> manually trigger the sql , it's doing a smart scan when triggered from
> other nodes but from one node only it's not doing that and the response
> time going high for that specific node. It seems that one query showing
> this behaviour and other running fine.Ideally my understanding was that it
> should not vary based on DB node as because the storage nodes are common
> across all the four DB nodes. So I'm wondering , why is it happening?
>
> Collected the 10358 trace and below is the trace. It does show a
> difference in the value of "ntcache" and "ntdist" for both the nodes and
> that is possibly driving the direct read/smartscan decision. So I wanted to
> understand what is that and why that value is different in both DB nodes
> and how to fix this odd behaviour?
>
> query:-
> SELECT c1, c8, c2, c3, c4, c5, c6, c7 from TAB1 where c1 = :1
>
> Plan hash value: 2108848690
>
> --------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> | Cost (%CPU)| Time |
>
> --------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | |
> | 1583 (100)| |
> | 1 | TABLE ACCESS STORAGE FULL| TAB1 | 9 | 711
> | 1583 (1)| 00:00:19 |
>
> --------------------------------------------------------------------------------------------------------
> Query Block Name / Object Alias (identified by operation id):
> -------------------------------------------------------------
> 1 - SEL$1 / TAB1_at_SEL$1
>
>
> Trace from DB node which is doing smartscan:-
>
> NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from
> caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
> NSMTIO: kcbdpc:DirectRead: tsn: 29, objd: 1607047, objn: 1607047
> ckpt: 1, nblks: 757301, ntcache: 10, ntdist:30
> Direct Path for tsn 29 objd 1607047 objn 1607047
> Direct Path 1 ckpt 1, nblks 757301 ntcache 10 ntdist 30
> Direct Path mndb 0 tdiob 12 txiob 0 tciob 66
> Direct path diomrc 128 dios 2
> NSMTIO: Additional Info: VLOT=65089150
> Object# = 1607047, Object_Size = 757301 blocks
> SqlId = 64d0vkmtjgq2x, plan_hash_value = 2108848690, Partition# = 0
> KCBO: prepared ksr msg for cid=131228, tsn=29, obj=1607047, 0
>
>
> Trace from DB node which is not doing smartscan:-
>
> NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from
> caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
> NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: tsn: 29, objd: 1607047, objn:
> 1607047
> ckpt: 0, nblks: 757301, ntcache: 756116, ntdist:1185
> Direct Path for tsn 29 objd 1607047 objn 1607047
> Direct Path 0 ckpt 0, nblks 757301 ntcache 756116 ntdist 1185
> Direct Path mndb 18 tdiob 10 txiob 0 tciob 70
> Direct path diomrc 128 dios 2
> NSMTIO: Additional Info: VLOT=65089150
> Object# = 1607047, Object_Size = 757301 blocks
> SqlId = 64d0vkmtjgq2x, plan_hash_value = 2108848690, Partition# = 0
>

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