library cache pin

When you try compile package on Oracle DB 10g , compile process may be hang, reason of this is any session which using this package, on this scenario you should see wait event like that library cache pin.
The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

use this select to see which session lock your compile process. run it with sys user!

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'

How Find Large Object in DB

This is small script how oracle developer can find large object in oracle DB for a schema.

script :

select round(sum(s),2),t
from (
select sum(b.bytes)/1024/1024/1024 s, a.table_name t from dba_indexes a, dba_segments b
where b.segment_type in ('INDEX','INDEX PARTITION')
and a.index_name=b.segment_name
and a.owner=&SCHEMA
group by a.table_name

union all

select sum(a.bytes)/1024/1024/1024 s,a.segment_name t from dba_segments a
where a.owner=&SCHEMA
and a.segment_type in ('TABLE','TABLE PARTITION')
group by a.segment_name

union all

select sum(b.bytes)/1024/1024/1024 s,a.table_name t from dba_lobs a, dba_segments b
where a.owner=&SCHEMA
and a.segment_name=b.segment_name
group by a.table_name

)
group by t
order by 1 desc

Run ADDM Statistic

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes

Run ADDM report user can with Oracle Enterprise manager, but there i want tell you about script which provided same information. After running this script in PL/SQL Developer you can get ADDM report result.

1. Report Interval 

 you should decide on what period do you want see ADDM report, thats why you run this select on DB :

select * from dba_hist_snapshot
order by snap_id

and know you know begin and end snap_id(snap_id_1, snap_id_2) 

2. Create ADDM Task

BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
  task_name => 'Test Report',
task_desc => 'Test Report');

-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
  task_name =>'Test Report',
parameter => 'START_SNAPSHOT',
  value => snap_id_1);

DBMS_ADVISOR.set_task_parameter (
  task_name => 'Test Report',
parameter => 'END_SNAPSHOT',
  value => snap_id_2);

-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'Test Report');

END;

3. Get Report

SELECT DBMS_ADVISOR.get_task_report('Test Report ','TEXT', 'TYPICAL', 'ALL') AS report
FROM dual;

In BLOB you can see result of report

4. Delete Report

for deleting report you can run this statement:

BEGIN
  DBMS_ADVISOR.delete_task ( task_name => 'Test Report');
END;

Personal

Hello,

I am Vazha Mantua, Head of DBA team and Senoi DB administrator at JSK Bank Of Georgia, on of the leading bank in Caucasus region. I am OCP 10g since 2009, I am married and have very nice daughter.

In my Blog i tell you all of administration oracle databases, i hope it will help you