event 10309

[oracle@oracle ~]$ oerr ora 10309
10309, 00000, "Trigger Debug event"
// *Document: NO
// *Cause:
// *Action:
// *Comment: This event replaces the earlier event number 10250
//           which had multiple definitions

Test case (run on 11.2.0.3):

drop user tc cascade;
grant connect to tc identified by tc;
grant alter session to tc;
grant create table to tc;
grant create trigger to tc;
alter user tc quota 100M on users;

conn tc/tc
create table t(x int);
create trigger t_bi
  before insert on t
declare
begin
  null;
end;
/
create trigger t_bir 
  before insert on t
  for each row
declare
begin
  null;
end;
/
create trigger t_air 
  after insert on t
  for each row
declare
begin
  null;
end;
/
alter session set events '10309';
insert into t values (0);
select value from v$diag_info where name='Default Trace File';

Trace file:

*** 2014-07-21 09:42:05.051
*** SESSION ID:(76.9819) 2014-07-21 09:42:05.051
*** CLIENT ID:() 2014-07-21 09:42:05.051
*** SERVICE NAME:(SYS$USERS) 2014-07-21 09:42:05.051
*** MODULE NAME:(SQL*Plus) 2014-07-21 09:42:05.051
*** ACTION NAME:() 2014-07-21 09:42:05.051

--------Dumping Sorted Master Trigger List --------
Trigger Owner : TC
Trigger Name : T_AIR
Trigger Owner : TC
Trigger Name : T_BIR
Trigger Owner : TC
Trigger Name : T_BI
--------Dumping Trigger Sublists --------
 trigger sublist 0 :
Trigger Owner : TC
Trigger Name : T_BI
 trigger sublist 1 :
Trigger Owner : TC
Trigger Name : T_BIR
 trigger sublist 2 :
 trigger sublist 3 :
Trigger Owner : TC
Trigger Name : T_AIR
 trigger sublist 4 :
--------Dumping Sorted Master Trigger List --------
Trigger Owner : TC
Trigger Name : T_AIR
Trigger Owner : TC
Trigger Name : T_BIR
Trigger Owner : TC
Trigger Name : T_BI
--------Dumping Trigger Sublists --------
 trigger sublist 0 :
Trigger Owner : TC
Trigger Name : T_BI
 trigger sublist 1 :
Trigger Owner : TC
Trigger Name : T_BIR
 trigger sublist 2 :
 trigger sublist 3 :
Trigger Owner : TC
Trigger Name : T_AIR
 trigger sublist 4 :

*** 2014-07-21 09:42:05.073
Firing Insert Before Table Trigger. Name: T_BI Owner TC
Firing Insert Before Row Trigger. Name: T_BIR Owner TC
Firing Insert After Row Trigger. Name: T_AIR Owner TC

Bug 15931756 - ORA-4031 Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds)

В одной из промышленных БД версии 11.2.0.3.7 обнаружил большое количество системных SQL, не использующих литералы.

/*
Find queries that are not using bind variables
*/
select s.*, 
      (select sql_text from v$sqlarea where force_matching_signature=sig and rownum=1) sql_text
  from (
       select inst_id, to_char(force_matching_signature) sig,
              count(exact_matching_signature) cnt
         from (
              select inst_id, force_matching_signature, exact_matching_signature
                from gv$sql
               group by inst_id, force_matching_signature, exact_matching_signature
              )
        group by inst_id, force_matching_signature
       having count(exact_matching_signature) > 1
        order by cnt desc
       ) s
 where rownum <= 10

В выводе оказалось множество запросов вида:

select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865226
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 1864581 and bitand(FLAGS, 128)=0

С кол-вом подобных курсоров в кол-ве 501 и 399 соответственно.

SQL> select sql_text from v$sql where force_matching_signature=15893216616221909352 and rownum<=10;
SQL_TEXT
---------------------------------------------------------------------------------
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865226
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865182
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865129
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864747
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864989
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865069
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864718
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864786
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864815
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864679

Поиск на MOS по SYS_FBA_TRACKEDTABLES тут же выдает, что имеет место быть баг: Bug 15931756 - ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds) (Doc ID 15931756.8)