# SQL for the Balanced Number Partitioning Problem

I noticed a post on AskTom recently that referred to an SQL solution to a version of the so-called Bin Fitting problem, where even distribution is the aim. The solution, How do I solve a Bin Fitting problem in an SQL statement?, uses Oracle's Model clause, and, as the poster of the link observed, has the drawback that the number of bins is embedded in the query structure. I thought it might be interesting to find solutions without that drawback, so that the number of bins could be passed to the query as a bind variable. I came up with three solutions using different techniques, starting here.

An interesting article in American Scientist, The Easiest Hard Problem, notes that the problem is NP-complete, or certifiably hard, but that simple greedy heuristics often produce a good solution, including one used by schoolboys to pick football teams. The article uses the more descriptive term for the problem of balanced number partitioning, and notes some practical applications. The Model clause solution implements a multiple-bin version of the main Greedy Algorithm, while my non-Model SQL solutions implement variants of it that allow other techniques to be used, one of which is very simple and fast: this implements the team picking heuristic for multiple teams.

Another poster, Stew Ashton, suggested a simple change to my Model solution that improved performance, and I use this modified version here. He also suggested that using PL/SQL might be faster, and I have added my own simple PL/SQL implementation of the Greedy Algorithm, as well as a second version of the recursive subquery factoring solution that performs better than the first.

This article explains the solutions, considers two simple examples to illustrate them, and reports on performance testing across dimensions of number of items and number of bins. These show that the solutions exhibit either linear or quadratic variation in execution time with number of items, and some methods are sensitive to the number of bins while others are not.

After I had posted my solutions on the AskTom thread, I came across a thread on OTN, need help to resolve this issue, that requested a solution to a form of bin fitting problem where the bins have fixed capacity and the number of bins required must be determined. I realised that my solutions could easily be extended to add that feature, and posted extended versions of two of the solutions there. I have added a section here for this.

Updated, 5 June 2013: added Model and RSF diagrams

Greedy Algorithm Variants

Say there are N bins and M items.

Greedy Algorithm (GDY)
Set bin sizes zero
Loop over items in descending order of size

• Add item to current smallest bin
• Calculate new bin size

End Loop

Greedy Algorithm with Batched Rebalancing (GBR)
Set bin sizes zero
Loop over items in descending order of size in batches of N items

• Assign batch to N bins, with bins in ascending order of size
• Calculate new bin sizes

End Loop

Greedy Algorithm with No Rebalancing - or, Team Picking Algorithm (TPA)
Assign items to bins cyclically by bin sequence in descending order of item size

Two Examples

Example: Four Items

Here we see that the Greedy Algorithm finds the perfect solution, with no difference in bin size, but the two variants have a difference of two.
Example: Six Items

Here we see that none of the algorithms finds the perfect solution. Both the standard Greedy Algorithm and its batched variant give a difference of two, while the variant without rebalancing gives a difference of four.

SQL Solutions

Original Model for GDY
See the link above for the SQL for the problem with three bins only.

The author has two measures for each bin and implements the GDY algorithm using CASE expressions and aggregation within the rules. The idea is to iterate over the items in descending order of size, setting the item bin to the bin with current smallest value. I use the word 'bin' for his 'bucket'. Some notes:

• Dimension by row number, ordered by item value
• Add measures for the iteration, it, and number of iterations required, counter
• Add measures for the bin name, bucket_name, and current minimum bin value, min_tmp (only first entry used)
• Add measures for each item bin value, bucket_1-3, being the item value if it's in that bin, else zero
• Add measures for each bin running sum, pbucket_1-3, being the current value of each bin (only first two entries used)
• The current minimum bin value, bin_tmp[1] is computed as the least of the running sums
• The current item bin value is set to the item value for the bin whose value matches the minimum just computed, and null for the others
• The current bin name is set similarly to be the bin matching the minimum
• The new running sums are computed for each bin

Brendan's Generic Model for GDY

SELECT item_name, bin, item_value, Max (bin_value) OVER (PARTITION BY bin) bin_value
FROM (
SELECT * FROM items
MODEL
DIMENSION BY (Row_Number() OVER (ORDER BY item_value DESC) rn)
MEASURES (item_name,
item_value,
Row_Number() OVER (ORDER BY item_value DESC) bin,
item_value bin_value,
Row_Number() OVER (ORDER BY item_value DESC) rn_m,
0 min_bin,
Count(*) OVER () - :N_BINS - 1 n_iters
)
RULES ITERATE(100000) UNTIL (ITERATION_NUMBER >= n_iters[1]) (
min_bin[1] = Min(rn_m) KEEP (DENSE_RANK FIRST ORDER BY bin_value)[rn <= :N_BINS],
bin[ITERATION_NUMBER + :N_BINS + 1] = min_bin[1],
bin_value[min_bin[1]] = bin_value[CV()] + Nvl (item_value[ITERATION_NUMBER + :N_BINS + 1], 0)
)
)
WHERE item_name IS NOT NULL
ORDER BY item_value DESC


My Model solution works for any number of bins, passing the number of bins as a bind variable. The key idea here is to use values in the first N rows of a generic bin value measure to store all the running bin values, rather than as individual measures. I have included two modifications suggested by Stew in the AskTom thread.

• Dimension by row number, ordered by item value
• Initialise a bin measure to the row number (the first N items will remain fixed)
• Initialise a bin value measure to item value (only first N entries used)
• Add the row number as a measure, rn_m, in addition to a dimension, for referencing purposes
• Add a min_bin measure for current minimum bin index (first entry only)
• Add a measure for the number of iterations required, n_iters
• The first N items are correctly binned in the measure initialisation
• Set the minimum bin index using analytic Min function with KEEP clause over the first N rows of bin value
• Set the bin for the current item to this index
• Update the bin value for the corresponding bin only

Recursive Subquery Factor for GBR

WITH bins AS (
SELECT LEVEL bin, :N_BINS n_bins FROM DUAL CONNECT BY LEVEL <= :N_BINS
), items_desc AS (
SELECT item_name, item_value, Row_Number () OVER (ORDER BY item_value DESC) rn
FROM items
), rsf (bin, item_name, item_value, bin_value, lev, bin_rank, n_bins) AS (
SELECT b.bin,
i.item_name,
i.item_value,
i.item_value,
1,
b.n_bins - i.rn + 1,
b.n_bins
FROM bins b
JOIN items_desc i
ON i.rn = b.bin
UNION ALL
SELECT r.bin,
i.item_name,
i.item_value,
r.bin_value + i.item_value,
r.lev + 1,
Row_Number () OVER (ORDER BY r.bin_value + i.item_value),
r.n_bins
FROM rsf r
JOIN items_desc i
ON i.rn = r.bin_rank + r.lev * r.n_bins
)
SELECT r.item_name,
r.bin, r.item_value, r.bin_value
FROM rsf r
ORDER BY item_value DESC

The idea here is to use recursive subquery factors to iterate through the items in batches of N items, assigning each item to a bin according to the rank of the bin on the previous iteration.

• Initial subquery factors form record sets for the bins and for the items with their ranks in descending order of value
• The anchor branch assign bins to the first N items, assigning the item values to a bin value field, and setting the bin rank in ascending order of this bin value
• The recursive branch joins the batch of items to the record in the previous batch whose bin rank matches that of the item in the reverse sense (so largest item goes to smallest bin etc.)
• The analytic Row_Number function computes the updated bin ranks, and the bin values are updated by simple addition

Recursive Subquery Factor for GBR with Temporary Table
Create Table and Index

DROP TABLE items_desc_temp
/
CREATE GLOBAL TEMPORARY TABLE items_desc_temp (
item_name  VARCHAR2(30) NOT NULL,
item_value NUMBER(8) NOT NULL,
rn         NUMBER
)
ON COMMIT DELETE ROWS
/
CREATE INDEX items_desc_temp_N1 ON items_desc_temp (rn)
/

Insert into Temporary Table

INSERT INTO items_desc_temp
SELECT item_name, item_value, Row_Number () OVER (ORDER BY item_value DESC) rn
FROM items;

RSF Query with Temporary Table

WITH bins AS (
SELECT LEVEL bin, :N_BINS n_bins FROM DUAL CONNECT BY LEVEL <= :N_BINS
), rsf (bin, item_name, item_value, bin_value, lev, bin_rank, n_bins) AS (
SELECT b.bin,
i.item_name,
i.item_value,
i.item_value,
1,
b.n_bins - i.rn + 1,
b.n_bins
FROM bins b
JOIN items_desc_temp i
ON i.rn = b.bin
UNION ALL
SELECT r.bin,
i.item_name,
i.item_value,
r.bin_value + i.item_value,
r.lev + 1,
Row_Number () OVER (ORDER BY r.bin_value + i.item_value),
r.n_bins
FROM rsf r
JOIN items_desc_temp i
ON i.rn = r.bin_rank + r.lev * r.n_bins
)
SELECT item_name, bin, item_value, bin_value
FROM rsf
ORDER BY item_value DESC

The idea here is that in the initial RSF query a subquery factor of items was joined on a calculated field, so the whole record set had to be read, and performance could be improved by putting that initial record set into an indexed temporary table ahead of the main query. We'll see in the performance testing section that this changes quadratic variation with problem size into linear variation.

Plain Old SQL Solution for TPA

WITH items_desc AS (
SELECT item_name, item_value,
Mod (Row_Number () OVER (ORDER BY item_value DESC), :N_BINS) + 1 bin
FROM items
)
SELECT item_name, bin, item_value, Sum (item_value) OVER (PARTITION BY bin) bin_total
FROM items_desc
ORDER BY item_value DESC


The idea here is that the TPA algorithm can be implemented in simple SQL using analyic functions.

• The subquery factor assigns the bins by taking the item rank in descending order of value and applying the modulo (N) function
• The main query returns the bin totals in addition by analytic summing by bin

Pipelined Function for GDY
Package

CREATE OR REPLACE PACKAGE Bin_Fit AS

TYPE bin_fit_rec_type IS RECORD (item_name VARCHAR2(100), item_value NUMBER, bin NUMBER);
TYPE bin_fit_list_type IS VARRAY(1000) OF bin_fit_rec_type;

TYPE bin_fit_cur_rec_type IS RECORD (item_name VARCHAR2(100), item_value NUMBER);
TYPE bin_fit_cur_type IS REF CURSOR RETURN bin_fit_cur_rec_type;

FUNCTION Items_Binned (p_items_cur bin_fit_cur_type, p_n_bins PLS_INTEGER) RETURN bin_fit_list_type PIPELINED;

END Bin_Fit;
/
CREATE OR REPLACE PACKAGE BODY Bin_Fit AS

c_big_value                 CONSTANT NUMBER := 100000000;
TYPE bin_fit_cur_list_type  IS VARRAY(100) OF bin_fit_cur_rec_type;

FUNCTION Items_Binned (p_items_cur bin_fit_cur_type, p_n_bins PLS_INTEGER) RETURN bin_fit_list_type PIPELINED IS

l_min_bin              PLS_INTEGER := 1;
l_min_bin_val             NUMBER;
l_bins                    SYS.ODCINumberList := SYS.ODCINumberList();
l_bin_fit_cur_rec         bin_fit_cur_rec_type;
l_bin_fit_rec             bin_fit_rec_type;
l_bin_fit_cur_list        bin_fit_cur_list_type;

BEGIN

l_bins.Extend (p_n_bins);
FOR i IN 1..p_n_bins LOOP
l_bins(i) := 0;
END LOOP;

LOOP

FETCH p_items_cur BULK COLLECT INTO l_bin_fit_cur_list LIMIT 100;
EXIT WHEN l_bin_fit_cur_list.COUNT = 0;

FOR j IN 1..l_bin_fit_cur_list.COUNT LOOP

l_bin_fit_rec.item_name := l_bin_fit_cur_list(j).item_name;
l_bin_fit_rec.item_value := l_bin_fit_cur_list(j).item_value;
l_bin_fit_rec.bin := l_min_bin;

PIPE ROW (l_bin_fit_rec);
l_bins(l_min_bin) := l_bins(l_min_bin) + l_bin_fit_cur_list(j).item_value;

l_min_bin_val := c_big_value;
FOR i IN 1..p_n_bins LOOP

IF l_bins(i) < l_min_bin_val THEN
l_min_bin := i;
l_min_bin_val := l_bins(i);
END IF;

END LOOP;

END LOOP;

END LOOP;

END Items_Binned;


SQL Query

SELECT item_name, bin, item_value, Sum (item_value) OVER (PARTITION BY bin) bin_value
FROM TABLE (Bin_Fit.Items_Binned (
CURSOR (SELECT item_name, item_value FROM items ORDER BY item_value DESC),
:N_BINS))
ORDER BY item_value DESC


The idea here is that procedural algorithms can often be implemented more efficiently in PL/SQL than in SQL.

• The first parameter to the function is a strongly-typed reference cursor
• The SQL call passes in a SELECT statement wrapped in the CURSOR keyword, so the function can be used for any set of records that returns name and numeric value pairs
• The item records are fetched in batches of 100 using the LIMIT clause to improves efficiency

Performance Testing
I tested performance of the various queries using my own benchmarking framework across grids of data points, with two data sets to split the queries into two sets based on performance.

Query Modifications for Performance Testing

• The RSF query with staging table was run within a pipelined function in order to easily include the insert in the timings
• A system context was used to pass the bind variables as the framework runs the queries from PL/SQL, not from SQL*Plus
• I found that calculating the bin values using analytic sums, as in the code above, affected performance, so I removed this for clarity of results, outputting only item name, value and bin

Test Data Sets
For a given depth parameter, d, random numbers were inserted within the range 0-d for d-1 records. The insert was:

 INSERT INTO items
SELECT 'item-' || n, DBMS_Random.Value (0, p_point_deep) FROM
(SELECT LEVEL n FROM DUAL CONNECT BY LEVEL < p_point_deep);

The number of bins was passed as a width parameter, but note that the original, linked Model solution, MODO, hard-codes the number of bins to 3.

Test Results

Data Set 1 - Small
This was used for the following queries:

• MODO - Original Model for GDY
• MODB - Brendan's Generic Model for GDY
• RSFQ - Recursive Subquery Factor for GBR
 Depth         W3         W3         W3
Run Type=MODO
D1000       1.03       1.77       1.05
D2000       3.98       6.46       5.38
D4000      15.79       20.7      25.58
D8000      63.18      88.75      92.27
D16000      364.2     347.74     351.99
Run Type=MODB
Depth         W3         W6        W12
D1000        .27        .42        .27
D2000          1       1.58       1.59
D4000       3.86        3.8       6.19
D8000      23.26      24.57      17.19
D16000      82.29      92.04      96.02
Run Type=RSFQ
D1000       3.24       3.17       1.53
D2000       8.58       9.68       8.02
D4000      25.65      24.07      23.17
D8000      111.3     108.25      98.33
D16000     471.17     407.65     399.99


The results show:

• Quadratic variation of CPU time with number of items
• Little variation of CPU time with number of bins, although RSFQ seems to show some decline
• RSFQ is slightly slower than MODO, while my version of Model, MODB is about 4 times faster than MODO

Data Set 2 - Large
This was used for the following queries:

• RSFT - Recursive Subquery Factor for GBR with Temporary Table
• POSS - Plain Old SQL Solution for TPA
• PLFN - Pipelined Function for GDY

This table gives the CPU times in seconds across the data set:

  Depth       W100      W1000     W10000
Run Type=PLFN
D20000        .31       1.92      19.25
D40000        .65       3.87      55.78
D80000       1.28       7.72      92.83
D160000       2.67      16.59     214.96
D320000       5.29      38.68      418.7
D640000      11.61      84.57      823.9
Run Type=POSS
D20000        .09        .13        .13
D40000        .18        .21        .18
D80000        .27        .36         .6
D160000        .74       1.07        .83
D320000       1.36       1.58       1.58
D640000       3.13       3.97       4.04
Run Type=RSFT
D20000        .78        .78        .84
D40000       1.41       1.54        1.7
D80000       3.02       3.39       4.88
D160000       6.11       9.56       8.42
D320000      13.05      18.93      20.84
D640000      41.62      40.98      41.09


The results show:

• Linear variation of CPU time with number of items
• Little variation of CPU time with number of bins for POSS and RSFT, but roughly linear variation for PLFN
• These linear methods are much faster than the earlier quadratic ones for larger numbers of items
• Its approximate proportionality of time to number of bins means that, while PLFN is faster than RSFT for small number of bins, it becomes slower from around 50 bins for our problem
• The proportionality to number of bins for PLFN presumably arises from the step to find the bin of minimum value
• The lack of proportionality to number of bins for RSFT may appear surprising since it performs a sort of the bins iteratively: However, while the work for this sort is likely to be proportional to the number of bins, the number of iterations is inversely proportional and thus cancels out the variation

Solution Quality

The methods reported above implement three underlying algorithms, none of which guarantees an optimal solution. In order to get an idea of how the quality compares, I created new versions of the second set of queries using analytic functions to output the difference between minimum and maximum bin values, with percentage of the maximum also output. I ran these on the same grid, and report below the results for the four corners.

Method:			PLFN		RSFT		POSS
Point:	W100/D20000
Diff/%:			72/.004%	72/.004%	19,825/1%
Point:	W100/D640000
Diff/%:			60/.000003%	60/.000003%	633499/.03%
Point:	W10000/D20000
Diff/%:			189/.9%		180/.9%		19,995/67%
Point:	W10000/D640000
Diff/%:			695/.003%	695/.003%	639,933/3%

The results indicate that GDY (Greedy Algorithm) and GBR (Greedy Algorithm with Batched Rebalancing) generally give very similar quality results, while TPA (Team Picking Algorithm) tends to be quite a lot worse.

Extended Problem: Finding the Number of Bins Required

An important extension to the problem is when the bins have fixed capacity, and it is desired to find the minimum number of bins, then spread the items evenly between them. As mentioned at the start, I posted extensions to two of my solutions on an OTN thread, and I reproduce them here. It turns out to be quite easy to make the extension. The remainder of this section is just lifted from my OTN post and refers to the table of the original poster.

