Standby Database In 'Max Availability' Mode Does Not Increase SCN0

刘泽宇

现象：
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:

PROCESS 59: LGWR
----------------------------------------
SO: 0x1200008528, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x1200008528, name=process, file=ksu.h LINE:12721, pg=1
(process) Oracle pid:59, ser:1, calls cur/top: 0x11d36ee730/0x11d36ee730
OSD pid info: Unix process pid: 293835, image: xxxxxxxxxxxxx (LGWR)

SO: 0x1200548dd8, type: 4, owner: 0x1200008528, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x1200008528, name=session, file=ksu.h LINE:12729, pg=0
(session) sid: 827 ser: 1 trans: (nil), creator: 0x1200008528
service name: SYS$BACKGROUND
Current Wait Stack:
0: waiting for 'library cache lock'
handle address=0x18bfd0cb80, lock address=0x191ff3f030, 100*mode+namespace=0x1004a0003
wait_id=991 seq_num=1014 snap_id=1
wait times: snap=1.829312 sec, exc=1.829312 sec, total=1.829312 sec
wait times: max=5.500000 sec, heur=1.829312 sec
wait counts: calls=2 os=2
in_wait=1 iflags=0x15a2
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 996, ser: 5

LibraryObjectLock: Address=0x191ff3f030 Handle=0x18bfd0cb80 RequestMode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

User=0x1200548dd8 Session=0x1200548dd8 ReferenceCount=0 Flags=[0000] SavepointNum=2
LibraryHandle: Address=0x18bfd0cb80 Hash=548dac5f LockMode=S PinMode=0 LoadLockMode=0 Status=0
ObjectName: Name=SYS.xxxxxxx
FullHashValue=2499dcf083f8e14276cd0eaf548dac5f Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0

PROCESS 107:
----------------------------------------
SO: 0x120000e978, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x120000e978, name=process, file=ksu.h LINE:12721, pg=1
(process) Oracle pid:107, ser:3, calls cur/top: 0x11d36f2680/0x11d36f20c0
OSD pid info: Unix process pid: 318247, image: oracle@xxxxxxxxxxxxxxx (TNS V1-V3)
Short stack dump:
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-kgh_heap_sizes()+267<-kkoDumpHeapSize()+92<-kkoqbc()+32778<-apakkoqb()+171<-apaqbdDescendents()+452<-apaqbdList()+76<-apaqbdDescendents()+772<-apaqbdList()+76<-apaqbdDescendents()+267<-apaqbdList()+76<-apaqbdDescendents()+772<-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()+1766<-kkqfppDrvDescendents()+1123<-kkqfppDrv()+184<-kkqfppPsh()+480<-kkqfppPsh()+5020<-kkqfppDrv1()+384<-kkqfppDrvDescendents()+1206<-kkqfppDrv()+184<-kkqfppPsh()+480<-kkqfppPsh()+5020<-kkqfppDrv1()+384<-kkqfppDrvDescendents()+1206<-kkqfppDrv()+184<-kkqfppPsh()+480<-kkqfppPsh()+5020<-kkqfppDrv1()+384<-kkqfppDrvDescendents()+1206<-kkqfppDrv()+184<-kkqfppPsh()+480<-kkqfppDrv1()+384<-kkqfppDrvDescendents()+1206<-kkqfppDrv()+184<-opitca()+1163<-kksFullTypeCheck()+69<-rpiswu2()+1776<-kksLoadChild()+11468<-kxsGetRuntimeLock()+2209<-kksfbc()+15108<-kkspsc0()+1171<-kksParseCursor()+116<-opiosq0()+2019<-kpooprx()+274<-kpoal8()+842<-opiodr()+915<-ttcpip()+2183<-opitsk()+1705<-opiino()+969<-opiodr()+915<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253

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:

kgh_heap_sizes kkoDumpHeapSize kkoqbc apakkoqb apaqbdDescendents ....

or

qcuIdnEqual qksqbCheckNameConflict qksqbCreateName qksqbGenerateName kkqvmTrMrg kkqvmTrMrg ...

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;