Download View this document as PDF

CHAPTER 2
❖
QPTune
Optimizing 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
Migration Technology Guide
37