Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Oracle Database 11gR2 Performance Tuning Cookbook
  • Table Of Contents Toc
Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

By : Ciro Fiorillo
4 (4)
Buy this Book
close
close
Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

4 (4)
By: Ciro Fiorillo
Buy this Book

Overview of this book

Oracle's Database offers great performance, scalability, and many features for DBAs and developers. Due to a wide choice of technologies, successful applications are good candidates to run into performance issues and when a problem arises it's very difficult to identify the cause and the right solution to the problem. The Oracle Database 11g R2 Performance Tuning Cookbook helps DBAs and developers to understand every aspect of Oracle Database that can affect performance. You will be guided through implementing the correct solution in a proactive way before problems arise, and how to diagnose issues on your Oracle database-based solutions. This fast-paced book offers solutions starting from application design and development, through the implementation of well-performing applications, to the details of deployment and delivering best-performance databases. With this book you will quickly learn to apply the right methodology to tune the performance of an Oracle Database, and to optimize application design and SQL and PL/SQL code. By following the real-world examples you will see how to store your data in correct structures and access and manipulate them at a lightning speed. You will learn to speed up sort operations, hack the optimizer and the data loading process, and diagnose and tune memory, I/O, and contention issues. The purpose of this cookbook is to provide concise recipes, which will help you to build and maintain a very high-speed Oracle Database environment.
Table of Contents (21 chapters)
close
close
close
Oracle Database 11gR2 Performance Tuning Cookbook
Icon
Oracle Database 11gR2 Performance Tuning Cookbook
Credits
Icon
Credits
About the Author
Icon
About the Author
Acknowledgement
Icon
Acknowledgement
About the Reviewers
Icon
About the Reviewers
www.PacktPub.com
Icon
www.PacktPub.com
Preface
Icon
Preface
Lock Free Chapter
1
Starting with Performance Tuning
chevron up
Icon
Starting with Performance Tuning
Icon
Introduction
Icon
Reviewing the performance tuning process
Icon
Exploring the example database
Icon
Acquiring data using a data dictionary and dynamic performance views
Icon
Analyzing data using Statspack reports
Icon
Diagnosing performance issues using the alert log
Icon
Analyzing data using Automatic Workload Repository (AWR)
Icon
Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
Icon
A working example
2
Optimizing Application Design
Icon
Optimizing Application Design
Icon
Introduction
Icon
Optimizing connection management
Icon
Improving performance sharing reusable code
Icon
Reducing the number of requests to the database using stored procedures
Icon
Reducing the number of requests to the database using sequences
Icon
Reducing the number of requests to the database using materialized views
Icon
Optimizing performance with schema denormalization
Icon
Avoiding dynamic SQL
3
Optimizing Storage Structures
Icon
Optimizing Storage Structures
Icon
Introduction
Icon
Avoiding row chaining
Icon
Avoiding row migration
Icon
Using LOBs
Icon
Using index clusters
Icon
Using hash clusters
Icon
Indexing the correct way
Icon
Rebuilding index
Icon
Compressing indexes
Icon
Using reverse key indexes
Icon
Using bitmap indexes
Icon
Migrating to index organized tables
Icon
Using partitioning
4
Optimizing SQL Code
Icon
Optimizing SQL Code
Icon
Introduction
Icon
Using bind variables
Icon
Avoiding full table scans
Icon
Exploring index lookup
Icon
Exploring index skip-scan and index range-scan
Icon
Introducing arrays and bulk operations
Icon
Optimizing joins
Icon
Using subqueries
Icon
Tracing SQL activity with SQL Trace and TKPROF
5
Optimizing Sort Operations
Icon
Optimizing Sort Operations
Icon
Introduction
Icon
Sorting—in-memory and on-disk
Icon
Sorting and indexing
Icon
Writing top n queries and ranking
Icon
Using count, min/max, and group-by
Icon
Avoiding sorting in set operations: union, minus, and intersect
Icon
Troubleshooting temporary tablespaces
6
Optimizing PL/SQL Code
Icon
Optimizing PL/SQL Code
Icon
Introduction
Icon
Using bind variables and parsing
Icon
Array processing and bulk-collect
Icon
Passing values with NOCOPY (or not)
Icon
Using short-circuit IF statements
Icon
Avoiding recursion
Icon
Using native compilation
Icon
Taking advantage of function result cache
Icon
Inlining PL/SQL code
Icon
Using triggers and virtual columns
7
Improving the Oracle Optimizer
Icon
Improving the Oracle Optimizer
Icon
Introduction
Icon
Exploring optimizer hints
Icon
Collecting statistics
Icon
Using histograms
Icon
Managing stored outlines
Icon
Introducing Adaptive Cursor Sharing for bind variable peeking
Icon
Creating SQL Tuning Sets
Icon
Using the SQL Tuning Advisor
Icon
Configuring and using SQL Baselines
8
Other Optimizations
Icon
Other Optimizations
Icon
Introduction
Icon
Caching results with the client-side result cache
Icon
Enabling parallel SQL
Icon
Direct path inserting
Icon
Using create table as select
Icon
Inspecting indexes and triggers overhead
Icon
Loading data with SQL*Loader and Data Pump
9
Tuning Memory
Icon
Tuning Memory
Icon
Introduction
Icon
Tuning memory to avoid Operating System paging
Icon
Tuning the Library Cache
Icon
Tuning the Shared Pool
Icon
Tuning the Program Global Area and the User Global Area
Icon
Tuning the Buffer Cache
10
Tuning I/O
Icon
Tuning I/O
Icon
Introduction
Icon
Tuning at the disk level and strategies to distribute Oracle files
Icon
Striping objects across multiple disks
Icon
Choosing different RAID levels for different Oracle files
Icon
Using asynchronous I/O
Icon
Tuning checkpoints
Icon
Tuning redo logs
11
Tuning Contention
Icon
Tuning Contention
Icon
Introduction
Icon
Detecting and preventing lock contention
Icon
Investigating transactions and concurrency
Icon
Tuning latches
Icon
Tuning resources to minimize latch contention
Icon
Minimizing latches using bind variables
1
Dynamic Performance Views
Icon
Dynamic Performance Views
Icon
ALL_OBJECTS
Icon
DBA_BLOCKERS
Icon
DBA_DATA_FILES
Icon
DBA_EXTENTS
Icon
DBA_INDEXES
Icon
DBA_SQL_PLAN_BASELINES
Icon
DBA_TABLES
Icon
DBA_TEMP_FILES
Icon
DBA_VIEWS
Icon
DBA_WAITERS
Icon
INDEX_STATS
Icon
DBA_SEQUENCES
Icon
DBA_TABLESPACES
Icon
DBA_TAB_HISTOGRAMS
Icon
V$ADVISOR_PROGRESS
Icon
V$BUFFER_POOL_STATISTICS
Icon
V$CONTROLFILE
Icon
V$DATAFILE
Icon
V$DB_CACHE_ADVICE
Icon
V$DB_OBJECT_CACHE
Icon
V$ENQUEUE_LOCK
Icon
V$FILESTAT
Icon
V$FIXED_TABLE
Icon
V$INSTANCE_RECOVERY
Icon
V$LATCH
Icon
V$LATCH_CHILDREN
Icon
V$LIBRARYCACHE
Icon
V$LOCK
Icon
V$LOCKED_OBJECT
Icon
V$LOG
Icon
V$LOG_HISTORY
Icon
V$LOGFILE
Icon
V$MYSTAT
Icon
V$PROCESS
Icon
V$ROLLSTAT
Icon
V$ROWCACHE
Icon
V$SESSION
Icon
V$SESSION_EVENT
Icon
V$SESSTAT
Icon
V$SGA
Icon
V$SGAINFO
Icon
V$SHARED_POOL_RESERVED
Icon
V$SORT_SEGMENT
Icon
V$SQL
Icon
V$SQL_PLAN
Icon
V$SQLAREA
Icon
V$STATNAME
Icon
V$SYSSTAT
Icon
V$SYSTEM_EVENT
Icon
V$TEMPFILE
Icon
V$TEMPSTAT
Icon
V$WAITSTAT
Icon
X$BH
2
A Summary of Oracle Packages Used for Performance Tuning
Icon
A Summary of Oracle Packages Used for Performance Tuning
Icon
DBMS_ADDM
Icon
DBMS_ADVISOR
Icon
DBMS_JOB
Icon
DBMS_LOB
Icon
DBMS_MVIEW
Icon
DBMS_OUTLN
Icon
DBMS_OUTLN_EDIT
Icon
DBMS_SHARED_POOL
Icon
DBMS_SPACE
Icon
DBMS_SPM
Icon
DBMS_SQL
Icon
DBMS_SQLTUNE
Icon
DBMS_STATS
Icon
DBMS_UTILITY
Icon
DBMS_WORKLOAD_REPOSITORY
3
Index
Icon
Index

