Hi, friends:
    Generally speaking, we hit library cache lock/pin when we do DDL on
object or compile package. I have a env with rac 9.2.0.3 on windows,
sometimes system is hang , only bounce the oracle instance work.
    when there is performance problems, the statspack report look like:

    I checked dba_objects, no ddl in the this day and last. No package
recompile in this day.And no snapshot/maverialized view.
    I tried to check v$session_event ,find  the session with most "library
cache pin/library cache lock" and did a event 10046 and tkprof that trace
file with waits=Yes. In the tracefile, two sql generate the most wait event
of library cache lock/pin, it is a procedure in a package. The common point
is that they used dbms_rls package heavily.

    question 1: Can we use tracefile of 10046 to diag the library cache
lock/pin wait event according to the p1,p1raw, p2 etc?
    Question 2: Besides ddl/compile, what can cause library cache contention
according to your  experience? Can row level security cause high library
cache contention?

    Thanks for your time.


---statspack report.
            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- ------------------
-
Begin Snap:     189 06-Jul-03 16:57:42      226       7.5
  End Snap:     190 06-Jul-03 17:27:47      104      27.1
   Elapsed:               30.08 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:       416M      Std Block Size:         8K
           Shared Pool Size:       128M          Log Buffer:       512K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              8,613.48             17,010.21
              Logical reads:                839.76              1,658.39
              Block changes:                 68.89                136.04
             Physical reads:                  0.51                  1.00
            Physical writes:                  0.15                  0.29
                 User calls:                  7.72                 15.25
                     Parses:                 51.08                100.88
                Hard parses:                  1.46                  2.88
                      Sorts:                 19.57                 38.65
                     Logons:                  0.04                  0.08
                   Executes:                104.41                206.20
               Transactions:                  0.51

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s) Ela
Time
-------------------------------------------- ------------ ----------- ------
--
library cache lock                                310,416     145,265
74.87
library cache pin                                  58,915      41,909
21.60
latch free                                        266,655       4,189
2.16
CPU time                                                        1,262
.65
library cache load lock                             7,017         709
.37
    I checked dba_objets, no ddl since

Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org
SQL> SELECT OWNER,OBJECT_NAME,STATUS,LAST_DDL_TIME FROM DBA_OBJECTS WHERE
LAST_DDL_TIME > S
YSDATE -5 AND OWNER<>'PERFSTAT' AND OWNER<>'PUBLIC';
OWNER               OBJECT_NAME          STATUS         LAST_DDL_TIME
-------------------- -------------------- -------------- -----------------
SYS                  DBA_KEEPSIZES        VALID          20030704 16:36:13
SYS                  DBMS_JOB             VALID          20030704 16:36:26
SYS                  DBMS_JOB             VALID          20030704 16:38:39
SYS                  DBMS_RLS             VALID          20030707 10:45:17
SYS                  DBMS_SHARED_POOL     VALID          20030704 16:36:26
SYS                  DBMS_SHARED_POOL     VALID          20030704 16:36:14
SYS                  DBMS_STATS           VALID          20030704 18:14:02
SYS                  DBMS_UTILITY         VALID          20030704 16:37:51
SYS                  PLAN_TABLE           VALID          20030703 21:37:52
SYS                  STATS$V_$FILESTATXS  VALID          20030704 16:36:24
SYS                  STATS$V_$SQLXS       VALID          20030704 16:36:24
SYS                  STATS$V_$TEMPSTATXS  VALID          20030704 16:36:24
SYS                  STATS$X_$KCBFWAIT    VALID          20030704 16:36:24
SYS                  STATS$X_$KSPPI       VALID          20030704 16:36:24
SYS                  STATS$X_$KSPPSV      VALID          20030704 16:36:24
SYS                  V_$BUFFER_POOL       VALID          20030704 16:36:25
SYS                  V_$BUFFER_POOL_STATI VALID          20030704 16:36:25
                     STICS
