Tag: Library Cache Pin

SYS.SYS$SERVICE_METRICS_TAB Size Grows Big and Affects AQ Performance

SYMPTOMS

1)Table SYS.SYS$SERVICE_METRICS_TAB keeps growing everyday.

SQL> select count(*) from SYS$SERVICE_METRICS_TAB;

COUNT(*)
----------
46946104

SQL> SELECT TRUNC(enq_time), COUNT(*)
  FROM sys$service_metrics_tab
 GROUP BY TRUNC(enq_time)
 ORDER BY 1 ;

TRUNC(ENQ   COUNT(*)
--------- ----------
07-JAN-19      41052
08-JAN-19      63338
09-JAN-19      63096
10-JAN-19      63338
11-JAN-19      62128
12-JAN-19      63338
13-JAN-19      63316
14-JAN-19      63316
...
..
.

2) Oracle background process ora_cjq — Job scheduler coordinator slows and hangs when running below SQL with “enq: TQ – DDL contention” contention.

begin dbms_aqadm_sys.remove_subscriber (
                            queue_name => 'SYS.SYS$SERVICE_METRICS', 
                 subscriber => sys.aq$_agent(:1, NULL, NULL)); end;

3) Oracle background process Qnnn: Queue cleanup processes hang with “library cache pin” contention.

4) oraagent.bin@host program also has “library cache pin” contention”.

5) Manageability Monitor Process MMON background slave process ora_m000 has “library cache lock” contention.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe
Advertisement

SQL Script to Format Library Cache Lock/pin wait event p3 value

CREATE OR REPLACE PROCEDURE lbc_p3(P3 number)
is

-- purpose: format p3 value (maybe p3 value large than 100,000,000)
-- author: anbob.com(weejar@gmail.com)
-- date: 2016-5-1
-- note: 11.2 tested

v_hex varchar2(50);
v_hexoid varchar2(50);
v_oid number;
v_namespace number;
v_mode varchar2(50);
begin
select to_char(p3,'xxxxxxxxxxxxxxxxxxxxx') into v_hex from dual;
-- object id
select to_number(substr(v_hex,1,length(v_hex)-8),'xxxxxxxxxxxxxx') into v_oid from dual;
-- namespace
select to_number(substr(v_hex,-8,4),'xxxxxxxxxxxxxx') into v_namespace  from dual;
-- mode
select decode(to_number(substr(v_hex,-4),'xxxxxxxxxxxxxx'),3,'exclusive mode',2,'share mode',1,'null mode',0,'no lock/pin held','unknown') into v_mode  from dual;
dbms_output.put_line('---------------------------------------------');
dbms_output.put_line(lpad('Library cache P3 value: ',50,'.')||p3);
dbms_output.put_line(lpad('Library cache P3 value HEX: ',50,'.')||ltrim(v_hex));
dbms_output.put_line(lpad('Object id: ',50,'.')||v_oid);
dbms_output.put_line(lpad('Namespace: ',50,'.')||v_namespace );
dbms_output.put_line(lpad('RequestMode: ',50,'.')||v_mode);
dbms_output.put_line('Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp=''NAMESPACE'' and indx='||v_namespace );
end;
/



SQL> set serveroutput on
SQL> exec lbc_p3(1571747577004035);
---------------------------------------------
..........................Library cache P3 value: 1571747577004035
......................Library cache P3 value HEX: 5957f00010003
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: exclusive mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.

SQL> exec lbc_p3(1571747577004034);
---------------------------------------------
..........................Library cache P3 value: 1571747577004034
......................Library cache P3 value HEX: 5957f00010002
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: share mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.