Start OTN Extract
So how do we determine the number of bins? The total quantity divided by bin capacity, rounded up, gives a lower bound on the number of bins needed. The actual number required may be larger, but mostly it will be within a very small range from the lower bound, I believe (I suspect it will nearly always be the lower bound). A good practical solution, therefore, would be to compute the solutions for a base number, plus one or more increments, and this can be done with negligible extra work (although Model might be an exception, I haven't tried it). Then the bin totals can be computed, and the first solution that meets the constraints can be used. I took two bin sets here.

SQL POS

WITH items AS (
SELECT sl_pm_code item_name, sl_wt item_amt, sl_qty item_qty,
Ceil (Sum(sl_qty) OVER () / :MAX_QTY) n_bins
FROM ow_ship_det
), items_desc AS (
SELECT item_name, item_amt, item_qty, n_bins,
Mod (Row_Number () OVER (ORDER BY item_qty DESC), n_bins) bin_1,
Mod (Row_Number () OVER (ORDER BY item_qty DESC), n_bins + 1) bin_2
FROM items
)
SELECT item_name, item_amt, item_qty,
CASE bin_1 WHEN 0 THEN n_bins ELSE bin_1 END bin_1,
CASE bin_2 WHEN 0 THEN n_bins + 1 ELSE bin_2 END bin_2,
Sum (item_amt) OVER (PARTITION BY bin_1) bin_1_amt,
Sum (item_qty) OVER (PARTITION BY bin_1) bin_1_qty,
Sum (item_amt) OVER (PARTITION BY bin_2) bin_2_amt,
Sum (item_qty) OVER (PARTITION BY bin_2) bin_2_qty
FROM items_desc
ORDER BY item_qty DESC, bin_1, bin_2

SQL Pipelined

SELECT osd.sl_pm_code item_name, osd.sl_wt item_amt, osd.sl_qty item_qty,
tab.bin_1, tab.bin_2,
Sum (osd.sl_wt) OVER (PARTITION BY tab.bin_1) bin_1_amt,
Sum (osd.sl_qty) OVER (PARTITION BY tab.bin_1) bin_1_qty,
Sum (osd.sl_wt) OVER (PARTITION BY tab.bin_2) bin_2_amt,
Sum (osd.sl_qty) OVER (PARTITION BY tab.bin_2) bin_2_qty
FROM ow_ship_det osd
JOIN TABLE (Bin_Even.Items_Binned (
CURSOR (SELECT sl_pm_code item_name, sl_qty item_value,
Sum(sl_qty) OVER () item_total
FROM ow_ship_det
ORDER BY sl_qty DESC, sl_wt DESC),
:MAX_QTY)) tab
ON tab.item_name = osd.sl_pm_code
ORDER BY osd.sl_qty DESC, tab.bin_1

Pipelined Function

CREATE OR REPLACE PACKAGE Bin_Even AS

TYPE bin_even_rec_type IS RECORD (item_name VARCHAR2(100), item_value NUMBER, bin_1 NUMBER, bin_2 NUMBER);
TYPE bin_even_list_type IS VARRAY(1000) OF bin_even_rec_type;

TYPE bin_even_cur_rec_type IS RECORD (item_name VARCHAR2(100), item_value NUMBER, item_total NUMBER);
TYPE bin_even_cur_type IS REF CURSOR RETURN bin_even_cur_rec_type;

FUNCTION Items_Binned (p_items_cur bin_even_cur_type, p_bin_max NUMBER) RETURN bin_even_list_type PIPELINED;

END Bin_Even;
/
SHO ERR
CREATE OR REPLACE PACKAGE BODY Bin_Even AS

c_big_value                 CONSTANT NUMBER := 100000000;
c_n_bin_sets                CONSTANT NUMBER := 2;

TYPE bin_even_cur_list_type IS VARRAY(100) OF bin_even_cur_rec_type;
TYPE num_lol_list_type      IS VARRAY(100) OF SYS.ODCINumberList;

FUNCTION Items_Binned (p_items_cur bin_even_cur_type, p_bin_max NUMBER) RETURN bin_even_list_type PIPELINED IS

l_min_bin                 SYS.ODCINumberList := SYS.ODCINumberList (1, 1);
l_min_bin_val             SYS.ODCINumberList := SYS.ODCINumberList (c_big_value, c_big_value);
l_bins                    num_lol_list_type := num_lol_list_type (SYS.ODCINumberList(), SYS.ODCINumberList());

l_bin_even_cur_rec        bin_even_cur_rec_type;
l_bin_even_rec            bin_even_rec_type;
l_bin_even_cur_list       bin_even_cur_list_type;

l_n_bins                  PLS_INTEGER;
l_n_bins_base             PLS_INTEGER;
l_is_first_fetch          BOOLEAN := TRUE;

BEGIN

LOOP

FETCH p_items_cur BULK COLLECT INTO l_bin_even_cur_list LIMIT 100;
EXIT WHEN l_Bin_Even_cur_list.COUNT = 0;
IF l_is_first_fetch THEN

l_n_bins_base := Ceil (l_Bin_Even_cur_list(1).item_total / p_bin_max) - 1;

l_is_first_fetch := FALSE;

l_n_bins := l_n_bins_base;
FOR i IN 1..c_n_bin_sets LOOP

l_n_bins := l_n_bins + 1;
l_bins(i).Extend (l_n_bins);
FOR k IN 1..l_n_bins LOOP
l_bins(i)(k) := 0;
END LOOP;

END LOOP;

END IF;

FOR j IN 1..l_Bin_Even_cur_list.COUNT LOOP

l_bin_even_rec.item_name := l_bin_even_cur_list(j).item_name;
l_bin_even_rec.item_value := l_bin_even_cur_list(j).item_value;
l_bin_even_rec.bin_1 := l_min_bin(1);
l_bin_even_rec.bin_2 := l_min_bin(2);

PIPE ROW (l_bin_even_rec);

l_n_bins := l_n_bins_base;
FOR i IN 1..c_n_bin_sets LOOP
l_n_bins := l_n_bins + 1;
l_bins(i)(l_min_bin(i)) := l_bins(i)(l_min_bin(i)) + l_Bin_Even_cur_list(j).item_value;

l_min_bin_val(i) := c_big_value;
FOR k IN 1..l_n_bins LOOP

IF l_bins(i)(k) < l_min_bin_val(i) THEN
l_min_bin(i) := k;
l_min_bin_val(i) := l_bins(i)(k);
END IF;

END LOOP;

END LOOP;

END LOOP;

END LOOP;

END Items_Binned;

END Bin_Even;

Output POS
Note BIN_1 means bin set 1, which turns out to have 4 bins, while bin set 2 then necessarily has 5.

ITEM_NAME         ITEM_AMT   ITEM_QTY      BIN_1      BIN_2  BIN_1_AMT  BIN_1_QTY  BIN_2_AMT  BIN_2_QTY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1239606-1080          4024        266          1          1      25562        995      17482        827
1239606-1045          1880        192          2          2      19394        886      14568        732
1239606-1044          1567        160          3          3      18115        835      14097        688
1239606-1081          2118        140          4          4      18988        793      17130        657
1239606-2094          5741         96          1          5      25562        995      18782        605
...
1239606-2107            80          3          4          2      18988        793      14568        732
1239606-2084           122          3          4          3      18988        793      14097        688
1239606-2110           210          2          2          3      19394        886      14097        688
1239606-4022           212          2          3          4      18115        835      17130        657
1239606-4021           212          2          4          5      18988        793      18782        605

Output Pipelined

ITEM_NAME         ITEM_AMT   ITEM_QTY      BIN_1      BIN_2  BIN_1_AMT  BIN_1_QTY  BIN_2_AMT  BIN_2_QTY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1239606-1080          4024        266          1          1      20627        878      15805        703
1239606-1045          1880        192          2          2      18220        877      16176        703
1239606-1044          1567        160          3          3      20425        878      15651        701
1239606-1081          2118        140          4          4      22787        876      14797        701
1239606-2094          5741         96          4          5      22787        876      19630        701
...
1239606-2089            80          3          4          1      22787        876      15805        703
1239606-2112           141          3          4          2      22787        876      16176        703
1239606-4022           212          2          1          1      20627        878      15805        703
1239606-4021           212          2          2          1      18220        877      15805        703
1239606-2110           210          2          3          2      20425        878      16176        703

End OTN Extract

Conclusions

• Various solutions for the balanced number partitioning problem have been presented, using Oracle's Model clause, Recursive Subquery Factoring, Pipelined Functions and simple SQL
• The performance characteristics of these solutions have been tested across a range of data sets
• As is often the case, the best solution depends on the shape and size of the data set
• A simple extension has been shown to allow determining the number of bins required in the bin-fitting interpretation of the problem
• Replacing a WITH clause with a staging table can be a useful technique to allow indexed scans

# SQL for Network Grouping

I noticed an interesting question posted on OTN this (European) Saturday morning,
Hierarchical query to combine two groupings into one broad joint grouping. I quickly realised that the problem posed was an example of a very general class of network problems that arises quite often:

Given a set of nodes and a rule for pair-wise (non-directional) linking, obtain the set of implied networks

Usually, in response to such a problem someone will suggest a CONNECT BY query solution. Unfortunately, although hierarchical SQL techniques can be used theoretically to resolve these non-hierarchical networks, they tend to be extremely inefficient for networks of any size and are therefore often impractical. There are two problems in particular:

1. Non-hierarchical networks have no root nodes, so the traversal needs to be repeated from every node in the network set
2. Hierarchical queries retrieve all possible routes through a network

I illustrated the second problem in my last post, Notes on Profiling Oracle PL/SQL, and I intend to write a longer article on the subject of networks at a later date. The most efficient way to traverse generalised networks in Oracle involves the use of PL/SQL, such as in my Scribd article of June 2010, An Oracle Network Traversal PL SQL Program. For this article, though, I will stick to SQL-only techniques and will write down three solutions in a general format whereby the tables of the specific problem are read by initial subquery factors links_v and nodes_v that are used in the rest of the queries. I'll save detailed explanation and performance analysis for the later article (see update at end of this section).

The three queries use two hierarchical methods and a method involving the Model clause:

1. CONNECT BY: This is the least efficient
2. Recursive subquery factors: This is more efficient than the first but still suffers from the two problems above
3. Model clause: This is intended to bypass the performance problems of hierarchical queries, but is still slower than PL/SQL

[Update, 2 September 2015] I have since written two articles on related subjects, the first, PL/SQL Pipelined Function for Network Analysis describes a PL/SQL program that traverses all networks and lists their structure. The second, Recursive SQL for Network Analysis, and Duality, uses a series of examples to illustrate and explain the different characteristics of the first two recursive SQL methods.

Problem Definition
Data Structure
I have taken the data structure of the OTN poster, made all fields character, and added three more records comprising a second isolated node (10) and a subnetwork of nodes 08 and 09. ITEM_ID is taken to be the primary key.

SQL> SELECT *
2    FROM item_groups
3  /

ITEM_ID    GROUP1   GROUP2
---------- -------- --------
01         A        100
02         A        100
03         A        101
04         B        100
05         B        102
06         C        103
07         D        101
08         E        104
09         E        105
10         F        106

10 rows selected.


Grouping Structure
The poster defines two items to be linked if they share the same value for either GROUP1 or GROUP2 attributes (which could obviously be generalised to any number of attributes), and items are in the same group if they can be connected by a chain of links. Observe that if there were only one grouping attribute then the problem would be trivial as that would itself group the items. Having more than one makes it more interesting and more difficult.

A real world example of such networks can be seen to be sibling networks if one takes people as the nodes and father and mother as the attributes.

Network Diagram

CONNECT BY Solution
SQL

WITH links_v AS (
SELECT t_fr.item_id node_id_fr,
t_to.item_id node_id_to,
t_fr.item_id || '-' || Row_Number() OVER (PARTITION BY t_fr.item_id ORDER BY t_to.item_id) link_id
FROM item_groups t_fr
JOIN item_groups t_to
ON t_to.item_id > t_fr.item_id
AND (t_to.group1 = t_fr.group1 OR t_to.group2 = t_fr.group2)
), nodes_v AS (
SELECT item_id node_id
FROM item_groups
), tree AS (
CONNECT BY NOCYCLE (node_id_fr = PRIOR node_id_to OR node_id_to = PRIOR node_id_fr OR
node_id_fr = PRIOR node_id_fr OR node_id_to = PRIOR node_id_to)
FROM tree
SELECT g.group_id, l.node_id_fr node_id
UNION
SELECT g.group_id, l.node_id_to
)
SELECT l.group_id "Network", l.node_id "Node"
UNION ALL
FROM nodes_v n
WHERE n.node_id NOT IN (SELECT node_id FROM linked_nodes)
ORDER BY 1, 2


Output

All networks by CONNECT BY - Unlinked nodes share network id 0
Network       Node
------------- ----
10
01-1          01
02
03
04
05
07
08-1          08
09

10 rows selected.


Notes on CONNECT BY Solution

• For convenience I have grouped the unlinked nodes into one dummy network; it's easy to assign them individual identifiers if desired

Recursive Subquery Factors (RSF) Solution
SQL

WITH links_v AS (
SELECT t_fr.item_id node_id_fr,
t_to.item_id node_id_to,
t_fr.item_id || '-' || Row_Number() OVER (PARTITION BY t_fr.item_id ORDER BY t_to.item_id) link_id
FROM item_groups t_fr
JOIN item_groups t_to
ON t_to.item_id > t_fr.item_id
AND (t_to.group1 = t_fr.group1 OR t_to.group2 = t_fr.group2)
), nodes_v AS (
SELECT item_id node_id
FROM item_groups
), rsf (node_id, id, root_id) AS (
SELECT node_id, NULL, node_id
FROM nodes_v
UNION ALL
SELECT CASE WHEN l.node_id_to = r.node_id THEN l.node_id_fr ELSE l.node_id_to END,
FROM rsf r
ON (l.node_id_fr = r.node_id OR l.node_id_to = r.node_id)
AND l.link_id != Nvl (r.id, '0')
) CYCLE node_id SET is_cycle TO '*' DEFAULT ' '
SELECT DISTINCT Min (root_id) OVER (PARTITION BY node_id) "Network", node_id "Node"
FROM rsf
ORDER BY 1, 2


Output

All networks by RSF - Unlinked nodes have their own network ids
Network       Node
------------- ----
01            01
02
03
04
05
07
06            06
08            08
09
10            10

10 rows selected.


Notes on Recursive Subquery Factors (RSF) Solution

• Here I have given the unlinked nodes their own network identifiers; they could equally have been grouped together under a dummy network

Model Clause Solution
SQL

WITH links_v AS (
SELECT t_fr.item_id node_id_fr,
t_to.item_id node_id_to,
t_fr.item_id || '-' || Row_Number() OVER (PARTITION BY t_fr.item_id ORDER BY t_to.item_id) link_id
FROM item_groups t_fr
JOIN item_groups t_to
ON t_to.item_id > t_fr.item_id
AND (t_to.group1 = t_fr.group1 OR t_to.group2 = t_fr.group2)
), nodes_v AS (
SELECT item_id node_id
FROM item_groups
), lnk_iter AS (
SELECT *
CROSS JOIN (SELECT 0 iter FROM DUAL UNION SELECT 1 FROM DUAL)
), mod AS (
SELECT *
FROM lnk_iter
MODEL
DIMENSION BY (Row_Number() OVER (PARTITION BY iter ORDER BY link_id) rn, iter)
MEASURES (Row_Number() OVER (PARTITION BY iter ORDER BY link_id) id_rn, link_id id,
node_id_fr nd1, node_id_to nd2,
1 lnk_cur,
CAST ('x' AS VARCHAR2(100)) nd1_cur,
CAST ('x' AS VARCHAR2(100)) nd2_cur,
0 net_cur,
CAST (NULL AS NUMBER) net,
CAST (NULL AS NUMBER) lnk_prc,
1 not_done,
0 itnum)
RULES UPSERT ALL
ITERATE(100000) UNTIL (lnk_cur[1, Mod (iteration_number+1, 2)] IS NULL)
(
itnum[ANY, ANY] = iteration_number,
not_done[ANY, Mod (iteration_number+1, 2)] = Count (CASE WHEN net IS NULL THEN 1 END)[ANY, Mod (iteration_number, 2)],
lnk_cur[ANY, Mod (iteration_number+1, 2)] =
CASE WHEN not_done[CV(), Mod (iteration_number+1, 2)] > 0 THEN
Nvl (Min (CASE WHEN lnk_prc IS NULL AND net = net_cur THEN id_rn END)[ANY, Mod (iteration_number, 2)],
Min (CASE WHEN net IS NULL THEN id_rn END)[ANY, Mod (iteration_number, 2)])
END,
lnk_prc[ANY, Mod (iteration_number+1, 2)] = lnk_prc[CV(), Mod (iteration_number, 2)],
lnk_prc[lnk_cur[1, Mod (iteration_number+1, 2)], Mod (iteration_number+1, 2)] = 1,
net_cur[ANY, Mod (iteration_number+1, 2)] =
CASE WHEN Min (CASE WHEN lnk_prc IS NULL AND net = net_cur THEN id_rn END)[ANY, Mod (iteration_number, 2)] IS NULL THEN
net_cur[CV(), Mod (iteration_number, 2)] + 1
ELSE
net_cur[CV(), Mod (iteration_number, 2)]
END,
nd1_cur[ANY, Mod (iteration_number+1, 2)] = nd1[lnk_cur[CV(), Mod (iteration_number+1, 2)], Mod (iteration_number, 2)],
nd2_cur[ANY, Mod (iteration_number+1, 2)] = nd2[lnk_cur[CV(), Mod (iteration_number+1, 2)], Mod (iteration_number, 2)],
net[ANY, Mod (iteration_number+1, 2)] =
CASE WHEN (nd1[CV(),Mod (iteration_number+1, 2)] IN (nd1_cur[CV(),Mod (iteration_number+1, 2)], nd2_cur[CV(),Mod (iteration_number+1, 2)]) OR
nd2[CV(),Mod (iteration_number+1, 2)] IN (nd1_cur[CV(),Mod (iteration_number+1, 2)], nd2_cur[CV(),Mod (iteration_number+1, 2)]))
AND net[CV(),Mod (iteration_number, 2)] IS NULL THEN
net_cur[CV(),Mod (iteration_number+1, 2)]
ELSE
net[CV(),Mod (iteration_number, 2)]
END
)
)
SELECT To_Char (net) "Network", nd1 "Node"
FROM mod
WHERE not_done = 0
UNION
SELECT To_Char (net), nd2
FROM mod
WHERE not_done = 0
UNION ALL
FROM nodes_v n
WHERE n.node_id NOT IN (SELECT nd1 FROM mod WHERE nd1 IS NOT NULL UNION SELECT nd2 FROM mod WHERE nd2 IS NOT NULL)
ORDER by 1, 2


Output

All networks by Model - Unlinked nodes share network id 00
Network       Node
------------- ----
10
1             01
02
03
04
05
07
2             08
09

10 rows selected.


Notes on Model Clause Solution

• For convenience I have grouped the unlinked nodes into one dummy network; it's easy to assign them individual identifiers if desired
• My Cyclic Iteration technique used here appears to be novel

Conclusions

• It is always advisable with a new problem in SQL to consider whether it falls into a general class of problems for which solutions have already been found
• Three solution methods for network resolution in pure SQL have been presented and demonstrated on a small test problem; the performance issues mentioned should be considered carefully before applying them on larger problems
• The Model clause solution is likely to be the most efficient on larger, looped networks, but if better performance is required then PL/SQL recursion-based methods would be faster
• For smaller problems with few loops the simpler method of recursive subquery factors may be preferred, or, for versions prior to v11.2, CONNECT BY

# Notes on Profiling Oracle PL/SQL

'Everything should be made as simple as possible, but not simpler'

This phrase is often attributed to Albert Einstein, although the attribution is apparently questionable:
Everything Should Be Made as Simple as Possible, But Not Simpler. In any case it's not a bad approach to follow, even if the quote did come from a non-Oracle guy :).

I recently started looking at the hierarchical profiler tool with a view to using it in an upcoming project. In order to understand the tool properly, I felt it would be a good idea to start by using it to profile a test program that would be as simple as possible while covering as wide a range of scenarios as possible. This article documents the results of that profiling, highlighting the different scenarios covered, discusses the output from the profiler, and includes a query I wrote to display the function call tree.

The article goes on to illustrate profiling through manual code instrumentation, and by the old flat profiler (DBMS_Profiler) on the same test program, concluding that each method has its own strengths and weaknesses.

Setup
The hierarchical profiler setup and use is described in Oracle® Database Advanced Application Developer's Guide 11g Release 2 (11.2), and some code snippets are available here:PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1

Scenarios
The test program consists of a driving script, Test_Rep_p.sql (attached), that calls a package (HProf_Test) and an object type (Table_Count_Type), both defined in the attached script, HProf_Test_Code.sql. The test program covers the following scenarios:

• Multiple root calls (__plsql_vm, A_CALLS_B)
• Recursive procedure calls (procedure calling itself: R_CALLS_R)
• Mutually recursive procedure calls (procedures call each other: A_CALLS_B and B_CALLS_A)
• Procedure called by multiple procedures (child with multiple parents: PUT_LINE)
• Procedure 'inlined' within PL/SQL (Rest_a_While)
• Static SQL within PL/SQL (__static_sql_exec_line8)
• Dynamic SQL within PL/SQL (__dyn_sql_exec_line12)
• 'Everything should be made as simple as possible, but not simpler'

This phrase is often attributed to Albert Einstein, although the attribution is apparently questionable:
Everything Should Be Made as Simple as Possible, But Not Simpler. In any case it's not a bad approach to follow, even if the quote did come from a non-Oracle guy :).

I recently started looking at the hierarchical profiler tool with a view to using it in an upcoming project. In order to understand the tool properly, I felt it would be a good idea to start by using it to profile a test program that would be as simple as possible while covering as wide a range of scenarios as possible. This article documents the results of that profiling, highlighting the different scenarios covered, discusses the output from the profiler, and includes a query I wrote to display the function call tree.

The article goes on to illustrate profiling through manual code instrumentation, and by the old flat profiler (DBMS_Profiler) on the same test program, concluding that each method has its own strengths and weaknesses.

Setup
The hierarchical profiler setup and use is described in Oracle® Database Advanced Application Developer's Guide 11g Release 2 (11.2), and some code snippets are available here:PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1

Scenarios
The test program consists of a driving script, Test_Rep_p.sql (attached), that calls a package (HProf_Test) and an object type (Table_Count_Type), both defined in the attached script, HProf_Test_Code.sql. The test program covers the following scenarios:

• Multiple root calls (__plsql_vm, A_CALLS_B)
• Recursive procedure calls (procedure calling itself: R_CALLS_R)
• Mutually recursive procedure calls (procedures call each other: A_CALLS_B and B_CALLS_A)
• Procedure called by multiple procedures (child with multiple parents: PUT_LINE)
• Procedure 'inlined' within PL/SQL (Rest_a_While)
• Static SQL within PL/SQL (__static_sql_exec_line8)
• Dynamic SQL within PL/SQL (__dyn_sql_exec_line12)
• Database function called from SQL in SQL*Plus (DBFUNC)
• Database function called from SQL in PL/SQL (DBFUNC)
• Object constructor call (TABLE_COUNT_TYPE)

Call Structure Diagram

Raw Results
The attached script Test_Rep_h.sql was used to report on the results. The record produced in the run table, DBMSHP_RUNS, was:

     RUNID RUN_TIMESTAMP                   MICRO_S    SECONDS RUN_COMMENT
---------- ---------------------------- ---------- ---------- ------------------------------------------------------------
11 04-MAR-13 07.07.36.803000        890719        .89 Profile for small test program with recursion

The records produced in the functions table, DBMSHP_FUNCTION_INFO, were:

OWNER MODULE               FUNCTION                         ID  LINE#      SUB_T      FUN_T  CALLS
----- -------------------- ------------------------------ ---- ------ ---------- ---------- ------
NET   HPROF_TEST           A_CALLS_B                         4     40      62340       4450      1
NET   HPROF_TEST           A_CALLS_B@1                       5     40      43729      13663      1
NET   HPROF_TEST           B_CALLS_A                         6     38      57890      14161      1
NET   HPROF_TEST           B_CALLS_A@1                       7     38      30066      30066      1
NET   HPROF_TEST           DBFUNC                            8     84      32629      32629      2
NET   HPROF_TEST           R_CALLS_R                         9     70      12823       4159      1
NET   HPROF_TEST           R_CALLS_R@1                      10     70       8633       8618      1
NET   HPROF_TEST           STOP_PROFILING                   11     16         21         21      1
NET   TABLE_COUNT_TYPE     TABLE_COUNT_TYPE                 12      3      55049         82      1
NET   TABLE_COUNT_TYPE     __static_sql_exec_line6          22      6      54967      54967      1
SYS   DBMS_HPROF           STOP_PROFILING                   13     59          0          0      1
SYS   DBMS_OUTPUT          GET_LINE                         14    129          8          8      3
SYS   DBMS_OUTPUT          GET_LINES                        15    160         68         60      3
SYS   DBMS_OUTPUT          NEW_LINE                         16    117          7          7      2
SYS   DBMS_OUTPUT          PUT                              17     77         28         28      2
SYS   DBMS_OUTPUT          PUT_LINE                         18    109         46         11      2
__anonymous_block                 1      0     809839        521      5
__dyn_sql_exec_line12            19     12        226        226      1
__plsql_vm                        2      0     828379         58      6
__plsql_vm@1                      3      0      14158         11      1
__sql_fetch_line13               20     13     726713     726713      1
__static_sql_exec_line8          21      8      14418        260      1

22 rows selected.

The SUB_T and FUN_T values are the total times in microseconds for the subtree including function, and function-only processing.

The records produced in the functions parent-child table, DBMSHP_PARENT_CHILD_INFO, were:

OWNER_P MODULE_P             FUNCTION_P                     OWNER_C MODULE_C             FUNCTION_C                          SUB_T      FUN_T  CALLS
------- -------------------- ------------------------------ ------- -------------------- ------------------------------ ---------- ---------- ------
NET     HPROF_TEST           STOP_PROFILING                 SYS     DBMS_HPROF           STOP_PROFILING                          0          0      1
NET     HPROF_TEST           R_CALLS_R@1                    SYS     DBMS_OUTPUT          PUT_LINE                               15          6      1
NET     HPROF_TEST           R_CALLS_R                      SYS     DBMS_OUTPUT          PUT_LINE                               31          5      1
NET     HPROF_TEST           R_CALLS_R                      NET     HPROF_TEST           R_CALLS_R@1                          8633       8618      1
NET     HPROF_TEST           B_CALLS_A                      NET     HPROF_TEST           A_CALLS_B@1                         43729      13663      1
NET     HPROF_TEST           A_CALLS_B@1                    NET     HPROF_TEST           B_CALLS_A@1                         30066      30066      1
NET     HPROF_TEST           A_CALLS_B                      NET     HPROF_TEST           B_CALLS_A                           57890      14161      1
NET     TABLE_COUNT_TYPE     TABLE_COUNT_TYPE               NET     TABLE_COUNT_TYPE     __static_sql_exec_line6             54967      54967      1
SYS     DBMS_OUTPUT          PUT_LINE                       SYS     DBMS_OUTPUT          NEW_LINE                                7          7      2
SYS     DBMS_OUTPUT          PUT_LINE                       SYS     DBMS_OUTPUT          PUT                                    28         28      2
SYS     DBMS_OUTPUT          GET_LINES                      SYS     DBMS_OUTPUT          GET_LINE                                8          8      3
__anonymous_block              NET     HPROF_TEST           STOP_PROFILING                         21         21      1
__anonymous_block              SYS     DBMS_OUTPUT          GET_LINES                              68         60      3
__anonymous_block                                           __dyn_sql_exec_line12                 226        226      1
__anonymous_block              NET     HPROF_TEST           R_CALLS_R                           12823       4159      1
__anonymous_block                                           __static_sql_exec_line8             14418        260      1
__plsql_vm                     NET     HPROF_TEST           DBFUNC                              18482      18482      1
__anonymous_block                                           __sql_fetch_line13                 726713     726713      1
__static_sql_exec_line8                                     __plsql_vm@1                        14158         11      1
__plsql_vm                                                  __anonymous_block                  809839        521      5
__plsql_vm@1                   NET     HPROF_TEST           DBFUNC                              14147      14147      1
__anonymous_block              NET     TABLE_COUNT_TYPE     TABLE_COUNT_TYPE                    55049         82      1

22 rows selected.

The SUB_T and FUN_T values are the total times in microseconds for the subtree including function, and function-only processing, respectively, for the child function while called from all instances of the parent.

Function Call Tree
The raw data above can be used to identify processing bottlenecks at a function level, but it's also useful to process the data in order to display the function hierarchies, both for performance tuning and also for understanding the program structure. This is not quite as trivial as it may seem. The oracle-base article provides an SQL statement that attempts to do this:

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name,
fi.function,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls
FROM   dbmshp_parent_child_info pci
JOIN dbmshp_function_info fi ON pci.runid = fi.runid AND pci.childsymid = fi.symbolid
WHERE  pci.runid = :RUN_ID
CONNECT BY PRIOR childsymid = parentsymid
START WITH pci.parentsymid = :START_ID

Here, bind variables replace the original hard-coded values. On running this query I often got the following result:

ERROR at line 1:
ORA-01436: CONNECT BY loop in user data

On the run used in this article, the query returned 157 records, which is obviously incorrect. There is of course a NOCYCLE keyword that can be used to return results in the case of loops. However, it is not worth adding in this case, because there are in fact no loops in the data (at least no cyclic loops - apparent loops are discussed later). Oracle avoids loops by treating a function call that is a descendant of itself as a call to a new function, identified by suffices @1, @2 etc. as we can see from the recursive procedures above (eg R_CALLS_R@1 is the second call of R_CALLS_R, this one from itself). The problem here is that the query is incorrect in its handling of runid, with the result that the tree-walk traverses records from other runs as well as the intended one. A further problem is that there may be several roots, and it would be best to calculate these within a subquery. We can correct these problems by the following query:

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name,
fi.symbolid || ': ' || fi.function function,
pci.subtree_elapsed_time sub_t,
pci.function_elapsed_time fun_t,
pci.calls
FROM dbmshp_parent_child_info	pci
JOIN dbmshp_function_info		fi
ON pci.runid	              = fi.runid
AND pci.childsymid	       = fi.symbolid
WHERE pci.runid                   = :RUN_ID
CONNECT BY PRIOR pci.childsymid    = pci.parentsymid
AND pci.runid	              = :RUN_ID
START WITH pci.parentsymid         IN (SELECT f.symbolid FROM dbmshp_function_info f WHERE NOT EXISTS
(SELECT 1 FROM dbmshp_parent_child_info i WHERE i.childsymid = f.symbolid AND i.runid = :RUN_ID) AND f.runid = :RUN_ID)
AND pci.runid	              = :RUN_ID

This query returns the results:

NAME                           FUNCTION                            SUB_T      FUN_T  CALLS
------------------------------ ------------------------------ ---------- ---------- ------
.                            1: __anonymous_block              809,839        521      5
NET.HPROF_TEST             9: R_CALLS_R                       12,823      4,159      1
NET.HPROF_TEST           10: R_CALLS_R@1                     8,633      8,618      1
SYS.DBMS_OUTPUT        18: PUT_LINE                           15          6      1
SYS.DBMS_OUTPUT      16: NEW_LINE                            7          7      2
SYS.DBMS_OUTPUT      17: PUT                                28         28      2
SYS.DBMS_OUTPUT          18: PUT_LINE                           31          5      1
SYS.DBMS_OUTPUT        16: NEW_LINE                            7          7      2
SYS.DBMS_OUTPUT        17: PUT                                28         28      2
NET.HPROF_TEST             11: STOP_PROFILING                     21         21      1
SYS.DBMS_HPROF           13: STOP_PROFILING                      0          0      1
NET.TABLE_COUNT_TYPE       12: TABLE_COUNT_TYPE               55,049         82      1
NET.TABLE_COUNT_TYPE     22: __static_sql_exec_line6        54,967     54,967      1
SYS.DBMS_OUTPUT            15: GET_LINES                          68         60      3
SYS.DBMS_OUTPUT          14: GET_LINE                            8          8      3
.                          19: __dyn_sql_exec_line12             226        226      1
.                          20: __sql_fetch_line13            726,713    726,713      1
.                          21: __static_sql_exec_line8        14,418        260      1
.                        3: __plsql_vm@1                    14,158         11      1
NET.HPROF_TEST         8: DBFUNC                          14,147     14,147      1
NET.HPROF_TEST               8: DBFUNC                          18,482     18,482      1
NET.HPROF_TEST               6: B_CALLS_A                       57,890     14,161      1
NET.HPROF_TEST             5: A_CALLS_B@1                     43,729     13,663      1
NET.HPROF_TEST           7: B_CALLS_A@1                     30,066     30,066      1

24 rows selected.

This is better, but we can identify some further issues.

Missing Roots
The true root results are missing: For example, A_CALLS_B is missing. This arises because the query is traversing the link records (DBMSHP_PARENT_CHILD_INFO), while the root information is stored in the nodes (DBMSHP_FUNCTION_INFO). This suggests a change from the CONNECT BY syntax to Oracle's v11.2 recursive subquery factoring syntax, which allows you easily to start from the nodes, then traverse recursively via the links. (Incidentally, moving the start of profiling to its own block would result in A_CALLS_B appearing under __anonymous_block, but I prefer to retain the current structure in order to deal with the general case in which multiple roots are possible.)

Notice that function PUT_LINE is reported separately under R_CALLS_R and R_CALLS_R@1, and the timings differ. Also, its own child calls appear under each of its instances, but in those cases the timings are identical. The reason for this is that in the first case, there are separate records of the times used in each call, whereas in the second, the child calls have only a single record giving the total times across both instances of the parent call. The call from R_CALLS_R shows (9 - 4 = ) 5µs used in child calls, while the call from R_CALLS_R@1 shows 14µs. The child calls show totals of (3 + 16 = ) 19µs, equalling the sum across the parent calls.

At this point it is worth looking at this from the more general perspective of a hierarchical data structure where parents can have multiple children and children multiple parents, with one or more roots. If a network diagram were constructed there would be loops apparent indicating multiple routes between nodes. In these situations, Oracle's hierarchical queries effectively traverse all routes, and this is what causes the link duplication (in other scenarios this behaviour can cause big performance problems, but probably not here). Oracle's cycle detection mechanism does not trigger because the loops do not result in any node being a descendant of itself (as noted above, extra nodes are generated by the profiler to avoid this).

It seems to me better to avoid this duplication, and also to signal those cases where times are not aggregated up the tree. We can achieve this by the use of analytic functions. Note that, although the query below refers to the specific tables and attributes for this problem, the proposed solution could be used for any member of this general class of problem. The new query, which orders sibling records by descending subtree elapsed time, is:

WITH last_run AS (
SELECT Max (runid) runid FROM dbmshp_runs
), full_tree (runid, lev, node_id, sub_t, fun_t, calls, link_id) AS (
SELECT fni.runid, 0, fni.symbolid, fni.subtree_elapsed_time, fni.function_elapsed_time, fni.calls, 'root' || ROWNUM
FROM dbmshp_function_info fni
JOIN last_run lrn
ON lrn.runid = fni.runid
WHERE NOT EXISTS (SELECT 1 FROM dbmshp_parent_child_info pci WHERE pci.childsymid = fni.symbolid AND pci.runid = fni.runid)
UNION ALL
SELECT ftr.runid,
ftr.lev + 1,
pci.childsymid,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls,
pci.parentsymid || '-' || pci.childsymid
FROM full_tree ftr
JOIN dbmshp_parent_child_info pci
ON pci.parentsymid = ftr.node_id
AND pci.runid = ftr.runid
) SEARCH DEPTH FIRST BY sub_t DESC, fun_t DESC, calls DESC, node_id SET rn
, tree_ranked AS (
SELECT runid, node_id, lev, rn,
sub_t, fun_t, calls,
Row_Number () OVER (PARTITION BY node_id ORDER BY rn) node_rn,
Count (*) OVER (PARTITION BY node_id) node_cnt,
FROM full_tree
)
SELECT RPad (' ', trr.lev*2, ' ') || fni.function "Function tree",
fni.symbolid sy, fni.owner, fni.module,
CASE WHEN trr.node_cnt > 1 THEN trr.node_rn || ' of ' || trr.node_cnt END "Inst.",
trr.sub_t, trr.fun_t, trr.calls,
trr.rn "Row"
FROM tree_ranked trr
JOIN dbmshp_function_info fni
ON fni.symbolid = trr.node_id
AND fni.runid = trr.runid
ORDER BY trr.rn

Query Structure Diagram

The results are then:

Function tree                        SY OWNER MODULE               Inst.         SUB_T      FUN_T  CALLS  Row
----------------------------------- --- ----- -------------------- -------- ---------- ---------- ------ ----
__plsql_vm                            2                                        828,379         58      6    1
__anonymous_block                   1                                        809,839        521      5    2
__sql_fetch_line13               20                                        726,713    726,713      1    3
TABLE_COUNT_TYPE                 12 NET   TABLE_COUNT_TYPE                  55,049         82      1    4
__static_sql_exec_line6        22 NET   TABLE_COUNT_TYPE                  54,967     54,967      1    5
__static_sql_exec_line8          21                                         14,418        260      1    6
__plsql_vm@1                    3                                         14,158         11      1    7
DBFUNC                        8 NET   HPROF_TEST           1 of 2       14,147     14,147      1    8
R_CALLS_R                         9 NET   HPROF_TEST                        12,823      4,159      1    9
R_CALLS_R@1                    10 NET   HPROF_TEST                         8,633      8,618      1   10
PUT_LINE                     18 SYS   DBMS_OUTPUT          1 of 2           15          6      1   11
PUT                        17 SYS   DBMS_OUTPUT          1 of 2           28         28      2   12
NEW_LINE                   16 SYS   DBMS_OUTPUT          1 of 2            7          7      2   13
PUT_LINE                       18 SYS   DBMS_OUTPUT          2 of 2           31          5      1   14
__dyn_sql_exec_line12            19                                            226        226      1   17
GET_LINES                        15 SYS   DBMS_OUTPUT                           68         60      3   18
GET_LINE                       14 SYS   DBMS_OUTPUT                            8          8      3   19
STOP_PROFILING                   11 NET   HPROF_TEST                            21         21      1   20
STOP_PROFILING                 13 SYS   DBMS_HPROF                             0          0      1   21
DBFUNC                              8 NET   HPROF_TEST           2 of 2       18,482     18,482      1   22
A_CALLS_B                             4 NET   HPROF_TEST                        62,340      4,450      1   23
B_CALLS_A                           6 NET   HPROF_TEST                        57,890     14,161      1   24
A_CALLS_B@1                       5 NET   HPROF_TEST                        43,729     13,663      1   25
B_CALLS_A@1                     7 NET   HPROF_TEST                        30,066     30,066      1   26

24 rows selected.

Notice that we now have a single record for each of the 22 links, plus the two root nodes. Also, the "Inst." column lists the instance number of a function having more than one instance, and the children of any such function are only listed once with the gaps in the "Row" column indicating where duplicates have been suppressed.

Network Diagrams
It may be interesting to display the call tree in two diagrams, one for each root.
Root __plsql_vm

Root A_CALLS_B

Notes on Tree Output
Anonymous Block (__anonymous_block)
This function seems to correspond to invocations of anonymous blocks, obviously enough. However, there is an apparent anomaly in the number of calls listed, 6, because the driving program has only three such blocks, and there are none in the called PL/SQL code. I would surmise that the apparent discrepancy arises from the enabling of SERVEROUTPUT, which appears to result in a secondary block being associated with each explicit SQL*Plus block, that issues a call to GET_LINES to process buffered output.

PL/SQL Engine (__plsql_vm)
This function seems to correspond to external invocations of PL/SQL such as from a SQL*Plus session. There are 7 calls, 6 of them presumably being linked with the external anonymous blocks, and the seventh with DBFUNC, where a PL/SQL function is called from a SQL statement from SQL*Plus.

Notice that the SQL statement calling a database function from within PL/SQL generates the recursive call to the engine, __plsql_vm@1

Second Root (A_CALLS_B)
The above function does not have the __plsql_vm/__anonymous_block ancestry that might be expected because profiling only started within the enclosing block.

Inlined Procedure (Rest_a_While)
I wrote a small procedure, Rest_a_While, to generate some elapsed time in the recursive procedures, but preceded it with the INLINE pragma, a new optimisation feature in 11g. This had the desired effect of removing the calls from the profiling output and including the times in the calling procedures. Rest_a_While does not make the obvious call to DBMS_Lock.Sleep because that procedure cannot be inlined. subprogram inlining in 11g provides some analysis of the inlining feature.

Sibling Ordering
We have ordered siblings by descending subtree elapsed time, using the SEARCH clause. It would be nice to have the option to order the siblings by initial invocation time, but Oracle does not provide the data to do this.

Loops and Hierarchies
The first diagram shows two loops, where there are two routes between the loop start and end points, indicated by different colours. The second loop has two child nodes coming from the end point, and hierarchical queries (both CONNECT BY and recursive subquery factors in Oracle) cause the links to be duplicated. Our query has filtered out the duplicates by analytic functions.

It's worth remembering this because it's a general feature of SQL for querying hierarchies, and judging by Oracle forums, not one that's widely understood. For larger hierarchies it can cause serious performance problems, and may justify a PL/SQL programmed solution that need not suffer the same problem.

Manual Instrumentation
Oracle's hierarchical profiler clearly provides extremely useful information on both performance and structure of PL/SQL programs with very little effort. However, it does have the limitation of only providing information down to the subprogram level (which includes embedded SQL statements in this context). It is also often considered good practice to implement timing and other instrumentation permanently in production code, sometimes in a switchable fashion. In the test program, one of the called procedures, A_Calls_B, makes two calls to the inlined procedure, Rest_a_While, the second doing about twice as much work as the first. The profiler reports total within-function times of 4,450µs and 13,663µs on first and second calls, respectively (the work is scaled by a call number parameter, equal to 1, then 3).

I created a second instance of the package and driver script (suffix _TS) to illustrate manual instrumentation. This uses an 'object-oriented' timing package that I wrote a couple of years ago Code Timing and Object Orientation and Zombies (November, 2010) to instrument at procedure and section level. I multiplied the work in Rest_a_While by a factor of ten to get larger times. This produced the output:

Timer Set: HProf, Constructed at 05 Mar 2013 10:21:27, written at 10:21:30
==========================================================================
[Timer timed: Elapsed (per call): 0.04 (0.000044), CPU (per call): 0.05 (0.000050), calls: 1000, '***' denotes corrected line below]

Timer                       Elapsed          CPU          Calls        Ela/Call        CPU/Call
----------------------   ----------   ----------   ------------   -------------   -------------
A_Calls_B, section one         0.06         0.05              2         0.03150         0.02500
A_Calls_B, section two         0.12         0.12              2         0.06050         0.06000
B_Calls_A: 2                   0.15         0.16              1         0.15400         0.16000
B_Calls_A: 4                   0.31         0.30              1         0.30700         0.30000
DBFunc                         0.32         0.31              2         0.15950         0.15500
Open cursor                    0.69         0.69              1         0.68900         0.69000
Fetch from cursor              0.70         0.70              1         0.69600         0.70000
Close cursor                   0.00         0.00              1         0.00000         0.00000
Construct object               0.06         0.04              1         0.05500         0.04000
R_Calls_R                      0.14         0.14              2         0.07000         0.07000
(Other)                        0.00         0.00              1         0.00000         0.00000
----------------------   ----------   ----------   ------------   -------------   -------------
Total                          2.54         2.51             15         0.16960         0.16733
----------------------   ----------   ----------   ------------   -------------   -------------


Notes on Code Timing

• Calls, CPU and elapsed times have been captured at the section level for A_Calls_B
• Observe that, while R_Calls_R and A_Calls_B aggregate over all calls, B_Calls_A records values by call; this is implemented simply by including a value that changes with call in the timer name
• The timing set object is designed to be very low footprint; here 9 statements (calls to Increment_Time), plus a small global overhead, produced 10 result lines, plus associated information
• The 'object-oriented' approach allows multiple programs to be be timed at multiple levels, without interference between timings
• There are Perl and Java implementations of this timing set object included in the Scribd article mentioned

Oracle's Flat Profiler (DBMS_Profiler)
The hierarchical profiler was introduced in v11.1, while prior to this there was a non-hierarchical profiler, DBMS_Profiler. This package still exists in v11: It is omitted from the advanced application developer's guide for v11, but is described in the packages and types manual (Oracle® Database PL/SQL Packages and Types Reference, 11g Release 2 (11.2)); also, SQL*Developer appears to support only the newer hierarchical verion (via right-click on a package). I thought it interesting to run the older version on the same test program (package Old_Test_Prof, driver script Test_Rep_p_Old.sql and reporting script Test_Rep_h_Old.sql). The output from the first three queries is:

Run header (PLSQL_PROFILER_RUNS)

RUNID RUN_DATE        MICRO_S    SECONDS
---------- ------------ ---------- ----------
3 11:03:13        2164000       2.16

Profiler data summary (PLSQL_PROFILER_DATA)

MICRO_S SECONDS    CALLS
---------- ------- --------
2126949    2.13       72

Profiler data by time (PLSQL_PROFILER_DATA)

MICRO_S SECONDS    CALLS UNIT_NAME            UNIT_NUMBER  LINE#
---------- ------- -------- -------------------- ----------- ------
729932    0.73        1                                5     13
569563    0.57        2 OLD_PROF_TEST                  1     56
377880    0.38        2 OLD_PROF_TEST                  1     82
166019    0.17        2 OLD_PROF_TEST                  1     70
150117    0.15        2 OLD_PROF_TEST                  1     43
72742    0.07        2 OLD_PROF_TEST                  1     40
56473    0.06        1 TABLE_COUNT_TYPE               6      6
3338    0.00        1                                5      8
258    0.00        1                                5     12
109    0.00        1                                5     16
68    0.00        2 OLD_PROF_TEST                  1     67
66    0.00        2                                4      1
60    0.00        2                                7      1
60    0.00        2                                3      1
44    0.00        1                                5     14
42    0.00        0                                2      5
31    0.00        1 OLD_PROF_TEST                  1     18
26    0.00        1                                8      5
13    0.00        0                                5      1
9    0.00        1 TABLE_COUNT_TYPE               6     11
9    0.00        2 OLD_PROF_TEST                  1     86
8    0.00        0 OLD_PROF_TEST                  1     51
8    0.00        1 TABLE_COUNT_TYPE               6     13
7    0.00        1                                5     18
6    0.00        0 OLD_PROF_TEST                  1     78
6    0.00        0 OLD_PROF_TEST                  1     64
6    0.00        0                                8      1
6    0.00        1 TABLE_COUNT_TYPE               6      3
5    0.00        0 OLD_PROF_TEST                  1     35
5    0.00        0 OLD_PROF_TEST                  1     15
4    0.00        1                                8      7
4    0.00        1 OLD_PROF_TEST                  1     76
3    0.00        1                                2      8
2    0.00        1 OLD_PROF_TEST                  1     62
2    0.00        1 OLD_PROF_TEST                  1     13
2    0.00        1 TABLE_COUNT_TYPE               6      5
2    0.00        2 OLD_PROF_TEST                  1     72
2    0.00        2 OLD_PROF_TEST                  1     45
2    0.00        2 OLD_PROF_TEST                  1     49
2    0.00        2 OLD_PROF_TEST                  1     46
2    0.00        2 OLD_PROF_TEST                  1     58
1    0.00        1 OLD_PROF_TEST                  1     73
1    0.00        1                                2      6
1    0.00        1 OLD_PROF_TEST                  1     59
1    0.00        1 OLD_PROF_TEST                  1     11
1    0.00        2 OLD_PROF_TEST                  1     54
1    0.00        2 OLD_PROF_TEST                  1     84
0    0.00        0 OLD_PROF_TEST                  1      1
0    0.00        0 OLD_PROF_TEST                  1     88
0    0.00        0                                8      9
0    0.00        0                                2      1
0    0.00        0                                2      2
0    0.00        0 OLD_PROF_TEST                  1      3
0    0.00        0 OLD_PROF_TEST                  1      5
0    0.00        0 OLD_PROF_TEST                  1      9
0    0.00        0 OLD_PROF_TEST                  1     20
0    0.00        1 TABLE_COUNT_TYPE               6      4
0    0.00        1                                8      2
0    0.00        2 OLD_PROF_TEST                  1     39
0    0.00        2 OLD_PROF_TEST                  1     55
0    0.00        2 OLD_PROF_TEST                  1     69
0    0.00        2 OLD_PROF_TEST                  1     38
0    0.00        2 OLD_PROF_TEST                  1     81
0    0.00        2 OLD_PROF_TEST                  1     42
0    0.00        2 OLD_PROF_TEST                  1     68

65 rows selected.



Referring to the package, type and anonymous blocks, I assigned labels to all the lines having more than 10µs, as follows:

   MICRO_S SECONDS    CALLS UNIT_NAME            UNIT_NUMBER  LINE#
---------- ------- -------- -------------------- ----------- ------
729932    0.73        1                                5     13  B2: FETCH
569563    0.57        2 OLD_PROF_TEST                  1     56  B_Calls_A (Rest_a_While)
377880    0.38        2 OLD_PROF_TEST                  1     82  DBFunc (Rest_a_While)
166019    0.17        2 OLD_PROF_TEST                  1     70  R_Calls_R (Rest_a_While)
150117    0.15        2 OLD_PROF_TEST                  1     43  A_Calls_B (Rest_a_While, section 2)
72742    0.07        2 OLD_PROF_TEST                  1     40  A_Calls_B (Rest_a_While, section 1)
56473    0.06        1 TABLE_COUNT_TYPE               6      6  SELECT
3338    0.00        1                                5      8  B2: SELECT DBFunc
258    0.00        1                                5     12  B2: OPEN
109    0.00        1                                5     16  B2: Assign Table_Count_Type
68    0.00        2 OLD_PROF_TEST                  1     67  Put_Line
66    0.00        2                                4      1  Auxiliary SERVEROUTPUT block for B2 (surmised)
60    0.00        2                                7      1  Auxiliary SERVEROUTPUT block for B3 (surmised)
60    0.00        2                                3      1  Auxiliary SERVEROUTPUT block for B1 (surmised)
44    0.00        1                                5     14  B2: CLOSE
42    0.00        0                                2      5  B1: Call to Start_Profiling
31    0.00        1 OLD_PROF_TEST                  1     18  RETURN DBMS_Profiler.Stop_Profiler;
26    0.00        1                                8      5  B3: Call R_Calls_R
13    0.00        0                                5      1  B2: DECLARE


Notes on Output of Flat Profiler
There were six units with no linked information in DBMS_PROFILER_UNITS. By examining the data, I was able to associate unit numbers 2, 5 and 8 with my anonymous blocks B1, B2 and B3. That left three unassigned, and I have surmised that these correspond to the auxiliary blocks associated with processing server output that we earlier surmised when examining the output from the hierarchical profiler.

• The useful call tree structure is not present in the data from the old profiler
• However, the results are at a line level, which the hierarchical profiler does not provide; for example, the two sections of A_Calls_B are reported separately
• Deciphering the output requires significantly more manual effort than with the hierarchical profiler
• Both old and new profiler have their own advantages, and so both should be considered of value
• Manual code timing offers more flexibility in terms of aggregating lines and call instances, but requires more effort

Conclusions

• Running Oracle's hierarchical profiler would seem to be the default first step in tuning PL/SQL programs from v11.1
• Some care is needed in interpreting the output data; I've provided a query for displaying the hierarchies
• Performance is recorded only down to function level, so it will still often be worthwhile to use the old flat profiler in addition
• Manually timing code sections also still has a part to play, in terms of instrumentation and greater flexibility where necessary

• Database function called from SQL in SQL*Plus (DBFUNC)
• Database function called from SQL in PL/SQL (DBFUNC)
• Object constructor call (TABLE_COUNT_TYPE)

Call Structure Diagram

Raw Results
The attached script Test_Rep_h.sql was used to report on the results. The record produced in the run table, DBMSHP_RUNS, was:

     RUNID RUN_TIMESTAMP                   MICRO_S    SECONDS RUN_COMMENT
---------- ---------------------------- ---------- ---------- ------------------------------------------------------------
11 04-MAR-13 07.07.36.803000        890719        .89 Profile for small test program with recursion

The records produced in the functions table, DBMSHP_FUNCTION_INFO, were:

OWNER MODULE               FUNCTION                         ID  LINE#      SUB_T      FUN_T  CALLS
----- -------------------- ------------------------------ ---- ------ ---------- ---------- ------
NET   HPROF_TEST           A_CALLS_B                         4     40      62340       4450      1
NET   HPROF_TEST           A_CALLS_B@1                       5     40      43729      13663      1
NET   HPROF_TEST           B_CALLS_A                         6     38      57890      14161      1
NET   HPROF_TEST           B_CALLS_A@1                       7     38      30066      30066      1
NET   HPROF_TEST           DBFUNC                            8     84      32629      32629      2
NET   HPROF_TEST           R_CALLS_R                         9     70      12823       4159      1
NET   HPROF_TEST           R_CALLS_R@1                      10     70       8633       8618      1
NET   HPROF_TEST           STOP_PROFILING                   11     16         21         21      1
NET   TABLE_COUNT_TYPE     TABLE_COUNT_TYPE                 12      3      55049         82      1
NET   TABLE_COUNT_TYPE     __static_sql_exec_line6          22      6      54967      54967      1
SYS   DBMS_HPROF           STOP_PROFILING                   13     59          0          0      1
SYS   DBMS_OUTPUT          GET_LINE                         14    129          8          8      3
SYS   DBMS_OUTPUT          GET_LINES                        15    160         68         60      3
SYS   DBMS_OUTPUT          NEW_LINE                         16    117          7          7      2
SYS   DBMS_OUTPUT          PUT                              17     77         28         28      2
SYS   DBMS_OUTPUT          PUT_LINE                         18    109         46         11      2
__anonymous_block                 1      0     809839        521      5
__dyn_sql_exec_line12            19     12        226        226      1
__plsql_vm                        2      0     828379         58      6
__plsql_vm@1                      3      0      14158         11      1
__sql_fetch_line13               20     13     726713     726713      1
__static_sql_exec_line8          21      8      14418        260      1

22 rows selected.

The SUB_T and FUN_T values are the total times in microseconds for the subtree including function, and function-only processing.

The records produced in the functions parent-child table, DBMSHP_PARENT_CHILD_INFO, were:

OWNER_P MODULE_P             FUNCTION_P                     OWNER_C MODULE_C             FUNCTION_C                          SUB_T      FUN_T  CALLS
------- -------------------- ------------------------------ ------- -------------------- ------------------------------ ---------- ---------- ------
NET     HPROF_TEST           STOP_PROFILING                 SYS     DBMS_HPROF           STOP_PROFILING                          0          0      1
NET     HPROF_TEST           R_CALLS_R@1                    SYS     DBMS_OUTPUT          PUT_LINE                               15          6      1
NET     HPROF_TEST           R_CALLS_R                      SYS     DBMS_OUTPUT          PUT_LINE                               31          5      1
NET     HPROF_TEST           R_CALLS_R                      NET     HPROF_TEST           R_CALLS_R@1                          8633       8618      1
NET     HPROF_TEST           B_CALLS_A                      NET     HPROF_TEST           A_CALLS_B@1                         43729      13663      1
NET     HPROF_TEST           A_CALLS_B@1                    NET     HPROF_TEST           B_CALLS_A@1                         30066      30066      1
NET     HPROF_TEST           A_CALLS_B                      NET     HPROF_TEST           B_CALLS_A                           57890      14161      1
NET     TABLE_COUNT_TYPE     TABLE_COUNT_TYPE               NET     TABLE_COUNT_TYPE     __static_sql_exec_line6             54967      54967      1
SYS     DBMS_OUTPUT          PUT_LINE                       SYS     DBMS_OUTPUT          NEW_LINE                                7          7      2
SYS     DBMS_OUTPUT          PUT_LINE                       SYS     DBMS_OUTPUT          PUT                                    28         28      2
SYS     DBMS_OUTPUT          GET_LINES                      SYS     DBMS_OUTPUT          GET_LINE                                8          8      3
__anonymous_block              NET     HPROF_TEST           STOP_PROFILING                         21         21      1
__anonymous_block              SYS     DBMS_OUTPUT          GET_LINES                              68         60      3
__anonymous_block                                           __dyn_sql_exec_line12                 226        226      1
__anonymous_block              NET     HPROF_TEST           R_CALLS_R                           12823       4159      1
__anonymous_block                                           __static_sql_exec_line8             14418        260      1
__plsql_vm                     NET     HPROF_TEST           DBFUNC                              18482      18482      1
__anonymous_block                                           __sql_fetch_line13                 726713     726713      1
__static_sql_exec_line8                                     __plsql_vm@1                        14158         11      1
__plsql_vm                                                  __anonymous_block                  809839        521      5
__plsql_vm@1                   NET     HPROF_TEST           DBFUNC                              14147      14147      1
__anonymous_block              NET     TABLE_COUNT_TYPE     TABLE_COUNT_TYPE                    55049         82      1

22 rows selected.

The SUB_T and FUN_T values are the total times in microseconds for the subtree including function, and function-only processing, respectively, for the child function while called from all instances of the parent.

Function Call Tree
The raw data above can be used to identify processing bottlenecks at a function level, but it's also useful to process the data in order to display the function hierarchies, both for performance tuning and also for understanding the program structure. This is not quite as trivial as it may seem. The oracle-base article provides an SQL statement that attempts to do this:

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name,
fi.function,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls
FROM   dbmshp_parent_child_info pci
JOIN dbmshp_function_info fi ON pci.runid = fi.runid AND pci.childsymid = fi.symbolid
WHERE  pci.runid = :RUN_ID
CONNECT BY PRIOR childsymid = parentsymid
START WITH pci.parentsymid = :START_ID

Here, bind variables replace the original hard-coded values. On running this query I often got the following result:

ERROR at line 1:
ORA-01436: CONNECT BY loop in user data

On the run used in this article, the query returned 157 records, which is obviously incorrect. There is of course a NOCYCLE keyword that can be used to return results in the case of loops. However, it is not worth adding in this case, because there are in fact no loops in the data (at least no cyclic loops - apparent loops are discussed later). Oracle avoids loops by treating a function call that is a descendant of itself as a call to a new function, identified by suffices @1, @2 etc. as we can see from the recursive procedures above (eg R_CALLS_R@1 is the second call of R_CALLS_R, this one from itself). The problem here is that the query is incorrect in its handling of runid, with the result that the tree-walk traverses records from other runs as well as the intended one. A further problem is that there may be several roots, and it would be best to calculate these within a subquery. We can correct these problems by the following query:

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name,
fi.symbolid || ': ' || fi.function function,
pci.subtree_elapsed_time sub_t,
pci.function_elapsed_time fun_t,
pci.calls
FROM dbmshp_parent_child_info	pci
JOIN dbmshp_function_info		fi
ON pci.runid	              = fi.runid
AND pci.childsymid	       = fi.symbolid
WHERE pci.runid                   = :RUN_ID
CONNECT BY PRIOR pci.childsymid    = pci.parentsymid
AND pci.runid	              = :RUN_ID
START WITH pci.parentsymid         IN (SELECT f.symbolid FROM dbmshp_function_info f WHERE NOT EXISTS
(SELECT 1 FROM dbmshp_parent_child_info i WHERE i.childsymid = f.symbolid AND i.runid = :RUN_ID) AND f.runid = :RUN_ID)
AND pci.runid	              = :RUN_ID

This query returns the results:

NAME                           FUNCTION                            SUB_T      FUN_T  CALLS
------------------------------ ------------------------------ ---------- ---------- ------
.                            1: __anonymous_block              809,839        521      5
NET.HPROF_TEST             9: R_CALLS_R                       12,823      4,159      1
NET.HPROF_TEST           10: R_CALLS_R@1                     8,633      8,618      1
SYS.DBMS_OUTPUT        18: PUT_LINE                           15          6      1
SYS.DBMS_OUTPUT      16: NEW_LINE                            7          7      2
SYS.DBMS_OUTPUT      17: PUT                                28         28      2
SYS.DBMS_OUTPUT          18: PUT_LINE                           31          5      1
SYS.DBMS_OUTPUT        16: NEW_LINE                            7          7      2
SYS.DBMS_OUTPUT        17: PUT                                28         28      2
NET.HPROF_TEST             11: STOP_PROFILING                     21         21      1
SYS.DBMS_HPROF           13: STOP_PROFILING                      0          0      1
NET.TABLE_COUNT_TYPE       12: TABLE_COUNT_TYPE               55,049         82      1
NET.TABLE_COUNT_TYPE     22: __static_sql_exec_line6        54,967     54,967      1
SYS.DBMS_OUTPUT            15: GET_LINES                          68         60      3
SYS.DBMS_OUTPUT          14: GET_LINE                            8          8      3
.                          19: __dyn_sql_exec_line12             226        226      1
.                          20: __sql_fetch_line13            726,713    726,713      1
.                          21: __static_sql_exec_line8        14,418        260      1
.                        3: __plsql_vm@1                    14,158         11      1
NET.HPROF_TEST         8: DBFUNC                          14,147     14,147      1
NET.HPROF_TEST               8: DBFUNC                          18,482     18,482      1
NET.HPROF_TEST               6: B_CALLS_A                       57,890     14,161      1
NET.HPROF_TEST             5: A_CALLS_B@1                     43,729     13,663      1
NET.HPROF_TEST           7: B_CALLS_A@1                     30,066     30,066      1

24 rows selected.

This is better, but we can identify some further issues.

Missing Roots
The true root results are missing: For example, A_CALLS_B is missing. This arises because the query is traversing the link records (DBMSHP_PARENT_CHILD_INFO), while the root information is stored in the nodes (DBMSHP_FUNCTION_INFO). This suggests a change from the CONNECT BY syntax to Oracle's v11.2 recursive subquery factoring syntax, which allows you easily to start from the nodes, then traverse recursively via the links. (Incidentally, moving the start of profiling to its own block would result in A_CALLS_B appearing under __anonymous_block, but I prefer to retain the current structure in order to deal with the general case in which multiple roots are possible.)

Notice that function PUT_LINE is reported separately under R_CALLS_R and R_CALLS_R@1, and the timings differ. Also, its own child calls appear under each of its instances, but in those cases the timings are identical. The reason for this is that in the first case, there are separate records of the times used in each call, whereas in the second, the child calls have only a single record giving the total times across both instances of the parent call. The call from R_CALLS_R shows (9 - 4 = ) 5µs used in child calls, while the call from R_CALLS_R@1 shows 14µs. The child calls show totals of (3 + 16 = ) 19µs, equalling the sum across the parent calls.

At this point it is worth looking at this from the more general perspective of a hierarchical data structure where parents can have multiple children and children multiple parents, with one or more roots. If a network diagram were constructed there would be loops apparent indicating multiple routes between nodes. In these situations, Oracle's hierarchical queries effectively traverse all routes, and this is what causes the link duplication (in other scenarios this behaviour can cause big performance problems, but probably not here). Oracle's cycle detection mechanism does not trigger because the loops do not result in any node being a descendant of itself (as noted above, extra nodes are generated by the profiler to avoid this).

It seems to me better to avoid this duplication, and also to signal those cases where times are not aggregated up the tree. We can achieve this by the use of analytic functions. Note that, although the query below refers to the specific tables and attributes for this problem, the proposed solution could be used for any member of this general class of problem. The new query, which orders sibling records by descending subtree elapsed time, is:

WITH last_run AS (
SELECT Max (runid) runid FROM dbmshp_runs
), full_tree (runid, lev, node_id, sub_t, fun_t, calls, link_id) AS (
SELECT fni.runid, 0, fni.symbolid, fni.subtree_elapsed_time, fni.function_elapsed_time, fni.calls, 'root' || ROWNUM
FROM dbmshp_function_info fni
JOIN last_run lrn
ON lrn.runid = fni.runid
WHERE NOT EXISTS (SELECT 1 FROM dbmshp_parent_child_info pci WHERE pci.childsymid = fni.symbolid AND pci.runid = fni.runid)
UNION ALL
SELECT ftr.runid,
ftr.lev + 1,
pci.childsymid,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls,
pci.parentsymid || '-' || pci.childsymid
FROM full_tree ftr
JOIN dbmshp_parent_child_info pci
ON pci.parentsymid = ftr.node_id
AND pci.runid = ftr.runid
) SEARCH DEPTH FIRST BY sub_t DESC, fun_t DESC, calls DESC, node_id SET rn
, tree_ranked AS (
SELECT runid, node_id, lev, rn,
sub_t, fun_t, calls,
Row_Number () OVER (PARTITION BY node_id ORDER BY rn) node_rn,
Count (*) OVER (PARTITION BY node_id) node_cnt,
FROM full_tree
)
SELECT RPad (' ', trr.lev*2, ' ') || fni.function "Function tree",
fni.symbolid sy, fni.owner, fni.module,
CASE WHEN trr.node_cnt > 1 THEN trr.node_rn || ' of ' || trr.node_cnt END "Inst.",
trr.sub_t, trr.fun_t, trr.calls,
trr.rn "Row"
FROM tree_ranked trr
JOIN dbmshp_function_info fni
ON fni.symbolid = trr.node_id
AND fni.runid = trr.runid
ORDER BY trr.rn

Query Structure Diagram

The results are then:

Function tree                        SY OWNER MODULE               Inst.         SUB_T      FUN_T  CALLS  Row
----------------------------------- --- ----- -------------------- -------- ---------- ---------- ------ ----
__plsql_vm                            2                                        828,379         58      6    1
__anonymous_block                   1                                        809,839        521      5    2
__sql_fetch_line13               20                                        726,713    726,713      1    3
TABLE_COUNT_TYPE                 12 NET   TABLE_COUNT_TYPE                  55,049         82      1    4
__static_sql_exec_line6        22 NET   TABLE_COUNT_TYPE                  54,967     54,967      1    5
__static_sql_exec_line8          21                                         14,418        260      1    6
__plsql_vm@1                    3                                         14,158         11      1    7
DBFUNC                        8 NET   HPROF_TEST           1 of 2       14,147     14,147      1    8
R_CALLS_R                         9 NET   HPROF_TEST                        12,823      4,159      1    9
R_CALLS_R@1                    10 NET   HPROF_TEST                         8,633      8,618      1   10
PUT_LINE                     18 SYS   DBMS_OUTPUT          1 of 2           15          6      1   11
PUT                        17 SYS   DBMS_OUTPUT          1 of 2           28         28      2   12
NEW_LINE                   16 SYS   DBMS_OUTPUT          1 of 2            7          7      2   13
PUT_LINE                       18 SYS   DBMS_OUTPUT          2 of 2           31          5      1   14
__dyn_sql_exec_line12            19                                            226        226      1   17
GET_LINES                        15 SYS   DBMS_OUTPUT                           68         60      3   18
GET_LINE                       14 SYS   DBMS_OUTPUT                            8          8      3   19
STOP_PROFILING                   11 NET   HPROF_TEST                            21         21      1   20
STOP_PROFILING                 13 SYS   DBMS_HPROF                             0          0      1   21
DBFUNC                              8 NET   HPROF_TEST           2 of 2       18,482     18,482      1   22
A_CALLS_B                             4 NET   HPROF_TEST                        62,340      4,450      1   23
B_CALLS_A                           6 NET   HPROF_TEST                        57,890     14,161      1   24
A_CALLS_B@1                       5 NET   HPROF_TEST                        43,729     13,663      1   25
B_CALLS_A@1                     7 NET   HPROF_TEST                        30,066     30,066      1   26

24 rows selected.

Notice that we now have a single record for each of the 22 links, plus the two root nodes. Also, the "Inst." column lists the instance number of a function having more than one instance, and the children of any such function are only listed once with the gaps in the "Row" column indicating where duplicates have been suppressed.

Network Diagrams
It may be interesting to display the call tree in two diagrams, one for each root.
Root __plsql_vm

Root A_CALLS_B

Notes on Tree Output
Anonymous Block (__anonymous_block)
This function seems to correspond to invocations of anonymous blocks, obviously enough. However, there is an apparent anomaly in the number of calls listed, 6, because the driving program has only three such blocks, and there are none in the called PL/SQL code. I would surmise that the apparent discrepancy arises from the enabling of SERVEROUTPUT, which appears to result in a secondary block being associated with each explicit SQL*Plus block, that issues a call to GET_LINES to process buffered output.

PL/SQL Engine (__plsql_vm)
This function seems to correspond to external invocations of PL/SQL such as from a SQL*Plus session. There are 7 calls, 6 of them presumably being linked with the external anonymous blocks, and the seventh with DBFUNC, where a PL/SQL function is called from a SQL statement from SQL*Plus.

Notice that the SQL statement calling a database function from within PL/SQL generates the recursive call to the engine, __plsql_vm@1

Second Root (A_CALLS_B)
The above function does not have the __plsql_vm/__anonymous_block ancestry that might be expected because profiling only started within the enclosing block.

Inlined Procedure (Rest_a_While)
I wrote a small procedure, Rest_a_While, to generate some elapsed time in the recursive procedures, but preceded it with the INLINE pragma, a new optimisation feature in 11g. This had the desired effect of removing the calls from the profiling output and including the times in the calling procedures. Rest_a_While does not make the obvious call to DBMS_Lock.Sleep because that procedure cannot be inlined. subprogram inlining in 11g provides some analysis of the inlining feature.

Sibling Ordering
We have ordered siblings by descending subtree elapsed time, using the SEARCH clause. It would be nice to have the option to order the siblings by initial invocation time, but Oracle does not provide the data to do this.

Loops and Hierarchies
The first diagram shows two loops, where there are two routes between the loop start and end points, indicated by different colours. The second loop has two child nodes coming from the end point, and hierarchical queries (both CONNECT BY and recursive subquery factors in Oracle) cause the links to be duplicated. Our query has filtered out the duplicates by analytic functions.

It's worth remembering this because it's a general feature of SQL for querying hierarchies, and judging by Oracle forums, not one that's widely understood. For larger hierarchies it can cause serious performance problems, and may justify a PL/SQL programmed solution that need not suffer the same problem.

Manual Instrumentation
Oracle's hierarchical profiler clearly provides extremely useful information on both performance and structure of PL/SQL programs with very little effort. However, it does have the limitation of only providing information down to the subprogram level (which includes embedded SQL statements in this context). It is also often considered good practice to implement timing and other instrumentation permanently in production code, sometimes in a switchable fashion. In the test program, one of the called procedures, A_Calls_B, makes two calls to the inlined procedure, Rest_a_While, the second doing about twice as much work as the first. The profiler reports total within-function times of 4,450µs and 13,663µs on first and second calls, respectively (the work is scaled by a call number parameter, equal to 1, then 3).

I created a second instance of the package and driver script (suffix _TS) to illustrate manual instrumentation. This uses an 'object-oriented' timing package that I wrote a couple of years ago Code Timing and Object Orientation and Zombies (November, 2010) to instrument at procedure and section level. I multiplied the work in Rest_a_While by a factor of ten to get larger times. This produced the output:

Timer Set: HProf, Constructed at 05 Mar 2013 10:21:27, written at 10:21:30
==========================================================================
[Timer timed: Elapsed (per call): 0.04 (0.000044), CPU (per call): 0.05 (0.000050), calls: 1000, '***' denotes corrected line below]

Timer                       Elapsed          CPU          Calls        Ela/Call        CPU/Call
----------------------   ----------   ----------   ------------   -------------   -------------
A_Calls_B, section one         0.06         0.05              2         0.03150         0.02500
A_Calls_B, section two         0.12         0.12              2         0.06050         0.06000
B_Calls_A: 2                   0.15         0.16              1         0.15400         0.16000
B_Calls_A: 4                   0.31         0.30              1         0.30700         0.30000
DBFunc                         0.32         0.31              2         0.15950         0.15500
Open cursor                    0.69         0.69              1         0.68900         0.69000
Fetch from cursor              0.70         0.70              1         0.69600         0.70000
Close cursor                   0.00         0.00              1         0.00000         0.00000
Construct object               0.06         0.04              1         0.05500         0.04000
R_Calls_R                      0.14         0.14              2         0.07000         0.07000
(Other)                        0.00         0.00              1         0.00000         0.00000
----------------------   ----------   ----------   ------------   -------------   -------------
Total                          2.54         2.51             15         0.16960         0.16733
----------------------   ----------   ----------   ------------   -------------   -------------


Notes on Code Timing

• Calls, CPU and elapsed times have been captured at the section level for A_Calls_B
• Observe that, while R_Calls_R and A_Calls_B aggregate over all calls, B_Calls_A records values by call; this is implemented simply by including a value that changes with call in the timer name
• The timing set object is designed to be very low footprint; here 9 statements (calls to Increment_Time), plus a small global overhead, produced 10 result lines, plus associated information
• The 'object-oriented' approach allows multiple programs to be be timed at multiple levels, without interference between timings
• There are Perl and Java implementations of this timing set object included in the Scribd article mentioned

Oracle's Flat Profiler (DBMS_Profiler)
The hierarchical profiler was introduced in v11.1, while prior to this there was a non-hierarchical profiler, DBMS_Profiler. This package still exists in v11: It is omitted from the advanced application developer's guide for v11, but is described in the packages and types manual (Oracle® Database PL/SQL Packages and Types Reference, 11g Release 2 (11.2)); also, SQL*Developer appears to support only the newer hierarchical verion (via right-click on a package). I thought it interesting to run the older version on the same test program (package Old_Test_Prof, driver script Test_Rep_p_Old.sql and reporting script Test_Rep_h_Old.sql). The output from the first three queries is:

Run header (PLSQL_PROFILER_RUNS)

RUNID RUN_DATE        MICRO_S    SECONDS
---------- ------------ ---------- ----------
3 11:03:13        2164000       2.16

Profiler data summary (PLSQL_PROFILER_DATA)

MICRO_S SECONDS    CALLS
---------- ------- --------
2126949    2.13       72

Profiler data by time (PLSQL_PROFILER_DATA)

MICRO_S SECONDS    CALLS UNIT_NAME            UNIT_NUMBER  LINE#
---------- ------- -------- -------------------- ----------- ------
729932    0.73        1                                5     13
569563    0.57        2 OLD_PROF_TEST                  1     56
377880    0.38        2 OLD_PROF_TEST                  1     82
166019    0.17        2 OLD_PROF_TEST                  1     70
150117    0.15        2 OLD_PROF_TEST                  1     43
72742    0.07        2 OLD_PROF_TEST                  1     40
56473    0.06        1 TABLE_COUNT_TYPE               6      6
3338    0.00        1                                5      8
258    0.00        1                                5     12
109    0.00        1                                5     16
68    0.00        2 OLD_PROF_TEST                  1     67
66    0.00        2                                4      1
60    0.00        2                                7      1
60    0.00        2                                3      1
44    0.00        1                                5     14
42    0.00        0                                2      5
31    0.00        1 OLD_PROF_TEST                  1     18
26    0.00        1                                8      5
13    0.00        0                                5      1
9    0.00        1 TABLE_COUNT_TYPE               6     11
9    0.00        2 OLD_PROF_TEST                  1     86
8    0.00        0 OLD_PROF_TEST                  1     51
8    0.00        1 TABLE_COUNT_TYPE               6     13
7    0.00        1                                5     18
6    0.00        0 OLD_PROF_TEST                  1     78
6    0.00        0 OLD_PROF_TEST                  1     64
6    0.00        0                                8      1
6    0.00        1 TABLE_COUNT_TYPE               6      3
5    0.00        0 OLD_PROF_TEST                  1     35
5    0.00        0 OLD_PROF_TEST                  1     15
4    0.00        1                                8      7
4    0.00        1 OLD_PROF_TEST                  1     76
3    0.00        1                                2      8
2    0.00        1 OLD_PROF_TEST                  1     62
2    0.00        1 OLD_PROF_TEST                  1     13
2    0.00        1 TABLE_COUNT_TYPE               6      5
2    0.00        2 OLD_PROF_TEST                  1     72
2    0.00        2 OLD_PROF_TEST                  1     45
2    0.00        2 OLD_PROF_TEST                  1     49
2    0.00        2 OLD_PROF_TEST                  1     46
2    0.00        2 OLD_PROF_TEST                  1     58
1    0.00        1 OLD_PROF_TEST                  1     73
1    0.00        1                                2      6
1    0.00        1 OLD_PROF_TEST                  1     59
1    0.00        1 OLD_PROF_TEST                  1     11
1    0.00        2 OLD_PROF_TEST                  1     54
1    0.00        2 OLD_PROF_TEST                  1     84
0    0.00        0 OLD_PROF_TEST                  1      1
0    0.00        0 OLD_PROF_TEST                  1     88
0    0.00        0                                8      9
0    0.00        0                                2      1
0    0.00        0                                2      2
0    0.00        0 OLD_PROF_TEST                  1      3
0    0.00        0 OLD_PROF_TEST                  1      5
0    0.00        0 OLD_PROF_TEST                  1      9
0    0.00        0 OLD_PROF_TEST                  1     20
0    0.00        1 TABLE_COUNT_TYPE               6      4
0    0.00        1                                8      2
0    0.00        2 OLD_PROF_TEST                  1     39
0    0.00        2 OLD_PROF_TEST                  1     55
0    0.00        2 OLD_PROF_TEST                  1     69
0    0.00        2 OLD_PROF_TEST                  1     38
0    0.00        2 OLD_PROF_TEST                  1     81
0    0.00        2 OLD_PROF_TEST                  1     42
0    0.00        2 OLD_PROF_TEST                  1     68

65 rows selected.



Referring to the package, type and anonymous blocks, I assigned labels to all the lines having more than 10µs, as follows:

   MICRO_S SECONDS    CALLS UNIT_NAME            UNIT_NUMBER  LINE#
---------- ------- -------- -------------------- ----------- ------
729932    0.73        1                                5     13  B2: FETCH
569563    0.57        2 OLD_PROF_TEST                  1     56  B_Calls_A (Rest_a_While)
377880    0.38        2 OLD_PROF_TEST                  1     82  DBFunc (Rest_a_While)
166019    0.17        2 OLD_PROF_TEST                  1     70  R_Calls_R (Rest_a_While)
150117    0.15        2 OLD_PROF_TEST                  1     43  A_Calls_B (Rest_a_While, section 2)
72742    0.07        2 OLD_PROF_TEST                  1     40  A_Calls_B (Rest_a_While, section 1)
56473    0.06        1 TABLE_COUNT_TYPE               6      6  SELECT
3338    0.00        1                                5      8  B2: SELECT DBFunc
258    0.00        1                                5     12  B2: OPEN
109    0.00        1                                5     16  B2: Assign Table_Count_Type
68    0.00        2 OLD_PROF_TEST                  1     67  Put_Line
66    0.00        2                                4      1  Auxiliary SERVEROUTPUT block for B2 (surmised)
60    0.00        2                                7      1  Auxiliary SERVEROUTPUT block for B3 (surmised)
60    0.00        2                                3      1  Auxiliary SERVEROUTPUT block for B1 (surmised)
44    0.00        1                                5     14  B2: CLOSE
42    0.00        0                                2      5  B1: Call to Start_Profiling
31    0.00        1 OLD_PROF_TEST                  1     18  RETURN DBMS_Profiler.Stop_Profiler;
26    0.00        1                                8      5  B3: Call R_Calls_R
13    0.00        0                                5      1  B2: DECLARE


Notes on Output of Flat Profiler
There were six units with no linked information in DBMS_PROFILER_UNITS. By examining the data, I was able to associate unit numbers 2, 5 and 8 with my anonymous blocks B1, B2 and B3. That left three unassigned, and I have surmised that these correspond to the auxiliary blocks associated with processing server output that we earlier surmised when examining the output from the hierarchical profiler.

• The useful call tree structure is not present in the data from the old profiler
• However, the results are at a line level, which the hierarchical profiler does not provide; for example, the two sections of A_Calls_B are reported separately
• Deciphering the output requires significantly more manual effort than with the hierarchical profiler
• Both old and new profiler have their own advantages, and so both should be considered of value
• Manual code timing offers more flexibility in terms of aggregating lines and call instances, but requires more effort...
• ...but not as much as I thought. As noted later on the second example, after reading another article on the profiler, I realised that I could join the system table ALL_SOURCE to see the text of the line (where available)

Second example: Flat profiler omits some detail timings
After posting the first draft of this article, which was about the newer hierarchical profiler only, I noticed a new post on an old AskTom thread on the older flat profiler. The post concerned a discrepancy between reported times at the aggregate level and detail levels. I suggested using the hierarchical profiler might resolve the problem Try the hierarchical profiler..., and then added sections on the old profiler and on manual timing to this article for comparison. However, my example programs above do not include the AskTom scenario, so I later decided to add a new small scenario to illustrate it and now report the results here. The new test code consists of a PL/SQL block with two calls to DBMS_Lock.Sleep, for 3 and 6 seconds, with the profiling code around them. The driving scripts and output files are included in the zip file attached, and I list summary results below:

I later came upon another artilce on the flat profiler, Profiling PL/SQL with dbms_profiler where the author has joined the system table ALL_SOURCE to get the text of the line profiled, which makes interpretation easier. I have then updated the line-level query as follows:

PROMPT Profiler data by time (PLSQL_PROFILER_DATA)
SELECT Round (dat.total_time/1000, 0)  micro_s,
Round (dat.total_time/1000000000, 2) seconds,
dat.total_occur calls,
unt.unit_name,
dat.unit_number,
dat.line#,
Trim (src.text) text
FROM plsql_profiler_data dat
LEFT JOIN plsql_profiler_units unt
ON unt.runid            = dat.runid
AND unt.unit_number      = dat.unit_number
LEFT JOIN all_source      src
ON src.type             IN ('PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
AND src.name             = unt.unit_name
AND src.line             = dat.line#
AND src.owner            = unt.unit_owner
AND src.type             = unt.unit_type
WHERE dat.runid            = :runid
AND dat.total_time       > 0
ORDER BY 1 DESC, 2, 3


Of course the text is only available for stored source, so excludes lines from anonymous blocks.

Flat Profiler

Run header (PLSQL_PROFILER_RUNS)

RUNID RUN_DATE        MICRO_S    SECONDS
---------- ------------ ---------- ----------
5 20:34:45        9220000       9.22

Profiler data by unit (PLSQL_PROFILER_DATA)

UNIT_NAME            UNIT_NUMBER    MICRO_S SECONDS    CALLS
-------------------- ----------- ---------- ------- --------
2        200    0.00        3
UTILS                          1         30    0.00        3

Profiler data by time (PLSQL_PROFILER_DATA)

MICRO_S SECONDS    CALLS UNIT_NAME            UNIT_NUMBER  LINE# TEXT
---------- ------- -------- -------------------- ----------- ------ ------------------------------------------------
136    0.00        1                     2      7
21    0.00        1                     2     10
19    0.00        1                     2     14
14    0.00        0 UTILS                          1    343 FUNCTION Stop_D_Profiling RETURN PLS_INTEGER IS
13    0.00        1 UTILS                          1    346 RETURN DBMS_Profiler.Stop_Profiler;
6    0.00        1 UTILS                          1    341 END Start_D_Profiling;
2    0.00        1 UTILS                          1    339 RETURN l_run_number;

7 rows selected.

Hierarchical Profiler

Run header (DBMSHP_RUNS)

RUNID RUN_TIMESTAMP                   MICRO_S    SECONDS RUN_COMMENT
---------- ---------------------------- ---------- ---------- -----------------------------
16 19-MAR-13 21.37.00.571000       9000292          9 Profile for DBMS_Lock.Sleep

Functions called (DBMSHP_FUNCTION_INFO)

OWNER      MODULE               FUNCTION                LINE#      SUB_T      FUN_T  CALLS
---------- -------------------- ---------------------- ------ ---------- ---------- ------
BRENDAN    UTILS                1: STOP_H_PROFILING       322          8          8      1
SYS        DBMS_HPROF           2: STOP_PROFILING          59          0          0      1
SYS        DBMS_LOCK            3: SLEEP                  197    9000279    9000279      2
SYS        DBMS_LOCK            4: __pkg_init               0          5          5      1

OWNER_P MODULE_P  FUNCTION_P            OWNER_C MODULE_C    FUNCTION_C         SUB_T FUN_T  CALLS
------- --------- --------------------- ------- ----------- ------------------ ----- ----- ------
BRENDAN UTILS     1: STOP_H_PROFILING   SYS     DBMS_HPROF  2: STOP_PROFILING      0     0      1

BPF Recursive Subquery Factor Tree Query

Function tree                              SUB_T      FUN_T  CALLS  Row
------------------------------------- ---------- ---------- ------ ----
3: SYS.DBMS_LOCK.SLEEP                   9000279    9000279      2    1
1: BRENDAN.UTILS.STOP_H_PROFILING              8          8      1    2
2: SYS.DBMS_HPROF.STOP_PROFILING             0          0      1    3
4: SYS.DBMS_LOCK.__pkg_init                    5          5      1    4


Manual Profiler

Timer Set: Profiling DBMS_Lock.Sleep, Constructed at 19 Mar 2013 21:38:54, written at 21:39:03
==============================================================================================
[Timer timed: Elapsed (per call): 0.05 (0.000045), CPU (per call): 0.04 (0.000040), calls: 1000, '***' denotes corrected line below]

Timer               Elapsed          CPU          Calls        Ela/Call        CPU/Call
--------------   ----------   ----------   ------------   -------------   -------------
3 second sleep         3.00         0.00              1         3.00000         0.00000
6 second sleep         6.00         0.00              1         6.00100         0.00000
(Other)                0.00         0.00              1         0.00000         0.00000
--------------   ----------   ----------   ------------   -------------   -------------
Total                  9.00         0.00              3         3.00033         0.00000
--------------   ----------   ----------   ------------   -------------   -------------


Notes on Results for Second Example

• The flat profiler shows 9s at header level but only 230µs at detail level because DBMS_Lock.Sleep does not permit profiling by the user running the script
• The hierarchical profiler shows 9s at header level and a total of 9s in 2 calls to DBMS_Lock.Sleep
• Manual profiling shows the two calls to DBMS_Lock.Sleep taking 3 and 6 seconds

Conclusions

• Running Oracle's hierarchical profiler would seem to be the default first step in tuning PL/SQL programs from v11.1
• Some care is needed in interpreting the output data; I've provided a query for displaying the hierarchies
• Performance is recorded only down to function level, so it will still often be worthwhile to use the old flat profiler in addition
• Manually timing code sections also still has a part to play, in terms of instrumentation and greater flexibility where necessary

Brendan HProf Code
Example 2

# An SQL Solution for the Multiple Knapsack Problem (SKP-m)

In my last article, A Simple SQL Solution for the Knapsack Problem (SKP-1), I presented an SQL solution for the well known knapsack problem in its simpler 1-knapsack form (and it is advisable to read the first article before this one). Here I present an SQL solution for the problem in its more difficult multiple-knapsack form. The solution is a modified version of one I posted on OTN, SQL Query for mapping a set of batches to a class rooms group, and I describe two versions of it, one in pure SQL, and another that includes a database function. The earlier article provided the solutions as comma-separated strings of item identifiers, and in this article also the solutions are first obtained as delimited strings. However, as there are now containers as well as items, we extend the SQL to provide solutions with item and container names in separate fields within records for each container-item pair. The solution is presented, as before, more for its theoretical interest than for practical applicability. Much research has been done on procedural algorithms for this important, but computationally difficult class of problems.

We will consider the same simple example problem as in the earlier article, having four items, but now with two containers with individual weight limits of 8 and 10. As noted in the earlier article, the problem can be considered as that of assigning each item to one of the containers, or to none, leading directly to the expression  for the number of not necessarily feasible assignment sets for the example. We can again depict the 24 possible item combinations in a diagram, with the container limits added.

We can see that there is one optimal solution in this case, in which items 1 and 3 are assigned to container 1, while items 2 and 4 are assigned to container 2, with a profit of 100. How to find it using SQL?

SQL Solution
The solution to the single knapsack problem worked by joining items recursively in increasing order of item id, accumulating the total weights and profits, and terminating a sequence when no more items can be added within the weight limit. The item sequences were accumulated as comma-separated strings, and the optimal solutions obtained by analytic ranking of the profits.

For the multiple knapsack problem, it's not quite as simple, but a similar approach may be a good starting point. Previously our anchor branch in the recursion selected all items below the single maximum weight, but we now have containers with individual weights. If we now join the containers table we can find all items falling within the maximum weights by container. The recursion can then proceed to find all feasible item combinations by container. Here is the SQL for this:

WITH rsf_itm (con_id, max_weight, itm_id, lev, tot_weight, tot_profit, path) AS (
SELECT c.id,
c.max_weight,
i.id,
0,
i.item_weight,
i.item_profit,
',' || i.id || ','
FROM items i
JOIN containers c
ON i.item_weight <= c.max_weight
UNION ALL
SELECT r.con_id,
r.max_weight,
i.id,
r.lev + 1,
r.tot_weight + i.item_weight,
r.tot_profit + i.item_profit,
r.path || i.id || ','
FROM rsf_itm r
JOIN items i
ON i.id > r.itm_id
AND r.tot_weight + i.item_weight <= r.max_weight
ORDER BY 1, 2
) SEARCH DEPTH FIRST BY con_id, itm_id SET line_no
SELECT con_id,
max_weight,
LPad (To_Char(itm_id), 2*lev + 1, ' ') itm_id,
path itm_path,
tot_weight, tot_profit
FROM rsf_itm
ORDER BY line_no


and here is the resulting output:

CON_ID MAX_WEIGHT ITM_ID ITM_PATH    TOT_WEIGHT TOT_PROFIT
------ ---------- ------ ----------- ---------- ----------
1          8 1      ,1,                  3         10
2    ,1,2,                7         30
3    ,1,3,                8         40
2      ,2,                  4         20
3      ,3,                  5         30
4      ,4,                  6         40
2         10 1      ,1,                  3         10
2    ,1,2,                7         30
3    ,1,3,                8         40
4    ,1,4,                9         50
2      ,2,                  4         20
3    ,2,3,                9         50
4    ,2,4,               10         60
3      ,3,                  5         30
4      ,4,                  6         40

15 rows selected.


Looking at this, we can see that the overall solution will comprise one feasible combination of items for each container, with the constraint that no item appears in more than one container. This suggests that we could perform a second recursion in a similar way to the first, but this time using the results of the first as input, and joining the feasible combinations of containers of higher id only. If we again accumulate the sequence in a delimited string, regular expression functionality could be used to avoid joining combinations with items already included. The following SQL does this recursion:

WITH rsf_itm (con_id, max_weight, itm_id, tot_weight, tot_profit, path) AS (
SELECT c.id,
c.max_weight,
i.id,
i.item_weight,
i.item_profit,
',' || i.id || ','
FROM items i
JOIN containers c
ON i.item_weight <= c.max_weight
UNION ALL
SELECT r.con_id,
r.max_weight,
i.id,
r.tot_weight + i.item_weight,
r.tot_profit + i.item_profit,
r.path || i.id || ','
FROM rsf_itm r
JOIN items i
ON i.id > r.itm_id
AND r.tot_weight + i.item_weight <= r.max_weight
)
, rsf_con (con_id, con_itm_set, con_itm_path, lev, tot_weight, tot_profit) AS (
SELECT con_id,
':' || con_id || ':' || path,
':' || con_id || ':' || path,
0,
tot_weight,
tot_profit
FROM rsf_itm
UNION ALL
SELECT r_i.con_id,
':' || r_i.con_id || ':' || r_i.path,
r_c.con_itm_path ||  ':' || r_i.con_id || ':' || r_i.path,
r_c.lev + 1,
r_c.tot_weight + r_i.tot_weight,
r_c.tot_profit + r_i.tot_profit
FROM rsf_con r_c
JOIN rsf_itm r_i
ON r_i.con_id > r_c.con_id
WHERE RegExp_Instr (r_c.con_itm_path || r_i.path, ',(\d+),.*?,\1,') = 0
) SEARCH DEPTH FIRST BY con_id SET line_no
SELECT
LPad (' ', 2*lev, ' ') || con_itm_set con_itm_set,
con_itm_path,
tot_weight, tot_profit
FROM rsf_con
ORDER BY line_no


Notice the use of RegExp_Instr, which takes the current sequence with potential new combination appended as its source string, and looks for a match against the search string ',(\d+),.*?,\1,'. The function returns 0 if no match is found, meaning no duplicate item was found. The sequence includes the container id using a different delimiter, a colon, at the start of each combination. The search string can be explained as follows:

,(\d+), = a sequence of one or more digits with a comma either side, and the digit sequence saved for referencing
.*?,\1, = a sequence of any characters, followed by the saved digit sequence within commas. The ? specifies a non-greedy search, meaning stop searching as soon as a match is found

The result of the query is:

CON_ITM_SET          CON_ITM_PATH         TOT_WEIGHT TOT_PROFIT
-------------------- -------------------- ---------- ----------
:1:,1,               :1:,1,                        3         10
:2:,2,             :1:,1,:2:,2,                  7         30
:2:,3,             :1:,1,:2:,3,                  8         40
:2:,4,             :1:,1,:2:,4,                  9         50
:2:,2,3,           :1:,1,:2:,2,3,               12         60
:2:,2,4,           :1:,1,:2:,2,4,               13         70
:1:,2,               :1:,2,                        4         20
:2:,1,             :1:,2,:2:,1,                  7         30
:2:,3,             :1:,2,:2:,3,                  9         50
:2:,1,3,           :1:,2,:2:,1,3,               12         60
:2:,4,             :1:,2,:2:,4,                 10         60
:2:,1,4,           :1:,2,:2:,1,4,               13         70
:1:,1,2,             :1:,1,2,                      7         30
:2:,3,             :1:,1,2,:2:,3,               12         60
:2:,4,             :1:,1,2,:2:,4,               13         70
:1:,3,               :1:,3,                        5         30
:2:,1,             :1:,3,:2:,1,                  8         40
:2:,2,             :1:,3,:2:,2,                  9         50
:2:,1,2,           :1:,3,:2:,1,2,               12         60
:2:,4,             :1:,3,:2:,4,                 11         70
:2:,1,4,           :1:,3,:2:,1,4,               14         80
:2:,2,4,           :1:,3,:2:,2,4,               15         90
:1:,1,3,             :1:,1,3,                      8         40
:2:,2,             :1:,1,3,:2:,2,               12         60
:2:,4,             :1:,1,3,:2:,4,               14         80
:2:,2,4,           :1:,1,3,:2:,2,4,             18        100
:1:,4,               :1:,4,                        6         40
:2:,1,             :1:,4,:2:,1,                  9         50
:2:,2,             :1:,4,:2:,2,                 10         60
:2:,1,2,           :1:,4,:2:,1,2,               13         70
:2:,3,             :1:,4,:2:,3,                 11         70
:2:,1,3,           :1:,4,:2:,1,3,               14         80
:2:,2,3,           :1:,4,:2:,2,3,               15         90
:2:,1,               :2:,1,                        3         10
:2:,2,               :2:,2,                        4         20
:2:,1,2,             :2:,1,2,                      7         30
:2:,3,               :2:,3,                        5         30
:2:,1,3,             :2:,1,3,                      8         40
:2:,4,               :2:,4,                        6         40
:2:,1,4,             :2:,1,4,                      9         50
:2:,2,3,             :2:,2,3,                      9         50
:2:,2,4,             :2:,2,4,                     10         60

42 rows selected.


We can see that the optimal solutions can be obtained from the output again using analytic ranking by profit, and in this case the solution with a profit of 100 is the optimal one, with sequence ':1:,1,3,:2:,2,4,'. In the full solution, as well as selecting out the top-ranking solutions, we have extended the query to output the items and containers by name, in distinct fields with a record for every solution/container/item combination. For the example problem above, the output is:

    SOL_ID S_WT  S_PR  C_ID C_NAME          M_WT C_WT  I_ID I_NAME     I_WT I_PR
---------- ---- ----- ----- --------------- ---- ---- ----- ---------- ---- ----
1   18   100     1 Item 1             8    8     1 Item 1        3   10
3 Item 3        5   30
2 Item 2            10   10     2 Item 2        4   20
4 Item 4        6   40


SQL-Only Solution - XSQL
There are various techniques in SQL for splitting string columns into multiple rows and columns. We will take one of the more straightforward ones that uses the DUAL table with CONNECT BY to generate rows against which to anchor the string-parsing.

WITH rsf_itm (con_id, max_weight, itm_id, lev, tot_weight, tot_profit, path) AS (
SELECT c.id,
c.max_weight,
i.id,
0,
i.item_weight,
i.item_profit,
',' || i.id || ','
FROM items i
JOIN containers c
ON i.item_weight <= c.max_weight
UNION ALL
SELECT r.con_id,
r.max_weight,
i.id,
r.lev + 1,
r.tot_weight + i.item_weight,
r.tot_profit + i.item_profit,
r.path || i.id || ','
FROM rsf_itm r
JOIN items i
ON i.id > r.itm_id
AND r.tot_weight + i.item_weight <= r.max_weight
)
, rsf_con (con_id, con_path, itm_path, tot_weight, tot_profit, lev) AS (
SELECT con_id,
To_Char(con_id),
':' || con_id || '-' || (lev + 1) || ':' || path,
tot_weight,
tot_profit,
0
FROM rsf_itm
UNION ALL
SELECT r_i.con_id,
r_c.con_path || ',' || r_i.con_id,
r_c.itm_path ||  ':' || r_i.con_id || '-' || (r_i.lev + 1) || ':' || r_i.path,
r_c.tot_weight + r_i.tot_weight,
r_c.tot_profit + r_i.tot_profit,
r_c.lev + 1
FROM rsf_con r_c
JOIN rsf_itm r_i
ON r_i.con_id > r_c.con_id
AND RegExp_Instr (r_c.itm_path || r_i.path, ',(\d+),.*?,\1,') = 0
)
, paths_ranked AS (
SELECT itm_path || ':' itm_path, tot_weight, tot_profit, lev + 1 n_cons,
Rank () OVER (ORDER BY tot_profit DESC) rnk
FROM rsf_con
), best_paths AS (
SELECT itm_path, tot_weight, tot_profit, n_cons,
Row_Number () OVER (ORDER BY tot_weight DESC) sol_id
FROM paths_ranked
WHERE rnk = 1
), row_gen AS (
SELECT LEVEL lev
FROM DUAL
CONNECT BY LEVEL <= (SELECT Count(*) FROM items)
), con_v AS (
SELECT  r.lev con_ind, b.sol_id, b.tot_weight, b.tot_profit,
Substr (b.itm_path, Instr (b.itm_path, ':', 1, 2*r.lev - 1) + 1,
Instr (b.itm_path, ':', 1, 2*r.lev) - Instr (b.itm_path, ':', 1, 2*r.lev - 1) - 1)
con_nit_id,
Substr (b.itm_path, Instr (b.itm_path, ':', 1, 2*r.lev) + 1,
Instr (b.itm_path, ':', 1, 2*r.lev + 1) - Instr (b.itm_path, ':', 1, 2*r.lev) - 1)
itm_str
FROM best_paths b
JOIN row_gen r
ON r.lev <= b.n_cons
), con_split AS (
SELECT sol_id, tot_weight, tot_profit,
Substr (con_nit_id, 1, Instr (con_nit_id, '-', 1) - 1) con_id,
Substr (con_nit_id, Instr (con_nit_id, '-', 1) + 1) n_items,
itm_str
FROM con_v
), itm_v AS (
SELECT  c.sol_id, c.con_id, c.tot_weight, c.tot_profit,
Substr (c.itm_str, Instr (c.itm_str, ',', 1, r.lev) + 1,
Instr (c.itm_str, ',', 1, r.lev + 1) - Instr (c.itm_str, ',', 1, r.lev) - 1)
itm_id
FROM con_split c
JOIN row_gen r
ON r.lev <= c.n_items
)
SELECT
/* SEL */
v.sol_id sol_id,
v.tot_weight s_wt,
v.tot_profit s_pr,
c.id c_id,
c.name c_name,
c.max_weight m_wt,
Sum (i.item_weight) OVER (PARTITION BY v.sol_id, c.id) c_wt,
i.id i_id,
i.name i_name,
i.item_weight i_wt,
i.item_profit i_pr
/* SEL */
FROM itm_v v
JOIN containers c
ON c.id = To_Number (v.con_id)
JOIN items i
ON i.id = To_Number (v.itm_id)
ORDER BY sol_id, con_id, itm_id


SQL with Function Solution - XFUN
The SQL techniques for string-splitting are quite cumbersome, and a better approach may be the use of a pipelined function that allows the string-parsing to be done in PL/SQL, a procedural language that is better suited to the task.

WITH rsf_itm (con_id, max_weight, itm_id, tot_weight, tot_profit, path) AS (
SELECT c.id,
c.max_weight,
i.id,
i.item_weight,
i.item_profit,
',' || i.id || ','
FROM items i
JOIN containers c
ON i.item_weight <= c.max_weight
UNION ALL
SELECT r.con_id,
r.max_weight,
i.id,
r.tot_weight + i.item_weight,
r.tot_profit + i.item_profit,
r.path || i.id || ','
FROM rsf_itm r
JOIN items i
ON i.id > r.itm_id
AND r.tot_weight + i.item_weight <= r.max_weight
ORDER BY 1, 2
)
, rsf_con (con_id, itm_path, tot_weight, tot_profit) AS (
SELECT con_id,
':' || con_id || ':' || path,
tot_weight,
tot_profit
FROM rsf_itm
UNION ALL
SELECT r_i.con_id,
r_c.itm_path ||  ':' || r_i.con_id || ':' || r_i.path,
r_c.tot_weight + r_i.tot_weight,
r_c.tot_profit + r_i.tot_profit
FROM rsf_con r_c
JOIN rsf_itm r_i
ON r_i.con_id > r_c.con_id
AND RegExp_Instr (r_c.itm_path || r_i.path, ',(\d+),.*?,\1,') = 0
)
, paths_ranked AS (
SELECT itm_path || ':' itm_path, tot_weight, tot_profit, Rank () OVER (ORDER BY tot_profit DESC) rn,
Row_Number () OVER (ORDER BY tot_profit DESC, tot_weight DESC) sol_id
FROM rsf_con
), itm_v AS (
SELECT s.con_id, s.itm_id, p.itm_path, p.tot_weight, p.tot_profit, p.sol_id
FROM paths_ranked p
CROSS JOIN TABLE (Multi.Split_String (p.itm_path)) s
WHERE rn = 1
)
SELECT v.sol_id sol_id,
v.tot_weight s_wt,
v.tot_profit s_pr,
c.id c_id,
c.name c_name,
c.max_weight m_wt,
Sum (i.item_weight) OVER (PARTITION BY v.sol_id, c.id) c_wt,
i.id i_id,
i.name i_name,
i.item_weight i_wt,
i.item_profit i_pr
FROM itm_v v
JOIN containers c
ON c.id = To_Number (v.con_id)
JOIN items i
ON i.id = To_Number (v.itm_id)
ORDER BY sol_id, con_id, itm_id


Pipelined Database Function

CREATE OR REPLACE TYPE con_itm_type AS OBJECT (con_id NUMBER, itm_id NUMBER);
/
CREATE OR REPLACE TYPE con_itm_list_type AS VARRAY(100) OF con_itm_type;
/
CREATE OR REPLACE PACKAGE BODY Multi IS

FUNCTION Split_String (p_string VARCHAR2) RETURN con_itm_list_type PIPELINED IS

l_pos_colon_1           PLS_INTEGER := 1;
l_pos_colon_2           PLS_INTEGER;
l_pos_comma_1           PLS_INTEGER;
l_pos_comma_2           PLS_INTEGER;
l_con                   PLS_INTEGER;
l_itm                   PLS_INTEGER;

BEGIN

LOOP

l_pos_colon_2 := Instr (p_string, ':', l_pos_colon_1 + 1, 1);
EXIT WHEN l_pos_colon_2 = 0;

l_con := To_Number (Substr (p_string, l_pos_colon_1 + 1, l_pos_colon_2 - l_pos_colon_1 - 1));
l_pos_colon_1 := Instr (p_string, ':', l_pos_colon_2 + 1, 1);
l_pos_comma_1 := l_pos_colon_2 + 1;

LOOP

l_pos_comma_2 := Instr (p_string, ',', l_pos_comma_1 + 1, 1);
EXIT WHEN l_pos_comma_2 = 0 OR l_pos_comma_2 > l_pos_colon_1;

l_itm := To_Number (Substr (p_string, l_pos_comma_1 + 1, l_pos_comma_2 - l_pos_comma_1 - 1));
PIPE ROW (con_itm_type (l_con, l_itm));
l_pos_comma_1 := l_pos_comma_2;

END LOOP;

END LOOP;

END Split_String;

END Multi;


Query Structure Diagram (embedded directly)
The QSD shows both queries in a single diagram as the early query blocks are almost the same (the main difference is that the strings contain a bit more information for XSQL to facilitate the later splitting). The directly-embedded version shows the whole query, but it may be hard to read the detail, so it is followed by a larger, scrollable version within Excel.

Query Structure Diagram (embedded via Excel)
This is the larger, scrollable version.

Performance Analysis
As in the previous article, we will see how the solution methods perform as problem size varies, using my own performance benchmarking framework.

Test Data Sets
Test data sets are generated as follows, in terms of two integer parameters, w and d:

• Insert w containers with sequential ids and random maximum weights between 1 and 100
• Insert d items with sequential ids and random weights and profits in the ranges 1-60 and 1-10000, respectively, via Oracle's function DBMS_Random.Value

Test Results
The embedded Excel file below summarises the results obtained over a grid of data points, with w in (1, 2, 3) and d in (8, 10, 12, 14, 16, 18).

The graphs tab below shows 3-d graphs of the number of rows processed and the CPU time for XFUN.

Notes

• There is not much difference in performance between the two query versions, no doubt because the number of solution records is generally small compared with rows processed in the recursions
• Notice that the timings correlate well with the rows processed, but not so well with the numbers of base records. The nature of the problem means that some of the randomised data sets turn out to be much harder to solve than others
• Notice the estimated rows on step 36 of the execution plan for the pipelined function solution. The value of 8168 is a fixed value that Oracle assumes since it has no statistics to go on. We could improve this by using the (undocumented) cardinality hint to provide a smaller estimate
• I extended my benchmarking framework for this article to report the intermediate numbers of rows processed, as well as the cardinality estimates and derived errors in these estimates (maximum for each plan). It is obvious from the nature of the problem that Oracle's Cost Based Optimiser (CBO) is not going to be able to make good cardinality estimates

Conclusions
Oracle's v11.2 implementation of the Ansii SQL feature recursive subquery factoring provides a means for solving the knapsack problem, in its multiple knapsack form, in SQL. The solution is not practical for large problems, for which procedural techniques that have been extensively researched should be considered. However, the techniques used may be of interest for combinatorial problems that are small enough to be handled in SQL, and for other types of problem in general.

# A Simple SQL Solution for the Knapsack Problem (SKP-1)

A poster on OTN (Combination using pl/sql) recently asked for an SQL solution to a problem that turned out to be an example of the well known Knapsack Problem, for the case of a single knapsack. I posted an SQL query as a solution, and also a solution in PL/SQL because the SQL solution uses a feature only available in Oracle v11.2. In this article I explain how the solutions work and provide the results of a performance analysis that involved randomised test problems of varying computational difficulty. I have taken a more general form of problem than the original poster described, and the solutions here have been improved.

Update, 14 July 2013: I used the technique in response to another OTN post here, SQL for the Fantasy Football Knapsack Problem. I have extended the idea there to allow for fast approximate solutions making it viable for larger problems, and have also used a similar idea here, SQL for the Travelling Salesman Problem.

Knapsack Problem (1-Knapsack)
The various forms of knapsack problem have been studied extensively. The problems are known to be computationally difficult and many algorithms have been proposed for both exact and approximate solutions (see reference above). The SQL solution in this article is quite simple and will not be competitive in performance for larger problems in the form described here, but may be interesting for being implemented in pure SQL (and without using Oracle's Model clause, or a purely brute force approach). However, I have later extended the approach to allow for search limiting and have shown this to be viable for larger problems (see links at the top).

The problem can be stated informally, as follows: Given a set of items, each having positive weight and profit attributes, and a weight limit, find the combinations of items that maximise profit within the weight limit. Variant versions include the addition of multiple constraints (easy to handle), and inclusion of multiple knapsacks (more difficult). I also have a solution for the multiple knapsacks version described here (An SQL Solution for the Multiple Knapsack Problem (SKP-m)).

The difficulty of the problem arises from the number of possible combinations increasing exponentially with problem size. The number of these (not necessarily feasible) combinations, N(n,1), can be expressed in terms of the number of items, n, in two ways. First, we can use the well known binomial expression for the number of combinations of r items, summed from  to :

N(n,1) = 

where 

Second, and more simply, we can observe that including an item in the combination, or not, is a binary choice, leading to:

N(n,1) = 

This generalises easily to the expression for the multiple knapsack problem, with m knapsacks:

N(n,m) = 

This can also be expressed using a binomial series as

N(n,m) = 

Here,  represents the number of combinations of r items from n, with  being the number of assignments of the r items to m containers.

Let's look at a simple example problem having four items, with a weight limit of 9, as shown below:

There are 24 possible combinations of these items, having from 0 to 4 items. These are depicted below:

We can see that there are two optimal solutions in this case. How to find them using SQL?

SQL Solution

Oracle's v11.2 implementation of the Ansii standard Recursive Subquery Factoring can be used as the basis for an SQL solution. This would works as follows: Starting from each item in turn, add items recursively while remaining within the weight limit, and considering only items of id greater than the current id. The SQL looks like this, where a marker is added for leaf nodes, following an approach from the Amis technology blog:

WITH rsf (nxt_id, lev, tot_weight, tot_profit, path) AS (
SELECT id nxt_id, 0 lev, item_weight tot_weight, item_profit tot_profit, To_Char (id) path
FROM items
UNION ALL
SELECT n.id,
r.lev + 1,
r.tot_weight + n.item_weight,
r.tot_profit + n.item_profit,
r.path || ',' || To_Char (n.id)
FROM rsf r
JOIN items n
ON n.id > r.nxt_id
AND r.tot_weight + n.item_weight <= 9
) SEARCH DEPTH FIRST BY nxt_id SET line_no
SELECT LPad (To_Char(nxt_id), lev + 1, '*') node,tot_weight, tot_profit,
CASE WHEN lev >= Lead (lev, 1, lev) OVER (ORDER BY line_no) THEN 'Y' END is_leaf,
path
FROM rsf
ORDER BY line_no


and the solution like this:

NODE       TOT_WEIGHT TOT_PROFIT I PATH
---------- ---------- ---------- - ------------------------------
1                   3         10   1
*2                  7         30 Y 1,2
*3                  8         40 Y 1,3
*4                  9         50 Y 1,4
2                   4         20   2
*3                  9         50 Y 2,3
3                   5         30 Y 3
4                   6         40 Y 4

8 rows selected.

The output contains 8 records, as opposed to the total of 23 non-null combinations, because only feasible items are joined, and permutations are avoided by the constraint that item ids increase along the path. Given positivity of weight and profit, we know that all solutions must be leaves, and we can represent the tree structure above in the following diagram:

We can now use the recursive subquery factor as an input to a main query that selects one of the most profitable solutions, or alternatively to a further subquery factor that ranks the solutions in order of descending profit. In the latter case, the main query can select all the most profitable solutions.

In the solution I posted on the OTN thread, I included a subquery factor to restrict the final query section to leaf nodes only. This was because we know that the solutions must be leaf nodes, and usually it is more efficient to filter out non-solution records as early as possible. However, I later realised that the work involved in the filtering might outweigh the saving for the final section, and this turned out to be the case here, as shown in the performance analysis section below. Here are the two queries, without the leaf node filtering:

Query - KEEP

WITH rsf (id, lev, tot_weight, tot_profit, path) AS (
SELECT id, 0, item_weight, item_profit, To_Char (id)
FROM items
UNION ALL
SELECT n.id,
r.lev + 1,
r.tot_weight + n.item_weight,
r.tot_profit + n.item_profit,
r.path || ',' || To_Char (n.id)
FROM rsf r
JOIN items n
ON n.id > r.id
AND r.tot_weight + n.item_weight <= 100
)
SELECT Max (tot_weight) KEEP (DENSE_RANK LAST ORDER BY tot_profit) tot_weight,
Max (tot_profit) KEEP (DENSE_RANK LAST ORDER BY tot_profit) tot_profit,
Max (path) KEEP (DENSE_RANK LAST ORDER BY tot_profit) path,
(Max (lev) KEEP (DENSE_RANK LAST ORDER BY tot_profit) + 1) n_items
FROM rsf

Query - RANK

WITH rsf (id, lev, tot_weight, tot_profit, path) AS (
SELECT id, 0, item_weight, item_profit, To_Char (id)
FROM items
UNION ALL
SELECT n.id,
r.lev + 1,
r.tot_weight + n.item_weight,
r.tot_profit + n.item_profit,
r.path || ',' || To_Char (n.id)
FROM rsf r
JOIN items n
ON n.id > r.id
AND r.tot_weight + n.item_weight <= 100
)
, paths_ranked AS (
SELECT tot_weight, tot_profit, path,
Dense_Rank () OVER (ORDER BY tot_profit DESC) rnk_profit,
lev
FROM rsf
)
SELECT tot_weight tot_weight,
tot_profit tot_profit,
path path,
(lev + 1) n_items
FROM paths_ranked
WHERE rnk_profit = 1
ORDER BY tot_weight DESC

Query Structure Diagram

It's worth noting that Oracle's proprietary recursive syntax, Connect By, cannot be used in this way because of the need to accumulate weights forward through the recursion. The new Ansii syntax is only available from v11.2 though, and I thought it might be interesting to implement a solution in PL/SQL that would work in earlier versions, following a similar algorithm, again with recursion.

PL/SQL Recursive Solution

This is a version in the form of a pipelined function, as I wanted to compare it with the SQL solutions, and be callable from SQL.
SQL

SELECT COLUMN_VALUE sol
FROM TABLE (Packing_PLF.Best_Fits (100))
ORDER BY COLUMN_VALUE

Package

CREATE OR REPLACE PACKAGE BODY Packing_PLF IS

PROCEDURE Write_Log (p_line VARCHAR2) IS
BEGIN
NULL;
END Write_Log;

FUNCTION Best_Fits (p_weight_limit NUMBER) RETURN SYS.ODCIVarchar2List PIPELINED IS

TYPE item_type IS RECORD (
item_id                 PLS_INTEGER,
item_index_parent       PLS_INTEGER,
weight_to_node          NUMBER);
TYPE item_tree_type IS        TABLE OF item_type;
g_solution_list               SYS.ODCINumberList;

g_timer                       PLS_INTEGER := Timer_Set.Construct ('Pipelined Recursion');

i                             PLS_INTEGER := 0;
j                             PLS_INTEGER := 0;
g_item_tree                   item_tree_type;
g_item                        item_type;
l_weight                      PLS_INTEGER;
l_weight_new                  PLS_INTEGER;
l_best_profit                 PLS_INTEGER := -1;
l_sol                         VARCHAR2(4000);
l_sol_cnt                     PLS_INTEGER := 0;

p_item_index_parent     PLS_INTEGER,
p_weight_to_node        NUMBER) RETURN PLS_INTEGER IS
BEGIN

g_item.item_id := p_item_id;
g_item.item_index_parent := p_item_index_parent;
g_item.weight_to_node := p_weight_to_node;
IF g_item_tree IS NULL THEN

g_item_tree := item_tree_type (g_item);

ELSE

g_item_tree.Extend;
g_item_tree (g_item_tree.COUNT) := g_item;

END IF;
RETURN g_item_tree.COUNT;

PROCEDURE Do_One_Level (p_tree_index PLS_INTEGER, p_item_id PLS_INTEGER, p_tot_weight PLS_INTEGER, p_tot_profit PLS_INTEGER) IS

CURSOR c_nxt IS
SELECT id, item_weight, item_profit
FROM items
WHERE id > p_item_id
AND item_weight + p_tot_weight <= p_weight_limit;
l_is_leaf           BOOLEAN := TRUE;
l_index_list        SYS.ODCINumberList;

BEGIN

FOR r_nxt IN c_nxt LOOP
Timer_Set.Increment_Time (g_timer,  'Do_One_Level/r_nxt');

l_is_leaf := FALSE;
Do_One_Level (Add_Node (r_nxt.id, p_tree_index, r_nxt.item_weight + p_tot_weight), r_nxt.id, p_tot_weight + r_nxt.item_weight, p_tot_profit + r_nxt.item_profit);
Timer_Set.Increment_Time (g_timer,  'Do_One_Level/Do_One_Level');

END LOOP;

IF l_is_leaf THEN

IF p_tot_profit > l_best_profit THEN

g_solution_list := SYS.ODCINumberList (p_tree_index);
l_best_profit := p_tot_profit;

ELSIF p_tot_profit = l_best_profit THEN

g_solution_list.Extend;
g_solution_list (g_solution_list.COUNT) := p_tree_index;

END IF;

END IF;
Timer_Set.Increment_Time (g_timer,  'Do_One_Level/leaves');

END Do_One_Level;

BEGIN

FOR r_itm IN (SELECT id, item_weight, item_profit FROM items) LOOP

Timer_Set.Increment_Time (g_timer,  'Root fetches');
Do_One_Level (Add_Node (r_itm.id, 0, r_itm.item_weight), r_itm.id, r_itm.item_weight, r_itm.item_profit);

END LOOP;

FOR i IN 1..g_solution_list.COUNT LOOP

j := g_solution_list(i);
l_sol := NULL;
l_weight := g_item_tree (j).weight_to_node;
WHILE j != 0 LOOP

l_sol := l_sol || g_item_tree (j).item_id || ', ';
j :=  g_item_tree (j).item_index_parent;

END LOOP;
l_sol_cnt := l_sol_cnt + 1;
PIPE ROW ('Solution ' || l_sol_cnt || ' (profit ' || l_best_profit || ', weight ' || l_weight || ') : ' || RTrim (l_sol, ', '));

END LOOP;

Timer_Set.Increment_Time (g_timer,  'Write output');
Write_Log ('Profit ' || l_best_profit || ' has ' || l_sol_cnt || ' solutions...');
Timer_Set.Write_Times (g_timer);

EXCEPTION
WHEN OTHERS THEN
Timer_Set.Write_Times (g_timer);
RAISE;

END Best_Fits;

END Packing_PLF;


Performance Analysis

It will be interesting to see how the solution methods perform as problem size varies, and we will use my own performance benchmarking framework to do this. As the framework is designed to compare performance of SQL queries, I have converted the PL/SQL solution to operate as a pipelined function, and thus be callable from SQL, as noted above. I included a version of the SQL solution, with the leaf filtering mentioned above, XKPLV - this was based on XKEEP, with filtering as in the OTN thread.

Test Data Sets

Test data sets are generated as follows, in terms of two integer parameters, w and d:

Insert w items with sequential ids, and random weights and profits in the ranges 0-d and 0-1000, respectively, via Oracle's function DBMS_Random.Value. The maximum weight is fixed at 100.

Test Results

The embedded Excel file below summarises the results obtained over a grid of data points, with w in (12, 14, 16, 18, 20) and d in (16, 18, 20).

Notes

• The two versions of the non-leaf SQL solution take pretty much the same time to execute, and are faster than the others
• The leaf version of the SQL solution (XKPLV) is slower than the non-leaf versions, and becomes much worse in terms of elapsed time for the more difficult problems; the step-change in performance can be seen to be due to its greater memory usage, which spills to disk above a certain level
• The pipelined function solution is significantly slower than the other solutions in terms of CPU time, and elapsed time, except in the case of the leaf SQL solution when that solution's memory usage spills to disk. The pipelined function continues to use more memory as the problem difficulty rises, until all available memory is consumed, when it throws an error (but this case is not included in the result set above)

Conclusions

Oracle's v11.2 implementation of the Ansii SQL feature recursive subquery factoring provides a simple solution for the knapsack problem, that cannot be achieved with Oracle's older Connect By syntax alone.

The method has been described here in its exact form that is viable only for small problems; however, I have later extended the approach to allow for search limiting and have shown this to be viable for larger problems.

# Master-Detail Transaction Reconciliation in SQL (MDTM3)

This is the final article in a sequence of three on the subject of master-detail transaction matching. In the first article, Master-Detail Transaction Matching in SQL (MDTM1), I described the problem and divided it into two subproblems, the first being to identify all pairs of matching transactions and the second being to reconcile the pairs so that one transaction matches against at most one other transaction. The underlying motivation here comes from the problem of reconciling intra-company credit and debit transactions where fields may need to match directly, or may need to match after some mapping function is applied, including inversion (contra-matching). We have taken a simple prototype problem defined on Oracle's system tables where only matching conditions are specified. It should be straightforward to extend the techniques demonstrated to more general matching conditions (I have done so myself on the real business problem that prompted this analysis).

The first article developed a series of queries to solve the first subproblem using the idea of pre-aggregation of the detail records as a key performance-enhancing feature. This resulted in a best query that used a temporary table and achieved a time variation that was quadratic in the number of master transactions (we kept the numbers of details per master fixed).

The second article, Holographic Set Matching in SQL (MDTM2), took the aggregation a step further, using list aggregation to bypass direct detail set matching altogether, and this enabled linear time variation.

In this third article, we take the last, linear-time query and extend it to solve the second subproblem, providing a solution to the overall problem in a single query that shows the same linear-time variation property in our test results. The sample problem will be the same as in the previous article.

Output Specification
The output will be ordered first by section, then by group, then by transaction unique identifier, with paired records appearing together using the first transaction for ordering within the group. The sections are defined thus:

• Reconciled pairs - two records for each matching pair, with no transaction appearing in more than one pair
• Matched but unreconciled transactions - transactions that match others but could not be reconciled because their matching partners are all paired off against other transactions
• Unmatched transactions - transactions not matching any other transaction

Queries
We'll include the best query from the last article (L2_SQF), as well as the new query (RECON) that extends it to solve the overall problem.

• L2_SQF - solves first subproblem by list aggregation without direct detil matching
• RECON - extends L2_SQF to solve the overall problem using a sequence of query subfactors

The first query will not be described below, as it appeared in the previous article but will be included in the results section for comparison purposes.

Query Structure Diagram (QSD)

Query Text

WITH rns AS (
SELECT r_owner,
r_constraint_name,
Row_Number () OVER (ORDER BY r_owner, r_constraint_name) - 1 rn
FROM con_cp
WHERE constraint_type	    = 'R'
GROUP BY
r_owner,
r_constraint_name
), rch AS (
SELECT r_owner,
r_constraint_name,
Chr (Floor (rn / 128)) ||
Chr ((rn - 128 * Floor (rn / 128))) chr_rank
FROM rns
), tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Count(c.ROWID)            n_det,
Listagg (r.chr_rank, '') WITHIN GROUP (ORDER BY r.chr_rank) lagg
FROM tab_cp                    t
JOIN con_cp                    c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
AND c.constraint_type         = 'R'
JOIN rch                       r
ON r.r_owner                 = c.r_owner
AND r.r_constraint_name       = c.r_constraint_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
), dup as (
SELECT t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.lagg                   = t1.lagg
AND t2.row_id                 > t1.row_id
), btw AS (
SELECT owner_1       owner_1_0,
table_name_1  table_name_1_0,
owner_1,
table_name_1,
owner_2,
table_name_2,
n_det
FROM dup
UNION
SELECT owner_1,
table_name_1,
owner_2,
table_name_2,
owner_1,
table_name_1,
n_det
FROM dup
), grp AS (
SELECT owner_1_0,
table_name_1_0,
owner_1,
table_name_1,
owner_2,
table_name_2,
n_det,
Least (owner_1 || '/' || table_name_1, Min (owner_2 || '/' || table_name_2)
OVER (PARTITION BY owner_1, table_name_1)) grp_uid
FROM btw
), rnk AS (
SELECT owner_1_0,
table_name_1_0,
owner_1,
table_name_1,
Dense_Rank () OVER  (PARTITION BY grp_uid ORDER BY owner_1, table_name_1) r1,
owner_2,
table_name_2,
Dense_Rank () OVER  (PARTITION BY grp_uid ORDER BY owner_2, table_name_2) r2,
n_det,
grp_uid
FROM grp
), rec AS (
SELECT owner_1_0,
table_name_1_0,
owner_1,
table_name_1,
owner_2,
table_name_2,
n_det,
grp_uid
FROM rnk
WHERE (r2 = r1 + 1 AND Mod (r1, 2) = 1) OR (r1 = r2 + 1 AND Mod (r2, 2) = 1)
), rcu AS (
SELECT owner_1_0,
table_name_1_0,
owner_1,
table_name_1,
owner_2,
table_name_2,
n_det,
grp_uid
FROM rec
UNION
SELECT owner_1,
table_name_1,
owner_1,
table_name_1,
NULL,
NULL,
n_det,
grp_uid
FROM grp
WHERE (owner_1, table_name_1) NOT IN (SELECT owner_1, table_name_1 FROM rec)
UNION
SELECT owner,
table_name,
owner,
table_name,
NULL,
NULL,
n_det,
NULL
FROM tab
WHERE (owner, table_name) NOT IN (SELECT owner_1, table_name_1 FROM btw)
)
SELECT
owner_1,
table_name_1,
owner_2,
table_name_2,
n_det,
grp_uid
FROM rcu
ORDER BY grp_uid,
CASE WHEN grp_uid IS NULL THEN 3
WHEN owner_2 IS NULL THEN 2
ELSE 1
END,
owner_1_0,
table_name_1_0,
owner_1,
table_name_1

How it Works
The query proceeds by ten stages using query subfactors. The first three stages correspond to query L2_SQF which then has a main query, whereas we now have another six stages before the main query, as shown:

1. Rank the distinct details [Group by matching fields, then use Row_Number to rank by same]
2. Convert ranks to base 128 [Use Floor() and Chr() functions; uses 2 characters here]
3. Aggregate detail ranks for master [Use Listagg on the rank fixed-length string]
4. Get all matching pairs one-way [Self-join on matching aggregate and second rowid greater]
5. Union in the reverse pairs, with sorting column [Add in records with uids swapped, but keep uid 1 separately for sorting]
6. Assign grouping field to each pair [Take minimum of uid 2 over uid 1, or uid 1 if smaller]
7. Rank each side of pair within its group [Use Dense_Rank() over grouping, ordering by uids]
8. Retain odd-even sequentially ranked pairs [Retain pairs with ranks (1,2) or (3,4) etc. and the reverses]
9. Add in unmatched and matched but unreconciled [3-way union: first the reconciled; then the matched but unreconciled; then unmatched]
10. Sort by the source uid 1, then the current uid 1 [Sort key ensures that reconciled pairs stay together within their matching group]

Notes:

• In our matching-only sample problem, matching transactions form mutually matching sets, whereas for contra-matching, there are pairs of contra-matching sets as discussed in the first article. The grouping subqueries will therefore differ in the latter case, and for example, pairing could be by matching numerical rank within the respective sets
• The final subquery factor could be incorporated in the main query, but I retain it because the benchmarking framework does not support unions in the main query, and CBO optimises it away in any case

Results
Both queries were run on the same sample problem as in the previous article. The output comparison gives the output listings for width parameter of 1, which corresponds to the tables and constraints on my v11.2 system copied to the test tables with owner prefix '_0' added. The timings and statistics are for widths from 1 to 6.

Output Comparison

The output file has tabs for the output from both queries, and a tab with examples from each of the three sections for the second. Notice that OEHR_EMPLOYEES and OEHR_JOB_HISTORY form a reconciled pair, with three detail records, while EMPLOYEES and JOB_HISTORY are unmatched, with four detail records. This is because, as I mentioned in the first article, I have added an extra record to each of the latter tables' detail tables (i.e. foreign key constraints), the extra record being a duplicate of one of the other three (in terms of the matching fields), but a different duplicate in each case. This tests correct handling of duplicates within the detail sets.

Performance Comparison
Click on the query name in the file below to jump to the execution plan for the largest data point, and click the tabs to see different views on the performance obtained.

• The timings in the file above are roughly consistent with linear-time variation with problem size; if anything L2_SQF appears sublinear, but the times are fairly small and there was some other activity on the PC at the time
• At the largest data point, RECON takes 5 times as much CPU time as L2_SQF, and 9 times as much elapsed time
• The differences between elapsed and CPU times for RECON are indicative of significant file I/O activity. This shows up in the disk reads and writes summaries on the statistics tab, and in more detail in the Plans tab, and is caused mainly by reading and writing of the subquery factors to and from disk
• The other main significant factor in the increase in times for the RECON query is the additional sorting; see, for example, steps 31 and 32 in the plan. These effects are the result of the additional subquery factors that were needed to achieve the final result

Conclusions

• This three-part sequence of articles has focussed on a special category of problem within SQL, but has highlighted a range of SQL techniques that are useful generally, including:
• Subquery factors
• Temporary tables
• Analytic functions
• Set matching by list aggregation
• Compact storage of unique identifiers by ranking and base-conversion via the Chr() function
• We have also noted different ways of matching sets, including use of the MINUS set operator and the NOT EXISTS construct, and discussed ways of handling issues such as duplication within a set, and directionality of the set operators
• The importance of polynomial order of solution performance for efficiency has been illustrated dramatically
• The final SQL solution provides a good illustration of the power of modern SQL to solve complex problems using set-based logic combined with sequence in a simpler and faster way than the more conventional procedural approach
• The subquery-sequence approach to SQL is well suited to diagrammatic design techniques
• It is often better to solve complex real-world problems by first working with simpler prototypes

# Holographic Set Matching in SQL (MDTM2)

This article is the second in a sequence of three dealing with a very general class of problems in SQL, and exploring various techniques to find efficient solutions. In the first article, Master-Detail Transaction Matching in SQL (MDTM1), the problem was outlined and divided into two subproblems, of which the first was solved in several variant SQL statements with performance analysis. This second article, takes the most efficient method and applies two new techniques to further improve performance. The third article, Master-Detail Transaction Reconciliation in SQL (MDTM3), adds a sequence of subquery factors to the best solution for the first subproblem to achieve an efficient solution to the overall problem within a single SQL statement.

The holographic principle is a mathematical principle that the total information contained in a volume of space corresponds to an equal amount of information contained on the boundary of that space. - Holographic Principle.

In my last article, I made large performance gains in SQL queries matching sets of detail records by obtaining aggregates of the sets in a subquery factor and matching those at master level before matching the detail sets directly. The performance gain came from the fact that the aggregation is cheap compared to matching sets of records and allows many matching pair candidates to be discarded before doing the expensive direct set matching. However, all of the actually matching transactions would have been directly matched and probably more, and it occurred to me to wonder whether it might not be possible to use aggregate matching to replace detail set matching altogether.

This article develops the previous one by taking the same sample transaction matching problem and adding queries that use the Oracle 11.2 function Listagg to allow just this replacement. This is possible so long as the list-aggregated detail matching fields do not exceed 4,000 characters. If that were to happen then some other aggregation technique would be needed, perhaps a user-defined CLOB version of Listagg. However, it's possible to extend the range of applicability by aggregating identifiers smaller than the actual fields, as I'll discuss at the end.

We'll keep the fastest query from the previous article, and add three new queries:

Query Variations

• MIN_NE - Detail grouping in temporary table, with set matching (GTT_NE previously)
• LAG_SQF - Detail grouping by Listagg only in subquery factor
• LAG_NE - Detail grouping by Listagg in subquery factor, with set matching
• LAG_GTT - Detail grouping by Listagg only in temporary table
************
MIN_NE
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_con)             n_det,
Min (c.r_constraint_name) min_det,
Max (c.r_constraint_name) max_det
FROM tab_cp                    t
JOIN grp_gtt                   c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.min_det                = t1.min_det
AND t2.max_det                = t1.max_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT 1
FROM grp_gtt                   d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
AND (
NOT EXISTS (
SELECT 1
FROM grp_gtt                   d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
AND d2.r_owner                = d1.r_owner
AND d2.r_constraint_name      = d1.r_constraint_name
AND d2.n_con                  = d1.n_con
)))
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
LAG_SQF
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Count(c.ROWID)            n_det,
Listagg (c.r_owner||c.r_constraint_name, '') WITHIN GROUP (ORDER BY c.r_owner||c.r_constraint_name) lagg
FROM tab_cp                    t
JOIN con_cp                    c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
AND c.constraint_type         = 'R'
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.lagg                   = t1.lagg
AND t2.row_id                 > t1.row_id
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
LAG_NE
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_con)             n_det,
Listagg (c.r_owner||c.r_constraint_name, '') WITHIN GROUP (ORDER BY c.r_owner||c.r_constraint_name) lagg
FROM tab_cp                    t
JOIN grp_gtt                   c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.lagg                   = t1.lagg
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT 1
FROM grp_gtt                   d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
AND (
NOT EXISTS (
SELECT 1
FROM grp_gtt                   d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
AND d2.r_owner                = d1.r_owner
AND d2.r_constraint_name      = d1.r_constraint_name
AND d2.n_con                  = d1.n_con
)))
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
LAG_GTT
************
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab_gtt                   t1
JOIN tab_gtt                   t2
ON t2.n_det                  = t1.n_det
AND t2.lagg                   = t1.lagg
AND t2.row_id                 > t1.row_id
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

