SQL Performance (MOSC)

MOSC Banner

SQL Profile not used

Hi!

Recently we migrated our development databases from 12.2 to 19.9. Several small issues and one performance problem. One of projects is using MV fast refresh, and commits now take 30s instead of 1s. Tracing revealed offending sql, it is

SQL ID: ccht34h93yxqg

DELETE from sys.snap_logdep$ where snapid=:1 and tableobj# in (   select tableobj#   from sys.snap$ s, sys.snap_logdep$ l   where s.snapid=:1 and      s.snapid=l.snapid and      tableobj# NOT IN ( select OBJECT_ID               from dba_objects o , dba_dependencies d               where o.OWNER=d.REFERENCED_OWNER and                  o.OBJECT_NAME=d.REFERENCED_NAME                  and d.TYPE='MATERIALIZED VIEW'                  and d.owner = s.sowner                  and d.name = s.vname));

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center