Re: Excessive parsing of queries involving views

"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