qptune is an SAP ASE utility written in Java/XML. It enables users to fix missing statistics and identify the best query plan, optimization goals, or other configuration settings, and apply them at the query or server level. This results in optimal performance of subsequent query executions.
qptune [-U username] [-P password] [-S hostname:port/database] [-A action] [-M mode] [-T appTime] [-i inputFile] [-o outputFile] [-f fileList(,)] [-c configFile] [-l limit] [-e evalField] [-d difference] [-m missingCount] [-n login] [-J charset>] [-N (noexec)] [-g (applyOptgoal)] [-v (verbose)] [-s (sort)] [-h (help)]
start
collect – the default value
collect_full
compare
fix
start_stats
collect_stats
fix_stats
undo_fix_stats
QPTune -A start_stats -S my_host:4816/my_database -v Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:4816/my_database -A start_stats -M allrows_dss -T 0 -i null -o metrics.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -m 5 -n null -v You are now connected to database: my_database [INFO] Config: sp_configure 'capture missing statistics', 1 [INFO] Config: sp_configure 'system table', 1 [INFO] Config: delete sysstatistics where formatid =110
QPTune -A collect_stats -m 1 -o missingstats.xml -v -S my_host:4816/my_database Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:4816/my_database -A collect_stats -M allrows_dss -T 0 -i null -o missingstats.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -m 1 -n null -v You are now connected to database: my_database Now collecting missing statistics information from sysstatistics on "Fri Sep 26 10:08:06 PDT 2008". <?xml version="1.0" encoding="UTF-8"?> <server url="jdbc:sybase:Tds:my_host:4816/my_database" file="missingstats.xml" type="missing stats" datetime="Fri Sep 26 10:08:06 PDT 2008" > <missingStat id="1"> <id>1068527809</id> <stats>Y(y4,y2)</stats> <count>2</count> </missingStat> <missingStat id="2"> <id>1068527809</id> <stats>Y(y3)</stats> <count>1</count> </missingStat> <missingStat id="3"> <id>1068527809</id> <stats>Y(y2,y1)</stats> <count>1</count> </missingStat> <missingStat id="4"> <id>1068527809</id> <stats>Y(y1)</stats> <count>1</count> </missingStat> </server> The missing statistics information is written into XML file: missingstats.xml [INFO] End config: sp_configure 'enable metrics capture', 0 [INFO] End config: sp_configure 'abstract plan dump', 0 [INFO] End config: sp_configure 'system table', 0 [INFO] End config: sp_configure 'capture missing statistics', 0 Program has restored the data source for metrics collection. ----- QPTune finished executing. ------
QPTune -A fix_stats -m 1 -i missingstats.xml -v -S my_host:4816/my_database Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:4816/my_database -A fix_stats -M allrows_dss -T 0 -i missingstats.xml -o metrics.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -m 1 -n null -v You are now connected to database: my_database Fix statistics on "Fri Sep 26 10:14:59 PDT 2008" ----------------------------------------------------------- Details of statements(s) fixed: ------------------------------- Fixed statistics:[Update] Y(y4,y2) [INFO] Fix Statement = update statistics Y(y4,y2) Fixed statistics:[Update] Y(y3) [INFO] Fix Statement = update statistics Y(y3) Fixed statistics:[Update] Y(y2,y1) [INFO] Fix Statement = update statistics Y(y2,y1) Fixed statistics:[Update] Y(y1) [INFO] Fix Statement = update statistics Y(y1) ----- QPTune finished executing. ------
QPTune -U sa -P -S my_host:5000/my_database -A fix_stats -m 5 -i missingstats.xml -N -o missingstats.sql
QPTune -S host:port/database -A start
[-M {allrows_oltp, allrows_dss, allrows_mix}]
QPTune -S host:port/database -A start -M custom_1 -i input.xml -l 3 [-v]
QPTune -S host:port/database -A collect -T 0 -o a2.xml -v Program has configured the data source for metrics collection. Now collecting information from sysquerymetrics on "Tue Feb 19 22:16:04 PST 2008". <?xml version="1.0" encoding="UTF-8"?> <server url="jdbc:sybase:Tds:SHANGHI:5000" type="ASE" mode="custom_1" datetime="Tue Feb 19 22:16:04 PST 2008"> <query id="1"> <qtext> select count(T.title_id) from authors A, titleauthor T where A.au_id = T.au_id </qtext> <elap_avg>300</elap_avg> <bestmode> custom_1 </bestmode> </query> </server>
QPTune -A compare -f a1.xml,a2.xml -d 51,10 -o best.xml -S my_host:5000/my_database
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
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
If this occurs, increase the value of the maximum heap size of the Java Virtual Machine (JVM) by using sp_jreconfig to set the -Xmx arguments of the PCA_JVM_JAVA_OPTIONS directive to a size larger than the default of 1024MB. See Chapter 2, “Managing the Java Environment” in Java in Adaptive Server Enterprise for more information about -Xmx.
When you use QPTune to collect a large amount of queries, do not use the -v verbose option.
QPtune’s compare action may be run by any user. All other actions of QPTune may only be run by users with sa_role and sso_role.