Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    Only €10,99/month after trial. Cancel anytime.

    Oracle Database 11g R2 Performance Tuning Cookbook
    Oracle Database 11g R2 Performance Tuning Cookbook
    Oracle Database 11g R2 Performance Tuning Cookbook
    Ebook1,228 pages5 hours

    Oracle Database 11g R2 Performance Tuning Cookbook

    By Ciro Fiorillo

    ()

    Read preview

    About this ebook

    In this book you will find both examples and theoretical concepts covered. Every recipe is based on a script/procedure explained step-by-step, with screenshots, while theoretical concepts are explained in the context of the recipe, to explain why a solution performs better than another. This book is aimed at software developers, software and data architects, and DBAs who are using or are planning to use the Oracle Database, who have some experience and want to solve performance problems faster and in a rigorous way. If you are an architect who wants to design better applications, a DBA who is keen to dig into the causes of performance issues, or a developer who wants to learn why and where the application is running slow, this is the book for you. Basic knowledge of SQL language is required and general knowledge of the Oracle Database architecture is preferable.
    LanguageEnglish
    PublisherPackt Publishing
    Release dateJan 20, 2012
    ISBN9781849682619
    Oracle Database 11g R2 Performance Tuning Cookbook

    Related to Oracle Database 11g R2 Performance Tuning Cookbook

    Related ebooks

    Information Technology For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      What is ELT?

      Apr 1, 2021

      It stands for extract, load, and transform- the processes a data pipeline uses for replicating the data from a source system into a target system such as a cloud data warehouse. 1. Extraction is the first step in which data is copied from the source

      6 min read

    • Article

      Types Of Databases

      Aug 27, 2019

      NoSQL databases provide the performance, scalability and stability that’s required by the modern data-driven apps we interact with these days. But that is where the similarity between NoSQL systems end. In fact, it wouldn’t be wrong to say that the o

      1 min read

    • Article

      The Future Of The Database

      Aug 27, 2019

      The Future Of The Database
      7 min read

    • Article

      Data Fabric

      Aug 13, 2020

      Data Fabric
      3 min read

    • Article

      A Place For Everything

      Aug 10, 2019

      A Place For Everything
      9 min read

    • Article

      Deep Into Storage Space

      Jun 25, 2019

      Deep Into Storage Space
      8 min read

    • Article

      Deep Into Storage Space

      Oct 7, 2019

      Deep Into Storage Space
      8 min read

    • Article

      Observability Of The Kernel And Containers

      Apr 4, 2023

      Mihalis Tsoukalos is currently working on Time Series. You can reach him at: @mactsouk. For our final delve into eBPF, we’re tackling applications, the kernel and Docker containers. At the end of the day, all Linux machines execute code for applicat

      10 min read

    • Article

      Scan And Scrape Websites Using Python

      Nov 14, 2023

      David Bolton once accidentally boosted the traffic for his firm’s website by 25% in one day by running a web scraper on it. Luckily, they never found out! Ever since the web made an appearance back in the mid-’90s, programmers have been writing softw

      6 min read

    • Article

      Google Answer Box Strategy

      Sep 21, 2020

      Leveraging the Google PAA (People Also Ask) element on a Search Results Page for Targeted Content Creation with a Python Scraper All businesses that are online today are creating content at a furious pace. According to Technavio, a research firm, con

      7 min read

    • Article

      Help Yourself To Avoid These Pitfalls

      Dec 11, 2018

      GETTING UP TO full speed with the Shortcuts app takes time, and you’ll inevitably make a few mistakes along the way. Having to troubleshoot your efforts doesn’t mean you’ve failed — with years of experience, even professional programmers do this. Tak

      2 min read

    • Article

      Letters

      Aug 18, 2020

      > Data Analysis> Case Construction> Microsoft Edge Hi folks—long-time reader and love the mag. I have a Maingear Shift PC that’s getting long in the tooth (seven-year-old Haswell processor, one panel doesn’t stay in). I’m looking into building a new

      6 min read

    • Article

      Follow a Workflow

      Jan 3, 2020

      Follow a Workflow
      7 min read

    • Article

      Mac 911

      Sep 18, 2018

      Mac 911
      5 min read

    • Article

      Code An Admin Back-end In Django

      Dec 13, 2022

      Credit: www.djangoproject.com OUR EXPERT Matt Holder has been a fan of the open source methodology for over two decades and uses Linux and other tools where possible. More featurepacked source code for this project can be downloaded from https://

      6 min read

    • Article

      The Three Amigos!

      Jun 30, 2020

      Elasticsearch (www.elastic.co), Kibana and Logstash enable you to collect data, store it and visualise it. Logstash is the tool for managing events and logs – Logstash usually sends its data to a storage engine such as Elasticsearch because Elasticse

      1 min read

    • Article

      About Kibana

      Mar 10, 2020

      Kibana offers analytics and a search dashboard for Elasticsearch, as well as visualisation capabilities for data stored in Elasticsearch. Kibana is so handy that it would be a shame to use Elasticsearch without combining it with Kibana. Generally spe

      1 min read

    • Article

      Mac 911

      Apr 20, 2021

      Mac 911
      7 min read

    • Article

      Observe The Observers

      Sep 22, 2020

      “Observability comes from control theory and means that you should be able to determine or infer a system’s state by its output. For some setups, this can involve using strace and grepping through logs, but the number of machines that DBA’s are deali

      1 min read

    • Article

      Finish Your Cataloguing App

      Jan 10, 2023

      Matt Holder has been a fan of the open source methodology for over two decades and uses Linux and other tools where possible. In his spare time, Matt enjoys listening to music and reading. More featurepacked source code for this project can be downlo

      7 min read

    • Article

      Code A Cataloguing Application In Python

      Nov 15, 2022

      Credit: www.djangoproject.com Matt Holder has been a fan of the open source methodology for over two decades and uses Linux and other tools where possible. More featurepacked source code for this project can be downloaded from https://github.com/mat

      8 min read

    Related categories

    Reviews for Oracle Database 11g R2 Performance Tuning Cookbook

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Oracle Database 11g R2 Performance Tuning Cookbook - Ciro Fiorillo

      Table of Contents

      Oracle Database 11gR2 Performance Tuning Cookbook

      Credits

      About the Author

      Acknowledgement

      About the Reviewers

      www.PacktPub.com

      Support files, eBooks, discount offers and more

      Why Subscribe?

      Free Access for Packt account holders

      Instant Updates on New Packt Books

      Preface

      What this book covers

      What you need for this book

      Who this book is for

      Conventions

      Reader feedback

      Customer support

      Downloading the example code

      Errata

      Piracy

      Questions

      1. Starting with Performance Tuning

      Introduction

      Incorrect session management

      Poorly designed cursor management

      Inadequate relational design

      Improper use of storage structures

      Reviewing the performance tuning process

      How to do it...

      How it works...

      There's more…

      See also

      Exploring the example database

      Getting ready

      How to do it...

      How it works...

      There's more...

      Acquiring data using a data dictionary and dynamic performance views

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Analyzing data using Statspack reports

      Getting ready

      How to do it...

      How it works...

      There's more...

      Collecting different amounts of data

      Producing a report on a specific SQL

      Automating snapshot generation

      Statspack maintenance

      Diagnosing performance issues using the alert log

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Analyzing data using Automatic Workload Repository (AWR)

      Getting ready

      How to do it...

      How it works...

      There's more...

      Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      A working example

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      2. Optimizing Application Design

      Introduction

      Optimizing connection management

      Getting ready

      How to do it...

      How it works...

      There's more...

      Dedicated server versus shared server

      Web applications

      Client-server Online Transaction Processing

      Batch processing

      See also

      Improving performance sharing reusable code

      Getting ready

      How to do it...

      How it works...

      There's more...

      PL/SQL and parsing

      Diagnosing soft and hard parsing

      See also

      Reducing the number of requests to the database using stored procedures

      How to do it...

      How it works...

      There's more...

      See also

      Reducing the number of requests to the database using sequences

      How to do it...

      How it works...

      There's more...

      Correct definition of a sequence

      See also

      Reducing the number of requests to the database using materialized views

      How to do it...

      How it works...

      There's more...

      Materialized views in depth

      Materialized views and grants

      Database parameters to use query rewrite

      Can I use materialized views in an OLTP environment?

      Optimizing performance with schema denormalization

      Getting ready

      How to do it...

      How it works...

      There's more...

      Not 1NF structures

      Avoiding dynamic SQL

      How to do it...

      How it works...

      There's more...

      See also

      3. Optimizing Storage Structures

      Introduction

      Avoiding row chaining

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Avoiding row migration

      How to do it...

      How it works...

      There's more...

      Estimating table size with different PCTFREE parameter

      Using LOBs

      Getting ready

      How to do it...

      How it works...

      There's more...

      Using index clusters

      How to do it...

      How it works...

      There's more...

      Cluster size

      Cluster index

      Clustering and truncating

      Using hash clusters

      How to do it...

      How it works...

      There's more...

      Sorted hash clusters

      Custom hash function

      Single-table hash clusters

      Indexing the correct way

      How to do it...

      How it works...

      There's more...

      What is the small percentage of the data which assures we can improve performances using B-tree indexes?

      See also

      Rebuilding index

      Getting ready

      How to do it...

      How it works...

      There's more...

      Index rebuild and statistics

      See also

      Compressing indexes

      How to do it…

      How it works...

      There's more...

      Using reverse key indexes

      How to do it...

      How it works...

      There's more...

      Using bitmap indexes

      How to do it...

      How it works...

      There's more...

      Bitmap join index

      See also

      Migrating to index organized tables

      How to do it...

      How it works...

      There's more...

      INCLUDING, OVERFLOW, PCTTHRESHOLD

      Logical ROWID

      See also

      Using partitioning

      How to do it...

      How it works...

      There's more...

      List partitioning

      Hash partitioning

      Composite partitioning

      4. Optimizing SQL Code

      Introduction

      Using bind variables

      Getting ready

      How to do it...

      How it works...

      There's more...

      Concurrency and scalability

      Security issues

      See also

      Avoiding full table scans

      How to do it...

      How it works...

      There's more...

      The High-Water Mark

      PctFree, PctUsed, and FREELISTs

      See also

      Exploring index lookup

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Exploring index skip-scan and index range-scan

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Introducing arrays and bulk operations

      How to do it...

      How it works...

      There's more...

      When to use direct path load

      See also

      Optimizing joins

      How to do it...

      How it works...

      There's more...

      See also

      Using subqueries

      How to do it...

      How it works...

      There's more...

      Tracing SQL activity with SQL Trace and TKPROF

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      5. Optimizing Sort Operations

      Introduction

      Sorting—in-memory and on-disk

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Sorting and indexing

      How to do it...

      How it works...

      There's more...

      See also

      Writing top n queries and ranking

      How to do it...

      How it works...

      There's more...

      See also

      Using count, min/max, and group-by

      How to do it...

      How it works...

      There's more...

      See also

      Avoiding sorting in set operations: union, minus, and intersect

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Troubleshooting temporary tablespaces

      How to do it...

      How it works...

      There's more...

      Optimal storage parameters for temporary tablespaces

      See also

      6. Optimizing PL/SQL Code

      Introduction

      Using bind variables and parsing

      How to do it...

      How it works...

      There's more...

      See also

      Array processing and bulk-collect

      How to do it...

      How it works...

      There's more...

      See also

      Passing values with NOCOPY (or not)

      How to do it...

      How it works...

      There's more...

      Using short-circuit IF statements

      How to do it...

      How it works...

      There's more...

      Avoiding recursion

      How to do it...

      How it works...

      There's more...

      See also

      Using native compilation

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Taking advantage of function result cache

      How to do it...

      How it works...

      There's more...

      See also

      Inlining PL/SQL code

      How to do it...

      How it works...

      There's more...

      See also

      Using triggers and virtual columns

      How to do it...

      How it works...

      There's more...

      Using WHEN and OF in trigger definition

      Avoid FOR EACH ROW in triggers, when possible

      See also

      7. Improving the Oracle Optimizer

      Introduction

      Exploring optimizer hints

      How to do it...

      How it works...

      There's more...

      Errors in hints

      See also

      Collecting statistics

      How to do it...

      How it works...

      There's more...

      Lock table statistics for load or highly volatile tables

      Other procedures in DBMS_STATS

      See also

      Using histograms

      How to do it...

      How it works...

      There's more...

      Height-based and value-based (frequency) histograms

      See also

      Managing stored outlines

      Getting ready

      How to do it...

      How it works...

      There's more...

      Private and public stored outlines

      See also

      Introducing Adaptive Cursor Sharing for bind variable peeking

      How to do it...

      How it works...

      There's more...

      See also

      Creating SQL Tuning Sets

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Using the SQL Tuning Advisor

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Configuring and using SQL Baselines

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      8. Other Optimizations

      Introduction

      Caching results with the client-side result cache

      Getting ready

      How to do it...

      How it works...

      There's more...

      Configuring the client-side result cache

      See also

      Enabling parallel SQL

      Getting ready

      How to do it...

      How it works...

      There's more...

      Parallel query and I/O

      When to use parallel SQL

      See also

      Direct path inserting

      How to do it...

      How it works...

      There's more...

      See also

      Using create table as select

      How to do it...

      How it works...

      There's more...

      See also

      Inspecting indexes and triggers overhead

      How to do it...

      How it works...

      There's more...

      See also

      Loading data with SQL*Loader and Data Pump

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      9. Tuning Memory

      Introduction

      Tuning memory to avoid Operating System paging

      How to do it...

      How it works...

      There's more...

      See also

      Tuning the Library Cache

      How to do it...

      How it works...

      There's more...

      How to minimize misses

      See also

      Tuning the Shared Pool

      How to do it...

      How it works...

      There's more...

      Tuning the Dictionary Cache

      See also

      Tuning the Program Global Area and the User Global Area

      How to do it...

      How it works...

      There's more...

      See also

      Tuning the Buffer Cache

      How to do it...

      How it works...

      There's more...

      See also

      10. Tuning I/O

      Introduction

      Tuning at the disk level and strategies to distribute Oracle files

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Striping objects across multiple disks

      How to do it...

      How it works...

      There's more...

      See also

      Choosing different RAID levels for different Oracle files

      Getting ready

      How to do it...

      How it works...

      There's more...

      RAID level 0

      RAID level 1

      RAID level 5

      RAID level 0+1

      See also

      Using asynchronous I/O

      How to do it...

      How it works...

      There's more...

      See also

      Tuning checkpoints

      How to do it...

      How it works...

      There's more...

      See also

      Tuning redo logs

      How to do it...

      How it works...

      There's more...

      See also

      11. Tuning Contention

      Introduction

      Detecting and preventing lock contention

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Investigating transactions and concurrency

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Tuning latches

      How to do it...

      How it works...

      There's more...

      See also

      Tuning resources to minimize latch contention

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Minimizing latches using bind variables

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      A. Dynamic Performance Views

      ALL_OBJECTS

      Fields

      DBA_BLOCKERS

      Fields

      See also

      DBA_DATA_FILES

      Fields

      See also

      DBA_EXTENTS

      Fields

      See also

      DBA_INDEXES

      Fields

      DBA_SQL_PLAN_BASELINES

      Fields

      DBA_TABLES

      Fields

      DBA_TEMP_FILES

      Fields

      See also

      DBA_VIEWS

      Fields

      DBA_WAITERS

      Fields

      See also

      INDEX_STATS

      Fields

      See also

      DBA_SEQUENCES

      Fields

      DBA_TABLESPACES

      Fields

      DBA_TAB_HISTOGRAMS

      Fields

      V$ADVISOR_PROGRESS

      Fields

      V$BUFFER_POOL_STATISTICS

      Fields

      See also

      V$CONTROLFILE

      Fields

      See also

      V$DATAFILE

      Fields

      See also

      V$DB_CACHE_ADVICE

      Fields

      See also

      V$DB_OBJECT_CACHE

      Fields

      See also

      V$ENQUEUE_LOCK

      Fields

      See also

      V$FILESTAT

      Fields

      See also

      V$FIXED_TABLE

      Fields

      V$INSTANCE_RECOVERY

      Fields

      V$LATCH

      Fields

      See also

      V$LATCH_CHILDREN

      Fields

      See also

      V$LIBRARYCACHE

      Fields

      V$LOCK

      Fields

      See also

      V$LOCKED_OBJECT

      Fields

      See also

      V$LOG

      Fields

      See also

      V$LOG_HISTORY

      Fields

      See also

      V$LOGFILE

      Fields

      See also

      V$MYSTAT

      Fields

      See also

      V$PROCESS

      Fields

      See also

      V$ROLLSTAT

      Fields

      V$ROWCACHE

      Fields

      V$SESSION

      Fields

      See also

      V$SESSION_EVENT

      Fields

      See also

      V$SESSTAT

      Fields

      See also

      V$SGA

      Fields

      See also

      V$SGAINFO

      Fields

      See also

      V$SHARED_POOL_RESERVED

      Fields

      V$SORT_SEGMENT

      Fields

      V$SQL

      Fields

      See also

      V$SQL_PLAN

      Fields

      See also

      V$SQLAREA

      Fields

      See also

      V$STATNAME

      Fields

      See also

      V$SYSSTAT

      Fields

      See also

      V$SYSTEM_EVENT

      Fields

      V$TEMPFILE

      Fields

      V$TEMPSTAT

      Fields

      See also

      V$WAITSTAT

      Fields

      See also

      X$BH

      Fields

      B. A Summary of Oracle Packages Used for Performance Tuning

      DBMS_ADDM

      Procedures

      DBMS_ADVISOR

      Procedures

      DBMS_JOB

      Procedures

      DBMS_LOB

      Procedures

      DBMS_MVIEW

      Procedures

      DBMS_OUTLN

      Procedures

      DBMS_OUTLN_EDIT

      Procedures

      DBMS_SHARED_POOL

      Procedures

      DBMS_SPACE

      Procedures

      DBMS_SPM

      Procedures

      DBMS_SQL

      Procedures

      DBMS_SQLTUNE

      Procedures

      DBMS_STATS

      Procedures

      DBMS_UTILITY

      Procedures

      DBMS_WORKLOAD_REPOSITORY

      Procedures

      Index

      Oracle Database 11gR2 Performance Tuning Cookbook

      Oracle Database 11gR2 Performance Tuning Cookbook

      Copyright © 2012 Packt Publishing

      All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

      Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

      Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

      First published: January 2012

      Production Reference: 1050112

      Published by Packt Publishing Ltd.

      Livery Place

      35 Livery Street

      Birmingham, B27 6PA, UK.

      ISBN 978-1-84968-260-2

      www.packtpub.com

      Cover Image by Stanford Murray (<stanmoore@live.com>)

      Credits

      Author

      Ciro Fiorillo

      Reviewers

      April C. Sims

      Advait V. Deo

      Asif Momen

      Paolo Napoletano

      Acquisition Editor

      Dhwani Devater

      Development Editors

      Kartikey Pandey

      Pallavi Iyenger

      Technical Editors

      Vanjeet D’souza

      Conrad Sardinha

      Merwine Machado

      Copy Editors

      Laxami Subramanian

      Brandt D’Mello

      Neha Shetty

      Project Coordinator

      Vishal Bodwani

      Proofreader

      Aaron Nash

      Indexers

      Monica Ajmera Mehta

      Rekha Nair

      Tejal Daruwale

      Graphics

      Manu Joseph

      Production Coordinators

      Prachali Bhiwandkar

      Shantanu Zagade

      Cover Work

      Prachali Bhiwandkar

      About the Author

      Ciro Fiorillo is an IT professional and consultant with more than a decade of experience in different roles (Developer, Analyst, DBA, Project Manager, Data and Software Architect) among software industries. He is an Oracle DBA Certified Professional and he has worked on different technologies and architectures, such as Oracle, SQL Server, Delphi, C# and .NET Framework, C/C++, Java, Flex, PHP, COBOL, Fortran, and Tibco.

      He is based in Italy, near Naples, in the beautiful and historic Ercolano.

      Ciro is currently employed as Information Systems Manager in a Financial Organization in Italy, and he is in charge of databases and systems management and development, coordinating the IT staff.

      As a freelancer, he writes articles for websites and printed magazines about software and computing, participates in workshops, and teaches C++ parallel programming with Intel Software tools.

      Ciro can be reached at <ciro@cirofiorillo.com>.

      Acknowledgement

      Writing a book was my dream and this is the first book I have written, so I would like to thank the entire staff at Packt Publishing, especially Dhwani Devater, who gave me the opportunity to write my first book, and in supporting me during the long way to the publication.

      I'd like to thank Vishal Bodwani, the project coordinator, who helped me in following the agreed timeline without too many delays and Lata Basantani, the project leader.

      I'd like to thank Kartikey Pandey and Pallavi Iyenger, the development editors, for their guidance and reviews.

      I'd like to thank Suzanne Ritter, the marketing executive, for the Behind the Book campaign.

      I'd also like to thank Vanjeet D'souza, Conrad Sardinha, and Merwine Machado, the technical editors, for their sincere efforts on this book.

      A special thanks to the technical reviewers: April Sims, Asif Momen, Advait Deo, and my friend Paolo Napoletano, for reviewing my errors and for helping me provide better content suggesting many improvements and helpful feedback.

      About the Reviewers

      April Sims is currently the Database Administrator at Southern Utah University and an Oracle Certified Professional: 8i, 9i, and 10g with a Master's degree in Business Administration from the University of Texas at Dallas. Involved as a volunteer with the Independent Oracle Users Group for over seven years, April is currently a Contributing Editor for the IOUG SELECT Journal. April is an annual presenter at Oracle OpenWorld, IOUG COLLABORATE, and numerous regional Oracle-related conferences.

      Advait V. Deo is a graduate from NIT, Nagpur and a post graduate from Birla Institute of Technology & Science (BITS), Pilani. After graduation he joined TCS and worked there for a couple of years. Later, he joined Oracle Corp. and worked closely with databases.

      He's had seven years' experience working in the database world, having spent time on many aspects of database till now starting from Oracle Version 8 until 11g. He mainly focuses on database performance tuning, integrating databases with front-end application, scripting, and automation. Currently he is working as a Lead Database Administrator in Amazon.com, Inc. (world leader in retail business), handling a fleet of around 300 production databases.

      Currently he resides in Hyderabad, India, with his wife Abha. In his time off from his busy work schedule, he spends quality time with his family, riding a bike, and watching movies.

      He updates some of his work and learning on his website at http://www.avdeo.com, whenever he gets time.

      Asif Momen has been working with Oracle technologies for over 12 years and has expertise in performance tuning and high availability. He has a master''s degree in Software Systems from Birla Institute of Technology & Science (BITS), Pilani.

      Asif is an Oracle ACE and is OCP-Certified DBA, Forms Developer, and RAC Expert. He is a speaker at Oracle OpenWorld and All India Oracle User Group (AIOUG). In addition, he is the Editor of Oracle Connect—the quarterly publication of AIOUG. His particular interests are Database tuning, Oracle RAC, Oracle Data Guard, and Backup and Recovery.

      Asif posts his ideas and opinions on The Momen Blog (http://momendba.blogspot.com). He can be reached at .

      www.PacktPub.com

      Support files, eBooks, discount offers and more

      You might want to visit www.PacktPub.com for support files and downloads related to your book.

      Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

      At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

      http://PacktLib.PacktPub.com

      Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books. 

      Why Subscribe?

      Fully searchable across every book published by Packt

      Copy and paste, print and bookmark content

      On demand and accessible via web browser

      Free Access for Packt account holders

      If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

      Instant Updates on New Packt Books

      Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.

      To my extraordinary wife, Monica, who helped me to make my dream come true, supporting me even when working late nights and on weekends.

      To my children, Miriam and Mario. You are the essence of my life.

      Preface

      People use databases to organize and to manage their data. Oracle Database is the leader in the relational database management systems market, with a broad adoption in many industries. Using the best tool is not enough to be sure that the results of our efforts will be satisfactory—driving the fastest car in a Formula 1 competition, though better than driving the slowest, doesn’t guarantee the first place at the checkered flag.

      Every developer—and every manager—knows that applications have to be responsive, because users hate to spend their time waiting for a transaction to end, looking at an hourglass. To meet this goal, it’s important to adopt a correct tuning strategy, which starts at the same time as the application design, then moves forward together, and will continue even when the application and the database are in production.

      Even though this is a cookbook on performance tuning, there are no silver bullets. Every recipe in this book will show us how to solve a problem with the correct approach, so when a similar problem arises in one of our databases, we can apply the correct solution even in different situations than the ones presented in the book.

      Before we start a database performance tuning process, we have to define what the tuning goals that we aim to reach are. As fast as possible is not a tuning goal. The primary tuning goal, generally speaking, is to reduce the response time or to reduce the resources needed to do a certain amount of work in the same time.

      At a lower level, to minimize response time we will try to:

      Reduce or eliminate waits

      Cache the largest number of blocks in memory

      Access the least number of data blocks (from disks)

      To increase the throughput and availability we will try to:

      Increment hit ratios

      Decrease system memory utilization

      Eliminate paging and swapping

      Reduce recovery time, decreasing the Mean Time To Recovery (MTTR)

      Increase load balancing (distributing data files to different disks) to reduce I/O times

      Increase scalability

      Before starting a tuning session, we have to define which are the goals, in terms of SLA, or define precise and measurable objectives. So at the end of the tuning process, we will know if we have reached the expected results. We will work to reduce the workload—so the same task will consume less resources, allowing other tasks to use those resources—and to minimize the response time.

      In this book, we will find many recipes that can help us reach these goals. Have a good read!

      What this book covers

      Chapter 1, Starting with Performance Tuning will show how to set up the example database, how to adopt a performance tuning process that can help in solving performance problems, and how to collect and analyze data from Oracle Database using various methods.

      Chapter 2, Optimizing Application Design presents the most common application design issues that prevent an application from running without performance issues. You will see how to improve database performance by sharing reusable code and by reducing the number of requests to the database by using various database objects.

      Chapter 3, Optimizing Storage Structures will show how to optimize the use of different database storage structures, presenting the optimal use for tables, clusters, indexes, and partitioning. You will see how to choose the appropriate structure to improve access time to your data, also analyzing the possible drawbacks in the operations that modify the data.

      Chapter 4, Optimizing SQL Code is focused on SQL code optimization. Throughout the chapter you will find many methods to diagnose and solve typical performance problems caused by poorly written SQL code. You will find answers on how (and when) to avoid full table scans, how to use indexes, bulk operations and arrays, join and subquery optimization. You will also see how to trace SQL activity to diagnose problems.

      Chapter 5, Optimizing Sort Operations will show the importance of optimizing sort operations to achieve better performance even when you don’t see any explicit sort operations in your SQL code. In this chapter, we will see the difference between in-memory and on-disk sort, how an index can improve the performance by reducing or avoiding sort operations, how to perform top-n queries, and how to use aggregate functions, and the use of set operations.

      Chapter 6, Optimizing PL/SQL Code will show how to optimize PL/SQL code in stored procedures, triggers, and user-defined functions. You will see the advantages of using bulk-collect and array processing, native compilation and function result cache.

      Chapter 7, Improving the Oracle Optimizer is focused on how to help the Oracle Optimizer in choosing the best execution plan using various tools, tricks, and tips, to obtain better performance. You will see the use of hints, statistics, histograms, stored outlines, adaptive cursor sharing, SQL tuning sets, and SQL baselines.

      Chapter 8, Other Optimizations will show how to use Client Side Result Cache, parallel SQL, CREATE TABLE AS SELECT, and direct path inserting to optimize performance in both queries and DML operations. You will also see how to use SQL*Loader and Data Pump to load data into your Oracle Database.

      Chapter 9, Tuning Memory will show how to avoid different memory-related issues, starting with Operating System paging. You will learn how to properly configure the library cache, the shared pool, the Program Global Area (PGA), the User Global Area (UGA), and the database buffer cache.

      Chapter 10, Tuning I/O will focus on how to optimize the I/O, learning how to distribute Oracle files and stripe objects on different disks, what RAID level is better for each type of database files. The use of asynchronous I/O, checkpoint and redo logs tuning are also discussed in this chapter.

      Chapter 11, Tuning Contention will show how to prevent, detect, and tune contention-related issues. You will see both lock and latch contention, why they occur, and how to prevent and solve any issue related to concurrency and contention in your database.

      In Appendix A, Dynamic Performance Views you will find a list of the most used dynamic performance views; for each view you will find a brief description and a list of the most useful fields of the view, to be used as a reference in your daily work.

      In Appendix B, A Summary of Oracle Packages Used for Performance Tuning you will find a brief summary of Oracle supplied packages useful in order to solve performance-related problems.

      What you need for this book

      You need an Oracle Database 11gR2 instance available on your system; you can download Oracle Software from Oracle Technology Network at the following site:

      http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

      In Chapter 1 there is a recipe on how to set up the example database to follow the recipes in this book and to use the code presented.

      Who this book is for

      This book is aimed at software developers, software and data architects, and DBAs who are beginning to use the Oracle Database, and want to solve performance problems faster and in a rigorous way.

      If you are an architect who wants to design fast performing applications, a DBA who is keen to dig into the causes of performance issues, or a developer who wants to learn why and where the application is running slowly this book will provide a good start for your career in performance tuning.

      Conventions

      In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

      Code words in text are shown as follows: Drop the MYSTATS table.

      A block of code is set as follows:

      SELECT

        C.CUST_FIRST_NAME, C.CUST_LAST_NAME

      FROM sh.CUSTOMERS C

      WHERE C.CUST_YEAR_OF_BIRTH = 1949;

      When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

      SELECT   C.CUST_FIRST_NAME, C.CUST_LAST_NAME

       

      FROM sh.CUSTOMERS C

      WHERE C.CUST_YEAR_OF_BIRTH = 1949;

      Any command-line input or output is written as follows:

      CONNECT sh@TESTDB/sh

      New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "From Oracle database 9iR2 onwards, Dynamic Sampling was introduced."

      Note

      Warnings or important notes appear in a box like this.

      Tip

      Tips and tricks appear like this.

      Reader feedback

      Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

      To send us general feedback, simply send an e-mail to <feedback@packtpub.com>, and mention the book title via the subject of your message.

      If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

      Customer support

      Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

      Downloading the example code

      You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

      Errata

      Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

      Piracy

      Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

      Please contact us at <copyright@packtpub.com> with a link to the suspected pirated material.

      We appreciate your help in protecting our authors, and our ability to bring you valuable content.

      Questions

      You can contact us at <questions@packtpub.com> if you are having a problem with any aspect of the book, and we will do our best to address it.

      Chapter 1. Starting with Performance Tuning

      Performance tuning is a complex process, which requires a deep knowledge of both physical and logical database structures. In this chapter, we will introduce the process and methodology to adopt in performance tuning an Oracle Database, covering the following recipes:

      Reviewing the performance tuning process

      Exploring the example database

      Acquiring data using a data dictionary and dynamic performance views

      Analyzing data using Statspack report

      Diagnosing performance issues using the alert log

      Analyzing data using Automatic Workload Repository (AWR)

      Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

      A working example

      Introduction

      There are a wide range of issues that could lead to poor performance. Performance of our Oracle database problems could be related to different areas of the system:

      Application design

      Application code

      Memory

      I/O

      Resource contention

      Operating System

      CPU

      When we want to tune a database in a proactive way, we can follow the previous list from the top to the bottom.

      Issues in the first two areas generally lead the database to very bad performance and to scalability issues. The most common performance problems in an Oracle database related to application design and coding are as follows:

      Incorrect session management

      Poorly designed cursor management

      Binding variables

      Cursor sharing

      Non-set operations

      Inadequate relational design

      Improper use of storage structures

      Let's explain each performance problem listed in the previous paragraph. Troubles related to memory, input/output, contention, and operating systems will be explored in the following chapters. A well-tuned application can lead to a significant performance improvement, so it's natural to concentrate the first efforts on performance tuning to application design and coding.

      Incorrect session management

      Poor session management can lead to scalability problems. For example, if a web page logs on to a database, gets some data, and logs off; the time spent for the log on procedure could be an order of magnitude greater than the time required to execute the queries needed to bring the data which the user has requested.

      Poorly designed cursor management

      There are different problems related to cursor management.

      The first rule in writing applications which connect to an Oracle database is to always use bind variables, which means not to include parameters in SQL statements as literals.

      For example, we could code something like the following (using SQL*Plus, connected as user HR):

      SQL>SELECT * FROM hr.jobs WHERE job_id = 'SA_MAN';

      This is equivalent to the following:

      SQL>VARIABLE JOBID VARCHAR2(10) SQL>EXEC :JOBID := 'SA_MAN' SQL>SELECT * FROM hr.jobs WHERE job_id = :JOBID;

      The big difference between the two examples is in the way the database parses the statements when they are called more than once with different values. Executing the statements the second time, in the first case will require a hard parse, whereas in the second case, Oracle will reuse the execution plan prepared at the time of the first execution, resulting in a huge performance gain.

      Note

      This behavior is due to the way Oracle checks whether a SQL statement is already in memory or needs to be parsed. A hash value of the

      Enjoying the preview?
      Page 1 of 1