The two temporary tables are as follows, with * marking unique keys:
tab_gtt

• owner*
• table_name*
• row_id
• lagg
• n_det

A unique index is defined on tab_gtt:
tab_gtt_uk

• owner
• table_name

A non-unique index is defined on tab_gtt:
tab_gtt_N1

• lagg

grp_gtt

• owner*
• constraint_name*
• r_owner*
• r_constraint_name*
• n_con

A unique index is defined on grp_gtt:
grp_gtt_uk

• owner
• constraint_name
• r_owner
• r_constraint_name

Performance Analysis

We presented four query variations above, and in this section give the results of benchmarking these queries across a 1-dimensional data domain obtained by copying the system views once, then by successive powers of two up to 32 times into my test tables described in the previous article. The problem sizes are as follows:
Record Counts

Timings and Statistics
Click on the query name in the file below to jump to the execution plan for the largest data point.

Comparison

The figures above are for the largest data pont, W32. The following points can be made:

• The query that performed best in the earlier article is now worst compared with the new queries
• The best query uses Listagg within a subquery factor and is more than 200 times faster than the worst at the largest data point
• Moving the new listagg-based subquery factor into a temporary table worsens performance because the index is not used
• The statistics tab shows that performance variation is now linear with problem size for the new Listagg queries, which is why they inevitably outperform the old one at large enough problem size

