Queries Using SQL Plan Baselines are Slow or Hang During Parse Waiting for 'row cache lock' (Doc ID 2027839.1)

Last updated on AUGUST 04, 2018

Applies to:

Symptoms

• Session waits for 'row cache lock':
  
• Active Session History (ASH) report identifies the top SQL waiting for the event:
  
• Query has a SQL Plan Baseline defined for it
• The parsing session is on CPU (Not in wait):
  
  (session) sid: 2180 ser: 25072 trans: 0x7000110e7eadad0, creator: 0x7000110c16ba308
  flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x40009) -/-/INC
  DID: 0001-0221-0000008B, short-term DID: 0000-0000-00000000
  txn branch: 0x0
  edition#: 100 user#/name: .x.x.x.x.x.x.x.x.x.
  oct: 3, prv: 0, sql: 0x700010ffee97498, psql: 0x700010fce960ac0
  stats: 0x7000110ddfdfa60, PX stats: 0x111b1974c
  ksuxds FALSE at location: 0
  service name: SYS$USERS
  client details:
  O/S info: user: xxxxxxxx, term: unknown, ospid: 1234
  machine: xx12xx12xx12 program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current Wait Stack:
  Not in wait; last wait ended 1 min 13 sec ago
  There are 148 sessions blocked by this session.
   
  The session can be identified from the SQL: (sql: 0x700010ffee97498)
  
         User=700011081a1d938 Session=700011081a1d938 ReferenceCount=1
         Flags=CNB/[0001] SavepointNum=5587618e
       LibraryHandle:  Address=700010ffee97498 Hash=7d853627 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
         ObjectName:  Name=SELECT ELEM_TYPE_NAME, ELEM_TY....
           FullHashValue=89605e026d885c4e713b8e667d853627 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=2105882151 OwnerIdn=959
  
   
  The session holds an exclusive(X) (mode=X)  'row cache lock' on 'SMO rowcache':
  
  
  SO: 0x7000110be0269a0, type: 90, owner: 0x7000110e7eadad0, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
  proc=0x7000110c16ba308, name=row cache enqueues, file=kqr.h LINE:2122 ID:, pg=0 conuid=0
  row cache enqueue: count=1 session=700011081a1d938 object=7000110eda46fd8, mode=X
  savepoint=0x11b
  row cache parent object: addr=7000110eda46fd8 cid=25(SMO rowcache) conid=0 conuid=0
  hash=4390382c typ=9 transaction=7000110e7eadad0 flags=00000201 inc=1, pdbinc=1
  own=7000110eda470a8[7000110be026a50,7000110be026a50] wat=7000110eda470b8[7000110dd363fe0,7000110eb9e1dd8] mode=X
  status=EMPTY/-/-/-/-/-/-/-/-
   
  
  This results in waiters on 'row cache lock'
• While parsing the SQL shows a stack containing the following modules:
  
  qksanCopySegText <- qsmoLoadHeap2 < qsmoObjLoad
  
  
  
  
  
  
• This causes a  Higher Shared Pool memory allocation for the component 'SMODI':
  
  
•  Dropping of SQL Plan Baseline that causes the issue may also be slow or appear to hang.

Changes

 Database upgraded to 12.1.0.2 or SQL Plan Baseline added

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.