Upgrade issues

QPTune helps you get the best performance when upgrading to Adaptive Server 15.0 . If there are queries that do not perform as well as pre 15.0 versions of the server, QPTune allows Adaptive Server Enterprise to generate version 12.5.4-like query plans. If these plans run faster than the corresponding version 15.0 query plans, QPTune retains and uses these plans for all subsequent query execution.

StepsUsing QPTune while migrating to Adaptive Server 15.0

  1. Depending on the application, get metrics information for any or all of the three Adaptive Server 15.0's pre-programmed modes (“mix,” “dss,” “oltp”):

    QPTune -A start -M allrows_oltp 
            -S my_host:5000/my_database
    

    <Run your query, application, or stored procedure>

    QPTune -A collect -M allrows_oltp -T 0 -o oltp.xml
            -S my_host:5000/my_database
    
    QPTune -A start -M allrows_mix 
            -S my_host:5000/my_database
    

    <Run your query, application, or stored procedure>

    QPTune -A collect -M allrows_mix -T 0 -o mix.xml
            -S my_host:5000/my_database
    
    QPTune -A start -M allrows_dss 
            -S my_host:5000/my_database
    

    <Run your query, application, or stored procedure>

    QPTune -A collect -M allrows_dss -T 0 -o dss.xml
            -S my_host:5000/my_database
    
  2. Get metrics information with optimization similar to version 12.5.4 in Adaptive Server 15.0:

    QPTune -A start -M _basic_ -i oltp.xml -l 10
            -S my_host:5000/my_database
    

    <Run your query, application, or stored procedure>

    QPTune -A collect -M _basic_ -T 0 -o basic.xml
            -S my_host:5000/my_database
    
  3. Compare the metrics information:

    QPTune -A compare -d 10 -o best.xml 
            -f basic.xml,oltp.xml,mix.xml,dss.xml
            -S my_host:5000/my_database
    
  4. Fix query plans with the best out of the comparison:

    QPTune -A fix -i best.xml 
            -S my_host:5000/my_database
    
  5. (Optional) Verify the performance improvement after the plan fixup, re-run the application, and collect the metrics information:

    QPTune -A collect -T 0 -o new_best.xml
            -S my_host:5000/my_database
    

    Performing a compare of new_best.xml with any of the other XML output files should establish that new_best.xml gives the best results.