Transcription
Performance and Tuning Series: QueryProcessing and Abstract PlansAdaptive Server Enterprise15.0.2
DOCUMENT ID: DC00743-01-1502-01LAST REVISED: November 2008Copyright 2008 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 otherinternational customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduledsoftware 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 listedare trademarks of Sybase, Inc. indicates registration in the United States of America.Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.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-7013for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.Sybase, Inc., One Sybase Drive, Dublin, CA 94568.
ContentsAbout This Book . xiCHAPTER 1Understanding Query Processing . 1Query optimizer . 3Factors analyzed in optimizing queries . 6Transformations for query optimization . 7Handling search arguments and useful indexes . 12Handling joins . 13Optimization goals. 16Limiting the time spent optimizing a query . 17Parallelism. 18Optimization issues . 18Lava query execution engine . 21Lava query plans . 22How update operations are performed. 29Direct updates . 29Deferred updates. 32Deferred index inserts . 33Restrictions on update modes through joins . 35Optimizing updates. 36Using sp sysmon while tuning updates . 38CHAPTER 2Using showplan.Displaying a query plan .Query plans in Adaptive Server Enterprise 15.0 and later .Using set showplan with noexec .Statement-level output .Query plan shape .Query plan operators.EMIT operator .SCAN operator .FROM cache message.FROM or LIST .Performance and Tuning Series: Query Processing and Abstract Plans3939404146495454545455iii
ContentsFROM TABLE . 56Union operators. 92UNION ALL operator . 92MERGE UNION operator . 93HASH UNION. 93SCALAR AGGREGATE operator. 95RESTRICT operator . 96SORT operator . 96STORE operator. 97SEQUENCER operator . 99REMOTE SCAN operator. 101SCROLL operator. 101RID JOIN operator. 102SQLFILTER operator . 104EXCHANGE operator. 106INSTEAD-OF TRIGGER operators. 108INSTEAD-OF TRIGGER operator. 109CURSOR SCAN operator . 110deferred index and deferred varcol messages . 112CHAPTER 3CHAPTER 4CHAPTER 5ivDisplaying Query Optimization Strategies and Estimates.set commands for text format messages .set commands for XML format messages.Using show execio xml to diagnose query plans.Usage scenarios .Permissions for set commands .113114116119122113Finding Slow Running Queries .Saving diagnostics to a trace file.Set options that save diagnostic information to a trace file .Which sessions are being traced? .Rebinding a trace .Displaying SQL text.Retaining session settings .123125126127127130Parallel Query Processing .Vertical, horizontal, and pipelined parallelism .Queries that benefit from parallel processing.Enabling parallelism .number of worker processes .max parallel degree.max resource granularity.131132133133134134123131Adaptive Server Enterprise
Contentsmax repartition degree .max scan parallel degree .prod-consumer overlap factor .min pages for parallel scan .max query parallel degree .Controlling parallelism at the session level .set command examples .Controlling query parallelism .Query-level parallel clause examples.Using parallelism selectively .Using parallelism with large numbers of partitions.When parallel query results differ.Queries that use set rowcount.Queries that set local variables .Understanding parallel query plans.Adaptive Server parallel query execution model.EXCHANGE operator.Using parallelism in SQL operations .Partition elimination .Partition skew .Why queries do not run in parallel.Runtime adjustment .Recognizing and managing runtime adjustments 45151194195196196197CHAPTER 6Eager and Lazy Aggregation .Overview .Eager aggregation .Aggregation and query processing .Examples .Using eager aggregation .Enabling eager aggregation .Checking for eager aggregation .Forcing eager aggregation with abstract plans .199199200201204211211211214CHAPTER 7Controlling Optimization . 217Special optimizing techniques. 217Viewing current optimizer settings . 218Specifying query processor choices. 221Specifying table order in joins . 222Specifying the number of tables considered by the query processor.223Specifying query index . 224Specifying I/O size in a query. 226Performance and Tuning Series: Query Processing and Abstract Plansv
ContentsIndex type and large I/O size. 227When prefetch specification cannot be followed . 228setting prefetch. 229Specifying cache strategy . 229In select, delete, and update statements. 230Controlling large I/O and cache strategies . 231Getting information on cache strategies. 231Asynchronous log service . 231Understanding the user log cache (ULC) architecture . 233When to use ALS . 233Using the ALS . 234Enabling and disabling merge joins . 234Enabling and disabling hash joins . 235Enabling and disabling join transitive closure . 235Controlling literal parameterization. 236Suggesting a degree of parallelism for a query. 238Query level parallel clause examples. 240Optimization goals. 240Setting optimization goals . 241Optimization criteria . 242Limiting optimization time . 245Controlling parallel optimization . 246number of worker processes . 247Specifying the number of worker processes available for parallelprocessing . 247max resource granularity. 247max repartition degree . 248Concurrency optimization for small tables . 248Changing the locking scheme . 249CHAPTER 8viOptimization for Cursors .Definition .
23.02.2007 · Locking, scans and sorts during update all statistics. 299 Using the with consumers clause. 299 Reducing the impact of update statistics on concurrent processes
