标题: Standby Database In 'Max Availability' Mode Does Not Increase SCN0 [打印本页] 作者: 刘泽宇 时间: 2023-10-22 10:27 标题: Standby Database In 'Max Availability' Mode Does Not Increase SCN0 本帖最后由 刘泽宇 于 2023-10-22 10:30 编辑
现象：
Standby Database is in 'Max Availability' mode does not increase the SCN.
LGWR hangs waiting for 'library cache lock'.
The blocker is a user session which runs a SQL statement. The SQL statement is in parsing phase and never completes.
There are several sessions waiting for 'library cache lock'. Hanganalysis shows:
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
........
process id: xxxxxxxxxxxxx(LGWR)
........
}
is waiting for 'library cache lock' with wait info:
{
p1: 'handle address'=0x18bfd0cb80
p2: 'lock address'=0x191ff3f030
p3: '100*mode+namespace'=0x1004a0003
current sql: <none>
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2047<-kslwaitctx()+163<-ksfwaitctx()+14<-kglLockWait()+898<-kgllkal()+1166<-kglLock()+1352<-kglLockInstance()+234<-kksExclusiveParseLock()+47<-krdrsb_lgwr_chintr()+1294<-ksbcti()+513<-ksbabs()+1680<-ksbrdp()+1045<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (dbname.dbname1)
os id: 318247
process id: 107, oracle@dbadm01.zzz (TNS V1-V3)
session id: 996
session serial #: 5
}
which is not in a wait:
{
.....
current sql: ...
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-kgh_heap_sizes()+267<-kkoDumpHeapSize()+337<-kkoqbc()+32778<-apakkoqb()+171<-apaqbdDescendents()+801<-apaqbdList()+76<-apaqbdDescendents()+267<-apaqbdList()+76<-apaqbdDescendents()+772<-apaqbdList()+76<-apaqbdDescendents()+267<-apaqbdList()+76<-apaqbdDescendents()+772<-apaqbdList()+76<-apaqbdDescendents()+267<-apaqbdList()+76<-apaqbdDescendents()+267<-apaqbdList()+76<-apaqbd()+14<-kkoggvc()+628<-kkogvcd()+582<-kkqljppuvs()+2612<-kkqljppur(
}
原因：
LGWR h1angs waiting for 'library cache lock' hence it does not increase the SCN in ADG. SSD shows LGWR is willing to acquire DBINSTANCE lock in exclusive mode (RequestMode=X). The LGWR is blocked by a user session but the DBINSTANCE lock does not look to be held by the user process. From SSD:
SO: 0x1200746ff8, type: 4, owner: 0x120000e978, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x120000e978, name=session, file=ksu.h LINE:12729, pg=0
(session) sid: 996 ser: 5 trans: (nil), creator: 0x120000e978
Current Wait Stack:
Not in wait; last wait ended 7 min 22 sec ago
There are 1 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 827, ser: 1
wait event: 'library cache lock'
p1: 'handle address'=0x18bfd0cb80
p2: 'lock address'=0x191ff3f030
p3: '100*mode+namespace'=0x1004a0003
The user process can have a different stack as it depends on the parsing issue; e.g. Short stack dump:
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-qksqbCheckNameConflict()+51<-qksqbCreateName()+1459<-qksqbGenerateName()+1109<-kkqvmTrMrg()+2678<-kkqvmTrMrg()+15502<-kkqvmTrMrg()+10338<-kkqvmTrMrg()+10338<-kkqvmTrMrg()+10338<-kkqvmTrMrg()+10338<-kkqvmTrMrg()+15502<-kkqvmTrMrg()+10338<-kkqvmTrMrg()+15502
The user session executes a SQL query which is in parsing phase that never ends. The sessions 10053 trace file shows that CBO is actively working in finding an access path but never does.
The problematic query shows spinning in a few specific call stacks:
The stacks have some common sequences: kkoggvc kkogvcd kkqljppuvs kkqljppur kkqfppDrvDescendents kkqfppDrv kkqfppPsh kkqfppPsh-kkqfppDrv1
解决方法：
1. For the 'library cache lock' issue seen in LGWR process and other user processes apply patch 24385983.
2. For the never ending hard parse apply patch 18795224 and enable its fix "_fix_control"='18795224:ON'.
3. For the SQL statement the following workarounds may work:
ALTER SESSION SET "_push_join_union_view"= false; --this is the one that was used before
ALTER SESSION SET "_optimizer_filter_pushdown"=false;
ALTER SESSION SET "_with_subquery" = inline;