Subquery Factors and Temporary Tables
Replacing a subquery factor by a temporary table can only help if indexed accesses are beneficial.

Execution Plan Hash Values
In my last article, I noted that the plan hash values differed for all queries between data points, although the plans were essentially the same, and surmised that this was due to the subquery factor internal naming system. LAG_GTT is the only query here that makes no use of subquery factors, and this is the only one that retains the same plan hash value, thus bearing out the surmise.

Extending Listagg Applicability

If there are a large number of matching fields then the Listagg limit of 4,000 characters could be hit in quite a small number of details for a master. It's not difficult to write a CLOB version of Listagg, but one way of mitigating the restriction would be to aggregate not the actual matching fields, but the ranking of the set within all distinct detail sets. A further reduction in the size of the aggregated values can be obtained by storing the ranking in a high number-base, rather than base 10, as a zero-left-padded string. If the database character set is UTF8 (as is my 11.2 XE database), base 128 is possible, while extended Ascii character sets should allow base 256. The number of characters assigned to the ranking value determines how many distinct sets and how many detail records per master record are allowed with the standard Listagg function, according to the table below (for UTF8):

Chars  Distinct Sets  Details/Master
=====  =============  ==============
1            128           4,000
2         16,384           2,000
3      2,097,152           1,333
4    268,435,456           1,000

