Re: Excessive parsing of queries involving views

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?

Not being able to see the explain plan my guess would be that the repeated use of USERENV (deprecated by the way in 10g: Use SYS_CONTEXT) is the issue. Why don't you get the value one time and then reuse it as in a WITH clause?

For a WITH clause demo go to Morgan's Library at www.psoug.org and scroll down to WITH Clause.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group