I received a call from one of my friends last night asking for an opinion on the production issue that he was facing -
Issue:
The end user of the application called in a ticket stating that there is a delay in showing OLTP transactions in a reporting system after the database upgrade.
Known facts:
- The database was upgraded from 10g R2 (10.2.0.4) to 11g R1 (11.1.0.7) and the upgrade was smooth, no issue were reported for about 24 hours from the upgrade except this ticket.
- One of the packages was modified and few indexes were added to speed up the adhoc SQL queries on log and history tables.
Analysis:
The facts that I had on hand (i.e. heard over phone) was not of much help in triaging the issue. To start with, we have decided to exclude the “upgrade” from the root cause because all the verification checks were passed prior to releasing the system for use. What’s left to check is (1) figure out what is happening in the system and (2) check the list of objects that were modified as part of the release after the upgrade.
I usually triage performance issues by enabling the SQL tracing – Since there were about 50 concurrent sessions – I had to put aside the idea of SQL tracing for a moment. My next recommended option is to take look at the V$ tables, while examining those, especially V$LOCK; showed a major contention on one of the log tables. There were about 30 connections from MQ messaging layer waiting for each other and the number of connections waiting for each did not go down over a period of time. By looking at V$SQL, we could say that the only DML statement executed against this table is an INSERT statement
INSERT is blocking another INSERT – Bingo! One of the indexes pushed into production to speed up SQL queries had a BITMAP Index on the message status column.
Fix
Dropped the BITMAP index and created B*Tree index on message status column.
About Bitmap Indexes:
The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in complex conditions in the WHERE clauses of queries.
Bitmap indexes can substantially improve performance of queries that have all of the following characteristics:
- The
WHEREclause contains multiple predicates on low- or medium-cardinality columns. - The individual predicates on these low- or medium-cardinality columns select a large number of rows.
- The bitmap indexes used in the queries have been created on some or all of these low- or medium-cardinality columns.
- The tables in the queries contain many rows.
Bitmap indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. For such applications, bitmap indexing provides:
- Reduced response time for large classes of ad hoc queries
- Reduced storage requirements compared to other indexing techniques
- Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory
- Efficient maintenance during parallel DML and loads
Simple demonstration below:
- Session1: (SID 125)
SQL> SELECT sid FROM v$session WHERE audsid = sys_context('userenv','sessionid');
SID
----------
125
SQL> CREATE TABLE mq_message_log (message_status varchar2(1));
Table created.
SQL> CREATE BITMAP INDEX mq_message_log_idx_01 on mq_message_log(message_status);
Index created.
SQL> INSERT INTO mq_message_log (message_status) VALUES ('Y');
1 row created.
- Session2: (SID 149)
SQL> SELECT sid FROM v$session WHERE audsid = sys_context('userenv','sessionid');
SID
----------
149
SQL> INSERT INTO mq_message_log (message_status) VALUES ('Y');
Session2 will hang until the Session1 issues a commit or rollback.
- If you open third session and examine the V$LOCK, which will clearly show Session 1 is blocking Session2
SQL> SELECT l1.SID ||' is blocking '||l2.SID blocking_session 2 FROM v$lock l1, v$lock l2 3* WHERE l1.BLOCK = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2 SQL> / BLOCKING_SESSION -------------------------------------------------------------------------------- 125 is blocking 149
It reminds me of a centuries old proverbial saying, “there is a time and place for everything”.