New Query L2_SQF

WITH rns AS (
SELECT r_owner,
r_constraint_name,
Row_Number () OVER (ORDER BY r_owner, r_constraint_name) - 1 rn
FROM con_cp
WHERE constraint_type	    = 'R'
GROUP BY
r_owner,
r_constraint_name
), rch AS (
SELECT r_owner,
r_constraint_name,
Chr (Floor (rn / 128)) ||
Chr ((rn - 128 * Floor (rn / 128))) chr_rank
FROM rns
), tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Count(c.ROWID)            n_det,
Listagg (r.chr_rank, '') WITHIN GROUP (ORDER BY r.chr_rank) lagg
FROM tab_cp                    t
JOIN con_cp                    c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
AND c.constraint_type         = 'R'
JOIN rch                       r
ON r.r_owner                 = c.r_owner
AND r.r_constraint_name       = c.r_constraint_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.lagg                   = t1.lagg
AND t2.row_id                 > t1.row_id
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name


QSDs

Record Counts
The same set of data points has been used for the new query (L2_SQF) with the best of the earlier ones for comparison (LAG_SQF). The record counts have slightly increased. Note that the constraints per table is for all constraints, not just foreign keys.

Timings and Statistics
Click on the query name in the file below to jump to the execution plan for the largest data point.

