Day: August 30, 2007

Many a times you must have encountered the issue ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it.

This issue basically persists in PLSQL triggers. At one time I was also hit by the same issue. One of my friend “Nikhil Tungare – PLSQL Guru” helped me to get rid of this issue.

Here is how the error can be reproducible.

SQL> create table test1
2  (col1 varchar2(10),
3  col2 number);

Table created.

SQL> create table test1_audit
2  (col1 number,
3  time date);    

Table created.

SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  declare
5  id number;
6  begin
7  select col2 into id from test1
8  where col2 = :NEW.col2;
9  insert into test1_audit values (id, sysdate);
10  end;
11  /

Trigger created.

SQL> insert into test1 values(‘test’,1);
insert into test1 values(‘test’,1)
*
ERROR at line 1:
ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it
ORA-06512: at “SYSTEM.MUTAT_TRIG”, line 4
ORA-04088: error during execution of trigger ‘SYSTEM.MUTAT_TRIG’

The reason for this error is because, you have a table and you are inserting a row. Now as soon as you insert a row, a trigger is fired which will select the inserted data. This causes a problem because the data is inconsistent, it is not yet commited. Oracle engine allows only commited data to be queried.

This problem comes with row level trigger only, because row level trigger will gets fired immidiately as soon as you insert a row in a table. Statement level trigger will get fired after every statement.

To get rid of this problem and still use row level trigger, we have the solution as given below.

We need to create following triggers to avoide this issue.

1. After RowLevel

2. After Statement Level

Also we need to declare a global variable to store the value of ID that we will select. This global variable should be declared in package, so that we can access it when ever required.

Create a package, which will hold global variable

SQL> CREATE OR REPLACE PACKAGE test_package AS
2  id test1.col2%TYPE;
3  END;
4  /

Package created.

1) Creating After Row level trigger to populate the global variable with required value

SQL> create or replace trigger mutat_trig
2  after insert on test1
3  for each row
4  begin
5    test_package.id := :new.col2;
6  end;
7  /

Trigger created.

2) Create a After Statement level trigger to insert into test1_audit table.

SQL> create or replace trigger insert_audit
2  after insert on test1
3  begin
4  insert into test1_audit values (test_package.id, sysdate);
5  end;
6  /

Trigger created.

Trying to insert value now.

SQL> insert into test1 values(‘test’,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1_audit;

      COL1 TIME
———- ———
1 30-AUG-07

So always avoid selecting the value from the table, which is not yet committed. Thanks for Nikhil for helping me figure out this very closely.

Oracle Database 11g extends the functionality of Automatic Storage Management (ASM)in following areas

ASM Fast Mirror resynchronization:

When we take a disk offline in case the disk is corrupted or database is not able to read or write from the disk. In case of Oracle database 10g, oracle engine use to balance the other disks with the content of offline disk. This process was a relatively costly operation, and could take hours to complete, even if the disk failure was only a transient failure.
Oracle Database 11g introduces the ASM Fast Mirror Resync feature that significantly reduces the time required to resynchronize a transient failure of a disk. When a disk goes off line oracle engine doesn’t balance other disk, instead ASM tracks the allocation units that are modified during the outage. The content present in the failed disk is tracked by other disk and any modification that is made to the content of failed disk is actually made in other available disks. Once we get the disk back and attach it, the data belonging to this disk and which got modified during that time will get resynchronized back again. This avoids the heavy re-balancing activity. Following thigs should be noted for this feature

1) This feature requires that the redundacy level for the disk should be set to NORMAL or HIGH

2) The disk has to be take offline and should not be dropped.

3) You need to set DISK_REPAIR_TIME parameter, which gives the time it takes for the disk to get repaired. The setting for this attribute determines the duration of a disk outage that ASM tolerates while still being able to resynchronize after you complete the repair. The default time for this is set to 3.6 hours

To take the disk offline use
ALTER DISKGROUP … OFFLINE DISKS command

Example: ALTER DISKGROUP dgroupA OFFLINE DISKS IN FAILGROUP controller2 DROP AFTER 5H;

