ORA-00060: deadlock detected while waiting for resource ( Deadlock due to Bitmap Indexes)

Yesterday I was testing the below scenario with development team using Bitmap indexes .

Concept :-

Generally Bitmap index are created in Data warehouse environments where database activity is mostly read and writing is significantly less.

Bitmap indexes are created on columns with less cardinality , so one row of a bitmap index points to multiple rowid's to the column in the table , irrespective of B-tree indexes which points to only one row id.

Now any DML on a column having bitmap index ends up locking many rows in the index.

Every record for a column having bitmap index, there will be a index record created for that which will point to the number of rows in that column having that value.

For Eg , Gender Column will have 2 distinct values, Male and Female. If we put a Bitmap index on Gender column then index created will have two index records, Male and Female , and each index records will have detail for all the column values.

If Column's 1st, 5th and 9th record has value as Male then the index record will have value '1' in 1st , 5th and 9th record and '0' in the other.

Same goes for the Female Index record also, if 2nd, 4th, 3rd and 6th have value as Female then the index record will have '1' in 2nd, 4th, 3rd and 6th record and 0 in other.

Especially concurrent single row DML operations goes for a deadlock.

What happens when we try to insert same value to a column having Bitmap index associated to it from different sessions. PFB detail:-

When we try to insert same value from different sessions, both session tries to aquire lock for the same region of Bitmap index to create a index record, resulting in Deadlock.

Below is a demo for that :-

SQL> create table test_bitmap ( name varchar(10),age number);

Table created.

SQL> create bitmap index test_bitmap_idx on test_bitmap(age);

Index created.

Session 1:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)
Waiting ……………………


Session 2:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)
insert into test_bitmap values ('rick',34)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Generally this is not the case with normal index while doing insert or any DML.

Solution :-

Prefer not to do insert/update/delete on columns having Bitmap indexes, if Business requirement is there then remove the Bitmap index from that column.

I hope this article helped you.

Thanks
Amit Rath

How to purge/flush a single SQL PLAN from shared pool in Oracle

Purging a SQL PLAN from shared pool is not a frequent activity , we generally do it when a query is constantly picking up the bad plan and we want the sql to go for a hard parse next time it runs in database.

Obviously we can pass a hint in the query to force it for a Hard Parse but that will require a change in query , indirectly change in the application code , which is generally not possible in a business critical application.

We can flush the entire shared pool but that will invalidate all the sql plans available in the database and all sql queries will go for a hard parse. Flushing shared pool can have adverse affect on your database performance.

Flush the entire shared pool :-

Alter system flush shared_pool;

Flushing a single SQL plan from database will require certain details for that sql statement like address of the handle and hash value of the cursor holding the SQL plan.

Steps to Flush/purge a particular sql plan from Shared pool :-

SQL>  select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '';

ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

Now we have the address of the handle and hash value of the cursor holding the sql. Flush this from shared pool.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL>  select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '';

no rows selected

SQL plan flushed for above particlar sql, Now next time above sql/query will go for a hard parse in database.

I hope this article helped you.

Thanks
Amit Rath

Relocating Service in Oracle RAC Database

Relocating Services in Oracle RAC is mainly used for maintenance purpose.

Maintenance purpose :- When for Patching(OS/Oracle), we wanted to do it in a rolling fashion, one node at a time as we don't have downtime for the application, we have to relocate service temporarily to other nodes of the cluster.

We can also permanently move a service from one node to another node. We will show both the methods in this article 

Temporary relocation of a Service in Oracle :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
Service is enabled
Server pool: amit_app_prd
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

==>srvctl status service -d AMIT
Service app_prod is running on instance(s) AMIT1

We can see from above that app_prod service is running on AMIT1 instance , now we wanted to do patching on AMIT1 instance.

We will relocate service temporarily to AMIT2 instance and once our maintenance work is done we will relocate it back. 

Relocating a service will not modify the preferred instance and available instance details for a service . This is a temporary job.

We can relocate service temporarily using below methods :-

Using Database instance :-

==>srvctl relocate service -d AMIT -s app_prod -i p1end1 -t p1end2

Using Node details :-

==>srvctl relocate service -d AMIT -s app_prod -c NODE1 -n NODE2

If you want to disconnect all session while relocating the service , we can use -f clause.

Now check the configuration of the service , it's the same :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
.......
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

Once maintenance job is done, relocate it as it was before using same command.

Permanent relocation of a service :-

When we have to permanently relocate a service then we have to modify the config parameters for the service , in this case the preferred instance and available instance details will be changed.

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
Service is enabled
Server pool: amit_app_prd
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

Now modify the service for preferred instance as AMIT2

=> srvctl modify service -d AMIT -s app_prod -i AMIT2 -a AMIT1

if multiple instances then we can use comma(,)

=>  srvctl modify service -d AMIT -s app_prod -i AMIT2,AMIT3 -a AMIT1,AMIT4