Comparison

The figures above are for the largest data pont, W32. The following points can be made:

• The new query is about 20% slower than the old one
• Performance variation remains linear with problem size for the new query
• The test problem has very few details per master, but the relative performances may change for real problems

Conclusions

This article has used a new idea that I termed holographic set matching to improve performance relative to the queries in my previous article on Master-Detail Transaction Matching in SQL (MDTM1), achieving linear time variation with size, compared with the earlier quadratic time. Although the new Oracle 11.2 function Listagg has been used, the method can by applied in earlier versions by adding a user-defined list aggregation function, which is easy to do. The third article in this sequence, Master-Detail Transaction Reconciliation in SQL (MDTM3), solves the overall problem described in the first article by adding a sequence of subquery factors to the query developed above.

# Master-Detail Transaction Matching in SQL (MDTM1)

This article is the first in a sequence of three dealing with a very general class of problems in SQL, and exploring various techniques to find efficient solutions. In this first article, the problem is outlined and is divided into two subproblems, of which the first is solved here in several variant SQL statements with performance analysis. The second article, Holographic Set Matching in SQL, takes the most efficient method and applies two new techniques to further improve performance. The third article, Master-Detail Transaction Reconciliation in SQL (MDTM3), adds a sequence of subquery factors to the best solution for the first subproblem to achieve an efficient solution to the overall problem within a single SQL statement.

