Direct read stopped suddenly
Date: Wed, 25 May 2022 00:59:35 +0530
Message-ID: <CAEjw_fjr9MRZxDH5ZEaiMifNpr4nxsRSeUdMmuSsALZi0GkQWw_at_mail.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
Object# = 1607047, Object_Size = 757301 blocks SqlId = 64d0vkmtjgq2x, plan_hash_value = 2108848690, Partition# = 0
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 24 2022 - 21:29:35 CEST