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):

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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, не использующих литералы.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

/* 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

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

?

1 2	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 соответственно.

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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)