Recently I wanted to benchmark a specific
SELECT query. It returned hundreds of thousands of rows and ran for way too long in either my application or sqlplus for it to be a reliable measurement.
Of course, benchmarking an SQL query is never entirely accurate, with the extreme amounts of caching and optimization performed by Oracle. And yet it’s a good and surprisingly reliable way of getting a baseline for the minimal runtime of a particular operation.
I tried all sorts of things - spooling,
set termout off, scripts, … - but nothing helped. In one way or another, the output would mess my measurements.
Then I came across this article by jOOQ, a formidable database-agnostic tool that aims to replace traditional and messy Java ORMs like JPA and Hibernate by a close-to-SQL approach. By the way, I recommend looking at their guide for benchmarking SQL.
Based on that article, here is a good PL/SQL program to benchmark SQL queries:
SET SERVEROUTPUT ON
Since it’s basic PL/SQL, it can just be run in any Oracle SQL tool: PL/SQL Developer, Oracle SQL Developer, sqlplus, you name it. And it works for all types SQL queries, and from the simplest to the heaviest.