Skip to Main Content

Database Software

Performance test issues in Oracle pl/sql sharded queues

Prachi_bhoreJul 12 2019 — edited Jul 12 2019

I worked on the performance testing of  PL/SQL sharded queues. I have successfully implemented the sharded queues in oracle using PL/SQL notification and monitored the dequeue time in comparison with the normal queues.

There is a employees table with 100,000 records ,we enqueue the employee_id in the sharded queue and dequeue the ID and fetch other details and insert all them into a database table. The dequeue processing time was noted to check the throughput. Ideally the sharded queues should perform better than the normal queues but I got unexpected results. The enqueue of 20,000 messages through two sessions in sharded queues took 25 min whereas normal took 15 min for the same. According to the oracle white paper , Sharded queues’ dequeue throughput can be checked on multiple nodes on the dequeue side but the development database has one node for enqueue and dequeue and hence I could not effectively check the enqueue-dequeue throughput.

I would like to know how PL/SQL sharded queue can be checked to prove that they can improve manageability, scalability and performance on highly concurrent systems?

Post Details
Added on Jul 12 2019