Fixing missing statistics using QPTune
Run QPTune with start_stats to prepare the server to collect missing statistics:
QPTune -A start_stats -v -S my_host:4816/my_database
Sample output:
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
Run the client application, stored procedure, or query.
Run QPTune with collect_stats action to collect statistics that exceed the threshold for count of missing statistics. You may let the utility wait for some period of time (specified by the -T option) before collecting the missing statistics information.
QPTune -A collect_stats -m 1 -o missingstats.xml -v -S my_host:4816/my_database
Sample output:
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". QPTune Utility <?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. ------
Update statistics that have exceeded or equalled the threshold for count of missing statistics, specified by the -m option. To fix missing statistics that are specified in the input file missingstats.xml, use:
QPTune -U sa -P -A fix_stats -m 1 -i missingstats.xml -v -S my_host:4816/my_database
Sample output:
Executing : QPTune -U sa -P -S jdbc:sybase:Tds:my_host:4816/my_database -A fix_stats -M allrows_dss -T 0 -i missingstats.1 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. ------
If the fix_stats action
is used with the -N option, QPTune does not execute
the statements to fix missing statistics, but instead sends them to an
output file specified by -o output_file.
(Optional) The undo_fix_stats command deletes the statistics specified in the -i XML file. The statistics deleted are those that have missing counts exceeding or equal to a number specified by -m. To undo the fix of missing statistics in the input file missingstats.xml use:
QPTune -U sa -P -A undo_fix_stats -m 1 -i missingstats.xml -v -S my_host:4816/my_database
Sample output:
Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:4816/my_database -A undo_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:20:23 PDT 2008" ----------------------------------------------------------- Details of statements(s) fixed: ------------------------------- Fixed statistics:[Delete] Y(y4,y2) [INFO] Fix Statement = delete statistics Y(y4,y2) Fixed statistics:[Delete] Y(y3) [INFO] Fix Statement = delete statistics Y(y3) QPTune Utility Fixed statistics:[1 Delete] Y(y2,y1) [INFO] Fix Statement = delete statistics Y(y2,y1) Fixed statistics:[Delete] Y(y1) [INFO] Fix Statement = delete statistics Y(y1) ----- QPTune finished executing. ------
Optimizing an application using QPTune
Run QPTune with start, specifying one of: allrows_oltp, allrows_mix, or allrows_dss:
QPTune -U sa -P -S my_host:11030/my_database -A start -M allrows_mix -v
In this example, Adaptive Server runs on a machine called “my_host” with a port number 11030 and a database called my_database.
Sample output:
Executing : QPTune -Usa -P [unshown] –S jdbc:sybase:Tds:my_host:11030/my_database -A start -M allrows_mix -T 0 -i null -o metrics.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -n null -v You are now connected to database: my_database [INFO] Config: sp_configure 'enable metrics capture', 1 [INFO] Config: sp_configure 'abstract plan dump', 1 [INFO] Config: sp_configure 'system table', 1 [INFO] Config: sp_metrics 'flush' [INFO] Config: delete sysqueryplans Apply "sp_configure optimization_goal, 0, allrows_mix" to the data source. Program has configured the data source for metrics collection.
Run the client application, stored procedure, or query. The client application may be a GUI-based or Web-based program, a set of stored procedures, or a batch of SQL queries in a script. For example:
isql -Usa -P < sp_telco.sql > sp_telco_allrows_mix.out
Run QPTune with collect to collect metrics on each of the queries in the application. The metrics are collected in a file called sp_telco_allrows_mix.xml.
QPTune -U sa -P -S my_host:11030/my_database -A collect -M allrows_mix -o sp_telco_allrows_mix.xml -v
Repeat steps 1 – 3 for each of other optimization goals or custom modes. For example, to use allrows_dss, run:
QPTune -U sa -P -S my_host:11030/my_database -A start -M allrows_dss isql -Usa -P < sp_telco.sql > sp_telco_allrows_dss.out QPTune -U sa -P -S my_host:11030/my_database -A collect -M allrows_dss -o sp_telco_allrows_dss.xml
Sample output for mode allrows_mix:
Executing : QPTune -U sa -P [not shown] -S jdbc:sybase:Tds:my_host:11030/my_database –A collect -M allrows_mix -T 0 -i null -o sp_telco_allrows_mix.xml -f null -c config.xml-l 5 -e elap_avg -d 5,5 -n null -v You are now connected to database: my_database Now collecting information from sysquery tables on "Tue Aug 26 22:00:49 PDT 2008". Metrics are flushed. <?xml version="1.0" encoding="UTF-8"?> <server url="jdbc:sybase:Tds:my_host:11030/my_database" file="sp_telco_allrows_mix.xml" mode="allrows_mix" datetime="Tue Aug 26 22:00:49 PDT 2008" > <query id="1"> <qtext>SELECT service_key , year , fiscal_period , count(*) FROM telco_facts T , month M , status S WHERE T.month_key=M.month_key AND S.status_key = T.status_key AND call_waiting_status="Dropped" GROUP BY year , fiscal_period , service_key ORDER BY year , fiscal_period , service_key </qtext> <hashkey>323626785</hashkey> <id>1568005586</id> <elap_avg>27408</elap_avg> <bestmode>allrows_mix</bestmode> </query> <query id="2"> <qtext>SELECT customer_last_name , customer_first_name FROM residential_customer R , telco_facts T , service S , month M WHERE M.month_text = 'February ' AND M.year = 1998 AND S.isdn_flag = 'Y' AND M.month_key = T.month_key AND S.service_key = T.service_key AND R.customer_key = T.customer_key -- end comment i </qtext> <hashkey>727793461</hashkey> <id>1552005529</id> <elap_avg>3355</elap_avg> <bestmode>allrows_mix</bestmode> </query> . . . . . . . . . . . . <query id="10"> <qtext>SELECT month_key , service_key , count(*) FROM telco_facts WHERE month_key = 1 GROUP BY month_key , service_key </qtext> <hashkey>1561133104</hashkey> <id>1680005985</id> <elap_avg>58</elap_avg> <bestmode>allrows_mix</bestmode> </query> </server> The metrics information is written into XML file: sp_telco_allrows_mix.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 Program has restored the data source for metrics collection. ----- QPTune finished executing. ------
Compare metrics collected from all the runs, with the best metrics for each query, in a file called best.xml. You can define a new mode, called “new_mode” for this metric.
QPTune -U sa -P -S my_host:11030/my_database -v -A compare -M new_mode -f sp_telco_allrows_dss.xml, sp_telco_allrows_mix.xml,sp_telco_allrows_oltp -o best.xml
Sample output:
Executing: QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:11030/my_database
-A compare -M new_mode -T 0 -I null -o best.xml
–f sp_telco_allrows_mix.xml, sp_telco_allrows_dss.xml,
sp_telco_allrows_oltp.xml
-c config.xml -l 5 -e elap_avg -d 5,5 -n null –v
Compare all the files:
sp_telco_allrows_mix.xml,sp_telco_allrows_dss.xml,sp_telco_allrows_oltp.xml
Report generated on "Wed Aug 27 16:29:01 PDT 2008"
--------------------------------------------------------
Sorted List By File Size (Desc.) =
sp_telco_allrows_mix.xml, sp_telco_allrows_dss.xml,
sp_telco_allrows_oltp.xml
File #1 : [name=sp_telco_allrows_mix.xml : mode=allrows_mix]
File #2 : [name=sp_telco_allrows_dss.xml : mode=allrows_dss]
Query count in File #1 [mode=allrows_mix]: 14
Query count in File #2 [mode=allrows_dss]: 12
=================================================
Query count improved in File #2[mode=allrows_dss]: 7
Total performance improved [from 37234 to 7781]: 79 %
Following queries run better in File #2 [mode=allrows_dss]:
-------------------------------------------------
Group 1: improved by no more than 25% [2 queries]
Query: SELECT state, count(*) FROM telco_facts T, service S,
residential_customer C, month M
WHERE T.service_key = S.service_key
AND T.customer_key = C.customer_key AND T.month_key = M.month_key
AND call_waiting_flag = 'Y' AND caller_id_flag = 'Y'
AND voice_mail_flag = 'N' AND state in ('NY', 'NJ', 'PA')
AND fiscal_period = 'Q1'
GROUP BY state
Average elapsed time(ms): File #1=837 File #2=803 Improvement=4.0%
Outstanding=No
Query: SELECT fiscal_period, T.service_key, sum(local_call_minutes),
sum(local_call_count) , count(*)
FROM telco_facts T ,residential_customer C, service S , month M
WHERE T.customer_key = C.customer_key
AND T.service_key = S.service_key AND T.month_key = M.month_key
AND fiscal_period = 'Q4' AND T.service_key in (02, 03)
AND state = 'CA'
GROUP BY fiscal_period , T.service_key
Average elapsed time(ms): File #1=832 File #2=635 Improvement=23.0%
Outstanding=Yes
-------------------------------------------------
Group 2: improved by 25% to 50% [2 queries]Group 3: improved by 50% to 75% [0 queries]
. . . . . . . . . . .
Group 4: improved by 75% to 100% [3 queries]
Query: SELECT service_key , year , fiscal_period , count(*)
FROM telco_facts T , month M , status S
WHERE T.month_key=M.month_key AND S.status_key = T.status_key
AND call_waiting_status="Dropped" GROUP BY year, fiscal_period,
service_key
ORDER BY year , fiscal_period , service_key -- end comment--
Average elapsed time(ms): File #1=27408 File #2=2126 Improvement=92.0%
Outstanding=Yes
. . . . . . . . .
File #3 : [name=sp_telco_allrows_oltp.xml : mode=allrows_oltp]
Query count in File #3[mode=allrows_oltp]: 13
=================================================
Query count improved in File #3[mode=allrows_oltp]: 4
Total performance improved [from 7781 to 6523]: 16 %
Following queries run better in File #3:-------------------------------------------------
Group 1: improved by no more than 25% [2 queries]
Query: SELECT fiscal_period , count(*) , sum(local_call_minutes)
FROM residential_customer R , telco_facts T , status S , month M
WHERE S.call_waiting_status=@status AND state =
. . . . . . . . . . .
Fix the query plans in your application by using the best plan from the comparison:
QPTune -U sa -P -S my_host:11030/my_database -g -A fix -i best.xml
Sample output:
Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:11030/my_database -A fix -M allrows_dss -T 0 -i best.xml -o metrics.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -n null -v You are now connected to database: my_database [INFO] Config: sp_configure 'abstract plan load', 1 [INFO] Config: sp_configure 'system table', 1 [INFO] Config: sp_metrics 'flush' [INFO] Config: delete sysqueryplans [INFO] Config: sp_configure 'enable metrics capture', 1 You are now connected to database: my_database Query plan(s) fixed on "Wed Aug 27 17:05:46 PDT 2008" --------------------------------------------------------- Fixed 3 queries using mode "allrows_oltp" Fixed 3 queries using mode "allrows_dss" Fixed 8 queries using mode "allrows_mix" Apply "sp_configure optimization_goal, 0, allrows_mix" as the default optgoal. Details of statements(s) fixed: ------------------------------- Query: SELECT service_key , year , fiscal_period , count(*) -- comment 1 it''s a comment. whatever "statments" /* comment 3 */ FROM telco_facts T , month M , status S WHERE T.month_key=M.month_key AND S.status_key = T.status_key AND call_waiting_status="Dropped" GROUP BY year, fiscal_period, service_key ORDER BY year , fiscal_period , service_key -- end comment -- *** Query #9 *** . . . . . . . . . . . . .
You may run select queries using your own custom modes defined in a configuration file. QPTune includes some custom modes like “_basic_,” which represents basic optimization of Adaptive Server 12.5. For example, the default configuration file config.xml contains custom mode “custom1” which allows an optimization goal of allrows_oltp, together with the rule merge_join_off:
<!-- "default" custom mode --> <mode name="default"> <optgoal>use optgoal allrows_mix</optgoal> <rule>use merge_join off</rule> <rule>use opttimeoutlimit 15</rule> </mode> <!-- "_basic_" mode is a reserved system mode. --> <mode name="_basic_"> </mode> <mode name="custom1"> <optgoal>use optgoal allrows_oltp</optgoal> <rule>use merge_join off</rule> </mode>
This example shows how to use the “_basic_” custom mode:
QPTune –A start –M _basic_ –Usa –P –S my_host:11030/my_database –i best.xml -l 0 -v isql -Usa -P < sp_telco_2.sql > sp_telco_basic.out QPTune -A collect -M _basic_ -Usa -P -S my_host:11030/my_database -o sp_telco_basic.xml –v QPTune -A compare -M best -Usa -P -Smy_host:11030/my_database -v -f sp_telco_basic.xml,best.xml -o best_basic.xml –d 1,0
Sample output:
Report generated on "Fri Aug 29 13:29:17 EDT 2008"
--------------------------------------------------------
{INFO]Sorted List By File Size (Desc.)=sp_telco_basic.xml,best.xml
File #1 : [name=sp_telco_basic.xml : mode=_basic_]
File #2 : [name=best.xml : mode=best]
Query count in File #1: 14
Query count in File #2: 14
=================================================
Query count improved in File #2: 7
Total performance improved [from 2441 to 1529]: 37 %
Following queries run better in File #2:
-------------------------------------------------
Group 1: improved by no more than 25% [4 queries]
Query: SELECT customer_last_name , customer_first_name
FROM residential_customer R , telco_facts T , service S , month M
WHERE M.month_text = 'February ' AND M.year = 1998
AND S.isdn_flag = 'Y' AND M.month_key = T.month_key
AND S.service_key = T.service_key AND R.customer_key = T.customer_key
Average elapsed time(ms): File #1=393 File #2=306 Improvement=22.0%
Outstanding=Yes
. . . . . . . . . . . . .
