That means the best SQL tuning books are those that tell you the right way to write it. I'd strongly suggest Itzik Ben-Gan's book: T-SQL Querying. SQL Tuning: Generating Optimal Execution Plans and millions of other books are available for site site. SQL Tuning: Generating Optimal Execution Plans Paperback – December 1, Start reading SQL Tuning: Generating Optimal Execution Plans on your site in under a minute. Covering today's most popular and widely installed database environments, this book is an indispensable resource for managing and tuning SQL across.

Sql Tuning Book

Language:English, Arabic, Hindi
Genre:Academic & Education
Published (Last):23.10.2015
ePub File Size:21.56 MB
PDF File Size:12.41 MB
Distribution:Free* [*Registration Required]
Uploaded by: FAYE

Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning concludes by addressing special concerns and unique. For completeness, this book covers these parts of SQL tuning as well, for Oracle, Microsoft SQL Server, and IBM DB2. Even on other databases, though (and on. Books shelved as sql-tuning: Sybase SQL Server: Performance and Tuning Guide: Sybase SQL Server Release X by Karen Paulsell, Microsoft SQL Server.

There are three basic steps to SQL tuning:. Figure out which execution plan path to reach the data your SQL statement demands you are getting. I deliberately show these steps out of logical order to reflect the state of most material written on the subject.

Almost everything written about SQL tuning focuses almost exclusively on the first two steps, especially the second. Coverage of the third step is usually limited to a short discussion about when indexed access is preferred to full table scans.

The implied SQL tuning process lacking a systematic approach to the third step is to repeat step 2, repeatedly tweaking the SQL, until you stumble on an execution plan that is fast enough, and, if you do not find such a plan, to keep going until you utterly lose patience. Here is an analogy that works pretty well. Understanding the first step gives you a clear windshield; you know where you are. Understanding the second step gives you a working steering wheel; you can go somewhere else.

Understanding the third step gives you a map, with marks for both where you are and where you want to be.

If you can imagine being in a strange city without street signs, without a map, in a hurry to find your hotel, and without knowing the name of that hotel, you begin to appreciate the problem with the average SQL tuning education. Even your computer cannot complete a trial-and-error search over that kind of search space.

For tuning, you need a method that you can handle manually. With this insight, we can turn the usual process on its head, and lay out a more enlightened process, now expressed in terms of questions:. If the difference between the actual and ideal execution plans is enough to matter, how can you change some combination of the SQL and the database to get close enough to the ideal execution plan for the performance that you need?

To be thorough, I cover all of these questions in this book, but by far the most important, and longest, parts of the book are dedicated to answering the first question, finding the best execution plan without trial and error. Furthermore, the range of answers to the first question heavily color my coverage of the third question.

I do explain, though, why you should always prefer a hash join on Oracle anywhere a sort-merge join looks good. When we look at the problem of SQL tuning in this new way, we get a surprise benefit: The best execution plan is still the best execution plan, whether we are executing the statement on Oracle, Microsoft SQL Server, or DB2, so this knowledge is far more useful than anything we learn that is specific to a database vendor. I even predict that the best execution plan is unlikely to change much in near-future versions of these databases.

The method this book describes reduces a query to an abstract representation that contains only the information relevant to tuning.

I often substitute query for SQL statement. Even for the rest, the problem usually lies in a subquery nested inside the problem update or insert. This is akin to reducing an elaborate word problem in high-school mathematics to a simple, abstract equation, where the solution of the equation is generally almost automatic once you know the necessary math.

The abstract representation of a SQL tuning problem, the query diagram, normally takes the form of an upside-down tree, with some numbers attached, as shown in Figure As it turns out, SQL is such a flexible language that it is capable of producing queries that do not map to the usual tree form, but it turns out that such queries almost never make sense from a business perspective.

This delivers an unplanned-for side benefit: Developers usually catch these problems before you see the SQL, unless the problems lie in the sort of corner cases that they might not test thoroughly, as these problems often do. These corner-case problems can be the worst kind for an application—for example, throwing accounts out of balance long after the application goes live and is assumed to be fine, in subtle ways that are hard to detect and hard to fix. The worst of these problems will never be found.

The business will simply operate based on wrong results, under-billing, over-billing, under-paying, over-paying, or otherwise just doing the wrong thing without anyone tying these problems to a correctable application bug.

Sometimes, fixing a performance problem also requires you to fix a logic problem. Even when the problems are independent when you can fix performance without fixing the logic flaw , you can perform a major added service by identifying these logic flaws and seeing that they are fixed.

This book covers these logic flaws at length, including detailed descriptions of how to find each one and what to do about it. I go so far as to recommend you take any significantly complex, manually written SQL through the SQL diagramming exercise just to find these subtle logic errors, even if you already know that it performs well. Depending on the tool, some products that autogenerate SQL avoid most of these problems.

Finally, this book discusses outside-the-box solutions: This brings up a class of problems where you really do need to pay some attention to what the application does, when you cannot just treat it as an abstract black box that needs a specified set of rows from some specified tables.

Even so, there are some reliable rules of thumb for the kinds of application-level changes that are likely to solve these types of problems. You will likely need to work with developers who know the application details assuming you do not to solve these problems, but by understanding the rules you can still offer valuable suggestions without application-specific knowledge. Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more. Start Free Trial No credit card required. Well begun is half done. V, Ch.

Why Tune SQL? Tip Online applications and applications that produce reports for human consumption should produce data volumes fit for human consumption, which are paltry for a computer to handle. Performance Versus Throughput. How This Book Can Help. Change SQL or the database to get a chosen execution plan. Figure out which execution plan is best. Which execution plan is best, and how can you find it without trial and error?

How does the current execution plan differ from the ideal execution plan, if it does? Does the new execution plan deliver the SQL performance you needed and expected? A Bonus. Combining Bitmaps: The Bitmap Merge Operation. How Bitmap Join Indexes Work. Bitmap Join Index Example. Exclusions for Bitmap Join Indexes. Beware of the Fast Fix.

Forcing Index Usage.

Using the round Date Function. Using the trunc Date Function. Index Usage and Built-in Functions. Finding BIFs. Using case Statements with an FBI. Indexing on Complex Functions. Statistics and FBIs. Conclusions on FBIs. Indexing on Regular Expressions. Case Sensitive Searches with Indexes. Testing New Oracle Indexes. Monitoring Index Usage. Monitoring for Index Range Scans. Verifying Optimal Index Usage. Finding Indexing Opportunities. Finding sub-optimal SQL in the library cache.

Locating Un-used Indexes. Finding Unused Indexes in Oracle i and Earlier. Finding Unused Indexes in Oracle i. Finding Unused indexes in Oracle g and Beyond. Dropping Un-used Indexes. Locating Infrequently Used Indexes. The Problem of Too Many Indexes. Determining Which Index to Delete. Large Multi-column Indexes. Choosing Candidates for Index Maintenance.

Index Hints. Sizing the PGA. Manual RAM allocation vs. Hidden parameters for Oracle PGA regions. The g Full Hash Join. Using the hash group by Plan. When Hash Joins Fail. Tracking Hash Joins. Tuning Distributed SQL.

Distributed Database Technology. Coordinating Distributed Databases. The remote-to-remote Distributed Join. The local-to-remote distributed join. Performance Issues with Distributed Queries.

Creating Cross-database Execution Plans. The Problem of Remote Joins. Sorting and Distributed SQL. Parallelism and Distributed Queries.

Parallel query speeds up full-table scans. A Brief History of Parallel Query. Tuning Parallel Join Operations. Tuning Parallel Sort Merge Joins. Tuning Parallel Hash Joins. How Oracle Parallel Query Works. Parallel Hints. The Parallel Hint. The noparallel Hint. Invoking Parallel Query. Single Instance Oracle Parallel Query.

Finding the Optimal Degree of Parallelism. Checking CPU Count. Parallel Query for Distributed Instances. Oracle Parallel Query Parameters. Oracle g R Parallel Parameters.

Parallel DML: Monitoring Oracle Parallel Query. Bottlenecks in Oracle Parallel Query. Finding Disk Enqueues.

SQL Tuning

Finding CPU Slowdowns. Parallel Query Execution Plans. SQL Tuning with Views. Inside Oracle Views. Tuning SQL with Views.

Benefits of Oracle Views. Predicate Pushing with Views. Combining Hints and Views. Oracle In-line Views. A Case Study in Materialized Views. Reorganizing Tables for High Performance. Multi-table Index Cluster Tables. Single-table Index Cluster Tables.

Managing Row Chaining in Oracle. A Summary of Object Tuning Rules. Oracle Index Maintenance. Not All Indexes Benefit from Rebuilding. When Should Indexes Be Rebuilt? The History of Data Sorting. Tuning with External Sorts. Internals of Oracle Sorting. Managing Oracle Sorting.

SQL Indexing in MySQL, Oracle, SQL Server, etc.

Avoiding Disk Sorts. Monitoring Disk Sorts. Tuning Object-oriented SQL. Repeating Data Items in Relational Tables. Inside Varray Tables. Execution Plans for varray Tables.

Oracle Nested tables. Tuning SQL Subqueries. Subquery Tuning and SQL. Types of SQL Subqueries. Tuning Guidelines for Subqueries. Avoiding SQL Subqueries. Subqueries in the where Clause.

In vs. Same Results, Different Syntax and Plans. Non-correlated subquery: Outer Join: Correlated Subquery: Tuning Scalar Subqueries. Scalar Subquery Performance.

Removing Subqueries Using the with Clause. Internals of Temporary Tables. Row Management of Temporary Tables. Remove subqueries with global temporary tables. Correlated vs. Noncorrelated Subqueries. Tuning Correlated Subqueries. Automatic Rewriting not exists Subqueries. Automatic Rewriting exists Subqueries.

Rewriting Non-equality Correlated Subqueries.

The top Oracle SQL Tuning book

Rewriting exists Subqueries with the rank Function. Subquery Hint Tuning. Subquery Tuning with Index Hints. Table Anti-Join Hints. Troubleshooting SQL. Troubleshooting Problem SQL. What is Bad SQL?

Identifying Problem SQL. Finding indexing opportunities. All About Parsing. All About Cursors. Use Host Variables. Tuning the Library Cache. The Library Cache Hit Ratio.

Oracle Cursor Sharing Enhancements. Using g Adaptive Cursor Sharing. Tracking SQL nested loop joins. Oracle DML Tuning. DML Tuning is not for neophytes. Oracle DML tuning. Optimizing Oracle SQL insert performance. High Impact insert Tuning Techniques. Tips for batch inserts. Tuning insert speed with the nologging option. Reverse key indexes and insert performance. Blocksize and insert performance.

Oracle Delete Tuning. Oracle Update Tuning. Oracle subquery factoring with clause for DML. Decision Support and Expert Systems Technology. Comparing the SPA Results. Gathering the SQL Tuning set. Oracle guided workflow screen. Oracle Automatic Database Diagnostics Monitor. Inside SQL Analytics.You will sometimes hear the term "filter early" when discussing execution plans. Oracle g R Parallel Parameters.

How do I learn to tune SQL?

SQL Tuning provides an essential next step for SQL developers and database administrators who want to extend their SQL tuning expertise and get the most from their database applications. The sort merge join. Sort-merge joins 2. Oracle Table Join Optimization. The subject of efficient function calls in SQL is discussed here. Two-node filter nonunique on both ends between nodes is already linked through normal joins 7.

NATOSHA from Little Rock
Please check my other articles. I have a variety of hobbies, like poi. I do relish potentially.