Re: cursors question

Allowing for minor corrections, you have

    2,000 parses
of which

    1,000 are hard parses.

Each statement of the form

    'select object_name from all_objects where object_id=99' which is what you are constructing,
causes a hard parse. This accounts
for the 1,000 hard parses.

Whenever you do a hard parse on a
statement containing a view, Oracle
reloads the text of the view definition. (This is perhaps the most costly aspect
of using views in Oracle, but fortunately occurs only in badly coded systems)

This is why every time you generate a
new query against ALL_OBJECTS, you
also have a recursive execution of:

    select text from view$ where rowid=:1

Fortunately, although Oracle explicitly parses this statement, it is (almost) always in the library cache, so it registers only as a 'soft' parse.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

murthy wrote in message <300217f3.0207010531.7b58e834_at_posting.google.com>...


>I was testing this while reading Tom Kyte's book

>Can anyone help clarify my doubt

>

>

>select name,value

>from v$sysstat

>where name like '%parse count%'

>

>NAME                                                    VALUE

>-------------------------------------------           -------

>Parse count(total)                                       5049

>Parse count(hard)                                        1214

>

>

>declare

>type rc is ref cursor;

>l_rc rc;

>l_dummy all_objects.object_name%type;

>begin

>for i in 1..1000 loop

>open l_rc for 'select object_name from all_objects where object_id=

>'||i;

>fetch l_rc into l_dummy;

>close l_rc;

>end loop;

>end;

>

>

>After the

>NAME                                                    VALUE

>-------------------------------------------           -------

>Parse count(total)                                       7117

>Parse count(hard)                                        2222

>

>

>

>My doubt is why did the anonymous PL/SQL block cause more than 2000

>soft parses

> and about 100 hard parses.

>I turned on tracing and saw that

>select text from view$ where rowid=:1 is taking 1000 parses, executes

>and fetches each.

>Why is this statement taking 1000 parses ?

>

>thanks in advance for any reply.