Master Yavor from Tryavna

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.

(tested on 10.2.0.4.1)
It’s Friday now, and I am watching something interesting. I will share it very briefly.

I made an empty table with the idea to fill it. Nothing uninteresting – it has just a primary key (in other words – index), a few partitions, some of them are compressed.

Then I began to fill it up with data – a few hundred million rows. I used: INSERT / * + APPEND PARALLEL (T, 8) * / INTO ...

At one point it flashed upon me: “Wow, I’ve done it wrong … I had to create the table without a primary key, to skip index maintenance during the data load”

Yes and no. My observations showed that, contrary to my expectation, the database first drew the data, all of it, then built the index. I saw this by the size of the segments – first table partitions inflated (several gigabytes), during which time the segment of the index had only its initial extent. And then the segment of the index began to grow and became a few gigabytes.

And this is not CTAS – it is INSERT.

A quick reminder: I dont know why, but expdp 10.2 does not work with tables in the following schemas: SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP и WMSYS. I’ve hit this while trying to export sys.aud$, in order to transfer it to another database for analysis. Nope – this cannot be done in 10.2. The problem is described in Metalink Note 553402.1, as well as here

This is very hasty. One way to workaround this is using the good old exp / imp. Unfortunately they are way slower on tables with LOB columns. Also exp/imp do not work across db link, and setting up REMAP_TBLESPACE is another adventure. One other possibility is using create table as select – not a great idea for big tables and heavily loaded systems.

One more interesting fact: expdp makes more than 300 megabytes physical reads before failing with “Schema SYS was not found”.

I’m back at the system load topic. I was thinking about this quite some time, and now I have it. I will post it here – to share and to remember.

The task is: find the values of some system statistic based on the AWR reports (in this example – PGA usage) – for the last, say, 60 days. It wouldn’t be right to compare the load at 3 PM to the one at 3 AM. So I want to check only reports, standing +/- 2.1 hours from the current time of day. In other words, st 3 AM I want the values from 1 to 5 AM during the last 60 nights.

And here is the query:

select p.value
  from dba_hist_pgastat p, dba_hist_snapshot s
 where s.snap_id = p.snap_id
   and p.name = 'total PGA allocated'
   and s.end_interval_time > sysdate - 60
   and abs(remainder(cast(s.end_interval_time as date)-sysdate, 1)) < 2.1/24;

It works like this
- take the difference between sysdate and the date of the awr snapshot. The result is in days. Works for all dates - older and newer then the current
- get only the time (in other words, repove the "day" part) using remainder. The beauty of this function is, it works in "limit" cases. E.g. in 1 AM you can take 11 PM from the day before (and vise versa). I did not expect to have such an easy and elegant solution! 🙂
- compare with the given limit, in our case - 2.1 hours (2.1/24)

The you can run the resulting number through the query, which I posted last year, to check if the current value is "normal" or suspicious.

The key here is to keep enough AWR reports history. The statistics I use will work only for 30 or more "observations" (as someone noted last year)

The error appears upon starting the ASM instance mounting a diskgroup manually

ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
. . .
ORA-15038: disk **** mismatch on 'Time Stamp' with target disk group [1752361090] [1751191692]


THis happend to us right after an upgrade of the ASM an was kind of thrilling. I thought we damaged some of the disk headers during the upgrade.

But the problem is far smaller. You just need to fix the value of asm_diskstring. With the right string, it works like charm.

11.1 would show far less shocking error message in this case…