Re: Why is this SQL not shared ?

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<4gf0vt0iqbto39vo2i19c73e4alra7vit4_at_4ax.com>...
> On 12 Nov 2001 11:09:07 -0800, bchorng_at_yahoo.com (Bass Chorng) wrote:
>
> >I have seen many cases where the same SQL (hash_value) has multiple
> >entries
> >in v$sql although they are all parsed by the same user, with each
> >entry only
> >executed 2 or 3 times. I am wondering why the SQL is not shared. In
> >the following example, why don't I see only one entry with, say 120+
> >of executions?
> >
> >SQL> select count(*) from v$sql where hash_value = 460964395 and
> >parsing_user_id = 248;
> >
> > COUNT(*)
> >----------
> > 53
> >
> >What would cause the same SQL to be not shared by the same parsing
> >user ?
> >
> >-Bass Chorng
>
>
> Because you are looking at v$sql instead of v$sqlarea, where the
> number of executions is tracked.
> Your result show you have 53 sessions sharing the same statement.
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Thanks for the answer.

But does it make sense to have 53 entries in v$sql ? Why not one entry with 120 executions as I see with many other SQLs ? And if you look at the
sharable_mem either in v$sqlarea or sum at v$sql, they are huge. This SQL has only a few lines, the execution plan is also simple, but the sharable_mem in v$sqlarea is 6.8 MB and the sum at v$sql is about 5.7 MB
( BTW, why don't they match ? )

That still tells me this SQL is not shared. It physically has 53 copies
in the buffer cache.

-bass Received on Tue Nov 13 2001 - 12:39:43 CST