OraTransplant

Slow performance of *_SOURCE views

14 May 2005 at 13:41 CEST | In Bugs and issues, Database, Oracle |

We experienced awful performance querying the data dictionary view DBA_SOURCE on our v8.1.7.4 database. Some investigation revealed the following query for this view:

SELECT u.NAME, o.NAME,
       DECODE (o.type#,
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               13, 'TYPE',
               14, 'TYPE BODY',
               'UNDEFINED'
              ),
       s.line, s.SOURCE
  FROM SYS.obj$ o, SYS.source$ s, SYS.user$ u
 WHERE o.obj# = s.obj#
   AND o.owner# = u.user#
   AND o.type# IN (7, 8, 9, 11, 13, 14)
UNION
SELECT u.NAME, o.NAME, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
  FROM SYS.obj$ o, x$joxfs s, SYS.user$ u
 WHERE o.obj# = s.joxftobn
   AND o.owner# = u.user#
   AND o.type# = 28;

Do you see the problem?

It’s the UNION. Apparently, Oracle changed the view in version 8.1.6 to include the Java source code. I don’t know what they were thinking when using a UNION and not a UNION ALL. That’s one of the beginner mistakes!

Our database has a couple of million lines of PL/SQL. Using the UNION each query on this view first forces a sort to get rid of any duplicate rows in the two sub-selects. But, they can never return duplicate rows. The second view always returns a type of ‘JAVA SOURCE’ whereas the other view can and will never return this type. So, there is no reason to use a UNION.

I was a bit surprised that Oracle made this mistake in the first case. I was even more surprised that they never fixed it. I opened a TAR at MetaLink and the support engineer found two (non-published) bugs for this. They indicate it was fixed in version 9.2.0.3 and was never back ported to 8.1.7.

Oracle does indicate you can safely change the view definition of USER_SOURCE, ALL_SOURCE and DBA_SOURCE to use a UNION ALL in stead of a UNION. Perhaps this is a useful tip to make this change on your pre-9.2.0.3 databases.