Re: intermittent library cache pin/lock problems

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Mon, 12 Dec 2005 21:11:54 GMT
Message-ID: <439dc631.253699828@localhost>

On 11 Dec 2005 05:13:46 -0800, "Zhu Chao" <zhuchao_at_gmail.com> wrote:

>Do u use plsql heavily? Could it be some package been compiled?
>or some core tables get ddled (like analyze/alter/grant etc)?
>
>anything can cause sql reparse should be checked first.

We do not recompile anything at any noticable frequency. We do not alter tables, etc. The Oracle 'solution' was to increase the shares pool, which we did to 600M from 400M.

There are some 'weird' things going on in library cache though:

Stmt                                     KOUNT        Mem  Open
---------------------------------------- ----- ---------- -----
               SELECT user_id, user_rece   192    5068902   151
          SELECT ms.member_id, mst.rank   6052  210093029  5174
DELETE FROM PHPBB_sessions   WHERE sessi    42    2947082     0

SELECT * FROM (SELECT rownum AS xrownum, 631 103011546 634 SELECT DISTINCT s.member_id, t.rank FROM 53 1727970 2340 SELECT ban_ip, ban_userid, ban_email 2003 476285375 197

SELECT f.forum_id, p.post_time    FROM P   447   60049479    22
SELECT t.topic_id, t.topic_title, t.topi  1456  313310324   222
SELECT u.username, u.user_id, u.user_all   793  128892719     0
SELECT u.username, u.user_id, u.user_pos    42    1748750  2432

Notice, the first 2 queries actually lack bind vars (they have a 'variable' in clause), however, the rest appear in there multiple time for no known reason.

E.g.

system_at_VCRS> select count(*), count(distinct sql_text) from v$sql where
sql_text like 'SELECT ban_ip, ban_userid, ban%'; COUNT(*) COUNT(DISTINCTSQL_TEXT)

---------- -----------------------

586 2

.......
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4 remove NSPAM to email Received on Mon Dec 12 2005 - 15:11:54 CST