Author: Donald K. Burleson
Publisher: Rampant Techpress
Release Date: 2014-03
It's impossible to tune an Oracle database without understanding SQL tuning. Oracle is a SQL processing engine and the execution speed of any SQL query is influenced by many factors, both internal and external. As a declarative data access method, SQL relies on the Oracle cost-based optimizer to always choose the "best" execution plan for every SQL query. However, Oracle's SQL is among the most flexible and robust in the world, and along with this great power comes complexity. Tuning Oracle SQL is the single most important skill of any Oracle professional, and Oracle professionals are challenged to create SQL statements that will support thousands of concurrent executions with sub-second response time. "Advanced Oracle SQL Tuning" is a pragmatic treatment of Oracle SQL tuning, short on theory and big on real-world techniques and tips. This book is the culmination of the author's 25 years of full-time DBA experience and he shares expert tips and secrets for hyper charging SQL execution speed. This book covers advanced topics and it is not appropriate for beginners, dilettantes or neophytes.
Author: Donald K. Burleson
Publisher: Rampant TechPress
Release Date: 2010
For Oracle tuning professionals wishing to add more tools to their Oracle tuning toolbox, this guidebook introduces the various tuning analytical tools and helpful strategies to make the database easier to use. Details describe how to extract information from the database and use it to determine and increase efficiency. It also provides specific steps with detailed information on how to congeal large amounts of database performance information into one pool from which the DBA can carefully choose tuning options based on what is predicted, all to give them the biggest improvement in performance for the least time and money investment. Sample code, sample code results, and guidelines on how to interpret the results help users manipulate code in an effective way. With countless hints, tips, and tools, the guide fully explains how to work with the Oracle system on order to achieve database performance excellence.
Author: Donald K. Burleson
Publisher: Rampant Techpress
Release Date: 2014-02
Oracle has become the world's most flexible and robust database and along with great power comes great complexity. This complexity requires that the DBA have expert knowledge of Oracle internals. This book provides a thorough step-by-step approach for holistic Oracle tuning in this challenging information technology era. It represents the knowledge accumulated from tuning thousands of Oracle databases. Oracle tuning has always been a complex task; however, it has become even more complex as Oracle evolves and yields new techniques for achieving optimal performance in the stressed production environment of today's high-tech world. Oracle STATSPACK and AWR has introduced a revolution in database tuning. By understanding these time-series tables, we can develop time-series tuning models to predict upcoming outages and dynamically change the instance to accommodate the impending resource changes. Database tuning efforts must become as sophisticated as the databases themselves. This book strives to show you how to leverage upon the wealth of Oracle performance information so that you can create a robust Oracle database engine, one that maximizes computing resources while minimizing overhead. If you are seeking theory, this is not the book for you. This book encapsulates the combined knowledge of over a century of hands-on DBA tuning experience, a pragmatic, practical approach for the professional Oracle DBA. This is not a book for beginners. Targeted at the senior Oracle DBA, this comprehensive book gives you all of the knowledge you need to be successful in tuning even the most complex Oracle database. The code download for this book is packed with ready-to-run scripts to monitor and identify even the most challenging performance issues.
Targeted at Oracle professionals who need fast and accurate working examples of complex issues, Oracle In-focus books target specific areas of Oracle technology in a concise manner. Plenty of working code is provided without a lot of theory, allowing database managers to solve their problems quickly without reviewing data that they already know. All code scripts are available for instant download from a companion web site.
This is the definitive guide to all Oracle utilities, and it is targeted at experienced Oracle professionals who need to perform powerful system-wide operating system internal operations on their Oracle databases. The authors approach Oracle Utilities from a functional perspective, adding value by explaining the "why" and "how" for the use of all Oracle Utilities. This comprehensive book includes details on all of the Oracle utilities, including experts-only sections on undocumented utilities, plus the Linux/UNIX utilities, Oracle command-line utilities, and the all-important DBA package utilities. This indispensable book covers all areas of Oracle database administration and includes secrets for database migration (with Data Pump and SQL*Loader) and detailed tips for using The Oracle OS utilities (e.g. BBED, dbv, orastack, orakill). The book also covers Oracle tuning utilities, including ADDM and the SQL tuning advisors as well as the traditional command-line tuning utilities such as dbms_alert and dbms_xplan, tkprof and dbms_debug. This book is special because noted Oracle experts supplement the operational considerations of Oracle utilities with expert insights and working scripts. The time savings from a single utility script is easily worth the price of this definitive book. This book is not for beginners. These are powerful utilities to be used only by experienced DBAs and developers who need to perform powerful system-wide operating system internal operations on their Oracle databases.
Despite its wide availability and usage, few developers and DBAs have mastered the true power of Oracle SQLPlus. This bestselling book--now updated for Oracle 10g--is the only in-depth guide to this interactive query tool for writing SQL scripts. It's an essential resource for any Oracle user.The new second edition of Oracle SQLPlus: The Definitive Guide clearly describes how to perform, step-by-step, all of the tasks that Oracle developers and DBAs want to perform with SQLPlus--and maybe some you didn't realize you could perform.With Oracle SQLPlus: The Definitive Guide, you'll expertly: write and execute script files generate ad hoc reports extract data from the database query the data dictionary tables customize an SQLPlus environment and much more It also includes a handy quick reference to all of its syntax options and an often-requested chapter on SQL itself, along with a clear, concise, and complete introduction.This book is truly the definitive guide to SQLPlus. It's an indispensable resource for those who are new to SQL*Plus, a task-oriented learning tool for those who are already using it, and an immediately useful quick reference for every user. If you want to leverage the full power and flexibility of this popular Oracle tool, you'll need this book.
With the expert techniques discussed in this book, Oracle database administrators can automate routine tasks to save time and money and better monitor the flow of work. Using shell scripts—an indispensable tool on UIX and Linux—any number of commands can be combined and executed either simultaneously or sequentially. More than 50 working shell scripts for both beginners and experts give Oracle professionals a fantastic head-start on automating their administration duties and are easily modifiable for any environment. Topics include the history of shells and shell scripting, detailed step-by-step instructions on building shell scripts, how to tell when things are working right, and how to effectively monitor the system for failures.
Author: Guy Harrison
Publisher: Prentice Hall PTR Oracle
Release Date: 2001
Canada was young during the First World War, and with as many as 20,000 underage soldiers leaving their homes to join the war effort, the country's army was, too. Jim, at 17, was one of them, and he penned countless letters home. But these weren't the writings of an ordinary boy. They were the letters of a lad who left a small farming community for the city on July 15, 1915, a boy who volunteered to serve with the 79th Queen's Own Cameron Highlanders. Jim's letters home gloss over the horrors of war, focusing instead on issues of the home front: of harvesting, training the horses, and the price of hogs. Rarely do these letters, especially those to his mother and father, mention the mud and rats, the lice and stench of the trenches, or the night duty of cutting barbed wire in no man's land. For 95 years his letters remained in a shoebox decorated by his mother. Jim was just 18 when he was wounded and died during the Battle of the Somme. Hold the Oxo! tells the story that lies between the lines of his letters, filling in the historical context and helping us to understand what it was like to be Jim.
A complete guide to SQL*Loader, a utility used to move data from external files into an Oracle database, offers step-by-step instruction in the various applications of SQL*Loader, providing a task-oriented approach that covers the latest Oracle 8 and Oracle 8i features. Original. (Beginner/Intermediate)
Author: Donald K. Burleson
Publisher: McGraw-Hill Education
Release Date: 2001-08-17
From the official Oracle Press comes a comprehensive guide to tuning SQL statements for optimal execution. This expert resource explains how to view the internal execution plan of any SQL statement and change it to improve the performance of the statement. You'll get details on Oracle's optimizer modes, SQL extensions, the STATSPACK utility, and a wealth of methods for tuning Oracle SQL statements.
Written by a Senior Database Administrator who has worked with the Oracle RDBMS for thirty years, this is a book which teaches the skill of SQL Tuning for the Oracle Database. Not a list of one-off tricks or tips, nor a glossing over of topics; this book offers an in-depth process covering discovery, analysis, and problem resolution. Learn the science behind SQL Tuning. Learn and apply the FILTERED ROWS PERCENTAGE Cardinality based method of tuning Determine a query's Driving Table and Join Order Construct Query Diagrams, Data Models, and Join Trees Build and use Count / Filter / and Reconstruction Queries Identify Waste in a Query Execution Plan Zero in on Cardinality Divergence using Estimated vs. Actuals Use the ACCESS / FILTER / COVERAGE strategy to build indexes for Problem Queries Exploit THE 2% RULE in analyzing Access method and Join method Classify queries as Precision Style or Warehouse Style Understand Hash Join mechanics and make Hash Joins go faster Make HINTS work as Detection Tools rather than clubs Avoid early Database Design flaws Manage Statistics and deal with common Statistics problems (NDV, Uniform Distribution, Independence, Dynamic Sampling) (Staleness, Skew, Dependence, Defaulting, Out-Of-Bounds, Transiency, Bloat) Perfect your Question Based Analyis Technique and more Included are: a special chapter for EXADATA, a LAB which demonstrates the cardinality based process of SQL Tuning, and twenty three magical SQL scripts that make the process of SQL Tuning easy to do. Learn the skill of SQL Tuning as taught by an expert who does it for a living, and become the go-to specialist in your company. Chapter 1: DRIVING TABLE and JOIN ORDER Chapter 2: Ways to Use a Query Execution Plan Chapter 3: The Best Indexes for a Query Chapter 4: JOINS Chapter 5: HINTS Chapter 6: BASICS Chapter 7: ROW COUNTS and RUN TIMES Chapter 8: EXADATA LAB: Reverse Engineering the QEP Appendix: Know Your Scripts Scripts for analyzing queries and plans Scripts for examining an active database Scripts for looking at metadata showplan showplanshort showplanconstraints showplancountqueries showplandatamodel showplandrivingtable showplanfilterqueries showplanfrpspreadsheetcode showplanindexes showplannumrows showplanquerydiagram showplantables showplantablesunique loadplanfromcache loadplanfromhist showtopcpu showowner showindexes showconstraints showcolstats showhistograms showallscanrates showallworkareas It's all about the Cardinalities
Author: Dan Tow
Publisher: "O'Reilly Media, Inc."
Release Date: 2003-11-19
A poorly performing database application not only costs users time, but also has an impact on other applications running on the same computer or the same network. 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.There are two basic issues to focus on when tuning SQL: how to find and interpret the execution plan of an SQL statement and how to change SQL to get a specific alternate execution plan. SQL Tuning provides answers to these questions and addresses a third issue that's even more important: how to find the optimal execution plan for the query to use.Author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database platform being used. You'll learn how to understand and control SQL execution plans and how to diagram SQL queries to deduce the best execution plan for a query. Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning concludes by addressing special concerns and unique solutions to "unsolvable problems."Whether you are a programmer who develops SQL-based applications or a database administrator or other who troubleshoots poorly tuned applications, SQL Tuning will arm you with a reliable and deterministic method for tuning your SQL queries to gain optimal performance.
Author: Guy Harrison
Publisher: Pearson Education
Release Date: 2009-10-09
Oracle Performance Survival Guide A Systematic Approach to Database Optimization The fast, complete, start-to-finish guide to optimizing Oracle performance Oracle Performance Survival Guide offers a structured, systematic, start-to-finish methodology for optimizing Oracle performance as efficiently as possible. Leading Oracle expert Guy Harrison shows how to maximize your tuning investment by focusing on causes rather than symptoms, and by quickly identifying the areas that deliver the greatest “bang for the buck.” Writing for DBAs and developers with all levels of experience, Harrison covers every area of Oracle performance management, from application design through SQL tuning, contention management through memory and physical IO management. He also presents up-to-the-minute guidance for optimizing the performance of the Oracle 11g Release 2. You’ll start by mastering Oracle structured performance tuning principles and tools, including techniques for tracing and monitoring Oracle execution. Harrison illuminates the interaction between applications and databases, guides you through choosing tuning tools, and introduces upfront design techniques that lead to higher-performance applications. He also presents a collection of downloadable scripts for reporting on all aspects of database performance. Coverage includes • “Tuning by layers,” the most effective, highest-value approach to Oracle performance optimization • Making the most of Oracle’s core tools for tracing, monitoring, and diagnosing performance • Highly efficient database logical and physical design, indexing, transaction design, and API use • SQL and PL/SQL tuning, including the use of parallel SQL techniques • Minimizing contention for locks, latches, shared memory, and other database resources • Optimizing memory and physical disk IO • Tuning Real Application Cluster (RAC) databases guyharrison.net informit.com/ph