Repair time for the disk is associated with disk group. You can override the repair time of disk group using following command

ALTER DISKGROUP dgroupA SET ATTRIBUTE ‘DISK_REPAIR_TIME’=’3H’;

If you cannot repair a failure group that is in the offline state, you can use the ALTER DISKGROUP DROP DISKS IN FAILGROUP command with the FORCE option. This ensures that data originally stored on these disks is reconstructed from redundant copies of the data and stored on other disks in the same disk group.

ASM Preferred Mirror Read:

When you configure ASM failure groups, ASM in Oracle Database 10g always reads the primary copy of a mirrored allocation unit. It may be more efficient for a node to read from a failure group allocation unit that is closest to the node, even if it is a secondary allocation unit.

This is especially true in RAC-extended cluster configurations, where reading from a local copy of an allocation unit provides improved performance.

With Oracle Database 11g, you can do this by configuring preferred mirror read using the new ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter to specify a list of preferred mirror read names.

This is a multivalued parameter and should contain a comma-delimited string of failure group names. The failure group names specified should be prefixed with its disk group name and a “.” character.
A new column, PREFERRED_READ, has been added to the V$ASM_DISK view. If the disk group that the disk is in pertains to a preferred read failure group, the value of this column is Y.

ASM Scalability and Performance Enhancements:

In 11g ASM supports variable size allocation units for file. Sizes for allocation units are 1, 4, 16, 64MB. For example when we first create a file in ASM, it will begin with an allocation unit of 1MB and as the size of the file increases and crosses the pre-determined file size threshold, ASM uses next allocation units size for assigning allocations. Advantage with this type of architecture is that there will be less number of allocation pointers needed to describe the file, less memory is requiired to map the memory unit allocation in shared memory.

Using variable size allocation units enables you to deploy Oracle databases using ASM that are several hundred terabytes (TB), even several petabytes (PB) in size. The management of variable size allocation units is completely automated and does not require manual administration.
Also if there are some non contiguous small allocation units are left inbetween the large blocks then those are defragmented during rebalancing operation. Also ASM will also automatically defragment during allocation, if the desired size is unavailable, thereby potentially affecting allocation times, but offering much faster file opens, given the reduction in the memory required to store file allocation units.

SYSASM:

Oracle Database 11g introduces a new privilage SYSASM to manage ASM specific task. SYSASM is in DBA installation group. Current release 1 of Oracle 11g will allow the user to use SYSDBA provilage to manage ASM task, but going forward in next release SYSDBA privilage will be restricted to ASM instance. However, SYSDBA will be available for all other database administration.

You can use the combination of CREATE USER and GRANT SYSMAN SQL statements from an ASM instance to create a new SYSASM user. This is possible as long as the name of the user is an existing OS username.

A new column SYSASM is been added in the V$PWFILE_USERS.

ASM Disk Group Compatibility:

Basically there are 2 level of compatibility setting for database. Lets clarify the same here.

RDBMS compatibility:

This is the minimum compatible version of the RDBMS instance that would allow the instance to mount the disk group. This compatibility dictates the format of messages that are exchanged between the ASM and database (RDBMS) instances. An ASM instance can support different RDBMS clients running at different compatibility settings.
Database instances are typically run from a different Oracle home than the ASM instance. This implies that the database instance may be running a different software version than the ASM instance. When a database instance first connects to an ASM instance, it negotiates the highest version that they both can support.

ASM compatibility:

It is the persistent compatibility setting controlling the format of data structures of ASM metadata on disk. The ASM compatibility level of a disk group must always be greater than or equal to the RDBMS compatibility level of the same disk group. ASM compatibility is concerned only with the format of the ASM metadata.
For example, the ASM compatibility of a disk group can be set to 11.0, whereas its RDBMS compatibility could be 10.1. This implies that the disk group can be managed only by the ASM software whose software version is 11.0 or later, whereas any database client whose software version is later than or equal to 10.1 can use that disk group.

The compatibility of a disk group is an irreversible operation. Once you set the compatibility, you can not bring it down. You can further advanced it.