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.

Unused Indexes in Oracle

Developers keep adding indexes to the database to improve the performance. Later they find it difficult to remove the unused indexes. Index monitoring feature of Oracle can be used to identify the unused indexes and remove them.Oracle gives the capability to add monitoring to the indexes.

alter index indexname monitoring usage;

The above command will mark the index for monitoring. It will add a record in the V$OBJECT_USAGE table. The columns in this table are

1. Index Name
2. Table Name
3. Used (this says if the index is used after the monitoring was enabled)
4. Monitoring (this says that monitoring is enabled or not)
5. Start_monitoring (start time of monitoring)
6. End_monitoring (end time of monitoring)

When the index is getting used, the USED flag against the index name will be marked to YES. So the best approach is to enable monitoring on all indexes in the database including the primary keys. After this step, access the application through the screens. Always try to cover all functionality so that maximum indexes are used.

Once the above exercise is completed, find all the indexes from the v$object_usage table which has the USED flag as NO. These indexes that are not used by the application during the monitoring time and can be dropped.

Select index_name from v$object_usage where used=’NO’;

After completing the exercise, all the monitoring on the indexes should be removed. The script for the same is

alter index indexname nomonitoring usage;

Multithreading - Downstream Impacts

Multi threaded model will in most of the cases give better performance for batch applications. I would look at the below mentioned points along with the reengineering of the application code.

• Processing Power – There should be enough CPU power available in the machine.
• Memory – Since all threads will be working in parallel, the amount of memory used also will be considerably high. This can be however be reduced by not placing too many objects in JVM. However, the amount of memory required will be proportional to the number of threads in the application.
• Network Load – If the application is having network interactions like Database queries, FTP etc, the network load also will be high. In some cases, it would be solved by adding GBit connections between servers communicating. In most of the cases, the normal NIC’s itself can process the load.
• Disk Speed – If the application has lot of File processing, the disk accesses also need to be tuned. It would be better to read the files from SAN rather than NAS as the disk response time is better on SAN (my experience).
• Database Setup – If there is lot of database interactions, the database also should be made aware of such a change in the application. The load on the database will increase as there will be multiple threads that will try to fetch data from database. Most probably, it will end up increasing the database parameters to accept more loads.
• GC processing – The GC tuning should be performed. Since many threads are working in parallel, the amount of garbage created also will be high. An effective tune up of GC is required, without which, application will end up in OutofMemory Error. You can consider Parallel GC but ensure that the number of GC threads is mentioned.
• Synchronization – Objects that are created in JVM scope should be accessed with proper synchronization. If this is not worked out properly, it can cause dreaded issues like data corruption, deadlocks etc…

Multithreading Multiprocessor Relation

A batch application is made scalable by ensuring that the executable can use the complete power provided by the machine. The batch application should be designed as multithreaded model if it’s possible to break the work into multiple smaller units of work. In this way, each thread can work on its own piece of work and complete the work. For e.g. In a single threaded model, the batch processes takes 10 hours to process 2000 customer records. If the same code is written in multi threaded model using 10 threads, the job can be split into 10 units with each unit having to process 200 customers. The same work can be completed in 1 hour. Caveat being the machine has the necessary processing power (CPU)

Any java process executes in a thread of execution. The thread can perform multiple activities like performing the task, waiting on IO, waiting on socket, waiting for lock release etc. While the thread is waiting on something, CPU is intelligent enough to remove the thread from its cycle and take up another thread which can perform the work. Any point in time, a CPU core can execute only one thread. So if the machine has 4 cores of CPU, an ideal count would be to provide 3 threads/core for the application to use. The number of threads per core is dependent on the application, primary driving factor being what is done in each thread. If there considerable wait that will happen in the thread of execution (like File IO, database read, Socket read) , the number of threads per CPU can be increased and if the thread is going to perform operations within the process area without any wait, the number of threads per CPU should be reduced. This is because the machine always pushes out the threads that are waiting and takes in thread that is ready for execution.

Impact on number of threads

More threads/core for application that has less amount of wait.
Let’s take an example where the machine has 2 CPU’s and application is configured to use 10 threads. Since there is not much wait time involved, the CPU will force the executing thread out of its cycle to give fair chance for the remaining 9 threads to execute. The thread that got pushed out will come back to execution after certain CPU cycles. At this time, it needs to rebuild till the point where it was pushed out. If there were only 1 thread of execution per CPU, this type of activity won’t happen and the single thread /CPU can complete the operation without heavy context switching. In such a scenario, it will be detrimental for the application. Such a case will be evident if the batch completes in lesser time when the number of threads for the application is reduced.
Less threads/core for application that has considerable amount of wait.
Let’s take an example where the machine has 8 CPU’s and application is configured to use 8 threads. Each CPU will execute a thread of execution and when any one of the thread goes into WAIT state, the CPU lies idle. Such a case will be evident if the batch completes in lesser time when the number of threads for the application is increased. At any point in time, the CPU usage will not be near 50% or 60%.

As mentioned in the above description, the number of threads per CPU should be decided based on the application characteristics.

HAAS - Hardware As A Service

SaaS or Software as a Service has picked up the momentum. The user pays the software for only what they use. This concept picked up in the industry because of many advantages. HAAS Hardware as a Service is catching up slowly. But was this not already existing in the form of hosting services? Is it something new? In hosting services, user for the hosting space for the period of time irrespective of the usage. Agreed, hosting services was existing but HAAS is not the same.

In HAAS, the user pay for the actual usage and not the usage decided in the beginning. In a hosting service, the end user for e.g. have to pay XUSD for say 1GB of space and 1 Web Application for 1 year. Irrespective of the usage of the site, the user need to pay the hosting provider. What if the user need to pay based on the amount of data that is transferred to the site or the amount of processing power the site uses instead of a fixed amount decided in the beginning. What if the application can take any amount of load i.e. scalability is available on- demand. This is possible through HAAS. User pays for the actual usage and gets the features of an enterprise class application.

Amazon has opened up the arena using Amazon Web Services. They have multiple products like Amazon EC2(Elastic Compute Cloud), Amazon S3(Simple Storage Service), Amazon SQS(Simple Queue Service). The whole idea is to achieve the application functionality by using the 3 above services. The unit of work is stored in the S3 area and the EC2 will use that unit to process the application in a scalable manner. And the benefits to the user, they get their application functional without spending heavily to setup the datacenters.