Re: Excessive parsing of queries involving views

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 26 Sep 2006 18:13:04 -0700
Message-ID: <1159319584.141990.42980@i3g2000cwc.googlegroups.com>

Jonathan Lewis wrote:
> "Ana Ribeiro" <ana.ribeiro_at_reflective.com> wrote in message
> news:1159279644.594393.292550_at_h48g2000cwc.googlegroups.com...
> >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?
> >

>
>

> Can you clarify one point.

>

> Are you saying that when you run this query once
> you see 580 parse calls take place ?

>

> If this is the case it probably has nothing to do
> with the literal strings or the userenv() calls in
> your code. Creating a view that classifies
> components of your data set by embedding
> a literal string in a view is actually a very smart
> idea (though I think that you're going to need
> several more smart ideas to make your
> implementation work efficiently given the
> implications of the join - for a start you might
> want to think about histograms or even list-partitioning
> on your modeltypes_tab table).

>

> If a single query does indeed cause 580 parse
> calls, then enable sql tracing before running the
> query once, and you should find the 579 other
> SQL statements in the trace file.

>
>

> --
> Regards

>

> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

>

> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

> Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

I tried to create a lightweight model of the setup that Ana seems to be using (modeled using Oracle 10.2.0.2), to help my understanding:

CREATE TABLE MY_TABLE (
  MY_SESSION NUMBER (10),
  CHECK_ME VARCHAR2(20)); INSERT INTO MY_TABLE
SELECT
  USERENV('SESSIONID'),
  'TEST'
FROM
  DUAL; CREATE TABLE MY_TABLE2 (
  CHECK_ME VARCHAR2(20),
  CHECK_NUMBER NUMBER (10)); INSERT INTO MY_TABLE2
VALUES (
  'TEST',
  10);

(PART is a table in the ERP system that I use) CREATE TABLE MY_TABLE3 AS
SELECT
  ID,
  DESCRIPTION
FROM
  PART
WHERE
  QTY_ON_HAND BETWEEN 9 AND 10; COMMIT; Now I have a couple tables to work with, including one that holds the value of USERENV('SESSIONID'). I then create a view that uses several of those tables and the PART table from my ERP system. CREATE VIEW MY_VIEW AS
SELECT
  P.ID,
  P.DESCRIPTION
FROM
  PART P,
  MY_TABLE2 MT
WHERE
  MT.CHECK_NUMBER=P.QTY_ON_HAND
  AND EXISTS (
    SELECT
      *
    FROM
      MY_TABLE
    WHERE
      MY_SESSION=USERENV('SESSIONID'))
  AND NOT EXISTS (
    SELECT
      *
    FROM
      MY_TABLE
    WHERE
      MY_SESSION=(USERENV('SESSIONID')-1)); Determine the current parse count:
SELECT
  S.NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME S
WHERE
  S.NAME='parse count (total)'
  AND S.STATISTIC#=M.STATISTIC#; parse count (total) 326

   (then executed a second time to make certain nothing odd is happening)
parse count (total) 327

Now a SELECT that accesses the view:
SELECT
  ID
FROM
  MY_TABLE3
WHERE
  UPPER(DESCRIPTION) LIKE 'BU%'
  AND ID IN (
    SELECT
      ID
    FROM
      MY_VIEW
    WHERE
      DESCRIPTION LIKE '%ING%'); ID

---

1486627
2503195

Execute the select statement to retrieve the parse count: parse count (total) 348

By processing the SQL statement that accessed the view, the parse count increased by 20 (plus 1 for the select to determine the parse count).

Execute the SELECT that accesses the view, then determine the parse count again:
parse count (total) 350

This time the parse count increased by 1 (plus 1 for the select to determine the parse count).

SELECT
  COUNT(*)
FROM
  PART
WHERE
  DESCRIPTION LIKE 'INSERT%'; Execute the above SELECT, then determine the parse count again: parse count (total) 353

With this simple SELECT, the parse count increased by 2 (plus 1 for the select to determine the parse count).

We might be able to draw from this experiment that the more complex queries, possibly those using USERENV('SESSIONID'), increase the parse count more than a simple query - in this case by a factor of 10, during a hard parse. Since the SQL statement is already in the shared pool the second time it is executed, the parse count increases by 1. OK, so what if the literals in the SQL statement change from one execution to the next:
  contains(descriptorid, '12610')
  contains(value, 'Enterprise') > 0)

If bind variables are not used (and CURSOR_SHARING is not set to FORCE), Oracle will be forced to hard parse the SQL statement every time it is submitted to the database with different literals, or the SQL statement is aged out of the shared pool. In my example, the parse count would increase by 20 with each execution, and a more complex SQL statement may increase the parse count at a greater rate during a hard parse.

Could the contains() syntax contribute to the parse count?

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