On a client DB, after migrating it to OL8, the application started to complaint the DB seems slower. Checking the OEM, there is not much done by the application, but I see a good blue stripe from W00x processes doing sequential reads. Nonstop. This is a 19.31 single-instance database.<\/p>\n\n\n\n
After tracing these sessions, they are reading two distinct LOB segments, each between 500 and 700GB, where a lot of deletes happened. All the time “db file sequential read”, up to 3 or 4 W00x parallel processes.<\/p>\n\n\n\n
WAIT #0: nam='db file sequential read' ela= 199 file#=476 block#=2460662 blocks=1 obj#=53176 tim=1024371028390<\/code><\/pre>\n\n\n\nI open an SR with Oracle. They ask more traces and session short stack. The short_stack looks like:<\/p>\n\n\n\n
SQL> oradebug setospid 15297\nOracle pid: 28, Unix process pid: 15297, image: oracle@srv0nnnn.xxx.ch (W000)\nSQL> oradebug unlimit\nStatement processed.\nSQL> oradebug short_stack\n\nksedsts()+426<-ksdxfstk()+63<-ksdxcb()+872<-sspuser()+236<-__sighandler()<-semtimedop()+14<-sskgpwwait()+245<-skgpwwait()+187<-ksliwat()+2296<-kslwaitctx()+188<-ktsj_slave_main()+944<-ksvrdp_int()+1906<-opirip()+579<-opidrv()+581<-sou2o()+165<-opimai_real()+173<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46<\/code><\/pre>\n\n\n\nNo underscore parameters are set and changing “_enable_space_preallocation” to 0 has no immediate effect.<\/p>\n\n\n\n
The tablespace is composed of several smallfiles, EXTENT_MANAGEMENT=LOCAL, ALLOCATION_TYPE=SYSTEM, SEGMENT_SPACE_MANAGEMENT=AUTO. All standard.<\/p>\n\n\n\n
The LOB segment is securefile, configured in-row, with several larger values which go out of row. <\/p>\n\n\n\n
I ask several times to Oracle: what triggers the KTSJ Slaves processes to fully read huge LOB segments? I do not get a satisfactory answer.<\/p>\n\n\n\n
We do not have this problem with all other LOB segments of the DB. The workaround at the end was to defragment the LOBs, moving them to a new tablespace (which we wanted to do anyway). The LOB segment reduced from >500GB to 75G and the Space Manager processes disappeared completely. This lead to the next problem (next post).<\/p>\n\n\n\n
I leave this post here just in case someone encounters the same.<\/p>\n","protected":false},"excerpt":{"rendered":"
On a client DB, after migrating it to OL8, the application started to complaint the DB seems slower. Checking the OEM, there is not much done by the application, but I see a good blue stripe from W00x processes doing sequential reads. Nonstop. This is a 19.31 single-instance database. After tracing these sessions, they are […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,6,39],"tags":[],"class_list":["post-1170","post","type-post","status-publish","format-standard","category-bug","category-oracle","category-oracle-19c","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=1170"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1170\/revisions"}],"predecessor-version":[{"id":1171,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1170\/revisions\/1171"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=1170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=1170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=1170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}