Re: Excessive parsing of queries involving views

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 26 Sep 2006 11:01:48 -0700
Message-ID: <1159293708.140752.23440@e3g2000cwe.googlegroups.com>

Ana Ribeiro wrote:
> I have a view
>
> VIEW CLASSIFICATIONOBJECT ( ID, DESCRIPTORID ) AS
> select ob.id, iv1.stringvalue DescriptorId
> from objects ob, simpleassociations sa1, orderedassociations oa,
> instancevalues iv1,
> attributenames an1, modeltypes_tab mt1, modeltypes_tab mt2,
> objects ob2
> where ob.id = sa1.end1 and sa1.id = mt1.id and mt1.typename =
> 'A3.ObjectDescriptorGroup' and ob2.id = sa1.end2
> and sa1.end2 = oa.end1 and oa.id = mt2.id and mt2.typename =
> 'RAS.ClassificationSchema.DescriptorValues'
> and iv1.object = oa.end2 and iv1.attribute = an1.attrnameid and
> an1.attrname = 'identifier'
> and exists (select connection from perspective where connection =
> userenv('sessionid') and repid = ob.repid and version = ob.startv)
> and not exists(select connection from perspective where connection =
> userenv('sessionid') and repid = ob.repid and version in (select
> distinct endv from vend where vid = ob.vid))
> and exists (select connection from perspective where connection =
> userenv('sessionid') and repid = sa1.repid and version = sa1.startv)
> and not exists(select connection from perspective where connection =
> userenv('sessionid') and repid = sa1.repid and version in (select
> distinct endv from vend where vid = sa1.vid))
> and exists (select connection from perspective where connection =
> userenv('sessionid') and repid = oa.repid and version = oa.startv)
> and not exists(select connection from perspective where connection =
> userenv('sessionid') and repid = oa.repid and version in (select
> distinct endv from vend where vid = oa.vid))
> and exists (select connection from perspective where connection =
> userenv('sessionid') and repid = ob2.repid and version = ob2.startv)
> and not exists(select connection from perspective where connection =
> userenv('sessionid') and repid = ob2.repid and version in (select
> distinct endv from vend where vid = ob2.vid))
> with read only
>
> which I use as part of a query
>
> e.g.
>
> select id
> from indObj
> where lower(attrname) = 'name'
> and id in (select id
> from classificationObject
> where contains(descriptorid, '12610') > 0
> intersect
> select distinct id
> from indexedObject
> where contains(value, 'Enterprise') > 0)
> order by stringvalue
>
> When this is run (On Oracle 10.2.0.1.0) I experience about 580
> parses/second. This seems very high to me. I am wondering whether the
> use of literals in the view definition would cause the view to be
> reparsed (frequently) during the query evaluation?

Daniel Mogan is very likely correct regarding the cause of most of the parses: userenv('sessionid').

If there is a performance problem, my guess is that it is not necessarily the number of parses that happen, but rather the number of subqueries that are being processed for each row in the indObj table where lower(attrname) = 'name'. For each of those rows, Oracle is having to process this - once per row:
SELECT
  OB.ID,
  IV1.STRINGVALUE DESCRIPTORID
FROM
  OBJECTS OB,
  SIMPLEASSOCIATIONS SA1,
  ORDEREDASSOCIATIONS OA,

  INSTANCEVALUES IV1,
  ATTRIBUTENAMES AN1,
  MODELTYPES_TAB MT1,
  MODELTYPES_TAB MT2,

OBJECTS OB2
WHERE
OB.ID = SA1.END1

  AND SA1.ID = MT1.ID
  AND MT1.TYPENAME = 'A3.ObjectDescriptorGroup'
  AND OB2.ID = SA1.END2
  AND SA1.END2 = OA.END1

AND OA.ID = MT2.ID

  AND MT2.TYPENAME = 'RAS.ClassificationSchema.DescriptorValues'
  AND IV1.OBJECT = OA.END2
  AND IV1.ATTRIBUTE = AN1.ATTRNAMEID
  AND AN1.ATTRNAME = 'identifier'

  AND EXISTS (
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OB.REPID
      AND VERSION = OB.STARTV)

  AND NOT EXISTS(
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OB.REPID
      AND VERSION IN (
        SELECT DISTINCT
          ENDV
        FROM
          VEND
        WHERE
          VID = OB.VID))

  AND EXISTS (
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = SA1.REPID
      AND VERSION = SA1.STARTV)

  AND NOT EXISTS(
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = SA1.REPID
      AND VERSION IN (
        SELECT DISTINCT
          ENDV
        FROM
          VEND
        WHERE
          VID = SA1.VID))

  AND EXISTS (
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OA.REPID
      AND VERSION = OA.STARTV)

  AND NOT EXISTS(
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OA.REPID
      AND VERSION IN (
        SELECT DISTINCT
          ENDV
        FROM
          VEND
        WHERE
          VID = OA.VID))

  AND EXISTS (
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OB2.REPID
      AND VERSION = OB2.STARTV)

  AND NOT EXISTS(
    SELECT
      CONNECTION
    FROM
      PERSPECTIVE
    WHERE

      CONNECTION = USERENV('SESSIONID')
      AND REPID = OB2.REPID
      AND VERSION IN (
        SELECT DISTINCT
          ENDV
        FROM
          VEND
        WHERE
          VID = OB2.VID));

The SQL statement should be reworked to eliminate as many of the subqueries as possible, possibly by using inline views, combining the subqueries, elminating unnecessary subqueries, or a combination of the above - look at how many times the PERSPECTIVE and VEND tables are hit. I tried to rewrite it, but could not determine enough information about the relationship of the tables.

It would be interesting to see the plan for this SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 26 2006 - 13:01:48 CDT