we can use -f to disconnect all active session in the relocation process.

Let's see if configuration modified or not :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
..................
Edition:
Preferred instances: AMIT2
Available instances: AMIT1

It's modified permanently

I hope this article helped you

Thanks

Amit Rath

ORA-25191: cannot reference overflow table of an index-organized table

Yesterday I faced below issue when I was trying to grant some privileges to a table :-

grant select,insert,update,delete on AMIT.SYS_IOT_OVER_14456 TO TEST;
                                             *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Issue :-

This issue comes when we deal with Index Organized tables(IOT).

Solution :-

Issue this statement against the Parent Index Organized table containing the specified overflow table.

SQL> select IOT_NAME from dba_tables where owner='AMIT' and table_name='SYS_IOT_OVER_14456';

IOT_NAME

------------------------------

AMIT_TABLE

SQL> grant select,insert,update,delete on AMIT.AMIT_TABLE to TEST;

Grant succeeded.

Any operation related to Index Oraganized table has to be done by above method else it will give ORA-25191.

I hope this article helped you.

Thanks

Amit Rath

How to force a SQl query to do a hard parse in Oracle

As a DBA we all know how to force all sql queries for a hard parse.

Just Flush the Shared pool and all sql's already present in shared pool will be purged and hence forth if a new sql comes it will go for a hard parse.

But what if I want to force hard parse for a single sql statement in a Production Box. I cannot flush the shared pool a sit lead to performance issues in production. All new sql's will go for a hard parse and performance will be degraded.

I just did a POC for this that how we can force hard parse for a particular sql.

I agree we cannot flush Shared pool as it leads for performance problems. But we can purge a single SQL statement from Shared pool so that when that sql comes again it goes for a hard parse. Reason for this is , as we know wheneven a sql comes to Oracle , it first checks in shared pool that whether this sql was already executed. If Oracle finds this sql it uses the same execution plan again, this is called Soft Parse else it goes for Hard parse. I will writing more on this topic in another article.

Force a SQL for Hard Parse :-

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                /             
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                       13
parse time elapsed                                                 14
parse count (total)                                                 153
parse count (hard)                                                 74
parse count (failures)                                              2
parse count (describe)                                             0

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                 /
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

9 rows selected.

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                          13
parse time elapsed                                                    14
parse count (total)                                                    155
parse count (hard)                                                    74
parse count (failures)                                                2
parse count (describe)                                               0

As we can see we ran above query twice and its not going for a hard parse. Its taking the existing execution plan from shared pool. PFB :-

From above , its clear that sql is already present in shared pool and whenever you execute this sql, it will go for a soft parse.

Now we remove this sql details from shared pool

SQL>  select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9ag79yf9mmh3p';

ADDRESS          HASH_VALUE

---------------- ----------

000000015EF29F38 2470035573

SQL> exec DBMS_SHARED_POOL.PURGE ('000000015EF29F38,2470035573','C');

PL/SQL procedure successfully completed.

As we can see from above, sql details deleted from Shared pool.

Now we will again check for the same query

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                     14
parse time elapsed                                               19
parse count (total)                                               163
parse count (hard)                                               80
parse count (failures)                                          2
parse count (describe)                                         0

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                /
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

9 rows selected.

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                           14
parse time elapsed                                                     19
parse count (total)                                                     164
parse count (hard)                                                     81
parse count (failures)                                                 2
parse count (describe)                                                0

6 rows selected.-

So we can see now it goes for a hard parse . 

I hope this article helped.

Regards,

Amit Rath

Alter System Switch Logfile VS Alter System Archive Log Current

Whenever I did a logswitch in past, one thought came in my mind that what's the difference between ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT.

Both commands force a log switch when fired. But the way of doing is different. Please find below details :-

When we fire an ALTER SYSTEM SWITCH LOGFILE, Command completes in less than a second and we get the control back. Reason for this is, it does not wait for ARCH process to write Current Online redo log file to Achive destiation File System. The writing happens in background. So Commad completes in less than a second.

Once this command fired and completed database immediately starts writing to next redo log file . 

But when we fire ALTER SYSTEM ARCHIVE LOG CURRENT, it waits for the ARCH process to write current online Redo Log to File system, Once it gets the acknowledge that redo log has been archived, it gives the control back. Till then it pauses, once writing completed it gives back the control.

In a RAC database both commands differs slightly. PFB :-

ALTER SYSTEM SWITCH LOGFILE when fired from an instance, it switched the current redo log of that particular instance where as ALTER SYSTEM ARCHIVE LOG CURRENT will do a redo log switch for all available instances. In RAC , ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice to use.

For RAC database Production backup scripts ,  ALTER SYSTEM ARCHIVE LOG CURRENT is best pactice to use with , as its waits for an acknowledgement from OS that whether last redo has been written to archive destination and we can be pretty sure that our backup contains the last redo from all theads.