The General Problem

We consider a transaction with a two-level structure consisting of a header (or master) and lines (or details) linked to the header, and the problem is to pair off transactions by matching, or contra-matching, subsets of the fields at both header and line level. This kind of problem can arise in the context of reconciliation of debit and credit transactions where both transactions are in the same system but are entered separately by two different groups and have no explicit linkage. Typically, in order for the transactions to match at header level, several fields such as transaction class have to be equal, while others such as credit and debit amounts have to be inverse, while others again such as unique identifiers will not match. At the line level, the same applies and matched lines also need to pair off against each other for the transaction to be considered a match. The first part of the problem is to identify all matching (or contra-matching) pairs, and this article will focus on that, while the second (and optional) part, that of pairing off, will be the subject of a later article.

To see why performance might be an important issue for this type of problem, consider the number of possible comparisons for an example with 10,000 headers each having 100 lines. In this case there would be 100,000,000 pairs of transactions, counting both ways, and 50,000,000 counting one way. A similar calculation gives 10,000 (not 5,000!) line comparisons per header-pair, and hence 500,000,000,000 line comparisons in total. The key of course is to minimise the number of comparisons made explicitly.

We will solve the problem through a single SQL query which will be developed through several versions, using a test example problem based on Oracle standard tables. The queries will be tested using my own SQL benchmarking framework, mentioned in earlier articles, and performance characteristics analysed. This will illustrate some performance aspects of the use of subquery factors and temporary tables, among other things.

Matching and Contra-Matching Sets

In the ERD above, each transaction falls into a logical Match Status Set, where the sets are of four distinct types:

• Unmatched - a single set for transactions having no matching or contra-matching transaction
• Matched - a set for each group of mutually matching transactions
• Contra-Matched A -?a set for each group of transactions that all contra-match to a corresponding B-set
• Contra-Matched B -?a set for each group of transactions that all contra-match to a corresponding A-set

We may define our problem without contra-matching fields, in which case only the first two types of set will be present; we may also have the case where only contra-matching is possible (likely the most common); and a special case may arise where both matching and contra-matching fields are present but where all contra-matching fields may have self-inverse values (for example amounts of zero) and those records having only self-inverse values might be best regarded as falling into one of the first two types of set.

The Sample Problem - Tables and Foreign Key Constraints

We will use two of the Oracle database system views as the basis for our sample problem. The master entity will be the Oracle table defined in the view all_tables, and the detail entity will be the foreign key constraint contained as a subentity in the view all_constraints. The views themselves are very complicated and it is better for our purposes to copy their records into new tables, and for performance testing we'll copy them multiple times according to the value of a dimensional parameter, using the parameter as a suffix on the owner and table name fields. The sample problem will involve matching only, and tables are defined to match if they have the same set of foreign key references, where the references are defined by the referenced owners and constraint names. As tables without foreign keys all match trivially, we'll filter these out in the queries.

The table and constraint entities can be represented by the following ERD:

The tables are, with * marking primary keys:
tab_cp

• owner*
• table_name*
• description

con_cp

• owner*
• constraint_name*
• table_name
• constraint_type
• r_owner
• r_constraint_name
• description

Indexes are defined on the two foreign keys on con_cp:
con_tab_fk_N1

• owner
• table_name

con_con_fk_N2

• r_owner
• r_constraint_name

The embedded Excel file below gives the solution for my 11g XE database, for the first problem, of identifying all matches.

Solution Methods
This problem might be considered to divide into two subproblems. The first is to identify all the matching pairs, while the second is to take those matching pairs and eliminate duplicate instances, so that each master record matches against at most one other record. This may reduce the number of master records that have matches; for example, if a matching set has three master records, then only two of them will be matched, against each other, in the final solution. We will consider the first subproblem in this article and the second in a later article.

To find the solution to the first subproblem in SQL, the obvious approach is simply to join the master table to itself to form the set of possible matching pairs, then to apply criteria to filter out any pairs that don't match. Obviously, we can immediately apply a constraint to avoid selecting the same pair twice by requiring that the rowid of the first record be higher than that of the second. This will halve the number of pairs considered, reducing the initial set of pairs from n! to n!/2 (where ! denotes the mathematical factorial function), and also halving the number after applying any other conditions.

Matching Detail Sets with MINUS Operator
The master-level criteria may be easy enough to apply, using conditions in the join clause, but the detail criteria are more difficult because we have to match two sets of records for any given pair of master records. This leads us to think of Oracle's set operators, specifically the MINUS operator that subtracts one set from another. Consider the matching pair on line 4028 of the Excel file above, with he solution for our example problem. This shows a match between the two tables OEHR_EMPLOYEES and OEHR_JOB_HISTORY in the TWODAYPLUS_0 schema, each of which has three foreign keys. The three constraints on each of these tables reference the same keys in the same schema, namely DEPT_ID_PK, JOB_ID_PK, EMP_EMP_ID_PK. The following query returns no records:

SELECT r_owner,
r_constraint_name
FROM con_cp
WHERE constraint_type = 'R'
AND table_name = 'OEHR_EMPLOYEES'
AND owner = 'TWODAYPLUS_0'
MINUS
SELECT r_owner,
r_constraint_name
FROM con_cp
WHERE constraint_type = 'R'
AND table_name = 'OEHR_JOB_HISTORY'
AND owner = 'TWODAYPLUS_0'

Perhaps then the detail set matching could be effected by a NOT EXISTS clause on the above query with the hard-coded owner and table_name replaced by correlation columns from the main query? There are two problems with this arising from the way Oracle's set operators work. First, if there were any extra foreign keys in the second table the query would still return no records, as it returns only records that are in the first query section and not in the second, thus showing a false match. Second, Oracle views a set in this context as being the set of distinct records, so if some records are duplicated in either table, but differently from the other one then again a false match is shown. These two tables also exist in Oracle's HR demo schema, without the OEHR_ prefix. In order to show the problem I added an extra field in each table with a foreign key matching one already present, as follows:

• EMPLOYEES.REL_EMP_ID -> EMP_EMP_ID_PK
• JOB_HISTORY.REL_JOB_ID -> JOB_ID_PK

Now the query above with new schema and table names still returns no records although in our terms the detail record sets are different: EMPLOYEES has set (DEPT_ID_PK, JOB_ID_PK, EMP_EMP_ID_PK, EMP_EMP_ID_PK), while JOB_HISTORY has set (DEPT_ID_PK, JOB_ID_PK, JOB_ID_PK, EMP_EMP_ID_PK). The solution to this problem is of course that we need to group the detail records by the matching fields and add a count, as follows, using our copied schema HR_0:

SELECT r_owner,
r_constraint_name,
Count(*)
FROM con_cp
WHERE constraint_type = 'R'
AND table_name = 'EMPLOYEES'
AND owner = 'HR_0'
GROUP BY r_owner,
r_constraint_name
MINUS
SELECT r_owner,
r_constraint_name,
Count(*)
FROM con_cp
WHERE constraint_type = 'R'
AND table_name = 'JOB_HISTORY'
AND owner = 'HR_0'
GROUP BY r_owner,
r_constraint_name

This returns two records:

R_OWNER  R_CONSTRAINT_NAME    COUNT(*)
=======  =================    ========
HR_0     EMP_EMP_ID_PK        2
HR_0     JOB_ID_PK            1

As for the first problem, this can be solved in two ways, either by repeating the NOT EXISTS clause with the two sections reversed, or by ensuring separately that the two record sets have the same numbers of records - if they don't they can't match, and if they do then the MINUS operator works. Obviously the first solution is going to double the work involved, while the second incurs a cost associated with the counting process but that's offset by avoidance of the NOT EXISTS execution.

Matching Detail Sets with nested NOT EXISTS Operator
If we consider the MINUS query above before we added grouping, it seems likely that Oracle would evaluate the outer NOT EXISTS by obtaining both record sets, then applying the MINUS opersator, before checking that no records are returned. This would seem inefficient since the outer condition fails if any single record is in the first set but not in the second, so one would want to truncate processing on finding a first such record. This suggests an alternative that might be more effficient, that uses another NOT EXISTS nested within the outer one, which would apply to the following subquery:

SELECT 1
FROM con_cp c1
WHERE c1.constraint_type = 'R'
AND c1.table_name = 'OEHR_EMPLOYEES'
AND c1.owner = 'TWODAYPLUS_0'
AND NOT EXISTS (
SELECT 1
FROM con_cp c2
WHERE c2.constraint_type = 'R'
AND c2.table_name = 'OEHR_JOB_HISTORY'
AND c2.owner = 'TWODAYPLUS_0'
AND c2.r_owner = c1.r_owner
AND c2.r_constraint_name = c1.r_constraint_name
)

Here we have not included the grouping solution because it is complicated within this structure, but if the detail table were replaced by either a subquery factor or a temporary table where the grouping were already done, then (as we'll see) this would work just by adding in an equality condition on the count fields. Again, if we know that the record counts are the same the reverse clause is unnecessary.

Pre-Matching Detail Sets by Aggregates
We noted above that the detail sets can only match if they have the same numbers of records, and that this could be used to avoid doing the set matching twice in opposite orders. We also noted that the work done in counting would be offset by the avoidance of expensive set matching for those pairs that don't have matching counts. In fact, we can extend this idea to all possible aggregates on the detail record set matching fields, and this will likely result in fewer set matchings in the overall query execution. In our simple test problem we will add minimum and maximum aggregates on the r_constraint_name field, giving the following join conditions, prior to the set matching clause, and where tab represents a subquery factor that computes the aggregates:

  FROM tab                      t1
JOIN tab                      t2
ON t2.n_det                 = t1.n_det
AND t2.min_det               = t1.min_det
AND t2.max_det               = t1.max_det
AND t2.row_id                > t1.row_id

Subquery Factors and Temporary Tables
Owing to the importance of aggregation at table level, as explained in the last section above, all query variations considered will include a subquery factor, tab, that does this aggregation. However, we have also noted the need to group and count at the level of detail records, and as this grouped record set needs to be used twice, for each member of a potential matching master pair, it would also seem an obvious candidate for a subquery factor. When we try this though, we'll see that the query structure now precludes the use of indexes within the detail matching subquery and so we'll also implement a query that uses a temporary table where the grouping and counting is done in advance.

Query Variations
We will test five query variations, as shown below, where MI and NE denote, respectively, the MINUS and NOT EXISTS methods of detail set matching.

• INL_MI - Detail grouping directly
• SQF_NE - Detail grouping in subquery factor
• GTT_NE - Detail grouping in temporary table
• GTT_NE_X - As GRP_GTT_NE but table-level count aggregation only
• GTT_MI - As GRP_GTT_NE but with MINUS
************
INL_MI
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Count(c.ROWID)            n_det,
Min (c.r_constraint_name) min_det,
Max (c.r_constraint_name) max_det
FROM tab_cp                    t
JOIN con_cp                    c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
AND c.constraint_type         = 'R'
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.min_det                = t1.min_det
AND t2.max_det                = t1.max_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT c2.r_owner,
c2.r_constraint_name,
Count(*)
FROM con_cp                    c2
WHERE c2.owner                  = t2.owner
AND c2.table_name             = t2.table_name
AND c2.constraint_type        = 'R'
GROUP BY c2.r_owner,
c2.r_constraint_name
MINUS
SELECT c1.r_owner,
c1.r_constraint_name,
Count(*)
FROM con_cp                    c1
WHERE c1.owner                  = t1.owner
AND c1.table_name             = t1.table_name
AND c1.constraint_type        = 'R'
GROUP BY c1.r_owner,
c1.r_constraint_name
)
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
SQF_NE
************
WITH det AS (
SELECT owner,
table_name,
r_owner,
r_constraint_name,
Count(*)                  n_dup
FROM con_cp
WHERE constraint_type           = 'R'
GROUP BY owner,
table_name,
r_owner,
r_constraint_name
), tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_dup)             n_det,
Min (c.r_constraint_name) min_det,
Max (c.r_constraint_name) max_det
FROM tab_cp                    t
JOIN det                       c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.min_det                = t1.min_det
AND t2.max_det                = t1.max_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT 1
FROM det                       d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
AND (
NOT EXISTS (
SELECT 1
FROM det                       d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
AND d2.r_owner                = d1.r_owner
AND d2.r_constraint_name      = d1.r_constraint_name
AND d2.n_dup                  = d1.n_dup
)))
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
GTT_NE
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_con)             n_det,
Min (c.r_constraint_name) min_det,
Max (c.r_constraint_name) max_det
FROM tab_cp                    t
JOIN grp_gtt                   c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.min_det                = t1.min_det
AND t2.max_det                = t1.max_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT 1
FROM grp_gtt                   d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
AND (
NOT EXISTS (
SELECT 1
FROM grp_gtt                   d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
AND d2.r_owner                = d1.r_owner
AND d2.r_constraint_name      = d1.r_constraint_name
AND d2.n_con                  = d1.n_con
)))
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
GTT_NE_X
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_con)             n_det
FROM tab_cp                    t
JOIN grp_gtt                   c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT 1
FROM grp_gtt                   d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
AND (
NOT EXISTS (
SELECT 1
FROM grp_gtt                   d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
AND d2.r_owner                = d1.r_owner
AND d2.r_constraint_name      = d1.r_constraint_name
AND d2.n_con                  = d1.n_con
)))
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

