/spds
on a machine named spot. A user has created a table with the following
SAS program:
LIBNAME my_data sasspds 'mydomain' server=spot.spdsname user='anonymous'; data my_data.test(index=(x)); do i = 1 to 30000; x = mod(i,3); output; end; run; data my_data.test1; do i = 1 to 10000; x = mod(i,2); output; end; run; PROC APPEND base=my_data.test data=my_data.test1; run; PROC SQL; delete from my_data.test where x=1; quit;
-stats
option of ixutil to
get the disk usage and segment list fragmentation statistics for the
index:
> ixutil -stats test -dsn test -libpath /spds SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2009 by SAS Institute Inc, Cary NC 27513 USA Statistics for Index X: --------------------------------------------------- +--segment_size = 8192 +--n_segments_in_tbl = 5 +--n_values_in_index = 2 +--n_vdeleted_values = 1 +--percent_vdeleted = 33.33 +--n_seglist_values = 2 +--n_seglist_chunks = 3 +--avg_chunks_per_list = 1.00 +--idx_file_bytes = 13304 +--idx_garbage_bytes = 4272 +--percent_idx_garbage = 32.11 Ixutil completed successfully
> ixutil -stats x -dsn test -libpath /spds -dist SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2008 by SAS Institute Inc, Cary NC 27513 USA Statistics for Index X: --------------------------------------------------- +--segment_size = 8192 +--n_segments_in_tbl = 5 +--n_values_in_index = 2 +--n_vdeleted_values = 1 +--percent_vdeleted = 33.33 +--n_seglist_values = 2 +--n_seglist_chunks = 3 +--avg_chunks_per_list = 1.00 +--idx_file_bytes = 13304 +--idx_garbage_bytes = 4272 +--percent_idx_garbage = 32.11 +--Distribution Stats for Non Unique Values +----minimum segments for all values = 4 +----maximum segments for all values = 5 +----average segments of any value = 4 +----average percentage of segments of any value = 90.00 Ixutil completed successfully
-reorg
option to reorganize the index to consolidate
segment lists and retrieve unused disk space:
> ixutil -reorg x -dsn test -libpath /spds SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2009 by SAS Institute Inc, Cary NC 27513 USA Reorg for Index x: Reorg successfully completed Ixutil completed successfully
-stats
option once more to review the index and segment
list data, in order to view the improved performance statistics.
> ixutil -stats x -dsn test -libpath /spds SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2009 by SAS Institute Inc, Cary NC 27513 USA Statistics for Index X: ----------------------------------------- +--segment_size = 8192 +--n_segments_in_tbl = 5 +--n_values_in_index = 2 +--n_vdeleted_values = 0 +--percent_vdeleted = 0.00 +--n_seglist_values = 2 +--n_seglist_chunks = 2 +--avg_chunks_per_list = 1.00 +--idx_file_bytes = 9008 +--idx_garbage_bytes = 0 +--percent_idx_garbage = 0.00
-statjidx
option of the ixutil command. The statistics
are printed for each join range of the index, as well as for the overall
index. The range statistics identify each range (sobs=starting observation,
eobs=ending observation), the number of unique join keys that exist
in the range, and the number of keys that will be joined in the range
for each table.
> ixutil -statjidx Table1,ID Table2,ID -libpath /tmp SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2009 by SAS Institute Inc, Cary NC 27513 USA Stat of Join Index Table1.jdxid.table2.jdxid.0.0.0.spds9: Nranges=4 ----------------------------------------------------------------------- +-Range 0 +----<Table1,ID>: sobs=1 eobs=25000 (Sorted) +-------unique_keys=25000, max_occurance=1 +-------obs=25000, joinobs=25000, rangepct=100.00 +----<Table2,ID>: sobs=1 eobs=10000 (Sorted) +-------unique_keys=10000, max_occurance=1 +-------obs=10000, joinobs=10000, rangepct=100.00 +-Range 1 +----<Table1,ID>: sobs=25001 eobs=50000 (Sorted) +-------unique_keys=25000, max_occurance=1 +-------obs=25000, joinobs=25000, rangepct=100.00 +----<Table2,ID>: sobs=-1 eobs=0 +-------unique_keys=0, max_occurance=0 +-------obs=2, joinobs=0, rangepct= 0.00 +-Range 2 +----<Table1,ID>: sobs=50001 eobs=75000 (Sorted) +-------unique_keys=25000, max_occurance=1 +-------obs=25000, joinobs=25000, rangepct=100.00 +----<Table2,ID>: sobs=-1 eobs=0 +-------unique_keys=0, max_occurance=0 +-------obs=2, joinobs=0, rangepct= 0.00 +-Range 3 +----<Table1,ID>: sobs=75001 eobs=100000 (Sorted) +-------unique_keys=25000, max_occurance=1 +-------obs=25000, joinobs=25000, rangepct=100.00 +----<Table2,ID>: sobs=-1 eobs=0 +-------unique_keys=0, max_occurance=0 +-------obs=2, joinobs=0, rangepct= 0.00 Table Table1, Column ID average range join row pct=100.00 Table Table2, Column ID average range join row pct= 25.00 Ixutil completed successfully
> ixutil -deljidx Table1,ID Table2,ID -libpath /tmp SAS Scalable Performance Data Server 4.5(TS M0) Build(Feb 26 2009, 11:50:08) Index File Utility Copyright (c) 1996-2009 by SAS Institute Inc, Cary NC 27513 USA Ixutil completed successfully