Re: Excessive parsing of queries involving views

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 29 Sep 2006 05:05:56 -0700
Message-ID: <1159531556.160515.261150@i3g2000cwc.googlegroups.com>

Many thanks for all the help!
Our parsing problem was solved when we eliminated the literals from the code.
Best regards,
Ana

Charles Hooper wrote:

> 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 Fri Sep 29 2006 - 07:05:56 CDT