Once metrics are collected, you can compare different XML files to get the best query optimization goal or criteria for each of the queries. For example:
QPTune -A compare -f a1.xml,a2.xml[,a3.xml..] -d 51,10 -o best.xml -S my_host:5000/my_database
The -f option specifies a list of two or more collected metrics sample files separated by commas. Use quotes to encapsulate the file name if it contains any spaces.
The -d option indicates a threshold percentage and absolute value. A performance improvement beyond the threshold percentage and absolute value is considered “outstanding” during the fix operation. The optimization goal/criteria for those outstanding queries is applied to the server as a plan fix.
The default for the threshold percentage and absolute value pair is “5,5”. If only percentage is specified, the absolute value defaults to 0. Percentage values are between 0 and 100; an absolute value can be any number greater than 0.
The -o option specifies the result of the comparison in a file. The file holds the best setting for all the queries being analyzed.
The -s option enables sorting the files from largest to smallest. The file with the largest set of queries is used as the basis for comparison.
The following example shows the result of a compare operation:
Compare all the files: | a1.xml, a2.xml|
Report generated on "Tue Aug 19 21:13:04 PST 2008"
--------------------------------------------------------------------------
File #1: [name= a1.xml : mode=allrows_mix]
File #2: [name= a2.xml : mode=custom_1]
Query count in File #1 : [mode=allrows_mix] 6
Query count in File #2 : [mode=custom_1] 7
==========================================================================
Query count improved in File #2: [mode=allrows_mix] 3
Total performance improved [from 422 to 129]: 69 %
Following queries run better in File #2:
[mode=allrows_mix]
--------------------------------------------------------------------------
Group 1: improved by no more than 25% [0 queries]
Group 2: improved by 25% to 50% [1 queries]
Query: select count(T.title_id) from authors A, titleauthors T where A.au_id = T.au_id
Average elapsed time (ms): File #1=100 File #2=50 Improvement=50.0% Outstanding=No
Group 3: improved by 50% to 75% [0 queries]
Group 4: improved by 75% to 100% [2 queries]
Query: select count(*) from titlles T, titleauthors TA where T.title_id = TA.title_id
Average elapsed time (ms): File #1=34 File #2=7 Improvement=79.0% Outstanding=Yes
Query: select au_lname, au_fname from authors where state in ("CA", "AZ")
Average elapsed time (ms): File #1=9 File #2=0 Improvement=100.0% Outstanding=No
The above example shows a comparison between two XML metrics files: a1.xml has six queries, and a2.xml has seven queries. Comparisons can only be made between the queries that are common to both files. There are three queries that ran faster in a2.xml. The improvements are categorized into four groups:
Group 1 – between 0 and 25%
Group 2 – between 25% and 50%
Group 3 – between 50%and 75%
Group 4 – between 75% and 100%
There is one query between 25 and 50% and two queries between 75% and 100%. The queries in Group 2 are marked as “Outstanding=No” which means that based on the threshold of 51%, this query will not be fixed.
While comparing more than two files, QPTune updates the first file with the best from both files, then compares the new file with the third file, and so on.
