Performance problems reported after upgrade from 11.2.0.4 to 12.2.0.1 when executing dbms_wm.gotoworkspace (Doc ID 2370920.1)

Last updated on APRIL 08, 2022

Applies to:

Symptoms

Performance problems reported after upgrade from 11.2.0.4 to 12.2.0.1 when executing dbms_wm.gotoworkspace

From 10046 trace, the following is the longest running query in 12.2, but it doesn't appear in 11.2

SELECT SV.WORKSPACE, WMSYS.LTADM.GETSTATELOCKID(SV.WORKSPACE) + 1, WORKSPACE_ID, SV.LOCKMODE, 
      (SELECT COUNT(*) 
         FROM WMSYS.WM$MW_TABLE MW 
        WHERE MW.WORKSPACE = SV.WORKSPACE) MWCNT 

  FROM WMSYS.WM$WORKSPACE_SESSIONS_VIEW SV 
 WHERE SV.SID = :B2 AND SV.SERIAL# = :B1 AND ISIMPLICIT = 0

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.42 0.44 0 36 0 0
Fetch 2000 167.51 207.28 0 24012 18 4000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 167.94 207.72 0 24048 18 4000

It is followed by: which also is not run in 11.2

SQL ID: 89126gyk4axq4 Plan Hash: 1905467518

SELECT MAX(DL.LMODE) FROM
V$LOCK DL WHERE DL.TYPE = 'UL' AND DL.ID1 = :B2 AND DL.SID = :B1

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.37 0.21 0 4 0 0
Fetch 2000 146.93 147.91 0 0 19 2000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 147.30 148.12 0 4 19 2000

Changes

Cause

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