High reloads by SYS?! large shared_pool_size.. was Re: where are all these hard parses coming from?

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 02 Apr 2003 18:18:43 GMT
Message-ID: <3e8b24bb.3200808298@nyc.news.speakeasy.net>

i think i found the problem:

 1 select namespace, gets,
gethitratio,pins,pinhitratio,invalidations,reloads   2* from v$librarycache

NAMESPACE GETS GETHITRATIO PINS PINHITRATIO INVALIDATIONS RELOADS SQL AREA 608782831 .998 -1.392E+09 1.005 8130 8079249 TABLE/PROCEDURE 69900866 .999 147124935 .999 0 16043  select name, value, sysdate from v$sysstat where name='parse count (hard)';

NAME                      VALUE SYSDATE
-------------------- ---------- --------------------
parse count (hard)      8834621 02-apr-2003 12:44:22

The number of hard parses it not too far off reloads.

From what I understand, reloads indicate the number of times that objects in the library cache have aged out and reacquired shortly after, which would require a reparse.
There is a large number of reloads, but shared pool is huge (400M), why would statements be aged out? Doesn't oracle age them out via LRU? The queries that seem to get aged out, are the ones most frequently used.
Also, why would reloads keep incrementing but executions stay constant, or be less than reloads? (i did flush shared pool the other day after we replaced a bunch of statements not using binds)

And additionally, why in the would v$sql.parsing_user_id be SYS when queries are not executed as SYS?

  1 select username,loads,invalidations,executions||chr(10)||sql_text   2 from v$sql , dba_users
  3 where loads>10
  4 and v$sql.parsing_user_id=dba_users.user_id   5* order by 2

PAY_PRODUCTS                       152257             1

86877
select id from teams where (member_id=:b0 and rownum<2)

PAY_PRODUCTS                       152257             1

86877
select val from cok ,teams where ((var='user_id' and val=to_char(teams.id)) and
 teams.member_id=:b0) order by useless_cok desc

SYS                                189195             0

1
select dc.id ,dc.product_id ,p.short_name ,dc.discount_code ,dc.description ,dc.
create_ts from discount_codes dc ,products p where (dc.product_id=p.id and dc.i
s_active=1) union select dc.id ,pgi.product_id ,p.short_name ,dc.discount_code ,
dc.description ,dc.create_ts from discount_codes dc ,products p ,product_group_
items pgi where ((dc.product_group_id=pgi.product_group_id and pgi.product_id=p.
id) and dc.is_active=1)

SYS                                189197             0

1
select id ,name ,description ,create_ts from product_sports

SYS                                189197             0

1
select id ,offer_code ,product_id ,modified_name ,modified_price ,description ,c
reate_ts from offer_details where enabled=1

SYS                                189197             0

1
select id ,code ,description from response_codes

SYS                                199598             0

0
select id ,name ,description ,create_ts from product_sports

SYS                                199598             0

0
select id ,offer_code ,product_id ,modified_name ,modified_price ,description ,c
reate_ts from offer_details where enabled=1

SYS                                199598             0

0
select id ,code ,description from response_codes

SYS                                199600             0

0
select dc.id ,dc.product_id ,p.short_name ,dc.discount_code ,dc.description ,dc.
create_ts from discount_codes dc ,products p where (dc.product_id=p.id and dc.i
s_active=1) union select dc.id ,pgi.product_id ,p.short_name ,dc.discount_code ,
dc.description ,dc.create_ts from discount_codes dc ,products p ,product_group_
items pgi where ((dc.product_group_id=pgi.product_group_id and pgi.product_id=p.
id) and dc.is_active=1)

SYS                                391210             0

0
select id from teams where (member_id=:b0 and rownum<2)

SYS                                391210             0

0
select val from cok ,teams where ((var='user_id' and val=to_char(teams.id)) and
 teams.member_id=:b0) order by useless_cok desc

SYS                                721522             1

11144
select id from teams where (member_id=:b0 and rownum<2)

SYS                                721522             1

11144
select val from cok ,teams where ((var='user_id' and val=to_char(teams.id)) and
 teams.member_id=:b0) order by useless_cok desc

Thanks

There

On Wed, 02 Apr 2003 07:22:09 GMT, andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>I've been fighting sql w/o bind vars for a couple of days now, and
>most of it is gone, but the number of hard parses still stays very
>high. v$sysstat data doesn't seem to be supported by v$sqlarea view
>
> select value,to_char(sysdate,'yyyy-mm-dd/hh24:mi:ss')
> from v$sysstat where name='parse count (hard)';
>---------- -------------------
> 8341931 2003-04-02/01:10:38
>
>
> select value,to_char(sysdate,'yyyy-mm-dd/hh24:mi:ss')
> from v$sysstat where name='parse count (hard)';
>
>---------- -------------------
> 8344067 2003-04-02/01:14:55
>
> select sum(parse_calls)
> from v$sql
> where
>to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss')>to_date('2003-04-02/01:10:38','yyyy-mm-dd/hh24:mi:ss');
>SUM(PARSE_CALLS)
>----------------
> 17
>
>shouldnt' i see something like 2000 here?
>
>I don't have an easy way of identifying hard parses due to
>invalidations, but I know very few objects change, we don't even run
>cbo stats on a regular basis on this db.
>How can I identify where hard parses are coming from?
>I also know that sql couldn't have aged out so quickly, we have sql
>sitting in there parsed few days ago and never used again.
>
>.......
>We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>remove NSPAM to email

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Apr 02 2003 - 12:18:43 CST