Using ALTER SYSTEM ARCHIVE LOG CURRENT we can spcify which thread to archive, if we leave the thead parameter in the commad, it will so a redo log switch for all threads. This facility is not available with ALTER SYSTEM SWITCH LOGFILE.

PFB detailed analysis :-

SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.02
SQL> alter system archive log current;

System altered.

Elapsed: 00:00:10.40

From above we can see there is much time difference between both commands.

Thread parameter in ALTER SYSTEM ARCHIVE LOG CURRENT command :-

SQL> alter system archive log thread 2 current;

System altered.

I hope this article helped you.

Regards,

Amit Rath

Concept of ROWNUM, Why ROWNUM=2 gives "no rows selected

ROWNUM is a psuedocolumn , its value is not predefined in a table. A number is assigned in the form of 1,2,3,...N to every row returned by a query when its executed, that number is called as ROWNUM.  Its generated dynamically.

Example :- Consider a table having 5 rows. PFB output of below select queries :-

1. SQL> select * from amit;

        ID NAME
---------- ------------------------------------
         1
         2
         3
         4
         5

2. SQL> select * from amit where rownum=1;

        ID NAME
---------- ------------------------------------
         1

3. SQL> select * from amit where rownum<3;

        ID NAME
---------- ------------------------------------
         1
         2

4. SQL> select * from amit where rownum=2;

no rows selected

5. SQL> select * from amit where rownum>2;

no rows selected

We see that when we use Rownum in Where clause then it gives output only for rownum<3 and Rownum=1 . But when we we use rownum=2 and rownum>2 it gives output as "no rown selected". 

Reason for this behaviour of rownum is that its value is not predefined. when a select query fetch some output then a number is assingned in the form of 1,2,3...N to all rows fetched by query.First selected row is always assigned as rownum=1.

Rownum value is incremented after a query passes the where clause.

So when we use rownum=1 or rownum<3 in where clause then where condition is matched as rownum=1 at that time value of rownum is 1 so it passes the where condition and gives us the output. IF rownum < 3 has been used in where clause then it gives us output for rownum=1 as it passes where clasuse, now rownum value incremented and becomes 2 again it passes where clause (rownum<3 ). Now  rownum value incremented and becomes 3, when rownum value becomes 3 then it fails the where clause(rownum<3) and gives us only two rows as output.

But when We have rownum=2 or rownum>2 then numbers is assigned to the output as 1,2,3..N , when first row's rownum which is 1 is matched with rownum=2 or rownum>2 then at this time rownum has value as 1 and it fails the where condition(rownum=2 or rownum>2) criteria on first attempt and gives us output as "no rows selected" 

We have to remember this statement that "Rownum is not preassigned in table"

I hope this article helped you.

Regards,
Amit Rath

Local Listener vs Remote Listener in Oracle RAC

Concept of Local listener and Remote Listener comes with Oracle RAC database where a single database has multiple instances. PFB example to understand the concept of Local Listener and Remote Listener.

Consider a two node RAC database. It has following components :-

1. Two nodes named NODE1 and NODE2
2. Two listeners on both nodes named LISTENER1 on NODE1 and LISTENER2 on NODE2.
3. Two instances of database on both nodes named ORCL1 on NODE1 and ORCL2 on NODE2 and database name is ORCL

Now Listener1 is running on Node1 where database instance Orcl1 is present. Listener1 is considered as Local Listener for Orcl1 instance as its present on the same node where Orcl1 is running and Listener2 is considered as Remote Listener for Orcl1 instance as its running on different node as the database instance Orcl1.

Similarly, Listener2 is considered as Local Listener for Orcl2 instance as its present on the same node where Orcl2 is running and Listener1 is considered as Remote Listener for Orcl2 instance as its running on different node as the database instance Orcl2.

Purpose of Remote Listener is to connect all instances with all listeners so the instances can propogate their load balance advisories to all listeners. Listener uses the advisories to decide which instance should service client request. If listener get to know from advisories that its local instance is least loaded and should service client request then listener passes client request to local instance. If local instance is over loaded then listener can use TNS redirect to redirect client request to a less loaded instance means remote instance. This Phenomenon is also called as Server Side Load balancing.

PFB steps to configure Local and Remote listener configuration for both instances :-

Node1 :-

orcl1.local_listener=(address of listener_host1)
orcl1.remote_listener=(addresses of both listener_host1 and listener_host2)

Node2 :-

orcl2.local_listener=(address of listener_host2)
orcl2.remote_listener=(addresses of both listener_host1 and listener_host2)

Without Configuration of Remote Listener, each listener will only know about its local instance only and it don't have any knowledge about the load of remote instance. Listener will direct the Client request to local instance only whatever may be the load on local instance. Without this configuration we only left with Client Side Load Balancing.

I hope this article helped you.

Regards,
Amit Rath