Home

View this document as PDF

1. 18 Adaptive Server Enterprise CHAPTER2 QPTune Using QPTune to fix missing statistics Use QPTune to fix or update the missing statistics after you have upgraded a server The main steps for using QPTune to fix missing statistics are Start QPTune using the start_stats action Run the application queries or stored procedure Collect any missing statistics information into a specified XML file See Collecting statistics on page 21 Use the fix_stats action to update statistics as specified in the above XML file See Fixing statistics on page 23 Optional Undo the fix of missing statistics using the undo_fix_stats action See Using undo_fix_stats on page 24 The tuning cycle to fix missing statistics is shown here Migration Technology Guide 19 Using QPTune to fix missing statistics Figure 2 1 Tuning cycle to fix missing statistics START_STATS Configure Adaptive Server Prepare to capture missing statistics lt start_stats_config gt Configuration file e Adaptive Server configuration options M Run the application queries stored procedure y XML output file eMissing statistics COLLECT_STATS e Specify wait time before collection e Missed statistics collected in client XML files FIX_STATS e Set threshold for count of missing statistics e Update statistics as specified in XML file e If noexec is
2. N used along with fix_stats and undo_fix_stats N generates a SQL script with update statistics or delete statistics statements The update or delete statements are not executed through QPTune The statements are written into a SQL script that is specified by the o option n login specifies the user s login whose query executions are collected and analyzed m missingCount specifies the threshold value for missing statistics The default value is 5 v specifies verbose mode 9 when used along with the fix action applies the default goal The default goal is the best optgoal setting that most queries used as the best plan using QPTune s fix action This option only generates plans for queries that do not currently use the server s default optimization goal If specific values are not indicated for the parameters the following defaults are used e A collect e M allrows_dss e T 0 e o metrics xml Adaptive Server Enterprise CHAPTER2 QPTune e c config xml e e elap_avg e d 5 5 If only percentage is specified absolute value defaults to 0 e l limit e m5 Permissions Only users with sa_role and sso_role can run actions other than compare on QPTune Migration Technology Guide 53 QPTune reference information 54 Adaptive Server Enterprise CHAPTER 3 Running the Query Processor in Compatibility Mode Topic Page Enabling compatibility mode 55 Feature
3. You can view or edit server configuration commands issued during the different stages of running QPTune Changes to the commands are written to the configuration file right before execution on the Comparison page Mode Selection You may select different modes to run QPTune All three pre programmed modes are selected by default e Decision Support System DSS e Online Transaction Processing OLTP Adaptive Server Enterprise CHAPTER2 QPTune Migration Technology Guide e Mixed Workload MIX To define a customized mode click the Add button To change the order of the modes use the Up and Down buttons Two or more modes must be selected for the tuning tasks in order to ensure at least two collected results for later comparison A customized mode is a collection of tuning parameters grouped under an optimization goal for a set of queries The OK button is enabled only if a name at least one rule and the result file are specified To add or edit a rule use the pop up text input box To delete a rule use the Remove button Collect Application You can specify an executable or a script file to include before the collection phase begins Collection You can specify collection settings on this page By default QPTune only collects optimization goal settings with no collection delay and evaluates the average elapsed time for collection Compare Comparison Specify comparison threshold setting percentag
4. Adaptive Server Enterprise About This Book SQL is a free form language There are no rules about the number of words you can put on a line or where you must break a line However for readability all examples and most syntax statements in this manual are formatted so that each clause of a statement begins on a new line Clauses that have more than one part extend to additional lines which are indented Complex commands are formatted using modified Backus Naur Form BNF notation Table 1 shows the conventions for syntax statements that appear in this manual Table 1 Font and syntax conventions for this manual Element Example Command names procedure names utility names and other keywords display in sans serif font select sp_configure Database names and datatypes are in sans serif font master database Book names file names variables and path names are in italics System Administration Guide sql ini file column_name SYBASE ASE directory Variables or words that stand for values that you fill in when they are part of a query or statement are in italics in Courier font select column_name from table name where search_conditions Type parentheses as part of the command compute row_aggregate column_name Double colon equals sign indicates that the syntax is written in BNF notation Do not type this symbol Indicates is defined as Curly braces mean that you m
5. SYBASEY SYBASE_ASE N ib To run the QPTune GUI these files must be present in your installation UNIX SYBASE SYBASE_ASE qptune config xml SYBASE SYBASE_ASE ib qptune jar SYBASE SYBASE_ASE qptune lib xercesImp jar SYBASE SYBASE_ASE qptune lib xml apis jar SYBASE Connect 6_O classes jconn3 jar SYBASE_JRE6 bin java e Windows SYBASEY SY BASE_ASE qptune config xml SYBASEY SYBASE_ASE ib qptune jar SYBASEYN SY BASE_ASE qptune lib xercesImp jar SYBASEYN SY BASE_ASE qptune lib xml apis jar SYBASE j Connect 6_O classes jconn3 jar SYBASE_JRE6 bin java Set the following environment variables e SYBASE_JRE6 to the Java runtime installation e SYBASE to the latest Sybase installation on your machine Migration Technology Guide 45 QPTune GUI e SYBASE_ASE to the Adaptive Server component directory of the installation on your machine Starting the QPTune GUI The QPTune GUI uses the ASE plug in in Sybase Central You must have installed Sybase Central in order to access the QPTune GUI For more information on the ASE plug in in Sybase Central see the System Administration Guide Volume 1 Start Sybase Central and configure your servers using the ASE plug in For any server that you can access with the plug in the QPTune GUI allows you to e Fix missing statistics in Adaptive Server After a server upgrade and before tuning the server use QPTune to update missing statistics on t
6. rule gt lt mode gt gt 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 1 0 v isql Usa P lt sp telco 2 sql gt 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 ole Group 1 improved by no more than 25 4 queries Query SELECT customer_last_name customer first name Adaptive Server Enterprise CHAPTER2 QPTune 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 Upgrade issues QPTune helps you get the best performance when upgrading to Adaptiv
7. Adaptive Server Enterprise CHAPTER 1 Migration Strategy The choice of optimization goal can have a significant impact on query performance If you know that a certain application has different workload characteristics than the rest of your system you may want to set an appropriate session level optimization goal for that application Either use the QPTune utility or manually experiment with different optimization goals and select one that provides the best overall performance for your particular set of applications and queries See Chapter 2 QPTune for more details You can define the optimization goal at the server session or individual query level e Server wide default sp_configure optimization goal 0 allrows_dss e Session level setting overrides server wide setting set plan optgoal allrows_dss e Query level setting overrides server wide and session level settings select from T1 T2 where Tl a T2 b plan use optgoal allrows dss Note You can also use a login trigger to set the session level optimization goal Optimization criteria An optimization goal is a collection of on off settings for a series of properties known as optimization criteria Optimization criteria allow or disallow the optimizer to consider a particular algorithm for access methods joins grouping sorting and so on For example to enable hash joins use the optimization criterion set hash join on
8. Clean up sysqueryplans amp sysquerymetrics tables gt lt start_config gt sp configure system table 1 lt start_config gt lt Start_config gt sp_metrics flush lt start_config gt lt start_config gt delete sysqueryplans where gid 1 or gid 2 lt start_config gt lt Optional settings that users can change or remove gt lt lt start_config gt sp configure enable literal autoparam 1 lt start_config gt gt lt lt start_config gt sp configure metrics elap max 0 lt start_config gt gt lt Hint sp_add_resource limit can be added to limit resource usage gt lt Specify a query plan group name to save all existing plans from ap_ stdin gt lt Existing plans from ap_stdout will be saved to the corresponding group name added with _out gt lt save_ plans pre start gt pre start _qpgroup lt save_ plans pre start gt lt start gt The lt end gt section corresponds to the lt start gt section and includes the configurations setting to be applied after metrics are collected For example lt end gt lt end_config gt sp_ configure enable metrics capture 0 lt end_config gt lt end_config gt sp_ configure abstract plan dump 0 lt end_config gt lt end_config gt sp configure system table 0 lt end_config gt lt end_config gt sp configure capture missing statistics 0 lt end_config gt lt lt end_config gt sp configure enable literal autoparam 0 lt end_conf
9. 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 lt qtext gt lt hashkey gt 323626785 lt hashkey gt lt id gt 1568005586 lt id gt lt elap_avg gt 27408 lt elap avg gt lt bestmode gt allrows_mix lt bestmode gt lt query gt lt query id 2 gt lt qtext gt 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 lt qtext gt lt hashkey gt 727793461 lt hashkey gt lt id gt 1552005529 lt id gt lt elap_avg gt 3355 lt elap_avg gt lt bestmode gt allrows_mix lt bestmode gt lt query gt lt query id 10 gt lt qtext gt SELECT month key service key count FROM telco facts WHERE month key 1 GROUP BY month_key service key lt qtext gt Adaptive Server Enterprise CHAPTER2 QPTune lt hashkey gt 1561133104 lt hashkey gt lt id gt 1680005985 lt id gt lt elap_avg gt 58 lt elap_ avg gt lt bestmode gt allrows_mix lt bestmode gt lt query gt lt server gt 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 config
10. Server 12 5 and database to Adaptive Server 15 0 3 ESD 1 or later which supports migration using QPTune Use allrows_oltp as the optimization goal and enable compatibility mode for the upgrade When you are ready to use Adaptive Server 15 0 features 1 2 Migration Technology Guide Use QPTune to turn on the statistics advisor Run the application queries QPTune advises you on what statistics are critical and creates them Typically most queries are tuned at this point Run QPTune using Adaptive Server 15 0 optimization goals Run the queries with different optimization goals and select the best performing optimization goal Troubleshooting Run the application queries again Check if there are any queries left to be tuned Run QPTune for queries that need further tuning N A Manually tune the remaining queries using abstract query plans Note You can incrementally migrate stored procedures using the same methodology Upgrading but not using new features If you are upgrading from Adaptive Server 12 5 but are not going to use Adaptive Server 15 0 features use allrows_oltp as an optimization goal and enable compatibility mode For more information on compatibility mode see Chapter 3 Running the Query Processor in Compatibility Mode Troubleshooting This section discusses query processing performance and strategies for addressing optimization problems Query processing tips 10 A
11. UPPERCASE TEXT as initials and MixedCase Text as words You might find it helpful to configure your tool to announce syntax conventions Consult the documentation for your tool For information about how Sybase supports accessibility see Sybase Accessibility at http Awww sybase com accessibility The Sybase Accessibility site includes links to information on Section 508 and W3C standards Each Sybase installation that has purchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support If you cannot resolve a problem using the manuals or online help please have the designated person contact Sybase Technical Support or the Sybase subsidiary in your area Migration Technology Guide xiii xiv Adaptive Server Enterprise CHAPTER 1 Migration Strategy Topic Page Preupgrade considerations 1 Migrating to Adaptive Server 15 0 features a Troubleshooting 10 Sybase Adaptive Server Enterprise includes a sophisticated query optimizer that analyzes statistics from queried tables using advanced algorithms to provide better performance Adaptive Server 15 0 3 ESD 1 and later include tools that support more effective use of Adaptive Server 15 0 s advanced query optimizer This book discusses optimization goals and recommendations for upgrading from Adaptive Server 12 5 to Adaptive Server 15 analyzing performance differences between the two versions and tuning Adaptive Serv
12. an Adaptive Server utility that is written in Java XML It enables users to identify the best query plan optimization goals or other configuration settings and apply them at the server or query level This results in optimal performance of subsequent query executions Once you have identified the best settings for application queries you can export and apply them to production servers QPTune allows users to e Fix missing statistics in an application e Tune an application to find the best optimizer settings for any number of queries e Selectively apply customized or standard settings to specific queries using user defined rules Use QPTune to analyze and compare any number of configuration settings or Adaptive Server installations to generate a performance impact analysis report or to perform plan fixes without degrading Adaptive Server s performance Adaptive Server gathers metrics with simple select statements and stores it in stored procedure or statement caches Adaptive Server fixes query plans using DDL statements that have little impact on the overall performance of the system In addition QPTune allows different threshold levels for monitoring thereby reducing the metrics that need to be collected 17 Setting up your system Setting up your system Before starting QPTune set these environment variables e SYBASE _JRE6 and JAVA_HOME to the Java runtime installation e SYBASE to the latest Sybase installation on you
13. and collect_stats steps collect_stats retrieves missing statistics information from the sysstatistics table for statistics that exceed a specified threshold for count of missing statistics QPTune consolidates the missing statistics and determines a minimum set of statistics that must be updated The m option indicates the threshold for count of missing statistics When the statistics for a query have been missed as many times as the threshold value or more they are collected and exported to an XML file The default threshold count is 5 The o option indicates the output XML file that holds missing statistics Use the output XML from collect_stats as input to the fix_stats and undo_fix_stats actions For example QPTune A collect_stats m 1 o missingstats xml v Migration Technology Guide 21 Using QPTune to fix missing statistics 22 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 1 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 lt xml version 1 0 encoding UTF 8 gt lt 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 gt lt mi
14. beginning users of the database management system and also contains detailed descriptions of the pubs2 and pubs3 sample databases Troubleshooting Series e Troubleshooting Error Messages Advanced Resolutions contains troubleshooting procedures for problems you may encounter The problems discussed here are the ones the Sybase Technical Support staff hear about most often e Troubleshooting and Error Messages Guide contains detailed instructions on how to resolve the most frequently occurring Adaptive Server error messages Encrypted Columns Users Guide describes how to configure and use encrypted columns with Adaptive Server In Memory Database Users Guide describes how to configure and use in memory databases Using Adaptive Server Distributed Transaction Management Features explains how to configure use and troubleshoot Adaptive Server DTM features in distributed transaction processing environments Using Backup Server with IBM Tivoli Storage Manager describes how to set up and use the IBM Tivoli Storage Manager to create Adaptive Server backups Using Sybase Failover in a High Availability System provides instructions for using Sybase Failover to configure an Adaptive Server as a companion server in a high availability system Unified Agent and Agent Management Console describes the Unified Agent which provides runtime services to manage monitor and control distributed Sybase resource
15. gt 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 lt sp_telco sql gt 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 Examples 38 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 1 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 lt xml version 1 0 encoding UTF 8 gt lt 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 gt lt query id 1 gt lt qtext gt SELECT service key year fiscal_period count FROM telco_facts T month M status S WHERE T month_key M month_key AND
16. in restricted compatibility mode e 3 a query executed with the 15 0 query processor Migration Technology Guide 59 Diagnostic tool Diagnostic tool set showplan output displays the query plan in a format similar to Adaptive Server 12 5 4 provided that the query is processed using full compatibility mode 60 Adaptive Server Enterprise Index Symbols BNF notation in SQL statements xi comma in SQL statements xi curly braces in SQL statements xi parentheses in SQL statements xi square brackets in SQL statements xi qpmode global variable 59 Numerics 101 errors 13 A actions collect 28 49 collect_stats 21 compare 29 49 custom start 27 fix 30 fix_stats 23 simple start 27 start 27 start_stats to fix missing statistics 21 undo_fix_stats 24 Adaptive Server tuning using the QPTune GUI 48 B Backus Naur Form BNF notation xi BNF notation in SQL statements xi Migration Technology Guide brackets See square brackets C case sensitivity inSQL xii collect action of QPTune 28 comma in SQL statements xi compare action of QPTune 29 compatibility mode definition 55 enabling at session or server level 55 feature support 56 full 56 restricted 56 trace flag 477 58 unsupported features 57 using qpmode 59 using set showplan 60 using sp_compatmode 58 configurable shared memory dump 13 configuration file 32 custom mode 27 end section 32 fix
17. 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 Adaptive Server Enterprise CHAPTER2 QPTune 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 1 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 Fi
18. on QPTune s cycle for application or query tuning see Using QPTune to tune queries or applications on page 24 To create a new tuning task for a server e Connect to the server on which you wish to tune your query Migration Technology Guide 47 QPTune GUI 48 e Click on your server name You see the Tuning tasks tab Note If you do not see the Tuning Task tab please check that your environment variables are set correctly and that your installation contains all the required files and directories e Click on the Tuning Tasks tab and then right click in the window to bring up the New gt TuningTask menu item e Select the New gt TuningTask menu item The QPTune Wizard opens Alternately you can bring up the Wizard using the Tuning Task creation button that is provided on the toolbar Note You must have sa_role and sso_role to use the menu item and the creation toolbar button The QPTune wizard includes these screens corresponding to the different stages in tuning the Adaptive Server e Setup Name and Configuration Specify the task name and the configuration file associated with the task When these are both specified the Next and Finish buttons ar enabled If the configuration file already exists the wizard indicates this by displaying a note under the file name You may select the Verbose Mode option to generate more detailed output Server Configuration
19. on column and density information stored in sysstatistics Adaptive Server 15 0 is more susceptible to incorrect statistical data than earlier Adaptive Server versions because multiple algorithms are used for sorting grouping unions joins and other operations In addition Adaptive Server 15 0 uses statistics in more ways than in Adaptive Server 12 x For example Adaptive Server 15 0 uses statistics to determine the join order in multitable queries Sybase recommends that you maintain up to date histograms for all columns referenced in where clauses both when the where clauses are used as join predicates and as search arguments Use the statistics advisor in QPTune to identify critical and missing statistics Recommended testing before upgrade Before upgrading your production systems to Adaptive Server 15 0 gather details about the performance characteristics of your applications in the production environment of the current pre 15 version of Adaptive Server Gathering such data provides a statistical basis for performance analysis To compare Adaptive Server 12 x and 15 0 performance run e Tests for as many application functions as possible especially the most critical ones For each function measure the response time or throughput If possible perform these measurements for each query executed by the application e Performance measurements in your current Adaptive Server 12 x production system Migration Technology Guid
20. procedure gt QPTune A collect M _basic_ T 0 o basic xml S my_host 5000 my_ database 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 Fix query plans with the best out of the comparison QPTune A fix i best xml S my_host 5000 my_ database 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 The QPTune command line utility has been localized so its messages can display in these 9 languages other than English Chinese French German Japanese Korean Polish Portuguese Spanish and Thai The language properties files are packaged in the gptune jar file QPTune sets the display according to the language set on the system s default locale The QPTune GUI is a set of Java libraries that are used by the Adaptive Server plug in Adaptive Server Enterprise CHAPTER2 QPTune Environment and system requirements To access QPTune functionality you must be using Adaptive Server version 15 0 3 ESD 1 or later The QPTune executable and libraries are installed in Unix SSYBASE SYBASE_ASE SYBASE SYBASE_ASENib Windows SY BASEX SY BASE_ASE
21. sp_monitorconfig all and sp_helpsort Migration Technology Guide 15 Troubleshooting System wide performance issues If the performance of Adaptive Server at the server level is not acceptable and you are running 15 0 2 ESD 3 or later you may shut down the Adaptive Server and restart it with trace flag 757 set in the RUN_server file This is also effective when you experience unusually high levels of CPU usage without a clear cause while running a multiengine Adaptive Server If the procedure cache is filled with idle cached plans and the CPU usage is not high run the following dbcc commands instead However using these commands is likely to have a lesser effect than restarting the server dbce traceon 757 go dbcc proc_ cache free_unused go Note Do not use trace flag 757 in Adaptive Server versions earlier than 15 0 2 ESD 3 Uploading diagnostics to Technical Support After you have created diagnostic files open a case with Technical Support To upload diagnostics to the Sybase FTP site contact Technical Support for current instructions 16 Adaptive Server Enterprise CHAPTER 2 Migration Technology Guide QPTune Topic Page Setting up your system 18 Using QPTune to fix missing statistics 19 Using QPTune to tune queries or applications 24 Configuration file 32 Examples 34 Upgrade issues 43 Localization 44 QPTune GUI 44 QPTune reference information 50 QPTune is
22. specified send update statistics statements to a script file Y UNDO_FIX_STATS Optional e Delete statistics specified in XML that exceed threshold 4 7 e If noexec is specified send delete statistics statements to a script file 20 Adaptive Server Enterprise CHAPTER2 QPTune Starting QPTune to fix missing statistics Start the utility with the start_stats action For example 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 1 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 You may also use the c option to specify a configuration file This extracts server level configuration settings from the lt start_stats gt section of your configuration file See Configuration file on page 32 Collecting statistics After preparing the system by running QPTune with the start_stats action you may begin collecting the missing statistics with the collect_stats action You can have QPTune either perform this action immediately or after waiting for some period of time This feature enables you to automate the start_stats
23. stored procedure you are tuning Adaptive Server Enterprise CHAPTER2 QPTune Collect the metrics into specified XML files See Collecting metrics on page 28 Compare the sets of metrics you have collected for different optimization goals This step uses the XML file from the above step as input and generates a performance comparison report See Comparing metrics on page 29 Apply the best results from the comparison to each of the specified queries on the target server See Applying the best results on page 30 QPTune s tuning cycle for applications or queries is shown here Migration Technology Guide 25 Using QPTune to tune queries or applications Figure 2 2 Q PTune cycle for application or query tuning EN n Ma A n y a Configuration File XML file Optional e T e Adaptive Server e Specifies queries for con guratlon w configuration options which rules are applied Apply rules to specified Rules Optional queries Run the application queries or stored procedure COLLECT COLLECT_FULL Output XML file Specify time interval before collection starts Q d metr e Metrics information captured in client XML file Ree Se eae J COMPARE Compare two or more sets of collected results Output XML file e Generate comparison report gt Result of comparison Apply the best results from
24. 6 sp_shmdumpconfig 13 square brackets in SQL statements xi statistics automatic updates 5 fix missing 46 fix missing with QPTune GUI 47 symbols in SQL statements xi syntax conventions Transact SQL xi T tasks creating with QPTune GUI 47 Technical Support contact 13 troubleshooting information 15 testing recommended steps before upgrade 5 tips 6 threshold count for missing statistics 21 trace flags 15307 11 15308 11 477 58 757 16 troubleshooting 10 701 errors 13 contacting Technical Support 13 dbcc traceon 16 63 Index information for Technical Support 15 obsolete optimization commands 12 performance problems with limited queries 14 query processing tips 10 sp_shmdumpconfig stored procedure 13 statement cache usage 12 system wide performance issues 16 tempdb space 11 tuning flowchart 25 tuning task creating 47 tuning task panel of QPTune GUI 47 U upgrade 43 64 migrating to Adaptive Server 15 0 43 recommended testing priorto 5 using new features 9 using new features later 9 Adaptive Server Enterprise
25. E EE T 44 OP Tune GUI 42 sented cen ad ented bika Eba dab K ba d r ka erk 44 Migration Technology Guide iii Contents Environment and system requirementes 45 Starting the QPTune GUI eee eeee cess eeeeeeenaeeeeeeeeeee 46 Fixing Missing stat St CS E E kk kek 46 TUG ES IS SR E EN A a A AETA 47 QPTune reference information ueee 50 CHAPTER 3 Running the Query Processor in Compatibility Mode 55 Enabling compatibility mode Eu keke 55 Feature support in compatibility mode 56 Additional trace flag for diagnostics 00 0 eee eeeeeeeeeeeneeeeneeeeneees 58 New stored procedure sp_compatmode E 58 Changes to qpmode global variable 59 Diagnostic toolist manii npn inin parinig dk kanal diana R dann Ana ikak E Hee dak 60 LE O Xi ay ene rE a Yak Ku kU KAN KEK VS Kv a Sa be UK A N EK k el SEK AV SE KE KA K SA KAS NR Vek kK EWA YEKEKE KE YEK 61 iv Adaptive Server Enterprise About This Book Audience How to use this book Related documents Migration Technology Guide This book is intended for System Administrators who are migrating to a different version of Adaptive Server Enterprise Chapter 1 Migration Strategy outlines strategies for users who want to upgrade and use new
26. Or to disable the store_index algorithm reformatting use the optimization criterion set store_index off Migration Technology Guide 3 Preupgrade considerations The optimizer may decide to ignore a given criteria or goal for semantic reasons For example if a user disables all join operators the new optimizer enables nested loop automatically Note Sybase recommends that you use optimization goals instead of explicit settings for optimization criteria unless advised to do otherwise by Sybase Technical Support Parallel query processing in Adaptive Server 15 0 Since version 11 5 Adaptive Server has supported parallelism within queries whereby a single query is processed by multiple worker processes You can use parallelism to improve response times for DSS type queries where a large number of rows are accessed but only a small result set is returned Since the query processing features in Adaptive Server 15 0 offer potential performance benefits for DSS type queries Sybase recommends that when you upgrade to Adaptive Server 15 0 you do not initially use parallel processing Serial processing is more resource efficient than parallel processing although parallel processing allows you to deliver better overall performance with the same hardware Also Adaptive Server 15 0 in serial mode runs queries faster than earlier versions of Adaptive Server with parallelism However parallelism may deliver bette
27. SYBASE Migration Technology Guide Adaptive Server Enterprise 15 5 DOCUMENT ID DC00967 01 1550 01 LAST REVISED October 2009 Copyright 2009 by Sybase Inc All rights reserved This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes Information in this document is subject to change without notice The software described herein is furnished under a license agreement and it may be used or copied only in accordance with the terms of that agreement To order additional documents U S and Canadian customers should call Customer Fulfillment at 800 685 8225 fax 617 229 9845 Customers in other countries with a U S license agreement may contact Customer Fulfillment via the above fax number All other international customers should contact their Sybase subsidiary or local distributor Upgrades are provided only at regularly scheduled software release dates No part of this publication may be reproduced transmitted or translated in any form or by any means electronic mechanical manual optical or otherwise without the prior written permission of Sybase Inc Sybase trademarks can be viewed at the Sybase trademarks page at http www sybase com detail id 1011207 Sybase and the marks listed are trademarks of Sybase Inc indicates registration in the United States of America Java and all Java based marks are trademarks or registered trademarks of
28. Sun Microsystems Inc in the U S and other countries Unicode and the Unicode Logo are registered trademarks of Unicode Inc IBM and Tivoli are registered trademarks of International Business Machines Corporation in the United States other countries or both All other company and product names mentioned may be trademarks of the respective companies with which they are associated Use duplication or disclosure by the government is subject to the restrictions set forth in subparagraph c 1 ii of DFARS 52 227 7013 for the DOD and as set forth in FAR 52 227 19 a d for civilian agencies Sybase Inc One Sybase Drive Dublin CA 94568 Contents ADOUt THIS BOOK E cak tsk eat kk Oa K bak ke v e PEKE H bax de lt k Re K Ya E ax Se eva a KEKE Ke ba k v CHAPTER 1 Migration StrateGy ssscccesccssseceeesseesesseeenseeeeseeesenseeeeseeeseneeneeees 1 Preupgrade considerations E kk keke 1 Understanding optimization goals LE 2 Resource recommendations for Adaptive Server 15 0 4 Incorporating statistics in Adaptive Server 15 0 5 Recommended testing before upgrade 5 Migrating to Adaptive Server 15 0 features U UUEUUUUEUjj j U 7 Upgrading and using new features immediately 9 Upgrading and using new features later 9 Upgrading b
29. _stats section 33 mode section 33 start section 32 start_stats section 33 conventions See also syntax Transact SQL syntax xi curly braces in SQL statements xi custom mode 27 custom start action of QPTune 27 61 Index D dbcc traceon 16 E errors 701 13 examples fixing missing statistics 34 36 optimizing an application 37 41 using custom modes 42 43 F fix action best settings 30 fix action using QPTune GUI 50 Fix missing statistics 46 full compatibility mode feature support 56 features 56 G Global variable qpmode 59 L literal autoparameterization 12 localization 44 migration strategy flow chart 7 not using new features 10 preupgrade steps 1 using new features 9 using new features later 9 missing statistics collect_stats action 21 62 fix_stats action 23 flow chart 19 noexec option 23 procedure to fix missing statistics 19 start_stats action 21 threshold count 21 undo_fix_stats action 24 missing statistics fixing with QPTune GUI 46 modes custom 27 pre programmed 27 O obsolete optimization commands 12 optimization criteria 3 optimization goals 2 allrows_dss 2 allrows_mix 2 allrows_oltp 2 defining 3 P parentheses in SQL statements xi performance comparison of different versions 5 problems with limited queries 14 pre programmed modes 27 Q QPTune applying best settings 30 collect action 28 collect_stats action 21 compare action 29 config
30. ation Technology Guide 39 Examples 40 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
31. b 19 22 16 04 PST 2008 lt xml version 1 0 encoding UTF 8 gt lt server url jdbc sybase Tds SHANGHI 5000 type ASE mode custom_1 datetime Tue Feb 19 22 16 04 PST 2008 gt lt query id 1 gt lt qtext gt select count T title id from authors A titleauthor T where A au_id T au_id lt qtext gt lt elap_avg gt 300 lt elap_avg gt lt bestmode gt custom 1 lt bestmode gt lt query gt lt server gt Note You can use the output XML file from the collect operation as input to compare fix or start operations 28 Adaptive Server Enterprise CHAPTER2 QPTune Comparing metrics Once metrics are collected you can compare different XML files to get the best query optimization goal or criteria for each of the queries For example QPTune A compare f al xml a2 xml a3 xml d 51 10 o best xml S my_host 5000 my database The f option specifies a list of two or more collected metrics sample files separated by commas Use quotes to encapsulate the file name if it contains any spaces The d option indicates a threshold percentage and absolute value A performance improvement beyond the threshold percentage and absolute value is considered outstanding during the fix operation The optimization goal criteria for those outstanding queries is applied to the server as a plan fix The default for the threshold percentage and absolute value pair is 5 5 If only percentage is specified the absolu
32. ct family and product under Search by Base Product or select the platform and product under Search by Platform Select Search to display the availability and certification report for the selection Creating a personalized view of the Sybase Web site including support pages Set up a MySybase profile MySybase is a free service that allows you to create a personalized view of Sybase Web pages 1 Point your Web browser to Technical Documents at http www sybase com support techdocs 2 Click MySybase and create a MySybase profile Finding the latest information on EBFs and software maintenance 1 Point your Web browser to the Sybase Support Page at http www sybase com support Select EBFs Maintenance If prompted enter your MySybase user name and password Select a product Specify a time frame and click Go A list of EBF Maintenance releases is displayed Padlock icons indicate that you do not have download authorization for certain EBF Maintenance releases because you are not registered as a Technical Support Contact If you have not registered but have valid information provided by your Sybase representative or through your support contract click Edit Roles to add the Technical Support Contact role to your MySybase profile Click the Info icon to display the EBF Maintenance report or click the product description to download the software The following sections describe conventions used in this manual
33. d issues to it e Undo Missing Statistics Fix When you select this option QPTune brings up the Undo Missing Statistics Fix Wizard Using statistics statements from a specified XML file QPTune can undo previous fixes to the server Specify the name of the XML file and the target database and click Next for the Options page On the Options page specify a threshold for count of missing statistics The default threshold count is 5 See Collecting statistics on page 21 for more information on threshold for count of missing statistics When you click Finish QPTune goes through the steps to undo the previous fixes for missing statistics QPTune then displays a summary page of commands that the wizard issues to it To use the Fix Missing Statistics or Undo Missing Statistics Fix wizards you must have sa_role and sso_role For more information on QPTune s cycle for fixing missing statistics see Using QPTune to fix missing statistics on page 19 Note You must run the application at least once before QPTune can collect or fix missing statistics QPTune includes a panel called Tuning Tasks that displays existing tuning tasks on every qualifying server A Wizard guides you through QPTune s tuning cycle The definition of a tuning task is stored on the client machine where ASEP runs and may only be accessed on that machine QPTune includes several stages in the tuning cycle for applications or queries For more information
34. daptive Server 15 0 offers a greatly improved query processing environment However if query plans or query performance are not what you expect here are some ways to isolate the problem e When using different optimization goals make sure no cached plans are used changing the session level or server wide optimization goal does not recompile cached plans For stored procedures either execute them with recompile or run sp_recompile on one of the tables being accessed For batches make sure the statement cache is disabled by running set statement_cache off first Adaptive Server Enterprise CHAPTER 1 Migration Strategy Migration Technology Guide To ensure that a stored procedure is always optimized with a particular optimization goal regardless of server wide or session level settings use set plan optgoal allrows_xxx as the first statement in the stored procedure This works only on Adaptive Server 15 0 2 ESD 2 or later If your SQL code from Adaptive Server 12 x contains explicitly forced join orders with set forceplan reexamine the join orders before upgrading to Adaptive Server 15 0 Such constructs may prevent you from benefiting fully from the capabilities of Adaptive Server 15 0 With Adaptive Server 15 0 1 ESD 2 or later you can enable two trace flags e Trace flag15307 nullifies the effect of any set forceplan statements during query plan compilation e Trace flag 15308 nullifies any explicit forcing of indexes
35. des the date and time of the memory dump is automatically generated Once the memory dump has been captured reset the system using sp_shmdumpconfig drop error 701 go By default Adaptive Server sends the 701 error message to the client You may also have this message reported in the error log by running sp_altermessage 701 with_log true To stop all configurable shared memory dumps set dump on conditions to 0 Once the memory dump has been captured open a case with Technical Support and upload the memory dump to the FTP site Include the output from the SQL statements below which use the monitoring tables within Adaptive Server select from master monProcedureCacheMemoryUsage select from master monProcedureCacheModuleUsage go The monitoring tables are automatically set up during execution of the installmaster script in Adaptive Server 15 0 2 or later The installation process for earlier versions of Adaptive Server execute the installmontables script See Adaptive Server 15 0 documentation for more details on configuring the monitoring tables Performance problems with a limited number of queries 14 If a limited number of queries are not performing well due to suboptimal query plans or suboptimal resource consumption install the latest Adaptive Server 15 0 x version on your development server If the problem still exists submit a reproduction of the problem or diagnostics to Technical Support To gather d
36. e 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 Using 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 lt Run your query application or stored procedure gt 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 lt Run your query application or stored procedure gt 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 lt Run your query application or stored procedure gt QPTune A collect M allrows_ dss T 0 o dss xml S my_host 5000 my_ database Migration Technology Guide 43 Localization Localization QPTune GUI 44 Get metrics information with optimization similar to version 12 5 4 in Adaptive Server 15 0 QPTune A start M basic_ i oltp xml 1 10 S my_host 5000 my_ database lt Run your query application or stored
37. e 5 Preupgrade considerations e The same function and performance measurement tests in a fully configured test system running Adaptive Server 15 0 with a copy of the full Adaptive Server 12 x production database and a realistic workload Run the same queries as in Adaptive Server 12 x and with the same level of concurrent user activity Capturing the performance footprint of your current Adaptive Server 12 x production environment provides a good baseline for any comparisons with Adaptive Server 15 0 The measurements you capture should include the number of logical I O operations elapsed time compilation time CPU utilization showplan output and so on To enable a sensible comparison of performance in Adaptive Server 12 x and Adaptive Server 15 0 gather performance data at two levels from e Individual queries in isolation and with a full workload run by multiple users e Adaptive Server as a whole from a server wide resource usage perspective Several critical aspects affect performance numbers between Adaptive Server 12 x and 15 To avoid misleading performance numbers e Warm up the cache in the same manner for both Adaptive Server 12 x and Adaptive Server 15 0 testing e Use identical cache buffer pool configurations e Increase the procedure cache in Adaptive Server 15 0 to about 2 6 times the amount used in Adaptive Server 12 x e Use similar data device layout and placement especially fo
38. e and absolute values and the output filename on this page Click Finish to save the task definition and the configuration file If you click Execute QPTune executes all the specified modes collects the metrics and compares and saves the results into the output file You may use the Preview button to list the commands that are about to be issued Results This page displays the output of the tuning process Comparison results depict the performance improvement if the best plans are chosen for each of the queries The output XML file contains the best plans or optgoal settings for each of the queries 49 QPTune reference information You may also apply the fixes to the server Click Fix to apply the best plans or optgoal setting to the queries This generates entries in the sysqueryplans table for the queries that are being fixed Select Apply Default Optimization Goal if you want to apply the default optimization goal to the server during the Fix operation The default optimization goal is the optgoal setting that most queries selected as their best optgoal during the Compare operation If Apply Default Optimization Goal is selected subsequent Fix operations apply the best result to the rest of the queries that have not selected this default optgoal setting as their best optgoal If Apply Default Optimization Goal is not selected the Fix operation is applied to all the queries in the result file QPTune reference informat
39. ent Integration Services to connect remote Sybase and non Sybase databases vi The Configuration Guide for your platform provides instructions for performing specific configuration tasks Glossary defines technical terms used in the Adaptive Server documentation Historical Server Users Guide describes how to use Historical Server to obtain performance information from Adaptive Server Java in Adaptive Server Enterprise describes how to install and use Java classes as datatypes functions and stored procedures in the Adaptive Server database Job Scheduler Users Guide provides instructions on how to install and configure and create and schedule jobs on a local or remote Adaptive Server using the command line or a graphical user interface GUI Migration Technology Guide describes strategies and tools for migrating to a different version of Adaptive Server Monitor Client Library Programmers Guide describes how to write Monitor Client Library applications that access Adaptive Server performance data Monitor Server Users Guide describes how to use Monitor Server to obtain performance statistics from Adaptive Server Monitoring Tables Diagram illustrates monitor tables and their entity relationships in a poster format Full size available only in print version a compact version is available in PDF format Performance and Tuning Series is a series of books that explain how to tune Ada
40. er 15 0 installations This book also documents a tool called QPTune which enables users to identify and apply the best query plan optimization goals and other configuration settings Preupgrade considerations Migration Technology Guide This section covers optimization goals and criteria and the steps you must complete before you upgrade to Adaptive Server 15 0 It includes a summary of preupgrade tests that help you evaluate performance after upgrading your production server Preupgrade considerations Understanding optimization goals A central concept of Adaptive Server 15 0 s query processing engine is the optimization goal which provides an indication of the nature of the query being optimized The Adaptive Server query optimizer determines how best to optimize a query based on optimization goals For example a typical OLTP online transaction processing query and a typical DSS decision support system query result in very different query plans due to the different data access patterns used by these queries OLTP queries generally affect only one or a few rows and join only a few well indexed tables However DSS queries typically affect many rows return a few rows and may join many tables Because of their different access patterns OLTP queries often run most efficiently using a classic nested loop join whereas DSS queries are more likely to run faster with a hash join If you indicate that a quer
41. es precedence over the server level enable compatibility mode is a dynamic configuration parameter you need not restart Adaptive Server for it to take effect However you must remove any compiled plans for stored procedures or ad hoc queries from the statement cache Note sp_configure generates warnings to indicate that enabling compatibility mode does not affect cached query plans that are already in the procedure or statement cache sp_configure also generates warnings if it detects configuration options that conflict with compatibility mode such as e One of abstract plan dump abstract plan load or abstract plan replace is set e statement cache and literal autoparam are set e The histogram tuning factor is set to a value other than 1 Feature support in compatibility mode 56 Once you enable compatibility mode Adaptive Server uses the query processor for all insert delete update and select queries The query processor uses either full or partial compatibility mode e Full compatibility mode Adaptive Server 15 0 uses an optimization and execution strategy similar to the one used in version 12 5 x e Restricted compatibility mode Adaptive Server uses only an optimization strategy similar to the one used in version 12 5 x Generally Adaptive Server uses full compatibility mode wherever possible If it cannot use full compatibility mode it switches to restricted compatibility mode Table 3 1 lists
42. features immediately and for users who would like to upgrade now and use the new features later Chapter 2 QPTune gives a comprehensive summary of the QPTune tool Chapter 3 Running the Query Processor in Compatibility Mode discusses the compatibility mode feature for users who want to upgrade to a new version but retain performance characteristics of a previous version The Adaptive Server Enterprise documentation set consists of e The release bulletin for your platform contains last minute information that was too late to be included in the books A more recent version of the release bulletin may be available To check for critical product or document information that was added after the release of the product CD use the Sybase Product Manuals Web site e The installation guide for your platform describes installation upgrading and some configuration procedures for all Adaptive Server and related Sybase products e New Feature Summary describes the new features in Adaptive Server the system changes added to support those features and changes that may affect your existing applications e Active Messaging Users Guide describes how to use the Active Messaging feature to capture transactions data changes in an Adaptive Server Enterprise database and deliver them as events to external applications in real time e Component Integration Services Users Guide explains how to use Compon
43. for updating statistics without executing the actual updates by using the N option to indicate noexec and the o option to indicate the output script file The output file is created with all the generated update statistics statements but the statements are not executed Generated scripts have a SQL file format Using the N option gives you the option of running the SQL script at a later time to optimize your resources For example 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 1 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 Sees QPTune finished executing For example QPTune U sa P S my_host 5000 my_ database A fix_stats m 5 i missingstats xml N o missingstats sql Migration Technology Guide 23 Using QPTune to tune queries or application
44. he SQL of the persistent plan are said to match when a checksum type of hash on the two SQL statements matches If literal parameterization is enabled explicitly the two statements may differ only in the static values of search arguments such as where CustomerID 12345 In this case the value 12345 is replaced by a placeholder variable so the hash value is the same regardless of the search value If the application changes the SQL in any manner such as adding a new predicate there is no longer a match to a persistent plan and the optimizer creates a query plan according to the current configuration and available Statistics Migration Technology Guide 31 Configuration file Configuration file You can define custom modes in a configuration file The QPTune installation includes a standard configuration file that contain some custom modes The custom mode _basic_ is reserved for basic optimization The configuration file for QPTune must include lt start gt lt start_stats gt lt fix gt and lt end gt sections The lt mode gt section is optional The lt start gt section indicates the configuration settings for Adaptive Server before metrics are collected For example lt start gt lt Recommended server settings gt lt start_config gt sp configure enable metrics capture 1 lt start_config gt lt start_config gt sp configure abstract plan dump 1 lt start_config gt lt
45. he general guideline for version 15 0 and later is to use a procedure cache that is 2 6 times the size of your 12 5 x procedure cache In some cases especially while using the optimization goal allrows_dss your procedure cache may need to be even larger If increasing the procedure cache does not resolve the 701 error and you cannot isolate the problem set up a configurable shared memory dump that includes the procedure cache pages sp_ configure dump on conditions 1 go sp_shmdumpconfig add error 701 1 my dump directory null include_proc go sp_shmdumpconfig adds the error 701 condition to initiate a memory dump The fourth parameter 1 in the examples above indicates the number of memory dumps to capture Adaptive Server does not capture additional memory dumps on this condition until Adaptive Server is restarted or until you manually reset the counter The parameter my_dump_directory is the name of a directory to hold the memory dump The file system on which the directory resides should have enough free space to hold the memory dump file which can be large Verify the dump conditions currently defined by running sp_shmdumpconfig without any parameters This also shows an estimated size of the memory dump to be captured Migration Technology Guide 13 Troubleshooting The parameter include_proc enables procedure cache information to be included in the configurable shared memory dump A file name that inclu
46. he server e Tune tasks in Adaptive Server Use this feature to define a tuning task that QPTune can execute and analyze in a report You can further apply the fixes to the server Fixing missing statistics Use the QPTune GUI to fix or update the missing statistics after you have upgraded a server To access QPTune s Fix missing statistics feature right click a server name and select one of the two available menu options for fixing missing statistics e Fix Missing Statistics When you select this option QPTune brings up the Fix Missing Statistics Wizard QPTune collects the information about missing statistics into an XML file and then uses the file to fix the missing statistics Specify the name of the XML file and the target database and click Next for the Options page On the Options page specify a threshold for the count of missing statistics The default threshold count is 5 See Collecting statistics on page 21 for more information on threshold for count of missing statistics Additionally you may opt to only send the update statistics statements to a script file To do this type in the filename where you would like to save the statements and click Finish to save the tuning task without execution 46 Adaptive Server Enterprise CHAPTER2 QPTune Tuning Task If you click Execute QPTune goes through the steps to fix the missing statistics QPTune then displays a Summary page of commands that the wizar
47. iagnostics 1 Create a script file sql txt containing these commands select version go select optgoal go sp_cacheconfig go sp configure nondefault only if you re running Adaptive Server 15 0 2 or later Adaptive Server Enterprise CHAPTER 1 Migration Strategy go dbcc traceon 3604 set showplan on set statistics time io plancost on set option show long go lt your query text gt go Note set option show long may produce a lot of output for complex queries 2 Use isql to execute sql txt and capture the output in a file isql Usa P yourpassword S YOUR_SERVER_NAME i sql txt o sql out Use the w option of isql to format the output 3 Send this information to Technical Support e The sql txt and sql out files If available include the fast sql fast txt and slow sql slow txt query plans and corresponding output files sql fast out sql slow out e DDL for the base tables and indexes which you can generate using the ddigen utility e Simulate statistics output for the base tables using optdiag optdiag statistics simulate lt table name gt Usa P yourpassword S YOUR_SERVER_NAME o lt output file gt e A copy of the Adaptive Server configuration file For Adaptive Server15 0 2 include the output of sp_configure nondefault e Ifthe query uses views or stored procedures then include their SQL source code obtained using defncopy or ddlgen e The output of
48. ies the default goal The default goal is the best optgoal setting that most queries used as the best plan using QPTune s fix action This option only generates plans for queries that do not currently use the server s default optimization goal The example fix action above produces this output Query Plan s fixed on Wed Sep 17 17 44 09 PDT 2008 Fixed 2 queries using mode custom_1 with following optimizer settings use optgoal allrows_mix use merge join off use opttimeoutlimit 15 Fixed 4 query using mode allrows_ mix Apply sp configure optimization_goal 0 allrows_ mix as the default optgoal Details of statement s fixed Query select count T title_ id from authors A titleauthor T where A au_id T au_id Fixed using custom_1 INFO Fix Statement create plan select count T title id from authors A titleauthor T where A au_id T au_id use optgoal allrows mix use merge join off use opttimeoutlimit 15 Query select from titleauthors where au id gt 20 and title id lt 100 Fixed using custom_1 INFO Fix Statement create plan select from titleauthors where au_id gt 20 and title id lt 100 use optgoal allrows_ mix use merge join off use opttimeoutlimit 15 QPTune then creates an optimized query plan which is saved in the sysqueryplans system table in the current database When a query with matching SQL is encountered this optimized plan is used Incoming SQL and t
49. ig gt gt lt lt end_config gt sp_ configure metrics elap max 0 lt end_config gt gt lt end gt 32 Adaptive Server Enterprise CHAPTER2 QPTune The lt start_stats gt section includes statistics settings For example lt start_stats gt lt Recommended server settings gt lt start stats _config gt sp configure capture missing Sstatistics 1 lt start_stats_config gt lt Reset counter of missing statistics gt lt start_stats_config gt sp configure system table 1 lt start_stats_config gt lt start_stats_config gt delete sysstatistics where formatid 110 lt start_stats_config gt lt start_stats gt The lt fix_stats gt section includes lt The following set of configurations apply at A fix gt lt fix gt lt Recommended server settings gt lt fix_config gt sp configure abstract plan load 1 lt fix_config gt lt Clean up sysqueryplans amp sysquerymetrics tables gt lt fix_config gt sp configure system table 1 lt fix_config gt lt fix_config gt sp metrics flush lt fix_config gt lt fix_config gt delete sysqueryplans where gid 1 orgid 2 lt fix config gt lt Optional settings that users can change or remove gt lt fix_config gt sp configure enable metrics capture 1 lt fix_config gt lt lt fix_config gt sp_ configure enable literal autoparam 1 lt fix_config gt gt lt lt fix_config gt sp_ config
50. ion Description Syntax Parameters 50 QPTune is an Adaptive Server utility written in Java XML It enables users to 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 later query executions QPTune U lt username gt P lt passwords S lt hostname port database gt A lt action start collect _full compare fix start collect fix undo_fix _stats gt M lt mode gt T lt appTime gt i lt inputFile gt o lt outputFile gt f lt fileList gt c lt configFile gt I lt limit gt e lt evalField gt d lt diff diff_abs gt m lt missingCount gt n lt login gt J lt charset gt N noexec g applyOptgoal v verbose s sort h help Example QPTune U sa P S my_host 5000 my_ database A collect M allrows mix T 0 o metrics xml c config xml e elap_ avg d 5 5 1 5 i metrics xml fal xml a2 xml a3 xml v s U username specifies the database user name P password Adaptive Server Enterprise CHAPTER2 QPTune specifies the database password S server specifies the database server The database server is denoted by host port database Note You must specify the S option while using any QPTune actions A action specifies the action to be taken One of start collect collect full compare fix start_s
51. lobal variables expressions identifiers and wildcards and reserved words e Commands documents commands e Procedures describes system procedures catalog stored procedures system extended stored procedures and dbcc stored procedures e Tables discusses system tables monitor tables and dbcc tables e System Administration Guide e Volume I provides an introduction to the basics of system administration including a description of configuration parameters resource issues character sets sort orders and instructions for diagnosing system problems The second part of Volume 1 is an in depth discussion about security administration e Volume 2 includes instructions and guidelines for managing physical resources mirroring devices configuring memory and data caches managing multiprocessor servers and user databases mounting and unmounting databases creating and using segments using the reorg command and checking database consistency The second half of Volume 2 describes how to back up and restore system and user databases e System Tables Diagram illustrates system tables and their entity relationships in a poster format Full size available only in print version a compact version is available in PDF format Migration Technology Guide vii viii Transact SQL Users Guide documents Transact SQL the Sybase enhanced version of the relational database language This guide serves as a textbook for
52. lt id gt 1068527809 lt id gt lt stats gt Y y2 yl1 lt stats gt lt count gt 1 lt count gt lt missingStat gt lt missingStat id 4 gt lt id gt 1068527809 lt id gt lt stats gt Y yl lt stats gt lt count gt 1 lt count gt lt missingStat gt lt server gt 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 Se AN 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 1 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 U
53. lude hash or hashbyte functions e User defined functions SQL UDFs Migration Technology Guide 57 Additional trace flag for diagnostics e Explicit timestamp inserts available for Adaptive Server version 15 0 2 and later and in Replication Server e SQL based replication available for Adaptive Server version 15 0 3 and later group by result rows that are wider than the maximum size that fits on a data page e xmitable function Note Query plans in compatibility mode are not executed as parallel plans Additional trace flag for diagnostics Trace flag 477 alters compatibility mode For every query evaluated Adaptive Server prints this message to the error log which indicates if Adaptive Server used full compatibility mode to process the query Compatibility true false The message includes the reason if compatibility mode is not chosen and the query text if available New stored procedure sp_compatmode 58 Use sp_compatmode on Adaptive Server 15 0 3 ESD 1 and later to verify if full compatibility mode can be used effectively sp_compatmode generates warnings if it detects configuration options that conflict with compatibility mode such as e One of abstract plan dump abstract plan load or abstract plan replace is set e statement cache and literal autoparam are set e The histogram tuning factor is set to a value other than 1 Example 1 Execute sp_compatmode with these server options e com
54. 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 The above example shows a comparison between two XML metrics files al xml has six queries and a2 xml has seven queries Comparisons can only be made between the queries that are common to both files There are three queries that ran faster in a2 xml The improvements are categorized into four groups e Group 1 between 0 and 25 e Group 2 between 25 and 50 e Group 3 between 50 and 75 e Group 4 between 75 and 100 There is one query between 25 and 50 and two queries between 75 and 100 The queries in Group 2 are marked as Outstanding No which means that based on the threshold of 51 this query will not be fixed While comparing more than two files QPTune updates the first file with the best from both files then compares the new file with the third file and so on Applying the best results After getting the results for all queries being analyzed use the fix action to apply the best settings to the queries in the database system For example QPTune S host port database A fix i best xml v g The i option specifies the queries and their best plans resulting from the comparison 30 Adaptive Server Enterprise CHAPTER2 QPTune The g option when used with the fix action appl
55. nt generated SQL queries in Adaptive Serer 15 0 1 or later This does not include stored procedures or execute immediate query forms and the queries may differ only in their search parameters Overall performance is improved by significantly reducing the time and resources spent on query optimization When the statement cache is enabled a query s plan is cached so you need not compile an identical query and thereby save time and resources The statement cache is enabled server wide with the configuration parameter statement cache size At the session level disable the statement cache with set statement_cache off Literal autoparameterization is enabled server wide with the configuration parameter enable literal autoparam and at the session level with set literal_autoparam on enable literal autoparam applies only when the statement cache is enabled With literal autoparameterization enabled caching is extended to almost identical queries that differ only in a constant value For example these two queries are not considered identical 123 456 select CustName from Customers where CustID select CustName from Customers where CustID However they are likely to generate the same query plan Enabling literal autoparameterization has the effect that the statement cache factors out the constant value in the where clause and caches a plan for all queries that look like this select CustName from Customers where CustID lt integer constan
56. on may be provided in PDF format which you can access through the PDF directory on the SyBooks CD To read or print the PDF files you need Adobe Acrobat Reader Refer to the SyBooks Installation Guide on the Getting Started CD or the README txt file on the SyBooks CD for instructions on installing and starting SyBooks The Sybase Product Manuals Web site is an online version of the SyBooks CD that you can access using a standard Web browser In addition to product manuals there are links to EBFs Maintenance Technical Documents Case Management Solved Cases newsgroups and the Sybase Developer Network To access the Sybase Product Manuals Web site go to Product Manuals at http www sybase com support manuals Sybase certifications Technical documentation at the Sybase Web site is updated frequently on the Web Finding the latest information on product certifications 1 Migration Technology Guide Point your Web browser to Technical Documents at http www sybase com support techdocs Click Certification Report In the Certification Report filter select a product platform and time frame and then click Go Sybase EBFs and software maintenance Conventions 4 Click a Certification Report title to display the report Finding the latest information on component certifications 1 Point your Web browser to Availability and Certification Reports at http certification sybase com Either select the produ
57. patibility mode is set Adaptive Server Enterprise CHAPTER 3 Running the Query Processor in Compatibility Mode e dump load replace is on e statement cache is on e literal autoparam is on e histogram tuning factor is set to 20 1 gt sp_compatmode Compatibility mode is enabled WARNING Compatibility mode will not be used when abstract plan dump load replace is on WARNING Compatibility mode may not be used when statement cache and literal autoparam are enabled WARNING The configuration option histogram tuning factor is configured with value 20 which is not the default value in ASE 12 5 This may lead to different accuracy of statistics and different query plans return status 0 Example 2 Execute sp_compatmode when compatibility mode is not set 1 gt sp_compatmode Compatibility mode is not enabled return status 0 Note Changing the configuration of the histogram tuning factor from the default in Adaptive Server 15 0 20 to the default in Adaptive Server 12 5 1 creates plans that are more consistent with Adaptive Server 12 5 Changes to qpmode global variable In compatibility mode qgpmode displays the query processing mode in which the previously executed query was processed There are four query processing modes e OQ a query that cannot be optimized for example create table set and so on e a query executed in full compatibility mode e 2 aquery executed
58. pdate Y y3 Migration Technology Guide 35 Examples INFO Fix Statement update statistics Y y3 Fixed statistics Update Y y2 y1 INFO Fix Statement update statistics Y y2 yl1 Fixed statistics Update Y y1 INFO Fix Statement update statistics Y y1 QPTune finished executing Note 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 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 1 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 Fix Statement delete statistics Y y4 y2 Fixed statistics Delete Y y3 INFO Fix Statement delete statistics Y y3 QPTune Utility Fixed statistics 1 Dele
59. prefetch parallelism or buffer replacement strategies You can set both these trace flags 15307 and 15308 during server start up or dynamically enable them using dbcc traceon The effects of both are server wide and neither affects any query plan properties defined by abstract query plans If your system consumes too much space in tempdb use the Monitoring and Diagnostic Access tables to see if any particular session consumes a lot of space in a worktable Enable the monitoring tables and run the following query select SPID DBName ObjectName PartitionSize from master monProcessObject where DBID tempdb_id SPID order by SPID Look for sessions that have a large value for PartitionSize Worktables have an ObjectName of temp worktable Find the corresponding SQL statement for the sessions by issuing queries to monProcessSQLText or monProcessStatement in the master database To stop sessions from filling up tempdb and thus affecting other sessions also requiring tempdb space create a resource limit of type tempdb_space You may also create multiple temporary databases and assign them to specific users To check the tempdb space used by a single session use select pssinfo spid 0 tempdb pages 11 Troubleshooting Obsolete optimization commands in Adaptive Server 15 0 12 Enable the statement cache and literal autoparameterization settings while running large numbers of identical or similar clie
60. ptive Server for maximum performance e Basics contains the basics for understanding and investigating performance questions in Adaptive Server e Improving Performance with Statistical Analysis describes how Adaptive Server stores and displays statistics and how to use the set statistics command to analyze server statistics e Locking and Concurrency Control describes how to use locking schemes to improve performance and how to select indexes to minimize concurrency e Monitoring Adaptive Server with sp_sysmon discusses how to use sp_sysmon to monitor performance Adaptive Server Enterprise About This Book e Monitoring Tables describes how to query Adaptive Server monitoring tables for statistical and diagnostic information e Physical Database Tuning describes how to manage physical data placement space allocated for data and the temporary databases e Query Processing and Abstract Plans explains how the optimizer processes queries and how to use abstract plans to change some of the optimizer plans e Quick Reference Guide provides a comprehensive listing of the names and syntax for commands functions system procedures extended system procedures datatypes and utilities in a pocket sized book regular size when viewed in PDF format e Reference Manual is a series of books that contains detailed Transact SQL information e Building Blocks discusses datatypes functions g
61. r log devices and for tempdb e Set up test systems where you can easily restore the original database after each test run especially when data is modified during testing Note You may need to increase the size of the data cache in Adaptive Server 15 0 6 Adaptive Server Enterprise CHAPTER 1 Migration Strategy Migrating to Adaptive Server 15 0 features You may want to use Adaptive Server 15 0 features immediately after upgrade or you may prefer to use new features later The following flowchart depicts the different strategies available for Adaptive Server migration Migration Technology Guide 7 Migrating to Adaptive Server 15 0 features Figure 1 1 Flowchart of Adaptive Server migration strategy a 12 5 database dump Load and upgrade Use ASE 15 0 features now Use allrows_oltp and compatibility mode yes amp 15 0 optimization Run missing statistics advisor through QPTune Vv Run application Vv Create missing statistics by using QPTune Run QPTune using ASE 15 0 optimization goals Run application __ Configure the best goal and tune the rest of the queries to run in various optimization goals Performance acceptable yes Done Run QPTune for badly performing queries Set up que
62. r machine e SYBASE_ASE to the Adaptive Server component directory of the installation on your machine The QPTune executable is named QPTune on UNIX and QPTune bat on Windows and is found in SYBASE SYBASE_ASE qptune on UNIX SYBASEY SYBASE_ASE qptune on Windows For complete syntax and reference information on QPTune see QPTune reference information on page 50 To verify your environment and installation and for information on basic syntax run QPTune with the h option QPTune h Sample output in a Windows environment QPTune lt Version 3 0 gt Windows Unix Built Fri Jan 21 14 00 15 PDT 2009 Syntax QPTune U lt username gt P lt password gt S lt hostname port database gt A lt action start collect _ full compare fix start collect fix undo_fix stats gt M lt mode gt T lt appTime gt i lt inputFile gt o lt outputFiles lt fileList gt c lt configFile gt 1 lt limit gt e lt evalField gt d lt diff diff_abs gt m lt missingCount gt n lt login gt J lt charsets N noexec g applyOptgoal v verbose s sort h help Example QPTune U sa P S WUXP 5000 scenario A collect M allrows mix T 0 o metrics xml c config xml e elap avg d 5 5 1 5 i metrics xml f al xml a2 xml a3 xml v s Note Only users with sa_role and sso_role can run QPTune actions except for compare which may be run by any user
63. r response times than serial processing for queries that use semantic table partitioning in Adaptive Server 15 0 or for DDL commands such as create index Resource recommendations for Adaptive Server 15 0 Adaptive Server 15 0 requires more procedure cache than version 12 5 This increased memory requirement applies to optimization as well as to query execution Sybase recommends that you increase your procedure cache 2 6 times the size of your procedure cache in Adaptive Server 12 5 You may also need to increase the space on tempdb for query processing on Adaptive Server 15 0 4 Adaptive Server Enterprise CHAPTER 1 Migration Strategy Incorporating statistics in Adaptive Server 15 0 Adaptive Server uses a cost based query optimizer to choose the best plan for a particular query The optimizer estimates the cost of different plans based on statistics about the tables indexes partitions and columns referenced in a query Cost is computed in terms of I O and CPU time The optimizer then chooses the query plan method that has the lowest cost Inaccurate statistics lead to incorrect cost estimates and may result in a suboptimal choice of plans and reduced performance Some statistics such as the number of pages or rows in a table stored in systabstats are updated automatically during query processing Other statistics are updated only when update statistics runs or when indexes are created Examples of this are the histograms
64. restricted compatibility mode support for features in Adaptive Server versions earlier than 15 0 Adaptive Server Enterprise CHAPTER 3 Running the Query Processor in Compatibility Mode Table 3 1 Version 12 5 feature support in compatibility mode 12 5 4 features with limited support in compatibility mode Supported in full compatibility mode Supported in restricted compatibility mode Queries with text and image No Yes columns Referential integrity No Yes Inserts that require referential No No integrity Proxy tables No Yes Round robin partitions No No Encryption and cipher text No Yes Queries that include the rand2 No Yes function Abstract plans either explicit with No Yes plan clause or implicit plan dump or plan load Extended datatypes such as Java No Yes ADT and Java UDF XML functions No Yes Browse mode No Yes Parallel hints No No Parallel sort No No Compatibility mode does not support these Adaptive Server 15 0 features e Partitioned tables e group bys with more than 31 columns e Scrollable and insensitive cursors e Commands on computed columns e Queries that fire instead of triggers e Queries executed inside instead of triggers e Queries that issue parameterized literals in the statement cache unless the query includes an insert values command e Query processing diagnostics used by showplan_in_xml e Queries that inc
65. ries to run basic optimization Performance acceptable Done Manually tune or force plans Adaptive Server Enterprise CHAPTER 1 Migration Strategy Upgrading and using new features immediately To upgrade to Adaptive Server 15 0 and use the new features immediately Sybase recommends that you skip setting the compatibility mode and tune the application using QPTune 1 Nn O o N A Upgrade your Adaptive Server 12 5 and database to Adaptive Server 15 0 3 ESD 1 or later which supports migration using QPTune Use QPTune to turn on the statistics advisor Run the application queries QPTune advises you on what statistics are critical and creates them Typically most queries are tuned at this point Run QPTune with Adaptive Server 15 0 optimization goals Run the queries with different optimization goals and select the best performing optimization goal Run the application queries again Check if there are any queries left to be tuned Run QPTune for queries that need further tuning Manually tune the remaining queries Use traditional techniques of analyzing query plans and either rewriting them to obtain the desired performance or using techniques such as abstract query plans Upgrading and using new features later If you are going to upgrade to Adaptive Server 15 0 and incrementally begin using Adaptive Server 15 0 features upgrade your Adaptive
66. rom publishers e Examples of output from the computer appear as follows pub id pub name city state 0736 New Age Books Boston MA 0877 Binnet amp Hardley Washington DC 1389 Algodata Infosystems Berkeley CA 3 rows affected In this manual most of the examples are in lowercase However you can disregard case when typing Transact SQL keywords For example SELECT Select and select are the same Adaptive Server sensitivity to the case of database objects such as table names depends on the sort order installed on Adaptive Server You can change case sensitivity for single byte character sets by reconfiguring the Adaptive Server sort order For more information see the System Administration Guide Accessibility This document is available in an HTML version that is specialized for features accessibility You can navigate the HTML with an adaptive technology such as a screen reader or view it with a screen enlarger xii Adaptive Server Enterprise About This Book If you need help Adaptive Server HTML documentation has been tested for compliance with U S government Section 508 Accessibility requirements Documents that comply with Section 508 generally also meet non U S accessibility guidelines such as the World Wide Web Consortium W3C guidelines for Web sites Note You might need to configure your accessibility tool for optimal use Some screen readers pronounce text based on its case for example they pronounce ALL
67. s Using undo _fix_stats To revert fixed missing statistics use the undo_fix_stats action undo_fix_stats deletes the statistics that are specified in an XML file whose missing counts are equal to or exceed the number specified by the m option For example QPTune A undo 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 undo fix stats M allrows_dss T 0 i missingstats xml o metrics xml f null c config xml 1 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 Fixed statistics Delete Y y2 y1 INFO Fix Statement delete statistics Y y2 yl1 Fixed statistics Delete Y y1 INFO Fix Statement delete statistics Y y1 QPTune finished executing Using QPTune to tune queries or applications 24 The main tasks for using QPTune for application or query tuning are e Start QPTune using either e Simple start on page 27 if you are applying standard optimization goal settings e Custom start on page 27 if you are applying special custom rules to specified queries e Run the application queries or
68. s Utility Guide documents the Adaptive Server utility programs such as isql and bcp which are executed at the operating system level Web Services Users Guide explains how to configure use and troubleshoot Web services for Adaptive Server XA Interface Integration Guide for CICS Encina and TUXEDO provides instructions for using the Sybase DTM XA interface with X Open XA transaction managers Adaptive Server Enterprise About This Book XML Services in Adaptive Server Enterprise describes the Sybase native XML processor and the Sybase Java based XML support introduces XML in the database and documents the query and mapping functions that are available in XML services Other sources of Use the Sybase Getting Started CD the SyBooks CD and the Sybase information Product Manuals Web site to learn more about your product The Getting Started CD contains release bulletins and installation guides in PDF format and may also contain other documents or updated information not included on the SyBooks CD It is included with your software To read or print documents on the Getting Started CD you need Adobe Acrobat Reader which you can download at no charge from the Adobe Web site using a link provided on the CD The SyBooks CD contains product manuals and is included with your software The Eclipse based SyBooks browser allows you to access the manuals in an easy to use HTML based format Some documentati
69. ssingStat id 1 gt lt id gt 1068527809 lt id gt lt stats gt Y y4 y2 lt stats gt lt count gt 2 lt count gt lt missingStat gt lt missingStat id 2 gt lt id gt 1068527809 lt id gt lt stats gt Y y3 lt stats gt lt count gt 1 lt count gt lt missingStat gt lt missingStat id 3 gt lt id gt 1068527809 lt id gt lt stats gt Y y2 yl1 lt stats gt lt count gt 1 lt count gt lt missingStat gt lt missingStat id 4 gt lt id gt 1068527809 lt id gt lt stats gt Y yl lt stats gt lt count gt 1 lt count gt lt missingStat gt lt server gt 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 Sstatistics 0 Program has restored the data source for metrics collection Jeba QPTune finished executing Adaptive Server Enterprise CHAPTER2 QPTune Fixing statistics After collecting missing statistics information into an XML file you can update the statistics that are equal to or exceed the threshold for count of missing statistics specified by the m option Use the fix_stats action to update Statistics The i option specifies the input XML file that contains all missing statistics You can generate a SQL script
70. support in compatibility mode 56 Additional trace flag for diagnostics 58 New stored procedure sp_compatmode 58 Changes to qpmode global variable 59 Diagnostic tool 60 Adaptive Server version 15 0 includes substantive changes to the query processor For most customers the new query processor provides a faster and more efficient environment However you may have tuned your server and applications based on the more restricted query processor from Adaptive Server version 12 5 4 and earlier and find the benefits of the version 15 0 query processor unsuitable in some situations In that case use the compatibility mode to upgrade to Adaptive Server 15 0 from version 12 5 x but retain performance characteristics similar to version 12 5 x When you enable compatibility mode Adaptive Server 15 0 uses a query engine similar to the one used in version 12 5 4 and provides an alternative optimization and execution strategy Enabling compatibility mode Migration Technology Guide On Adaptive Server 15 0 3 ESD 1 and later you can enable compatibility mode at the session or server wide level e Session level use set compatibility_mode on off to enable or disable compatibility mode for the current session e Server wide use sp_configure enable compatibility mode 1 0 to enable or disable compatibility mode for the server 55 Feature support in compatibility mode Setting compatibility mode at the session level tak
71. t gt Various optimization related settings from 12 x are no longer relevant in Adaptive Server 15 0 Although the following commands still exist in Adaptive Server 15 0 they are relevant only in compatibility mode and do not have any effect on the Adaptive Server 15 0 optimization process set sort_merge this has been replaced by set merge_join optimization goals and the configuration parameter enable merge join set jtc join transitive closure is always enabled in Adaptive Server 15 0 set table count this setting is no longer relevant in Adaptive Server 15 0 enable sort merge join and JTC this configuration parameter has been replaced by optimization goals and by the configuration parameter enable merge join Adaptive Server Enterprise CHAPTER 1 Migration Strategy e Start up trace flags 334 and 384 these flags enabled merge joins and JTC and are no longer relevant Sybase recommends that you remove any references to these features from your applications Information to capture before contacting Technical Support Before contacting Tech Support gather as many diagnostics statistics as possible especially when you can reproduce the problem 701 errors When a regular query excluding update index statistics generates a 701 error it indicates that Adaptive Server has exhausted the procedure cache space If you are running with the default procedure cache size increase procedure cache and try again T
72. tats collect_stats fix_stats undo_fix_stats J charset specifies the character set used to connect to Adaptive Server If this option is not specified the Adaptive Server uses the server s default character set Note If the installed JRE does not support the server s default charset encoding you see an error message during the login process Use the J option to specify a more generic character set such as J utf8 M mode specifies the optimization goal or custom mode for an application One of allrows_oltp allrows_dss allrows_mix You may also define custom modes _basic_is a system reserved custom mode T appTime specifies the application running time in minutes 0 outputFile specifies the output file i inputFile specifies the input file for the fix fix_stats and undo_fix_stats actions You can also use i to apply special rules to the specified queries for start for custom modes f fileList compares a list of files to get the best plans use commas to separate filenames c configFile Migration Technology Guide 51 QPTune reference information 52 specifies the configuration file limit specifies a limit on the number of queries that should be analyzed and applied with special rules e evalField evaluation field used for performance comparison d difference specifies the percentage and absolute value difference for performance improvement to be considered outstanding
73. te Y y2 y1 INFO Fix Statement delete statistics Y y2 y1 Fixed statistics Delete Y y1 INFO Fix Statement delete statistics Y y1 B Haa QPTune finished executing 36 Adaptive Server Enterprise CHAPTER2 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 1 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 lt sp telco sql
74. te value defaults to 0 Percentage values are between 0 and 100 an absolute value can be any number greater than 0 The o option specifies the result of the comparison in a file The file holds the best setting for all the queries being analyzed The s option enables sorting the files from largest to smallest The file with the largest set of queries is used as the basis for comparison The following example shows the result of a compare operation Compare all the files al xml a2 xml Report generated on Tue Aug 19 21 13 04 PST 2008 File 1 name al 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 Migration Technology Guide 29 Using QPTune to tune queries or applications 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
75. the comparison 26 Adaptive Server Enterprise CHAPTER2 QPTune Starting QPTune to tune queries or applications Simple start Custom start The start action of QPTune prepares the server with correct server level configuration settings If a configuration file is used the settings are extracted from the lt start gt section of the configuration file The lt end gt section of the configuration file specifies the settings that enable the system to revert to its original state at the end of QPTune collect action See Configuration file on page 32 for more information on the configuration file If you are applying standard optimization goal settings start QPTune using QPTune S host port database A start M allrows oltp allrows dss allrows mix Use the M option to invoke one of the pre programmed modes that correspond to the three optimization goals in Adaptive Server allrows_mix e allrows_oltp e allrows_dss default If you are applying custom rules to specified queries use QPTune S host port database A start M custom_1 i input xml 1 3 v Use the M option to indicate a custom mode A custom mode is a group of special rules that are specified in the configuration file under the lt mode gt section Rules are Adaptive Server 15 0 optimization criteria that are applicable at the query level using abstract query plans The example above uses a custom mode called custom_I
76. tics 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 1 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 lt xml version 1 0 encoding UTF 8 gt lt 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 gt lt missingStat id 1 gt lt id gt 1068527809 lt id gt lt stats gt Y y4 y2 lt stats gt Adaptive Server Enterprise CHAPTER2 QPTune lt count gt 2 lt count gt lt missingStat gt lt missingStat id 2 gt lt id gt 1068527809 lt id gt lt stats gt Y y3 lt stats gt lt count gt 1 lt count gt lt missingStat gt lt missingStat id 3 gt
77. uration 18 configuration file 32 custom start action for tuning 27 description 17 environment variables 18 Adaptive Server Enterprise examples 34 36 37 41 42 43 fix statistics action 30 fix_stats action 23 flow chart for fix missing statistics 19 flow chart for tuning applications or queries 25 optimized query plan 31 parameters 50 permissions 53 procedure to fix missing statistics 19 procedure to tune queries or applications 24 reference page 50 simple start action for tuning applications or queries 27 start action for tuning applications or queries 27 start_stats action to fix missing statistics 21 syntax 50 tuning queries or applications 24 undo_fix_stats action 24 QPTune GUI 44 Adaptive Server Name and Configuration 48 Adaptive Server set up 48 collect action 49 compare action 49 configuration commands 48 creating a tuning task 47 environment 45 fix action 50 fix missing statistics 46 modes 48 results page 49 starting the GUI 46 system requirements 45 tuning task panel 47 undo missing statistics fix 47 wizard for tuning Adaptive Server 48 query plan optimized 31 query processing parallel 4 query processing tips 10 R resource recommendations procedure cache 4 Migration Technology Guide Index tempdb 4 restricted mode features 56 S set compatibility mode 55 settings applying fix action 30 set up GUI 48 set up environment 18 simple start action of QPTune 27 sp_compatmode 58 sp_configure 55 5
78. ure abstract plan dump 0 INFO End config sp configure system table 0 Program has restored the data source for metrics collection aS 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 1 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 Migr
79. ure metrics elap max 0 lt fix_config gt gt lt Specify a query plan group name to save all existing plans from ap_ stdin gt lt Existing plans from ap_stdout will be saved to the corresponding group name added with out gt lt save_plans pre fix gt pre fix _qpgroup lt save_plans_ pre fix gt lt f ix gt The optional lt mode gt section allows users to specify custom optimization settings to one or more queries specified through another input file The M option of the start and collect actions specifies the mode setting When the M option specifies anything other than a standard optimization goal setting QPTune treats the mode as customized and retrieves the optimization goal and rules settings for the indicated name from the lt mode gt section of the configuration file QPTune then applies the custom settings to the list of specified queries Migration Technology Guide 33 Examples Examples 34 Fixing 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 1 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 statis
80. ust choose at least one of the enclosed options Do not type the braces cash check credit Brackets mean that to choose one or more of the enclosed options is optional Do not type the brackets cash check credit The comma means you may choose as many of the options shown as you want Separate your choices with commas as part of the command cash check credit The pipe or vertical bar means you may select only one of the options shown cash check credit An ellipsis means that you can repeat the last unit as many times as you like buy thing price cash check credit thing price cash check credit You must buy at least one thing and give its price You may choose a method of payment one of the items enclosed in square brackets You may also choose to buy additional things as many of them as you like For each thing you buy give its name its price and optionally a method of payment Migration Technology Guide xi e Syntax statements displaying the syntax and all options for a command appear as follows sp_dropdevice device_name For a command with more options select column_name from table_name where search_conditions In syntax statements keywords commands are in normal font and identifiers are in lowercase Italic font shows user supplied words e Examples showing the use of Transact SQL commands are printed like this select f
81. ut not using new features EELU EUEEEUEUUUUU UEU 10 MPOUDIESHOOTING 4 5 44 a kaka acetate ie hse the asthe hag RE 10 Query processing TIPS ee eeeeeeeeeeeeeeeeeeeeeeeeereeeeeereeeretereeanes 10 Information to capture before contacting Technical Support 13 CHAPTER 2 QPTUNG ss etek dal rinek cibe cee eee teste career teed tetanic 17 Setting UP your SYSTOM 0 eee eee EEE Ek Ek Ek kK KAKA KAKA KAK AA 18 Using QPTune to fix missing statistics U EEUEEEEEEEUEUUEUUUj 19 Starting QPTune to fix missing statistics 21 Collecting Statistics 4 J Zin iuldak l dka de leka adana e derek rake Da 21 FIXINQ StAtistiCs 2 isa n ad da ka Sade ke dad is feel de Hu dede Sig det Kek de deyk R ad 23 Using UNdO_fiX_Stats d sa u l Sill ake Kid Kaka de lek ra ean ara dakaen k 24 Using QPTune to tune queries or applicatione 24 Starting QPTune to tune queries or applicatione 27 ColleCtinGimetrics N Kn 28 Comparing meltfl68 asd persiera d dabi ak naene d la kk ka centers deraa rak dan 29 Applying the best results ELuEEEE EEE kkk 30 G rlig ration fil g2 lt sis x n diaya di ena ea iney kak dek danek aa feteneed desde 32 Examples E E les 2 cis dace ten Lad ousting be eta dee 34 Upgrade iSSUCS E E esd chs fede ik did obi seden is aed kn erent dee 43 LOCAlIZ AON Ea
82. which may be a combination of rules such as use optgoal allrows_mix use merge_join off use opttimeoutlimit 15 Migration Technology Guide 27 Using QPTune to tune queries or applications Use the i option to indicate an input XML file which has been generated by QPTune while applying a standard goal setting during the collect phase A number of collected metrics files may be compared to generate a file with the best goal settings The input file contains SQL text for the queries Use the I option along with the i option to indicate the number of queries that should be applied with these special rules The queries are counted from the start of the file The default value of the I option is 0 which implies that all queries in the input file are applied Collecting metrics After starting the system run your applications and collect metrics into an XML file Use the o option to specify the output metrics file The v option provides a verbose output The M option indicates custom or standard modes You can collect metrics either e Immediately using the T 0 option or e After t minutes using the T f option For example the command below writes XML into a file named a2 xml The custom mode is depicted within the lt bestmode gt tags QPTune S host port database A collect T 0 0 a2 xml v Program has configured the data source for metrics collection Now collecting information from sysquerymetrics on Tue Fe
83. xed 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 Migration Technology Guide 41 Examples 42 Using 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 custom 1 which allows an optimization goal of allrows_oltp together with the rule merge_join_off lt default custom mode gt lt mode name default gt lt optgoalsuse optgoal allrows_mix lt optgoal gt lt rule gt use merge join off lt rule gt lt rule gt use opttimeoutlimit 15 lt rule gt lt mode gt lt _basic_ mode is a reserved system mode gt lt mode name _basic_ gt lt mode gt lt mode name custom1 gt lt optgoalsuse optgoal allrows_oltp lt optgoal gt lt rule gt use merge join off lt
84. y is for OLTP or DSS purposes the optimizer uses that information to generate a query plan that may save time memory and CPU usage Adaptive Server 15 0 provides three optimization goals ordered from narrow to wide which correspond to the number of options and strategies that they allow the optimizer to consider e allrows_oltp is best for OLTP queries allrows_oltp offers the narrowest selection of join methods the query optimizer considers only nested loop joins e allrows_mix is the default after upgrading to Adaptive Server 15 0 allrows_mix allows the optimizer to consider merge joins as well as parallel plans if the Adaptive Server is configured for parallelism e allrows_dss is best for DSS queries allrows_dss offers the widest selection of join methods The optimizer considers hash joins as well as nested loop joins merge joins and parallel plans If you use allrows_mix and allrows_dss additional low level processing algorithms are enabled for SQL operations these algorithms are disabled if you use allrows_oltp When you widen the optimization goal the query optimizer might use significantly more resources time and procedure cache to generate a query plan If the optimizer generates the same query plan with only nested loop joins under allrows_dss and allrows_oltp you may expect the optimization under allrows_dss to take more time and procedure cache than under allrows_oltp 2

View this document as PDF

Contents

Download Pdf Manuals

Related Search

Related Contents