Comments inline
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"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.
> 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.
>
> 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
>
Do you have multiple children in v$sql for each statement ?
When using proper bind variable techniques, I believe that
if the maximum size of the bind variable changes significantly
between callls, then a new child cursor has to be created and
is therefore subject to a hard parse, and some programming tools
set the maximum size to the actual size automatically. Is it
possible that this might be related in some way to your problem -
viz. using more memory that expected, therefore causing more
reloads than expected.
Received on Sun Apr 06 2003 - 05:27:30 CDT
