A few years ago I wanted to investigate the performance of different SQL queries for the same problem, and wanted to include analysis of how the queries’ performance varied with problem size. In order to do this efficiently I wrote an Oracle framework consisting of tables, packages, types etc., and which I have now published here, dim_bench_sql_oracle on GitHub. As well as the obvious cpu and elapsed times, I included statistics contained in the execution plan tables, and also the differences in v$ view statistics that are gathered in the well known Runstats scripts, (originally developed by Tom Kyte, and for which there now seem to be lots of variations around, such as
Runstats utility). My approach is to collect these statistics in tables keyed by both query and dimensions to allow for more elaborate reporting, and to easily detect unscaleable queries, for example that use resources at a rate that grows quadratically, or worse, with problem size, as in one of the demo queries. Output goes to both a text log file, and to summary csv files for importing to Excel.

This article has design information on the framework, and is best read in conjunction with the first article on its use for a specific problem, Dimensional Benchmarking of Oracle v10-v12 Queries for SQL Bursting Problems, which includes the output log produced. This problem is included in the GitHub project, so anyone interested can run it themselves fairly easily.

Update, 26 November 2016: A notes section has been added discussing design issues and features.

Here is one of the Scribd articles for which I originally developed the framework:

Bench Data Model – ERD

Code Structure Diagram

Test_Queries Package Call Structure Table

 
Outputs

out folder

The demo script Test_Bur.sql writes the log data to ..\out\Test_Bur.LST.

The program loops over each (W, D) data point included in the driving script lists, and outputs for each query in the group data including:

• Full execution plan
• CPU and elapsed timings of query execution steps, file writing and data setup

On completion of the loops, summary reports are written to both the main log and to summary csv files, mentioned below, with information including

• Distinct execution plans (main log only)
• Data point statistics including setup timings and records created
• Numbers of output records from the queries
• cpu and elapsed times for the queries
• Execution plan statistics
• Numerous v$ After-Before statistic differences (following the Run_Stats model)

Oracle directory: output_dir

For a query group and query with data points W-D, the results of running the query are written to:

<query_group>_<query>_<W>-<D>.csv

For example:

BURST_MTH_QRY_30-30

Two summary files are written, with the bench run id as part of the name:

<bench run id>_W.csv
<bench run id>_D.csv

These files contain all the detailed statistics in csv format, so that they can be imported into Excel and used to obtain graphs. _W has the width parameter as the row and depth as the column, and _D the other way round.

Notes

Query Timing

Obtaining reliable comparative timings of queries in a test environment is not as straightforward as it may seem. Some of the issues are considered in this article, for example, Timing an ALL_ROWS query. For ad hoc tests, running the query in SQL*Plus after ‘SET AUTOTRACE TRACEONLY’ is one of the better approaches. However, in this framework a different approach is taken in order to simulate the performance that might be obtained if the query records were fetched in batches to be processed, say as an outbound interface, where they may be written to a file on the server. To do this, the query select list is converted into CSV format and the records are written to a file, with care taken to separate the timings of the query operations from those of the file processing.

Hard Parsing

This framework is not intended for testing OLTP SQL but relatively long-running batch-type SQL, where the cost of parsing is generally negligible. As the dataset sizes vary it is possible that the execution plan may vary, so it is important that the SQL engine performs a hard-parse on each execution of a query to ensure plan re-calculation. A hard parse is ensured by appending a placeholder field into the select list CSV string of the transformed queries, which is then replaced before each execution by a random number: The SQL engine considers the queries then to be distinct and therefore re-parses them.

Code Timing

The processing within the framework is heavily instrumented using the author’s own code timing utility package Code Timing and Object Orientation and Zombies. This is very low footprint in terms both of code and of performance, operating entirely in memory with individual timers keyed by name, and (logically) object oriented so that multiple timer sets can be running at once. Timings are printed to log, and the cpu and elapsed times for the query executed are summed from the individual components for the query, together with the times for any pre-query step:

• Pre SQL
• Open cursor
• First fetch
• Remaining fetches

Benchmarking Non-Query SQL

The framework is centred around the concept of a group of queries that are run in turn against the same dataset for each dataset point. However, non-query SQL can also be benchmarked in two ways: First, the query can include database PL/SQL functions; and secondly, the query metadata record includes a clob field for pre-query SQL that can be a PL/SQL block, while the actual query could just be ‘select 1 from dual’.

Query Transformation by Regular Expression Processing

The query output is written to file in csv format, includes the hint GATHER_PLAN_STATISTICS, and has a placeholder for a random number. Rather than cluttering up the input queries with this formatting, it seemed better to have the framework do the formatting. To this end the input queries instead have a select list with individual expressions and (mandatory) aliases, which can be simple or can be in double-quotes. The aliases form the header line of the csv file. To facilitate formatting the main query select list has to be of the form:

SELECT 
/* SEL */
        expr_1          alias_1,
        expr_2          alias_2
/* SEL */

Each expression must be on a separate line, and the list must be delimited by comment lines /* SEL */ as shown. The query formatting is performed in a procedure Get_Queries using some fairly complex regular expression processing.

Statistic Output Formatting

The various kinds of statistic (basic timing, execution plan aggregates, v$ statistics) are generally output in matrix format, both WxD and DxW. First the base numbers are printed for the whole grid for each query; then the last row for each query is printed, the deep or wide ‘slice’; then the same two sets of output are printed for the ratios of each number compared to the smallest number at the same data point across all queries.

Execution Plan Aggregation

After executing a query the execution plan statistics are copied from the system view v$sql_plan_statistics_all into bench_run_v$sql_plan_stats_all, and the formatted plan is written to a nested varray in bench_run_statistics using DBMS_XPlan.Display_Cursor.

At the end useful statistics in the plans are printed in aggregate by query execution, including maximum values of memory used and disk reads and writes, etc.

Estimated vs Actual Cardinalities

Oracle’s Cost Based Optimizer (CBO) uses estimated cardinalities at each step to evaluate candidate execution plans, and using the hint GATHER_PLAN_STATISTICS causes the actual cardinalities to be collected. Differences between estimated and actuals are generally recognised as being an important factor in whether or not a ‘good’ plan is chosen, so the maximum difference is included in the aggregates printed.

V$ Statistics

The statistics in the system views v$mystat, v$latch, v$sess_time_model are written to bench_run_v$stats before query execution (value_before, wait_before) and after execution (value_after, wait_after).

At the end a selection of the (after – before) differences of these statistics is written to log and csv file in the same format as the other statistics, based on the variance across the queries at the highest data point. A simple heuristic is included in the reporting query to restrict the statistics written to those deemed of most interest in comparing the queries, but all of the statistics remain available in bench_run_v$stats for ad hoc querying if desired.

See Also…

• Dimensional Benchmarking of Oracle v10-v12 Queries for SQL Bursting Problems
• Dimensional Benchmarking of General SQL Bursting Problems
• Dimensional Benchmarking of Bracket Parsing SQL
• Dimensional Benchmarking of SQL for Fixed-Depth Hierarchies
• Benchmarking of Hash Join Options in SQL for Fixed-Depth Hierarchies
• Dimensional Benchmarking of String Splitting SQL