Examples

StepsFixing missing statistics using QPTune

  1. 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
    
  2. Run the client application, stored procedure, or query.

  3. 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. ------
    
  4. 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. ------
    

    NoteIf 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.

  5. (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. ------
    

StepsOptimizing an application using QPTune

  1. 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.
    
  2. 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
    
  3. 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. ------
    
  4. 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 =
    . . . . . . . . . . .
    
  5. 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 ***
    . . . . . . . . . . . . .
    

StepsUsing QPTune custom modes

  1. 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>
    
  2. 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
    . . . . . . . . . . . . .