he performance of queries in applications is critical. You usually can gain manifold performance improvements by just improving query efficiency, and with IT budgets being squeezed, getting the maximum performance out of the existing infrastructure has become mandatory. Adding more hardware/RAM/faster disk or more CPU is likely not an option in the current economic climate.
Determining which queries will provide the best performance for a given application often comes down to a simple question: Does Query X perform better than Query Y? Normally, performance is defined with respect to response time, throughput, and scalability. Response time is the time difference between when a response is received and when a request is submitted. Throughput is the number of requests/transactions handled in a given span of time. Scalability is the ability of a system to increase throughput, response time, or both when the underlying resources (that is, CPU, memory, and disk) change.
This article explores utilities and methods can help compare query response times in Oracle, enabling you to answer the which-query-is-better question. It does not explore utilities that can be used to isolate the problem queries, as such queries usually are addressed at development/testing times or as a result of deployment/production DBAs pointing them out.
To compare requires, some test cases are required. Let's set those up first.
Setting Up the Test Cases
To create the data and queries required for the examples in this article, run two scripts, the Create Data script (see Listing 1
) and the Example Queries script (see Listing 2
), under Oracle.
You will use three sets of comparable queries that return equivalent results. These queries are somewhat contrived, but are nonetheless derived from actual real-world examples:
- Set 1:
- Query 1 (Version A): Returns totals and sub-totals for all objects, by owner and object types
- Query 1 (Version B): Returns totals and sub-totals for all objects, by owner and object types, based on the new GROUP BY CUBE syntax (find examples of such queries in Mastering Oracle SQL, 2nd Edition by Sanjay Mishra and Alan Beaulieu)
- Set 2:
- Query 2 (Version A): Retrieves the last updated object by type, per owner
- Query 2 (Version B): Retrieves the last updated object by type, per owner, based on the analytical function syntax MAX OVER PARTITION BY (see Tom Kyte's article "On Cursors, SQL, and Analytics" for a longer discussion)
- Set 3:
- Query 3 (Version A): Returns all tables that have one column with frequency histograms, one column with nulls, and one column with a length larger than 25 (The actual query gets data for questions such as "get all orders where diapers, bananas, and beer were bought together in one order.")
- Query 3 (Version B): Retrieves the same data as Version A, but uses the WITH clause and the INTERSECT set operator
The tests that I ran were run under Oracle 11g, but I didn't use any 11g-specific features in the queries or the tools. Output from previous versions may differ slightly, but Oracle 10g and above should be fine for reproducing the tests.