Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: High reloads by SYS?! large shared_pool_size.. was Re: where are all these hard parses coming from?
the main figures is that reloads are very close to parse count(hard)
and that certain very frequently executed queries, seem to have very
high reloads, in fact they seem to reload per execution, however the
executions field doesn't seem to increment.
Here are some simpler outputs:
select name, value from v$sysstat where name='parse count (hard)
select reloads
from v$librarycache where namespace='SQL AREA'
RELOADS
8298483
NAME VALUE -------------------- ---------- parse count (hard) 9053067
select loads, invalidations, executions,
chr(10)||sql_text
from v$sql sql, dba_users du
where loads>100
and sql.parsing_user_id=du.user_id
order by 1
746454 1 11144
select id from teams
here (member_id=:b0 and rownum<2)
746454 1 11144
select val from cok ,teams
where ((var='user_id' and val=to_char(teams.id))
nd teams.member_id=:b0) order by useless_cok desc
On Thu, 3 Apr 2003 06:55:30 +1000, "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:
>
>"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
>
>
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email
Received on Wed Apr 02 2003 - 15:14:40 CST