Step 7: Retest System Performance After
Tuning

After recreating the test data set with the selected sort keys, distribution styles,
and compressions encodings, you will retest the system performance.

To Retest System Performance After
Tuning

Record storage use.

Determine how many 1 MB blocks of disk space are used for each table by
querying the STV_BLOCKLIST table and record the results in your benchmarks
table.

Copy

select stv_tbl_perm.name as "table", count(*) as "blocks (mb)"
from stv_blocklist, stv_tbl_perm
where stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name in ('customer', 'part', 'supplier', 'dwdate', 'lineorder')
group by stv_tbl_perm.name
order by 1 asc;

Uneven distribution, or data distribution skew, forces some nodes to do
more work than others, which limits query performance.

To check for distribution skew, query the SVV_DISKUSAGE system view. Each
row in SVV_DISKUSAGE records the statistics for one disk block. The
num_values column gives the number of rows in that disk
block, so sum(num_values) returns the number of rows on each
slice.

Execute the following query to see the distribution for all of the tables
in the SSB database.

Copy

select trim(name) as table, slice, sum(num_values) as rows, min(minvalue), max(maxvalue)
from svv_diskusage
where name in ('customer', 'part', 'supplier', 'dwdate', 'lineorder')
and col =0
group by name, slice
order by name, slice;

The following chart illustrates the distribution of the three largest
tables. (The columns are not to scale.) Notice that because CUSTOMER uses
ALL distribution, it was distributed to only one slice per node.

The distribution is relatively even, so you don't need to adjust for
distribution skew.

In the EXPLAIN plan for Query 2, notice that the DS_BCAST_INNER labels
have been replaced by DS_DIST_ALL_NONE and DS_DIST_NONE, which means that no
redistribution was required for those steps, and the query should run much
more quickly.