Buffer Busy Waits in Oracle

Last week, I was working with a sample program to test the performance of an oracle query under load. The program spawns multiple threads and each thread will execute the same select statement against the database. When I tried the sample program with just one thread, the select statement gave a response of 20 milliseconds. When the number of threads was increased to 60, the response time increased to 1 second per query execution. The reason- Buffer Busy Waits.

When the data is read by the query, the data is read from the physical disk to the buffer pool first. Any subsequent execute of the same query accessing the same data, Oracle will read the data from the buffer pool itself instead of reading it from the physical disk. When 60 instances tried to execute the same query, all the queries were trying to access the same set of blocks at the same time, creating HOT BLOCKS. Whenever Oracle accesses a block in the buffer pool, a latch is obtained by the process on the memory address in the buffer pool pointing to the block. For the next process (session) to access the same block, it needs to acquire a latch on the block first. When the process or session waits to get the access to the block in the buffer, buffer busy waits are caused. When all of the 60 sessions tried to execute the same query, the response time was higher as there were wait event that was happening in the background.

Apart from this, while one session reads the block from the disk to the buffer pool, other sessions trying to access the same block will need to wait till the buffer population activity by the first session is over. This is yet another cause for buffer busy waits.

Removing buffer busy waits is not simple or straightforward. The only option is to remove hot blocks i.e. blocks that are causing the contention. The possible fix is to reduce the amount of wait event by tuning the queries so that the queries execute faster and doesn’t cause noticeable waits. Another option is to avoid executing the query against the same blocks. This can be done by developing application level cache so that data is not requested frequently from the database.

Finding bind parameter values in queries in Oracle

While we write sql queries inside the application or use any ORM products, almost all queries are executed with bind parameters rather than giving the actual values in the query string itself. This approach has many benefits but it has certain limitations too in the case of debugging. If the logging of the application is turned off, it’s difficult to find the bind parameter passed onto the query. Hence it’s difficult to find as to what the bind values were and how the query plan was and what data did it return. (Of course a trace would help but there are simpler ways) With Oracle 10g, we can use the system tables to gather the information.

When a query gets executed, an entry is placed in the V$SQL table. For the query that you need to find the bind parameter, find the SQLID from the V$SQL table. For e.g., if the query that I’m trying to find is “select * from employee where empno = :1” . The steps to be followed are as given below

1. Identify the SQL ID by querying the V$SQL table

SELECT SQL_ID FROM V$SQL WHERE SQL_FULLTEXT LIKE ‘%employee%’ ORDER BY LAST_ACTIVE_TIME

The above select may return many records but its easy to find your SQL based on the timestamp. Also you can get the SQL ID from the AWR report in case you use it.

2. Based on the SQLID value, query the V$SQL_BIND_CAPTURE system table

SELECT POSITION, DATATYPE,VALUE_STRING, VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='anysqlidfromvsql'

From the above query, the position of bind parameters and its value (value_string) can be obtained. The original query with bind positions can be obtained from the V$SQL table. The sql_fulltext column has the full sql query text.

Now you have the actual query and the bind parameters too. Enjoy!

Clustering Factor for Indexes in Oracle

Indexes are a good way to increase the performance of a query, it can avoid table scan and directly help us to reach the data we need. However there are cases when the CBO will not consider the indexes that are associated to the table. The normal scenario that I see is that the index is not getting used because of poor selectivity or cardinality. There is another factor called Clustering Factor which also determines if the index needs to be used by the query. If the Clustering Factor is almost equal to the same number of rows in the table, then the index is poor and the CBO might not use the index.

To solve the problem, the best way is to reload the table and rebuild the index. During the reload the process, make sure that the column which is referenced by the index is in the ascending order. For e.g. For Employee table, the index XPKEMP is indexed on EMPID column. The process that needs to be followed are

create table EMPLOYEE_BKP as select * from EMPLOYEE order by empid asc;

drop table EMPLOYEE;

Create table EMPLOYEE as select * from EMPLOYEE_BKP;

Create index XPKEMP on EMPLOYEE(empid);

Now the index is created fresh and the clustering factor would be high. This can be verified by

Select clustering_factor from all_indexes where index_name=’XPKEMP’;

The value should be equal to the number of blocks occupied by the table. Now that all the indexes has good clustering factor, the chances of CBO taking the index is relatively high.