Upgrading to Oracle 11.2 – BGOUG’11

 Oracle  Comments Off on Upgrading to Oracle 11.2 – BGOUG’11
Nov 202011
 

My presentation “The adventure of upgrading to 11.2” from BGOUG seminar in Hisar can be downloaded – here

I’d like to thank once again to Milena, SPI and all other people that invest lots of time and effort to make BGOUG seminars come true. You’ve done a great job, again!

 Posted by at 11:50

11g: no more “AUDIT … BY SESSION”

 Oracle  Comments Off on 11g: no more “AUDIT … BY SESSION”
Nov 162011
 

Another interesting “feature” coming with the upgrade from 10g to 11g.

During the automated pre-upgrade tests we have noticed, the audit trail table grows rapidly and one part of the IO load is caused by inserts in SYS.AUD$.

We found the following Metalink note:
Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database [ID 1171314.1]

1.) Starting with Oracle 11g the BY SESSION clause is obsolete. This is documented in the Database Security Guide :

” The BY SESSION clause of the AUDIT statement now writes one audit record for every audited event. In previous releases, BY SESSION wrote one audit record for all SQL statements or operations of the same type that were executed on the same schema objects in the same user session. Now, both BY SESSION and BY ACCESS write one audit record for each audit operation. In addition, there are separate audit records for LOGON and LOGOFF events. ”

Because of the above change it is expected that the number of the audit records/files will grow considerably.

In the documentation we find the following amboguity:
Oracle® Database SQL Language Reference 11g Release 1 (11.1)

BY SESSION

In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. If you specify neither clause, then BY SESSION is the default.

The same can be seen in Oracle® Database SQL Language Reference 11g Release 2 (11.2)

It does not get better in Oracle® Database Security Guide 11g Release 2 (11.2)

Benefits of Using the BY ACCESS Clause in the AUDIT Statement

By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.)

Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows:

To be honest, this sounds to me like “Errrr… something broke with this BY SESSION. We managed to make it work somehow, but now it acts almost like BY ACCESS”

So we had to redefine our audit policy before the upgrade. Fortunately most noaudit operations can be executed online. Only noaudit execute uses some nasty latches and we had to execute those during the downtime.

Here is one useful query about this:

select 'noaudit select on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where sel like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit execute on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where exe like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit update on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where upd like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit insert on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where ins like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit delete on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where del like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')

And one more, important: do not forget to check what is written in ALL_DEF_AUDIT_OPTS before the upgrade. If you do not do this, catupgrd will create thousands objects with inappropriate auditing.

 Posted by at 12:04

Sessions blocked by TM locks after upgrade to 11g

 Oracle  Comments Off on Sessions blocked by TM locks after upgrade to 11g
Nov 022011
 

Well, this is something new. Usually every new version of Oracle Database comes with fewer blocking scenarios than the previous. But this is a new behavior, introduced with Oracle 11g.

Some people would say this is a problem with application design. Indeed, having a non-indexed foreign keys is a well known no-no. But with 10g it would lead to slower deletes from parent table, or updates of primary key on parent, in order to have the child scanned. And now we have a whole new story – enqueue wait, which can last as long as you want. A simple INSERT on parent blocks any UPDATEs and DELETEs from other sessions, until the INSERTing session commits. To make things worse, this have hit us with 724-row child table, which was full scanned in an instant before, so the “performance” penalty was unnoticed.

* * *

Here is the whole story. Note 1343365.1 introduces the following

There is change in lock modes behavior introduced intentionally by fix 5909305 in 11.1 onward.

Change has been made to DML (TM) lock modes for foreign key constraints (Doc ID 5909305.8).

The change in behavior mentioned in note 5909305.8 , introduces higher level of lock on parent table while running DML against Child table. ( SX vs SS )

There are cases unrelated to this bug which lack of indexes on foreign key columns increase the time locks take place. This reflect as wait on TM-enq.

Sounds quite innocent. The behavior of SS and SX locks is almost the same. There are two differences – SX is not “compatible” with S (Share) and SSX (Share Sub-eXclusive). But those two are not seen with DML. Usually.

Here comes the unindexed foreign key. This is the only DML I know, that puts Share lock on whole table. And with the following scenario I managed to get locked every time on 11.2.0.2:

1. Prepare data

drop table t2 purge;
drop table t1 purge;

create table t1 (pk number primary key);

create table t2 (pk number primary key, fk number references t1(pk));

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);

insert into t2 values (11, 1);
insert into t2 values (21, 2);
insert into t2 values (22, 2);
insert into t2 values (31, 3);
insert into t2 values (32, 3);
insert into t2 values (33, 3);

commit;

2. Put the trap form Session 1

insert into t1 values (5);

If you look at v$lock, you will see Session 1 holds 3 locks (and a few more in 11g, but they are not interesting)
– mode 6 (eXclusive) TX lock on the newly inserted row
– mode 3 (Sub eXclusive) TM lock on table t1. This is our parent table.
– mode 2 (Sub Share) on 10g or Mode 3 (Sub eXclusive) on 11g, TM lock on table t2. This is our child table. Note the lock is on the table itself, and it doesn’t matter if there are any rows affected on the child. Also note there is one such lock on every table that has a foreign key, pointing to our parent.
(if there were child records affected by cascade delete, for example, we would have TX locks on them, which is expected)

3. Try to delete or update one row on parent from Session 2:

delete from t1 where pk=4;

or

 update t1 set pk=2 where pk=2;

In 10g the statements work just fine. But on 11g both statements and hanging forever, waiting to acquire Mode 4 (Share) lock on child table – because the foreign key column is not indexed.

How to diagnose this issue:
First of all, we see the sessions waiting for TM locks in Grid Control. The color is red, so they are easily noticed. We can also get the session information form V$SESSION:

 SQL> select sid, final_blocking_session
  2    from v$session
  3   where event = 'enq: TM - contention';

       SID FINAL_BLOCKING_SESSION
---------- ----------------------
       132                    332

In order to find the resource, causing the problem (on other words – the unindexed table), I use the following query:

 SQL> select SID, type, id1, lmode, request, block
  2    from v$lock
  3   where sid in (132, 332)
  4     and type = 'TM';

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       132 TM       950744          3          0          0
       132 TM       950746          0          4          0
       332 TM       950744          3          0          0
       332 TM       950746          3          0          1

On line number 4 we see BLOCK=1, which indicates this is a blocker. On line number 2 we have REQUEST=4 and LMODE=0 – this means we want mode 4 lock, but we cannot get it. The column ID1 in this case shows the object ID (for TM locks):

 SQL> select owner, object_name, object_type
  2    from dba_objects
  3   where object_id = 950746;

OWNER      OBJECT_NAM OBJECT_TYPE
---------- ---------- -------------------
YAVOR      T2         TABLE

The solution is easy, once you know the problem. Simply create an index on child table’s foreign key column

 create index t2_fk on t2(fk);

When we create the index, the UPDATE and DELETE statement do not need mode 4 (S) lock on the child table. Instead they use the index to acquire mode 3 (SX) lock on the child, which is compatible with the other SX on the same object.

Now, this is kind of hard to explain to developers. To be honest, even I do not understand how the index helps in converting the need of S lock to SX. We do not affect any rows on child here. But this is how it works.

 Posted by at 21:50