Exploring the example database

In this recipe, we will prepare a database to use for our examples.

Getting ready

We need an Oracle Database 11gR2 system up and running to create our database. The host system could be a UNIX/Linux or Windows physical or virtual machine. If you want to use a virtual machine, be sure to follow the minimum CPU and memory requirements for the Oracle installation.

If you have installed the database software along with the Create Database option, then you have already set up a database with the necessary schema installed.

How to do it...

We will use the default demo database installed by the default OLTP template of Oracle Database Configuration Assistant (DBCA) for all our examples.

Note

You can find the official Oracle Database Installation Guide 11gR2 for Linux at http://download.oracle.com/docs/cd/E11882_01/install.112/e16763/toc.htm.

  1. Log on to the Operating System as a member of the administrative group, authorized to install Oracle software and to create and run database instances.

  2. Launch DBCA (for Windows users: Start | Programs | Oracle – home_name | Configuration and Migration Tools | Database Configuration Assistant) for *nix systems enter the following command at system prompt:

    $ dbca

    Please note that the dbca executable is by default in the $ORACLE_HOME/bin directory.

  3. A welcome screen is shown. Click Next.

  4. You are presented with some options. Select the first, namely Create a database, and click Next.

  5. You are presented a list of database templates. Choose the first, namely General purpose / OLTP, and click Next.

  6. You are asked for the global database name and SID; enter TESTDB in the global database name (the SID should be set accordingly) and click Next.

  7. In the next screen—shown in the following screenshot—leave the default options selected (OEM configuration). If you wish, you can enable e-mail notifications, checking the corresponding flag and entering the SMTP server to use (something like smtp.yourdomain.com or smtp.yourISP.com) and the e-mail address where the alerts will be delivered. Click Next to go to the next screen.

  8. Choose to use the same password for all administrative accounts, enter the password you want to use twice, and click Next. If you are advised that the password you entered is weak (not responding to the minimum complexity requirements) you can ignore the message and go on. Please note that for a production database these are very bad choices, but we are installing a demo database for testing purposes only and don't want to bother with security issues.

  9. In the next screen, leave the default option for the files position (Use Database File Locations from Template) and click Next.

  10. Leave the default options for the flash recovery area and click Next.

  11. In the next screen, check the Sample Schemas flag and click Finish.

  12. You are presented with the operations summary. Click OK and wait until the database creation process is finished.

  13. At the end of the creation process, we have to unlock the accounts created. In the summary form, there is a Password Manager button; click on it, and you will be presented with the list of accounts created.

  14. Find the following accounts: BI, HR, IX, OC, OE, PM, SH and uncheck the second column (unlocking them). Insert the password for the accounts in the last two columns, setting them the same as the account name.

    Tip

    You can click on the username column to sort accordingly.

    Don't use sample schemas or passwords the same as the username in production databases!

Now our TESTDB database is ready for experimenting.

How it works...

Oracle DBCA lets us create a database using predefined templates. For our examples, we will use the default example schemas provided by Oracle (which are installed in the EXAMPLE tablespace).

The sample schemas are HR (Human Resources), OE (Order Entry), OC (Order Catalog), PM (Product Media), IX (Information eXchange), SH (Sales History), and BI (Business Intelligence). We will use mostly HR and SH schemas.

There's more...

If we want to reset the sample schemas to the initial state, we can use the script mksample.sql located in the $ORACLE_HOME/demo/schema/ directory. This script requires eleven parameters, with the following syntax:

SQL>@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/

Note

Please note that the log_file_directory is an already existing folder and also the path must be terminated by a slash.

Our database—assuming test as the system and system password—will be reset with the following statement:

SQL>@?/demo/schema/mksample test test hr oe pm ix sh bi EXAMPLE TEMP testlog/

Note

Please note that in the default installation of Oracle Database 11gR2 Enterprise Edition the mksample.sql script is not present.

You can find it in the Companion CD.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Oracle Database 11gR2 Performance Tuning Cookbook
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon