Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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?

Re: 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 21:14:40 GMT
Message-ID: <3e8b5169.3212245985@nyc.news.speakeasy.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US