************
GTT_MI
************
WITH tab AS (
SELECT t.owner,
t.table_name,
t.ROWID                   row_id,
Sum (c.n_con) n_det,
Min (c.r_constraint_name) min_det,
Max (c.r_constraint_name) max_det
FROM tab_cp                    t
JOIN grp_gtt                   c
ON c.owner                   = t.owner
AND c.table_name              = t.table_name
GROUP BY
t.owner,
t.table_name,
t.ROWID
)
SELECT
t1.owner                  owner_1,
t1.table_name             table_name_1,
t2.owner                  owner_2,
t2.table_name             table_name_2,
t1.n_det                  n_det
FROM tab                       t1
JOIN tab                       t2
ON t2.n_det                  = t1.n_det
AND t2.min_det                = t1.min_det
AND t2.max_det                = t1.max_det
AND t2.row_id                 > t1.row_id
WHERE NOT EXISTS (
SELECT d2.r_owner,
d2.r_constraint_name,
d2.n_con
FROM grp_gtt                   d2
WHERE d2.owner                  = t2.owner
AND d2.table_name             = t2.table_name
MINUS
SELECT d1.r_owner,
d1.r_constraint_name,
d1.n_con
FROM grp_gtt                   d1
WHERE d1.owner                  = t1.owner
AND d1.table_name             = t1.table_name
)
ORDER BY t1.owner,
t1.table_name,
t2.owner,
t2.table_name

The query structure diagrams (QSDs) are in the embedded Excel file below:

Performance Analysis

We presented five query variations above, and in this section give the results of benchmarking these queries across a 1-dimensional data domain obtained by copying the system views 1, 2 and 4 times into my test tables described above. The problem sizes are as follows:
Record Counts

Timings and Statistics
Click on the query name in the file below to jump to the execution plan for the largest data point.

Comparison

The timings above are only for the main queries, so we need also to consider the time to populate and delete the temporary table, for the three GTT queries. This is performed as part of the data point setup, and the framework prints out timings for this part separately. For the last data point, the output was:

5144 records inserted in grp_gtt
8956 tables, 44780 constraints, 5 c/t

Timer Set: Setup, Constructed at 09 Oct 2012 22:28:49, written at 22:29:04
==========================================================================
[Timer timed: Elapsed (per call): 0.05 (0.000047), CPU (per call): 0.05 (0.000050), calls: 1000, '***' denotes corrected line below]

Timer                  Elapsed          CPU          Calls        Ela/Call        CPU/Call
-----------------   ----------   ----------   ------------   -------------   -------------
Delete test data          3.33         2.78              1         3.33200         2.78000
Delete GTT                0.18         0.17              1         0.18000         0.17000
Insert tab                0.55         0.44              1         0.54500         0.44000
Insert con                7.89         5.94              1         7.89000         5.94000
Insert grp_gtt            0.14         0.14              1         0.14000         0.14000
Count records             0.02         0.01              1         0.01600         0.01000
Gather statistics         2.59         2.06              1         2.58900         2.06000
(Other)                   0.00         0.00              1         0.00000         0.00000
-----------------   ----------   ----------   ------------   -------------   -------------
Total                    14.69        11.54              8         1.83650         1.44250
-----------------   ----------   ----------   ------------   -------------   -------------

The elapsed times for deleting from, then inserting into the temporary table are given by the 'Delete GTT' and 'Insert grp_gtt' timers and add up to 0.32s, so do not make much difference (about 5% on the best and less on the others). The following points can be made:

• Doing the detail grouping and counting directly gives the worst performance
• Moving the detail grouping and counting into a subquery factor improves performance by a factor of about 4
• Moving the detail grouping into a temporary table improves performance the most
• Using NOT EXISTS instead of MINUS for detail matching improves performance, as expected, by a factor of about 2
• Using the minimum and maximum aggregates for pre-filtering, in addition to the counts, improves performance by a factor of about 20

Subquery Factors and Temporary Tables
If you look at the execution plan for INL_MI, most of the work is done in the HASH GROUP BY steps, 16 and 20, on totals of 127K records each. Moving this grouping into a subquery factor in SQF_NE means that the operation is done only once rather than many times (110K), and the execution plan for SUBQ_NE shows that it takes very little time (line 3).

However, the execution plan for SUBQ_NE shows (lines 14-22) that the factors are read using full scans, because indexes are not possible. This observation led to the improvement of moving the grouping out of the query altogether and into a separate stage that populates a temporary table, on which indexes can be defined. Lines 15-18 in the plan for GTT_NE show the access is now by index on the detail records.

Memory Usage in INL_MI Query with Grouping
Originally, I tried to have a larger range of data points, but doubling the size again always resulted in an Oracle error on INL_MI, ORA-04030: out of process memory when trying to allocate 123404 bytes (QERGH hash-agg,kllcqas:kllsltba). This is surprising because the execution plan statistics include memory statistics that appear to indicate that all queries use the same maximum amount of memory, which is just over 3MB, incurred in the SORT ORDER BY step (e.g. line 7 below).

My framework also collects statistics from the system view v\$mystat, and prints out those showing large variations in 'after minus before' differences across the queries. The framework printed the statistic 'session pga memory' and this tells a different story (the values are in the embedded Excel files under Statistics above). The INL_MI query shows increases of 14MB, then 170MB, then 768MB approx. while the other queries all show no increases. It's hard to understand what's going on here, but one guess is that the query is revealing an Oracle bug that causes memory not to be released after use and then re-used, but for new executions of the relevant operation to request new memory, and that the execution plans are not reporting this. However, as discussed later, the variation in execution time with problem size is also difficult to understand and suggests that the HASH GROUP BY operations are really being performed on the entire record sets, which would also greatly increase the memory usage. The version, running under Windows 7 is: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

Execution Plan Hash Values
In my last article, I was able to easily identify the distinct plans by looking at the matrix of plan hash values, with values the same indicating the same plan. This time though, that doesn't work: all hash values are different, but in fact, inspection of the plans shows that for each query there was essentially only one plan. I believe this may be due to the subquery factors, which result in a system-generated view name, which differs each time. For example, here are the last two plans for the INL_MI, where the only difference appears to be in the view names (SYS_TEMP_0FD9D6681_1B0CFB4 for W2 and SYS_TEMP_0FD9D6687_1B0CFB4 for W4) (note that different statistics don't make the plans different):

Point W2:

Plan hash value: 89269728

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |      1 |        |   8054 |00:01:40.96 |     191K|     21 |     21 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION        |                            |      1 |        |   8054 |00:01:40.96 |     191K|     21 |     21 |       |       |          |
|   2 |   LOAD AS SELECT                  |                            |      1 |        |      0 |00:00:00.05 |    3182 |      0 |     21 |   264K|   264K|  264K (0)|
|   3 |    HASH GROUP BY                  |                            |      1 |   2606 |   1628 |00:00:00.05 |    3158 |      0 |      0 |   766K|   766K| 1273K (0)|
|   4 |     NESTED LOOPS                  |                            |      1 |   2606 |   2606 |00:00:00.05 |    3158 |      0 |      0 |       |       |          |
|*  5 |      TABLE ACCESS FULL            | CON_CP                     |      1 |   2606 |   2606 |00:00:00.01 |     625 |      0 |      0 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN            | TCP_PK                     |   2606 |      1 |   2606 |00:00:00.02 |    2533 |      0 |      0 |       |       |          |
|   7 |   SORT ORDER BY                   |                            |      1 |      1 |   8054 |00:01:40.89 |     188K|     21 |      0 |  1824K|   650K| 1621K (0)|
|*  8 |    FILTER                         |                            |      1 |        |   8054 |00:01:24.17 |     188K|     21 |      0 |       |       |          |
|*  9 |     HASH JOIN                     |                            |      1 |      1 |  27242 |00:00:00.15 |      47 |     21 |      0 |   720K|   720K| 1282K (0)|
|  10 |      VIEW                         |                            |      1 |   2606 |   1628 |00:00:00.01 |      25 |     21 |      0 |       |       |          |
|  11 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6681_1B0CFB4 |      1 |   2606 |   1628 |00:00:00.01 |      25 |     21 |      0 |       |       |          |
|  12 |      VIEW                         |                            |      1 |   2606 |   1628 |00:00:00.01 |      22 |      0 |      0 |       |       |          |
|  13 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6681_1B0CFB4 |      1 |   2606 |   1628 |00:00:00.01 |      22 |      0 |      0 |       |       |          |
|  14 |     MINUS                         |                            |  27242 |        |  19188 |00:01:40.05 |     188K|      0 |      0 |       |       |          |
|  15 |      SORT UNIQUE                  |                            |  27242 |      1 |  28722 |00:00:53.68 |   73872 |      0 |      0 |  2048 |  2048 | 2048  (0)|
|  16 |       HASH GROUP BY               |                            |  27242 |      1 |  31314 |00:00:45.36 |   73872 |      0 |      0 |   750K|   750K|  610K (0)|
|* 17 |        TABLE ACCESS BY INDEX ROWID| CON_CP                     |  27242 |      1 |  31335 |00:00:01.57 |   73872 |      0 |      0 |       |       |          |
|* 18 |         INDEX RANGE SCAN          | CON_TAB_FK_N1              |  27242 |      1 |    175K|00:00:01.26 |   42504 |      0 |      0 |       |       |          |
|  19 |      SORT UNIQUE                  |                            |  27242 |      1 |  30502 |00:00:45.94 |     114K|      0 |      0 |  2048 |  2048 | 2048  (0)|
|  20 |       HASH GROUP BY               |                            |  27242 |      1 |  31314 |00:00:37.86 |     114K|      0 |      0 |   750K|   750K|  910K (0)|
|* 21 |        TABLE ACCESS BY INDEX ROWID| CON_CP                     |  27242 |      1 |  31335 |00:00:01.64 |     114K|      0 |      0 |       |       |          |
|* 22 |         INDEX RANGE SCAN          | CON_TAB_FK_N1              |  27242 |      1 |    183K|00:00:01.29 |   83068 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("C"."CONSTRAINT_TYPE"='R')
6 - access("C"."OWNER"="T"."OWNER" AND "C"."TABLE_NAME"="T"."TABLE_NAME")
8 - filter( IS NULL)
9 - access("T2"."N_DET"="T1"."N_DET" AND "T2"."MIN_DET"="T1"."MIN_DET" AND "T2"."MAX_DET"="T1"."MAX_DET")
filter("T2"."ROW_ID">"T1"."ROW_ID")
17 - filter("C2"."CONSTRAINT_TYPE"='R')
18 - access("C2"."OWNER"=:B1 AND "C2"."TABLE_NAME"=:B2)
21 - filter("C1"."CONSTRAINT_TYPE"='R')
22 - access("C1"."OWNER"=:B1 AND "C1"."TABLE_NAME"=:B2)

Point W4:

Plan hash value: 892071883

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |      1 |        |  16108 |00:25:33.98 |     788K|     42 |     42 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION        |                            |      1 |        |  16108 |00:25:33.98 |     788K|     42 |     42 |       |       |          |
|   2 |   LOAD AS SELECT                  |                            |      1 |        |      0 |00:00:00.10 |    5802 |      0 |     42 |   521K|   521K|  521K (0)|
|   3 |    HASH GROUP BY                  |                            |      1 |   5007 |   3256 |00:00:00.09 |    5757 |      0 |      0 |  1001K|   943K| 1273K (0)|
|   4 |     NESTED LOOPS                  |                            |      1 |   5007 |   5212 |00:00:00.09 |    5757 |      0 |      0 |       |       |          |
|*  5 |      TABLE ACCESS FULL            | CON_CP                     |      1 |   4980 |   5212 |00:00:00.01 |     625 |      0 |      0 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN            | TCP_PK                     |   5212 |      1 |   5212 |00:00:00.04 |    5132 |      0 |      0 |       |       |          |
|   7 |   SORT ORDER BY                   |                            |      1 |      1 |  16108 |00:25:33.84 |     782K|     42 |      0 |  3596K|   822K| 3196K (0)|
|*  8 |    FILTER                         |                            |      1 |        |  16108 |00:22:30.61 |     782K|     42 |      0 |       |       |          |
|*  9 |     HASH JOIN                     |                            |      1 |      1 |    110K|00:00:00.62 |      89 |     42 |      0 |   900K|   900K| 1328K (0)|
|  10 |      VIEW                         |                            |      1 |   5007 |   3256 |00:00:00.02 |      46 |     42 |      0 |       |       |          |
|  11 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6687_1B0CFB4 |      1 |   5007 |   3256 |00:00:00.01 |      46 |     42 |      0 |       |       |          |
|  12 |      VIEW                         |                            |      1 |   5007 |   3256 |00:00:00.03 |      43 |      0 |      0 |       |       |          |
|  13 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6687_1B0CFB4 |      1 |   5007 |   3256 |00:00:00.02 |      43 |      0 |      0 |       |       |          |
|  14 |     MINUS                         |                            |    110K|        |  94488 |00:25:29.91 |     782K|      0 |      0 |       |       |          |
|  15 |      SORT UNIQUE                  |                            |    110K|      1 |    113K|00:14:20.41 |     300K|      0 |      0 |  2048 |  2048 | 2048  (0)|
|  16 |       HASH GROUP BY               |                            |    110K|      1 |    127K|00:11:47.70 |     300K|      0 |      0 |   789K|   789K|  527K (0)|
|* 17 |        TABLE ACCESS BY INDEX ROWID| CON_CP                     |    110K|      1 |    127K|00:00:08.15 |     300K|      0 |      0 |       |       |          |
|* 18 |         INDEX RANGE SCAN          | CON_TAB_FK_N1              |    110K|      1 |    722K|00:00:06.55 |     156K|      0 |      0 |       |       |          |
|  19 |      SORT UNIQUE                  |                            |    110K|      1 |    123K|00:11:07.57 |     481K|      0 |      0 |  2048 |  2048 | 2048  (0)|
|  20 |       HASH GROUP BY               |                            |    110K|      1 |    127K|00:09:52.37 |     481K|      0 |      0 |   789K|   789K|  907K (0)|
|* 21 |        TABLE ACCESS BY INDEX ROWID| CON_CP                     |    110K|      1 |    127K|00:00:08.37 |     481K|      0 |      0 |       |       |          |
|* 22 |         INDEX RANGE SCAN          | CON_TAB_FK_N1              |    110K|      1 |    735K|00:00:06.31 |     337K|      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("C"."CONSTRAINT_TYPE"='R')
6 - access("C"."OWNER"="T"."OWNER" AND "C"."TABLE_NAME"="T"."TABLE_NAME")
8 - filter( IS NULL)
9 - access("T2"."N_DET"="T1"."N_DET" AND "T2"."MIN_DET"="T1"."MIN_DET" AND "T2"."MAX_DET"="T1"."MAX_DET")
filter("T2"."ROW_ID">"T1"."ROW_ID")
17 - filter("C2"."CONSTRAINT_TYPE"='R')
18 - access("C2"."OWNER"=:B1 AND "C2"."TABLE_NAME"=:B2)
21 - filter("C1"."CONSTRAINT_TYPE"='R')
22 - access("C1"."OWNER"=:B1 AND "C1"."TABLE_NAME"=:B2)

Performance Variation Polynomials
The timings above show that CPU and elapsed times increased by different powers of the problem size increases, according to query.

The inline grouping query INL_MI shows a variation close to the fourth power, which like its memory usage, is very hard to understand. Most of the time is used in the HASH GROUP BY operations at lines 16 and 20, and it rises about 16 times betwen W2 and W4. The numbers of starts rise by 4 times, as expected, but the number of rows per start remains constant at about 1.15, so the work done should rise by about 4 times. It's almost as though the SQL engine is really processing the entire record set in the HASH GROUP BY, rather than just the subset for the correlated tables, contrary to what the plan says. Again, this looks buggy.

The double subquery factor query SUBQ_NE has about a cubic variation, which is plausible because the table pairing introduces a quadratic term, with the third power coming from the full scans of the detail subquery factor.

All three of the temporary table queries show quadratic variation, which is likely the best obtainable while matching sets directly (but see my next article Holographic Set Matching in SQL for linear solutions bypassing set matching), and arises from the table pairing, but with the details for each pair being constant in size and accessed via indexes. It's worth noting that the query GTT_NE_X is actually slower than INL_MI and SUB_NE on the smallest data point, but much quicker on the largest, showing the importance of polynomial order for scalability.

Conclusions

• We have shown how to solve master-detail transaction matching problems efficiently, using an example problem, but emphasising the generality of the techniques
• Appropriate use of subquery factors and temporary tables have been demonstrated, with performance analysis
• It's worth highlighting the technique of pre-filtering on aggregates before comparing sets in detail
• The importance for scalability of performance variation powers has been illustrated, being revealed by dimensional benchmarking
• On finishing this article it occurred to me to wonder whether it might not be possible to use aggregate matching to replace detail set matching altogether, and at least in some cases it is, with linear performance resulting, described in my next article, Holographic Set Matching in SQL (MDTM2)

# Query Structure Diagramming

Last bank holiday Monday I posted a solution to an SQL problem on OTN, and I later thought that the SQL would make a nice example to illustrate my Query Structure Diagramming (QSD) technique. I published my first example of this in May 2009 on scribd,

and have continued to develop it in subsequent articles. I use the technique here to illustrate the SQL structure for the OTN example mentioned and also for a second OTN example that I posted shortly after. Both examples structure the queries using subquery factors.

SQL Subquery Factors

Subquery factors were introduced in Oracle Database v9.2 and have since become a key technique in developing queries of any complexity. They generalise the v8 inline view technique, allowing subqueries now to be declared with an alias (using the 'WITH' clause), then referenced as often as desired later in the query. When referenced multiple times, Oracle's Cost Based Optimiser (CBO) normally executes the subquery once and writes the results to temporary space to aid performance (this can be seen in the Explain Plan as a LOAD AS SELECT action). Using subquery factors can make queries much easier to read, even when they are referenced only once, in which case CBO normally restructures them internally to incorporate them within another subquery or the main query. It is important to note, though, that subquery factors, when retained by CBO, will be joined by full scans when referenced later in the query and in some cases it is more efficient to retain the table references to allow indexed joins (my next blog post will include an example of this).

Subquery factors, along with inline views, are the building blocks of modern SQL, as subroutines are of other languages. My QSDs are intended to show how they allow a procedural flow at the structural level, while retaining the set-based logic within the subqueries.

Leave and Attendance Query

The problem here is that the poster has a daily attendance table and a leave table, where leave is stored as date ranges, but wants a single query that outputs data in daily form. The keys to this are:

• Realising that you cannot drive from the event tables but must generate a continuous set of days to drive from, for each employee (assuming you don't have them in a separate reference table)
• Converting the leave ranges to leave days by joining to the generated days rowset
• Joining the leave daily rowset with the attendance table by a union

Read more here (I'm BrendanP on OTN): Attendance and Leave table Join

ERD

Note that tables were not provided for Employee and Day in the OTN post, but it is useful to include them as entities nonetheless.

SQL

Note that in the query below, both the date range and the employee set are generated from the transactional data, which is obviously an artificial feature arising from this being for a problem on a forum, but it's no harm in terms of the purpose of this article.

WITH ext AS (
SELECT Min (att_date) min_date, Max (att_date) - Min (att_date) + 1 n_days
FROM attendance
), dys AS (
SELECT min_date + LEVEL - 1 day
FROM ext
CONNECT BY LEVEL < n_days + 1
), ems AS (
SELECT emp_id
FROM attendance
UNION
SELECT employee_number
FROM leave
), edy AS (
SELECT
dys.day,
ems.emp_id
FROM dys
CROSS JOIN ems
), ldy AS (
SELECT
edy.emp_id,
edy.day,
lve.leave_reason
FROM edy
JOIN leave                lve
ON edy.day              BETWEEN lve.date_start AND lve.date_end
AND lve.employee_number  = edy.emp_id
), uni AS (
SELECT
emp_id,
att_date,
timein,
timeout,
late_in,
early_out,
reason
FROM attendance
UNION
SELECT
emp_id,
day,
NULL,
NULL,
NULL,
NULL,
leave_reason
FROM ldy
)
SELECT
edy.emp_id,
edy.day,
uni.timein,
uni.timeout,
uni.late_in,
uni.early_out,
uni.reason
FROM edy
LEFT JOIN uni
ON uni.att_date     = edy.day
AND uni.emp_id       = edy.emp_id
ORDER BY 1, 2{code}

QSD

Counting Flight Statistics Query

The problem here is that the poster has three tables with data on events for frequent fliers and wants to show aggregate counts by year, but wants all years within a range to be included in the output, including years with no events. The key to this is realising that you cannot drive from the event tables but must generate a continuous set of years to drive from (assuming you don't have them in a separate reference table). Read more here: Multiple Count aggregates from different sources grouped by Year

ERD

Note that a table was not provided for Year in the OTN post, but it is useful to include it as an entity nonetheless.

SQL

WITH yrs AS (
SELECT Add_Months (To_Date ('01011989', 'ddmmyyyy'), 12*LEVEL) YEAR
FROM DUAL
CONNECT BY LEVEL < 24
), ffe AS (
SELECT Trunc (start_date, 'YEAR') year, Count(*) n_enr
FROM freq_flyer_enrollment
GROUP BY Trunc (start_date, 'YEAR')
), ffs AS (
SELECT Trunc (survey_date, 'YEAR') year, Count(*) n_sur
FROM freq_flyer_survey
GROUP BY Trunc (survey_date, 'YEAR')
), fff AS (
SELECT Trunc (flt_date, 'YEAR') year, Count(*) n_fly
FROM freq_flyer_flights
GROUP BY Trunc (flt_date, 'YEAR')
)
SELECT  yrs.year,
Nvl (ffe.n_enr, 0) n_enr,
Nvl (ffs.n_sur, 0) n_sur,
Nvl (fff.n_fly, 0) n_fly
FROM yrs
LEFT JOIN ffe
ON ffe.year = yrs.year
LEFT JOIN ffs
ON ffs.year = yrs.year
LEFT JOIN fff
ON fff.year = yrs.year
ORDER BY 1

QSD

# Data Structure Diagramming

Like many SQL developers I have always used entity-relationship diagrams to help in writing queries, and would extract sections to document them. Some years ago, however, I realised that having a single static diagram was not sufficient for complex queries with large numbers of tables, structures such as inline views, and multiple table instances. I therefore developed a diagram-based design methodology that I published in May 2009 on scribd. Since then I have extended the ideas in that approach to develop diagrams to cover various additional structures in SQL and in other areas. These diagrams were developed as needed for particular scenarios and have been published in several documents on scribd. I thought it would be a good idea to bring them together in one place, namely here, with example diagrams and the scribd document embedded thereafter. [Incidentally, I wonder what readers make of this 8-dimensional document structure?]

I would categorise them under four headings:

• Entity-Relationship Diagrams
• Structured Design Methodology
• SQL Special Structures
• Object Structures

Entity-Relationship Diagrams
Oracle Spatial Schema
The embedded document below also includes an ERD of the much simpler HR schema, but this one is more interesting as it shows extensive use of subtypes. The document is concerned with networks and I superimposed tree and non-tree network links on the diagram.

Oracle Customer Model and Multi-Org
Here I used shading to distinguish between org-striped, org-linked (my term) and other entities.

Structured Design Methodology
The methodology involves a sequence of diagrams and tables, so I have not extracted a diagram in this case.

SQL Special Structures
Multiple Table Instances with Scalar Subqueries in Where Clause
Subquery Factor

Selecting Database Function

Selecting Scalar Subqueries

Nested Analytics Subqueries

Model Clause

Recursive Subquery Factor

Object Structures
I use a different type of diagram for object structures from those for SQL and ERDs, and it's intended to be very general, being independent of programming language and applicable to any object structure, allowing arbitrary nesting of array and record types.
Code Timer Object
This object was implemented in three languages: Oracle, Perl and Java.

Excel Array Object
This object was implemented in Perl.