The remainder of this article will focus on tuning SQL, or more precisely, preventing slow SQL execution. Aren't these the same thing? Mostly yes, but a common approach in development is making a statement perform well enough or fast enough. Each and every statement does not have to be optimal, but some thought has to go into coding them. You do not have the time to optimize hundreds or even thousands of SQL statements, but at the same time, there are guidelines you can follow to avoid common mistakes and bad coding. 17 Tips for Avoiding Problematic Queries The source of these 17 tips is from Oracle9i Performance Tuning: Optimizing Database Productivity by Hassan Afyouni (Thompson Course Technology, 2004). These tips provide a solid foundation for two outcomes: making a SQL statement perform better, and determining that nothing else can be done in this regard (i. e., you have done all you can with the SQL statement, time to move on to another area). The 17 tips are listed below. Avoid Cartesian products 2.
As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U. Government. This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications. Oracle and Java are registered trademarks of Oracle and/or its affiliates.
Top reviews from the United States There was a problem filtering reviews right now. Please try again later. Reviewed in the United States on March 12, 2000 Verified Purchase Knowledge is dripping off every page of this book. Reading chapter 1 alone helped me greatly increase the performance of an Oracle instance. This is the only tuning book you will ever need for Oracle 8. Reviewed in the United States on May 9, 2000 My review is focused on this book as it relates to a developer, rather than a DBA. A great deal of this book deals specifically with DBA-related activities which I will pass on reviewing for now. It is a bit scary to think that I actually wrote queries before reading this book. The tips and techniques in this book provide a great deal of insight into how to tune the database and are truly invaluable. "Tuning a database" is a rather silly way of describing the content of this book, because there are many aspects of that. You can tune the Oracle software itself (DBA), the structure of the data (Architect/Developer), or the SQL queries (Developer).
Avoid the following kinds of complex expressions: NVL (col1, -999) = …. TO_DATE(), TO_NUMBER(), and so on These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method 17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator 18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data 19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. 20. While querying on a partitioned table try to use the partition key in the "WHERE" clause if possible.
Performance Tuning Guide 12 c Release 1 (12. 1) E49058-08 January 2017 Oracle Database Performance Tuning Guide, 12 c Release 1 (12. 1) Copyright © 2013, 2017, Oracle and/or its affiliates. All rights reserved. Primary Author: Rajesh Bhatiya, Immanuel Chan, Lance Ashdown Contributors: Hermann Baer, Deba Chatterjee, Maria Colgan, Kevin Jernigan, Sue K. Lee, William Lee, David McDermid, Uri Shaft This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited. The information contained herein is subject to change without notice and is not warranted to be error-free.
Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.
If you find any errors, please report them to us in writing. If this is software or related documentation that is delivered to the U. S. Government or anyone licensing it on behalf of the U. Government, then the following notice is applicable: U. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U. Government end users are "commercial computer software" or "commercial computer software documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract.
Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.
Featured Database Articles Oracle Posted Sep 14, 2005 By Steve Callan Performance tuning is a broad and somewhat complex topic area when it comes to Oracle databases. Two of the biggest questions faced by your average DBA concern where to start and what to do. All you may know is that someone (a user) reports a problem about a slow or poor performing application or query. Where do you even begin to start when faced with this situation? Oracle's Approach to Tuning For anyone who has taken the Performance Tuning exam for Oracle8i certification, one of the testable areas dealt with Oracle's Tuning Methodology. Oracle's emphasis on this particular methodology changed when Oracle9i was released. The approach has gone from top-down in 8i to that of following principles in 9i/10g. Neither methodology is absolute as each has its advantages and disadvantages. In Oracle8i, the steps consisted of the following: 1. Tuning the Business Rules 2. Tuning the Data Design 3. Tuning the Application Design 4.
Avoid full table scans on large tables 3. Use SQL standards and conventions to reduce parsing 4. Lack of indexes on columns contained in the WHERE clause 5. Avoid joining too many tables Monitor V$SESSION_LONGOPS to detect long running operations 7. Use hints as appropriate 8. Use the SHARED_CURSOR parameter 9. Use the Rule-based optimizer if I is better than the Cost-based optimizer 10. Avoid unnecessary sorting 11. Monitor index browning (due to deletions; rebuild as necessary) 12. Use compound indexes with care (Do not repeat columns) 13. Monitor query statistics 14. Use different tablespaces for tables and indexes (as a general rule; this is old-school somewhat, but the main point is reduce I/O contention) 15. Use table partitioning (and local indexes) when appropriate (partitioning is an extra cost feature) 16. Use literals in the WHERE clause (use bind variables) 17. Keep statistics up to date That is quite a list and overall is thorough and accurate. Step 9, referring to the use of the Rule-based optimizer, may cause a reliance or dependency on a feature Oracle has identified as a future item to be deprecated.