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

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3e8b24bb.3200808298_at_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?

I won't comment on your figures, because I can barely read them due to formatting. But of course queries are executed as sys: all recursive SQL statements are performed as SYS (that is, 'select * from emp' gets converted into things like 'select x, y and z from obj$, tab$, ts$ and so on').

Regards
HJR Received on Wed Apr 02 2003 - 14:55:30 CST