SYS                  V_$DATABASE          VALID          20030704 16:36:24
SYS                  V_$DB_CACHE_ADVICE   VALID          20030704 16:36:25
SYS                  V_$DLM_MISC          VALID          20030704 16:36:25
SYS                  V_$ENQUEUE_STAT      VALID          20030704 16:36:25
SYS                  V_$INSTANCE          VALID          20030704 16:36:24
SYS                  V_$INSTANCE_RECOVERY VALID          20030704 16:36:25
SYS                  V_$LATCH             VALID          20030704 16:36:25
SYS                  V_$LATCH_CHILDREN    VALID          20030704 16:36:25
SYS                  V_$LATCH_MISSES      VALID          20030704 16:36:25
SYS                  V_$LATCH_PARENT      VALID          20030704 16:36:25
SYS                  V_$LIBRARYCACHE      VALID          20030704 16:36:25
SYS                  V_$PARAMETER         VALID          20030704 16:36:24
SYS                  V_$PGASTAT           VALID          20030704 16:36:25
SYS                  V_$PGA_TARGET_ADVICE VALID          20030704 16:36:25
SYS                  V_$RESOURCE_LIMIT    VALID          20030704 16:36:25
SYS                  V_$ROLLSTAT          VALID          20030704 16:36:25
SYS                  V_$ROWCACHE          VALID          20030704 16:36:25
SYS                  V_$SEGMENT_STATISTIC VALID          20030704 16:36:26
                     S
SYS                  V_$SEGSTAT           VALID          20030704 16:36:25
SYS                  V_$SEGSTAT_NAME      VALID          20030704 16:36:26
SYS                  V_$SESSION           VALID          20030704 16:36:25
SYS                  V_$SESSION_EVENT     VALID          20030704 16:36:25
SYS                  V_$SESSTAT           VALID          20030704 16:36:25
SYS                  V_$SGA               VALID          20030704 16:36:25
SYS                  V_$SGASTAT           VALID          20030704 16:36:25
SYS                  V_$SHARED_POOL_ADVIC VALID          20030704 16:36:25
                     E
SYS                  V_$SQL               VALID          20030704 16:36:25
SYS                  V_$SQLAREA           VALID          20030704 16:36:25
SYS                  V_$SQLTEXT           VALID          20030704 16:36:25
SYS                  V_$SQL_PLAN          VALID          20030704 16:36:25
SYS                  V_$SQL_WORKAREA_HIST VALID          20030704 16:36:25
                     OGRAM
SYS                  V_$SYSSTAT           VALID          20030704 16:36:25
SYS                  V_$SYSTEM_EVENT      VALID          20030704 16:36:25
SYS                  V_$SYSTEM_PARAMETER  VALID          20030704 16:36:24
SYS                  V_$UNDOSTAT          VALID          20030704 16:36:25
SYS                  V_$WAITSTAT          VALID          20030704 16:36:25
DEV                  BF$ACCOUNT_BALANCE   VALID          20030703 14:00:21
OWNER               OBJECT_NAME          STATUS         LAST_DDL_TIME
-------------------- -------------------- -------------- -----------------
DEV                  BF$AUDITED_EVENT     VALID          20030703 14:00:17
DEV                  BF$AUDITED_EVENT     VALID          20030703 14:00:19
DEV                  BF$BILL_SERVER_SOAP  VALID          20030703 14:46:27
DEV                  BF$BILL_SERVER_SOAP1 VALID          20030703 14:00:18
DEV                  BF$BP_PROCESS        VALID          20030703 14:46:31
DEV                  BF$CD_KEY            VALID          20030703 14:09:49
DEV                  BF$GEN_BP_PROCESS    VALID          20030703 14:46:31
DEV                  BF$TRANSACTION       VALID          20030703 14:09:46
DEV                  BF$TRANSACTION       VALID          20030703 14:09:51

63 rows selected.


SQL> select sid,event,total_waits,time_waited from v$session_event
  2  where event like 'library cache %';
       SID EVENT                         TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
        12 library cache pin                       18        1241
        32 library cache pin                     4520         140
        26 library cache pin                       77           4
        22 library cache pin                     1552          78
        13 library cache pin                        1           0
        17 library cache pin                       26           2
        14 library cache pin                        1           0
        12 library cache lock                      10           1
        17 library cache lock                     125           5
        26 library cache lock                     367          11
        32 library cache lock                    2109         118
        22 library cache lock                     952          61
SQL> select sid,serial#,username,program,machine,status from v$session where
sid=32;
       SID    SERIAL# USERNAME
---------- ---------- ---------------
PROGRAM
                                 MACHINE    STATUS
----------------------------------------------------------------------------
-----------------
----------------------------------- ---------- ----------------
        32      23698 DISTRIBUTORMATR
                      IX
aspnet_wp.exe
                                 WORKGROUP\ INACTIVE

                                 KK97M6D

SQL> exec dbms_system.set_ev(32,23968,10046,8,'')

PL/SQL procedure successfully completed.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Reply via email to