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

On Sun, 6 Apr 2003 11:27:30 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> 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.
>
> Are you running shared servers (MTS) without configuring
> a large pool ? This could have a significant impact on the
> fraction of the shared pool that was available for the library
>cache.

using large_pool_size and MTS

>> 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?
>
> An entry in v$sql consists of at least two chunks of memory,
> and it is possible for one chunk to be held whilst another is
>flushed
> (this is subsequently allows a reload to occur). It is an oddity
>that
> some of the statistics stay in memory with the SQL, and others
> are flushed. Consequently you can get some highly contradictory
> numbers in your query. I haven't checked it, but it is possible
>that
> the parsing_user_id is also one of the values in the flushed
>memory
> chunk - in which case the value returned by your query would
>presumably
> be returned as zero - hence SYS.

it could be flushed b/c the statement is invalid :) (see my other post, but basicly looks like these queries are querying against objects that don't exist).

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Apr 08 2003 - 13:09:30 CDT