Note 1. All tests are done in Oracle 12.1.0.2.0 on AIX, Solaris, Linux.
Note 2. ORA-04025 Maximum Allowed Library Object Lock Allocated is also discussed in Blog:
ORA-04025 library object lock on AQ queue and SQL cursor
1. DATABASE Truncate Triggers
In Oracle 12c, there exist two DATABASE Triggers:
XDB_PI_TRIG
AW_TRUNC_TRG
They can be discovered by:
create or replace function getlong(p_trigger_name varchar2) return varchar2 as
l_data long;
begin
select trigger_body into l_data from dba_triggers where trigger_name = p_trigger_name;
return substr(l_data, 1, 4000);
end;
/
column owner format a6
column trigger_name format a12
column trigger_type format a12
column triggering_event format a15
column status format a8
column description format a40
set wrap off
SQL> select owner, trigger_name, trigger_type, triggering_event, status, description
--,getlong(trigger_name) trigger_body
from dba_triggers where triggering_event like '%TRUNCATE%';
OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVEN STATUS DESCRIPTION
------ ------------ ------------ --------------- -------- ----------------------------------------
SYS XDB_PI_TRIG BEFORE EVENT DROP OR TRUNCAT ENABLED sys.xdb_pi_trig
SYS AW_TRUNC_TRG AFTER EVENT TRUNCATE ENABLED aw_trunc_trg AFTER TRUNCATE ON DATABASE
set wrap on
SQL> select getlong(trigger_name) trigger_body from dba_triggers where trigger_name = 'XDB_PI_TRIG';
BEGIN
BEGIN
IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
SQL> select getlong(trigger_name) trigger_body from dba_triggers where trigger_name = 'AW_TRUNC_TRG';
BEGIN
aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
END;
Note: ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type are published Attributes (public synonyms) of System Events.
(replacing functions of earlier releases: sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_type
defined in dbms_standard).
2. Test
Run following test (see appended Test Code):
alter system set session_cached_cursors=2000 scope=spfile;
alter system set processes=1100 scope=spfile;
alter system set job_queue_processes=1000 scope=spfile;
alter system set "_session_cached_instantiations"=1001 scope=spfile;
-- restart DB
SQL> startup force
-- start Test by launching 66 Jobs (66 > floor(65535/1000)=65)
SQL> exec gtt_truncate_job(66, 1e9);
--after Test, clean jobs
-- exec clean_jobs;
column name format a80
set wrap off
SQL> select hash_value, locks, pins, locked_total, pinned_total, name --v.*
from v$db_object_cache v
where locks > 1000
and (hash_value in (2219505151, 679830350)
or (name like '%xdb.DBMS_XDBZ.%' or name like '%aw_trunc_proc%'));
HASH_VALUE LOCKS PINS LOCKED_TOTAL PINNED_TOTAL NAME
---------- ------ ----- ------------ ------------ --------------------------------------------------------------------------------
2219505151 64141 0 1164253 1164234 BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionar
2219505151 64238 0 1164365 1 BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionar
679830350 64506 0 1164192 1164167 BEGIN aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
679830350 64602 0 1164289 1 BEGIN aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
database alert.log shows ORA-04025 Errors caused by two triggers:
ORA-04025: maximum allowed library object lock allocated for BEGIN
aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
END;
ORA-04025: maximum allowed library object lock allocated for BEGIN
BEGIN
IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
column sql_text format a100
set wrap off
SQL > select sql_id, hash_value, executions, parse_calls, sql_text from v$sqlarea v
where sql_id in ('ct6c4h224pxgz', 'gx87sann8asuf') or hash_value in (2219505151, 679830350);
SQL_ID HASH_VALUE EXECUTIONS PARSE_CALLS SQL_TEXT
------------- ---------- ---------- ----------- --------------------------------------------------
ct6c4h224pxgz 2219505151 13537973 13623969 BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dicti
gx87sann8asuf 679830350 13649026 13603140 BEGIN aw_trunc_proc(ora_dict_obj_type, ora_dict_
Note that in the above test, we set
session_cached_cursors=2000because of two DATABASE Truncate Triggers, each of both can acquire about 1000 LOCKs.
As a justification, even we start one single session, LOCK_CNT is around 1000.
SQL > exec clean_jobs;
SQL > exec gtt_truncate_job(1, 1e9);
SQL > with sq as
(select /*+ materialize */ sid, program, saddr, event
,to_char(p1, 'xxxxxxxxxxxxxxx') p1, p1text
,to_char(p2, 'xxxxxxxxxxxxxxx') p2, p2text
,to_char(p3, 'xxxxxxxxxxxxxxx') p3, p3text
from v$session
where program like '%(J%')
select o.kglnahsh, s.sid, holding_user_session, object_handle, l.type, mode_held, mode_requested,
count(*) lock_cnt -- it goes up to about 1000
from v$libcache_locks l, x$kglob o, sq s
where 1= 1
and o.kglnahsh in (2219505151, 679830350)
and l.object_handle = o.kglhdadr
and l.holding_user_session = s.saddr
group by o.kglnahsh, s.sid, holding_user_session, object_handle, l.type, mode_held, mode_requested
order by lock_cnt desc, s.sid, holding_user_session, l.type, mode_held, mode_requested;
KGLNAHSH SID HOLDING_USER_SES OBJECT_HANDLE TYPE MODE_HELD MODE_REQUESTED LOCK_CNT
---------- ---- ---------------- ---------------- ---- ---------- -------------- ----------
2219505151 152 070001010D31BD08 07000100EDC018C8 LOCK 1 0 1012
2219505151 152 070001010D31BD08 07000100ED2F4110 LOCK 1 0 1011
679830350 152 070001010D31BD08 07000100ECDBA460 LOCK 1 0 1009
679830350 152 070001010D31BD08 07000100ECDBBF28 LOCK 1 0 1009
3. "library cache: mutex X" and "cursor: pin X"
During test, heavy "library cache: mutex X" and "cursor: pin X" are observed on GTT truncate statement:
truncate table my_gtt
SQL > select mutex_identifier, mutex_type, count(*) cnt from v$mutex_sleep_history
group by mutex_identifier, mutex_type
order by cnt desc;
MUTEX_IDENTIFIER MUTEX_TYPE CNT
---------------- -------------- ----
167539573 Library Cache 278
167539573 Cursor Pin 81
SQL > select hash_value, locks, pins, locked_total, pinned_total, invalidations, name
from v$db_object_cache where hash_value in (167539573);
HASH_VALUE LOCKS PINS LOCKED_TOTAL PINNED_TOTAL INVALIDATIONS NAME
---------- ------ ----- ------------ ------------ ------------- ---------------------
167539573 1 0 14295 26690 13002 truncate table my_gtt
167539573 2 0 14309 26510 13026 truncate table my_gtt
167539573 1 0 14134 26218 12874 truncate table my_gtt
......
SQL > select sql_id, hash_value, executions, parse_calls, invalidations, sql_text
from v$sqlarea v where sql_id in ('1jh104s4zswvp') or hash_value in (167539573);
SQL_ID HASH_VALUE EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_TEXT
------------- ---------- ---------- ----------- ------------- ---------------------
1jh104s4zswvp 167539573 86 90 1325129 truncate table my_gtt
execute immediate 'truncate table my_gtt'
and both above DATABASE Truncate Triggers are probably also called by "EXECUTE IMMEDIATE"
since v$db_object_cache.NAME and v$sqlarea.SQL_TEXT are formatted in lower and upper mixed cases.See Blogs:
Divide and conquer the true mutex contention
"library cache: mutex X" and Application Context
for more discussions.
4. XDB_PI_TRIG Trigger Components and Cost
Run GTT truncate with SQL Trace 10046:
alter trigger sys.AW_TRUNC_TRG disable;
alter trigger sys.XDB_PI_TRIG enable;
SQL > alter system flush shared_pool;
SQL > alter session set events '10046 trace name context forever, level 12';
SQL > exec gtt_truncate(1);
SQL > alter session set events '10046 trace name context off';
--SQL_ID: 86kwhy1f0bttn
SELECT /*+ ALL_ROWS */
COUNT (*)
FROM DBA_POLICIES V
WHERE V.OBJECT_OWNER = :B3
AND V.OBJECT_NAME = :B2
AND (V.POLICY_NAME LIKE '%xdbrls%' OR V.POLICY_NAME LIKE '%$xd_%')
AND V.FUNCTION = :B1
--SQL_ID: 1jh104s4zswvp
truncate table my_gtt
--SQL_ID: 1u4r80k42x8jj
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 1001511 and bitand(FLAGS, 128)=0
--SQL_ID: au7qd3c3yuk1u
LOCK TABLE "MY_GTT" IN EXCLUSIVE MODE WAIT 5
--SQL_ID: 9ff116syaf279
SELECT TO_NUMBER(XS_SYS_CONTEXT('XS$SESSION', 'SESSION_XS_USER_GUID')) FROM DUAL
--SQL_ID: au7qd3c3yuk1u
LOCK TABLE "MY_GTT" IN EXCLUSIVE MODE WAIT 5
SELECT /*+ ALL_ROWS */
*
FROM DBA_POLICIES V
WHERE V.OBJECT_OWNER = 'XDB'
--AND V.OBJECT_NAME = 'MY_GTT'
AND (V.POLICY_NAME LIKE '%xdbrls%' OR V.POLICY_NAME LIKE '%$xd_%')
AND V.FUNCTION in ('CHECKPRIVRLS_SELECTPF', 'CHECKPRIVRLS_SELECTPROPF');
5. Query against SYS_FBA_TRACKEDTABLES
We also noticed one query on SYS_FBA_TRACKEDTABLES having TOP Executions and Parse Calls. Both number are very close, which means that each Execution requires a Parse Call.
SQL > select sql_id, hash_value, executions, parse_calls, sql_text from v$sqlarea v
where sql_id in ('1u4r80k42x8jj') or hash_value in (2284757553);
SQL_ID HASH_VALUE EXECUTIONS PARSE_CALLS SQL_TEXT
------------- ---------- ---------- ----------- -------------------------------------------------------------------------------------------
1u4r80k42x8jj 2284757553 35663414 35536924 select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 1001511 and bitand(FLAGS, 128)=0
-- where 1001511 is MY_GT
SQL > select object_name, object_id, object_type from dba_objects where object_id = 1001511;
OBJECT_NAME OBJECT_ID OBJECT_TYPE
----------- ---------- ------------
MY_GTT 1001511 TABLE
Bug 15931756 - ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds) (Doc ID 15931756.8)
6. Disable/Drop Triggers
Trigger XDB_PI_TRIG is applied for Oracle XML Database (XDB), and AW_TRUNC_TRG for OLAP Analytic Workspaces(AW). If they are not installed/used, one can disable/drop them. These can be checked by:
select comp_name, version, status from dba_registry order by 1;
select * from v$option where parameter = 'OLAP';
select * from dba_feature_usage_statistics where name like '%XDB%' or name like '%OLAP%';
select * from dba_aws;
(Note: XDB_PI_TRIG trigger moved from schema XDB to SYS with PSU 11.1.0.7.16)
7. Boundary Value and Cursor Leak ?
Set session_cached_cursors to Boundary Value: 65535:
alter system set session_cached_cursors=65535 scope=spfile;
alter system set "_session_cached_instantiations"=1001 scope=spfile;
alter system set open_cursors=400 scope=spfile;
alter system set cursor_sharing=exact scope=spfile;
-- enable only one single truncate trigger
alter trigger sys.AW_TRUNC_TRG disable;
alter trigger sys.XDB_PI_TRIG enable;
-- Restart DB
--SQL> startup force
SQL (567) > exec gtt_truncate(65535);
set lines 400
column name format a50
set wrap off
select hash_value, locks, pins, locked_total, pinned_total, executions, child_latch, name
from v$db_object_cache v
where (hash_value in (2219505151) or (name like 'BEGIN%xdb.DBMS_XDBZ.%'))
and locks > 100;
HASH_VALUE LOCKS PINS LOCKED_TOTAL PINNED_TOTAL EXECUTIONS CHILD_LATCH NAME
---------- ------ ----- ------------ ------------ ---------- ----------- --------------------------------------------------
2219505151 65532 0 131070 131072 65535 0 BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dicti
2219505151 65533 0 131073 1 65535 62975 BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dicti
select hash_value, sql_id, child_number, users_opening, executions, parse_calls, invalidations
from v$sql v
where (sql_id in ('ct6c4h224pxgz') or hash_value in (2219505151))
and users_opening > 100;
HASH_VALUE SQL_ID CHILD_NUMBER USERS_OPENING EXECUTIONS PARSE_CALLS INVALIDATIONS
---------- ------------- ------------ ------------- ---------- ----------- -------------
2219505151 ct6c4h224pxgz 1 65531 65535 65535 1
select count(*) from v$open_cursor
where (sql_id in ('ct6c4h224pxgz') or hash_value in (2219505151))
and sid in (567);
COUNT(*)
---------
65530
select * from v$open_cursor
where (sql_id in ('ct6c4h224pxgz') or hash_value in (2219505151))
and sid in (567)
and rownum <= 3;
SID HASH_VALUE SQL_ID SQL_TEXT LAST_SQL SQL_EXEC CURSOR_TYPE CHILD_ADDRESS
---- ---------- ------------- ------------------------------------- -------- -------- ------------------------------- ----------------
567 2219505151 ct6c4h224pxgz BEGIN BEGIN IF (sys.is_vpd_enab DICTIONARY LOOKUP CURSOR CACHED 000000017636BA68
567 2219505151 ct6c4h224pxgz BEGIN BEGIN IF (sys.is_vpd_enab DICTIONARY LOOKUP CURSOR CACHED 000000017636BA68
567 2219505151 ct6c4h224pxgz BEGIN BEGIN IF (sys.is_vpd_enab DICTIONARY LOOKUP CURSOR CACHED 000000017636BA68
select s.program, s.sid, n.name p_name, t.value
from v$session s, v$sesstat t, v$statname n
where s.sid=t.sid and n.statistic# = t.statistic#
and name = 'session cursor cache count'
and s.sid in (567);
PROGRAM SID P_NAME VALUE
------------ ---- --------------------------- ------
sqlplus.exe 567 session cursor cache count 65535
select sql_id, child_number from v$sql_shared_cursor
where sql_id in ('ct6c4h224pxgz');
SQL_ID CHILD_NUMBER
------------- ------------
ct6c4h224pxgz 0
ct6c4h224pxgz 1
1. v$db_object_cache.LOCKS is close to 65535 2. v$sql.USERS_OPENING is close to 65535 3. v$open_cursor count is close to 65535 4. 'session cursor cache count' is 65535Contrary to Blog: ORA-04025 library object lock on AQ queue and SQL cursor
1. SESSION_CACHED_CURSORS (65535) sets hard limit of LIBRARY OBJECT LOCK,
instead of "_session_cached_instantiations".
2. OPEN_CURSORS has no effect.
v$open_cursor shows that one single session for one single cursor has 65535 CURSORS (opened and parsed, or cached),
all marked with CURSOR_TYPE: "DICTIONARY LOOKUP CURSOR CACHED".65535 is clearly over open_cursors=400, and according to Oracle Doc:
OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once.
It is not clear if this a Cursor Leak.
(MOS: Bug 25477055: INCORRECT CURSOR_TYPE IN V$OPEN_CURSOR FOR SESSION CACHED CURSOR said:
"DICTIONARY LOOKUP CURSOR CACHED" is misreported, it should be "SESSION CURSOR CACHED").
Immediately after above gtt_truncate(65535) terminated, run following statement in another session, it will raise ORA-04025:
SQL (1124)> exec gtt_truncate(100);
ORA-04025: maximum allowed library object lock allocated for BEGIN
BEGIN
IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
8. Simple Truncate Trigger Reproducing
We will use a (almost) empty Truncate Trigger to demonstrate that ORA-04025 is caused by the existence of Truncate Trigger, but irrelevant to its substances.
Disable (or Drop) both Oracle Truncate Triggers:
alter trigger sys.AW_TRUNC_TRG disable;
alter trigger sys.XDB_PI_TRIG disable;
create or replace trigger SYS.KSUN_TEST_TRIG
before drop or truncate ON database
BEGIN
IF 1=2 THEN
null;
END IF;
END;
/
alter system set session_cached_cursors=65535 scope=spfile;
alter system set "_session_cached_instantiations"=1001 scope=spfile;
alter system set open_cursors=400 scope=spfile;
alter system set cursor_sharing=exact scope=spfile;
SQL> exec gtt_truncate_job(2, 1e9);
column name format a80
set wrap off
SQL> select hash_value, locks, pins, locked_total, pinned_total, executions, name --v.*
from v$db_object_cache v
where locks > 1000
and (name like '%1=2%' or name like '%KSUN_TEST_TRIG%');
HASH_VALUE LOCKS PINS LOCKED_TOTAL PINNED_TOTAL EXECUTIONS NAME
---------- ------ ----- ------------ ------------ ---------- -----------------------------------------------------
2564248212 65514 0 65516 65517 65516 BEGIN IF 1=2 THEN null; END IF; END;
2564248212 65514 0 65516 1 65515 BEGIN IF 1=2 THEN null; END IF; END;
ORA-04025: maximum allowed library object lock allocated for BEGIN
IF 1=2 THEN
null;
END IF;
END;
9. Hidden Parameter: _system_trig_enabled
Oracle provided a hidden Parameter:
_system_trig_enabled are system triggers enabled default: TRUEto disable system triggers.
If it is deactivated by:
alter system set "_system_trig_enabled"=false;
select hash_value, locks, pins, locked_total, pinned_total, name --v.*
from v$db_object_cache v
where locks > 0
and (hash_value in (2219505151, 679830350)
or (name like '%xdb.DBMS_XDBZ.%' or name like '%aw_trunc_proc%'))
select a.obj#, a.sys_evts, b.name, a.*, b.*
from sys.trigger$ a, sys.obj$ b
where a.sys_evts > 0 and a.obj#=b.obj#
and name in ('XDB_PI_TRIG', 'AW_TRUNC_TRG');
(see MOS Doc ID 244524.1: How to find triggers being disabled with _SYSTEM_TRIG_ENABLED parameter)
10. Test Code
create global temporary table my_gtt (id number) on commit preserve rows;
create or replace procedure gtt_truncate(p_cnt number) is
begin
for i in 1..p_cnt loop
execute immediate 'truncate table my_gtt';
--dbms_lock.sleep(0.01);
end loop;
end;
/
create or replace procedure gtt_truncate_job(p_job_cnt number, p_cnt number) as
l_job_id pls_integer;
begin
for i in 1..p_job_cnt loop
dbms_job.submit(l_job_id, 'begin while true loop gtt_truncate('||p_cnt||'); end loop; end;');
end loop;
commit;
end;
/
create or replace procedure clean_jobs as
begin
for c in (select job from dba_jobs) loop
begin
dbms_job.remove (c.job);
exception when others then null;
end;
commit;
end loop;
for c in (select d.job, d.sid, (select serial# from v$session where sid = d.sid) ser
from dba_jobs_running d) loop
begin
execute immediate
'alter system kill session '''|| c.sid|| ',' || c.ser|| ''' immediate';
dbms_job.remove (c.job);
exception when others then null;
end;
commit;
end loop;
-- select * from dba_jobs;
-- select * from dba_jobs_running;
end;
/
