Sign in
|
Register
|
Mobile
Home
Browse
About us
Help/FAQ
Advanced search
Home
>
Browse
>
Oracle Press
>
Oracle Database 10g Performance Tuning Tips & Techniques
CITATION
Niemiec, Richard
.
Oracle Database 10g Performance Tuning Tips & Techniques
.
US
: McGraw-Hill Osborne Media, 2007.
Add to Favorites
Email to a Friend
Download Citation
Oracle Database 10g Performance Tuning Tips & Techniques
Authors:
Richard Niemiec
Published:
June 2007
eISBN:
9780071596435 0071596437
|
ISBN:
9780072263053
Open eBook
Book Description
Table of Contents
Contents
Acknowledgments
Introduction
1 Oracle Database 10g New Features (DBA and Developer)
Installation Improvements
SYSAUX Tablespace
Automatic Storage Management
Cluster Ready Services (CRS)
Server-Generated Alerts
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
SQL Tuning Advisor
Automatic Shared Memory Management (ASMM)
Flash Recovery Area
Recycle Bin
Recovery Manager Changes
Transparent Data Encryption (10gR2)
LogMiner Changes
New DBMS_STATS Options
Tracing Enhancements
DBMS_SCHEDULER
Default (Permanent) Tablespace
Temporary Tablespace Groups
Rename Tablespaces
Bigfile Tablespaces
Shrinking Segments
Data Pump
Cross-Platform Transportable Tablespaces
Write to External Table
Automatic Undo Retention Tuning
V$SESSION Include New Information
OEM Changes
Grid Control
New Background Processes in 10g
Version Comparison Table
New Features Review
References
2 Basic Index Principles (Beginner Developer and Beginner DBA)
Basic Index Concepts
Concatenated Indexes
Suppressing Indexes
Using the NOT EQUAL Operators '<>', '!='
Using IS NULL or IS NOT NULL
Using Functions
Comparing Mismatched Data Types
Selectivity
The Clustering Factor
The Binary Height
Using Histograms
Fast Full Scans
Skip-Scans
Types of Indexes
B-Tree Indexes
Bitmap Indexes
Hash Indexes
Index-Organized Tables
Reverse Key Indexes
Function-Based Indexes
Partitioned Indexes
Bitmap Join Indexes
Fast Index Rebuilding
Tips Review
References
3 Disk Implementation Methodology and ASM (DBA)
Disk Arrays: Not a Choice Anymore
Use Disk Arrays to Improve Performance and Availability
How Many Disks Do We Need?
What Are Some of the RAID Levels Available?
The Newer RAID 5
Setup and Maintenance of the Traditional File System
What Is the Cost?
Distributing "Key" Data Files Across Hardware Disks
Storing Data and Index Files in Separate Locations
Avoiding I/O Disk Contention
Moving Data Files to Balance File I/O
Locally Managed Tablespaces
Creating Tablespaces as Locally Managed
Migrating Dictionary-Managed Tablespaces to Locally Managed
Oracle Bigfile Tablespaces
Oracle Managed Files
ASM Introduction
Communication Across IT Roles
ASM Instances
ASM init.ora Parameters
ASM Installation
ASM Parameters and SGA Sizing
ASM and Privileges
ASM Disks
ASM and Multipathing
ASM DiskGroups
ASM Diskgroups and Databases
ASM Redundancy and Failure Groups
New Space-Related Columns in Oracle Database 10g Release 2
Cluster Synchronization Services
Database Instances and ASM
Database Consolidation and Clustering with ASM
Database Processes to Support ASM
Bigfile and ASM
Database Init.ora Parameters to Support ASM
ASM and Database Deployment Best Practices
ASM Storage Management and Allocation
ASM Rebalance and Redistribution
Avoiding Disk Contention by Using Partitions
Getting More Information about Partitions
Other Types of Partitions
Other Partitioning Options
Index Partitioning
Exporting Partitions
Eliminating Fragmentation
Using the Correct Extent Size
Create a New Tablespace and Move the Segments to It
Exporting and Then Re-Importing the Table
To Avoid Chaining, Set Percents Correctly
Rebuilding the Database
Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for Speed
Determining If Redo Log File Size Is a Problem
Determining the Size of Your Log Files and Checkpoint Interval
Flash Recovery
Increasing Chances of Recovery: Committing after Each Batch
Using Rollback Segments
Avoiding Contention among Rollback Segments
Monitoring Rollback Segment Waits and Contention
Increasing Rollback Segments
Isolating Large Transactions to Their Own Rollback Segments
The Simpler Approach: UNDO Tablespace
Monitoring Undo Space
Killing Problem Sessions
Don't Sort in the SYSTEM or SYSAUX Tablespaces
Have Multiple Control Files on Different Disks and Controllers
Using Raw Devices to Improve I/O for Write-Intensive Data
Reasons for Using Raw Devices
Drawbacks
Other Disk I/O Precautions and Tips
Issues to Consider in the Planning Stages
Tips Review
References
4 Tuning the Database with Initialization Parameters (DBA)
Identifying Crucial Initialization Parameters
Changing the Initialization Parameters Without a Restart
Viewing the Initialization Parameters with Enterprise Manager
Increasing Performance by Tuning the DB_CACHE_SIZE
Using V$DB_CACHE_ADVICE in tuning DB_CACHE_SIZE
Keeping the Hit Ratio for the Data Cache Above 95 Percent
Monitoring the V$SQLAREA View to Find Bad Queries
Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads
Tuning the SHARED_POOL_SIZE for Optimal Performance
Using Oracle Multiple Buffer Pools
Tuning the PGA_AGGREGATE_TARGET for Optimal Use of Memory
Modifying the Size of Your SGA to Avoid Paging and Swapping
Understanding the Cost-Based Optimizer
Creating Enough Dispatchers
25 Important Initialization Parameters to Consider
Finding Undocumented Initialization Parameters
Understanding the Typical Server
Modeling a Typical Server
Sizing the Oracle Applications Database
Tips Review
References
5 Enterprise Manager and Grid Control (DBA and Developer)
The Enterprise Manager (EM) Basics
Starting with All Targets and Other Groupings
Policies (Violations) Tab
Monitoring the Database
Database Administration Tab
Database Administration Tab: Tablespaces
Database Administration Tab: Instance Level
Database Administration Tab: All Initialization Parameters
Database Administration Tab: Manage Optimizer Statistics
Database Administration Tab, Instance Level: Resource Manager (Consumer Groups)
Database Maintenance Tab
Database Topology Tab
Database Performance Tab
Monitoring the Hosts
Monitoring the Application Servers
Monitoring the Web Applications
Deployments Tab (Patching Options)
Jobs Tab
Reports Tab
Automatic Storage Management Performance
Summary
Tips Review
References
6 Using EXPLAIN and STORED OUTLINES (Developer and DBA)
The Oracle SQL TRACE Utility
Simple Steps for SQL TRACE with a Simple Query
The Sections of a TRACE Output
A More Complex TKPROF Output
Digging into the TKPROF Output
Using DBMS_MONITOR (10g New Feature)
TRCSESS Multiple Trace Files into One File (10g New Feature)
Using EXPLAIN PLAN Alone
EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?
Reading the EXPLAIN PLAN
Using DBMS_XPLAN
Yet Another EXPLAIN PLAN Output Method: Building the Tree Structure
Another Example Using the Tree Approach
Tracing/Explaining Problem Queries in Developer Products
Important Columns in the PLAN_TABLE Table
Helpful Oracle-Supplied Packages
Initialization Parameters for Undocumented TRACE
Using Stored Outlines
Using Plan Stability (Stored Outlines)
Tips Review
References
7 Basic Hint Syntax (Developer and DBA)
Top Hints Used
Use Hints Sparingly
Fix the Design First
Available Hints and Groupings
Execution Path
Access Methods
Query Transformation Hints
Join Operations
Parallel Execution
Other Hints
Specifying a Hint
Specifying Multiple Hints
When Using an Alias, Hint the Alias, Not the Table
The Hints
The FIRST_ROWS Hint
The ALL_ROWS Hint
The FULL Hint
The INDEX Hint
The NO_INDEX Hint
The INDEX_JOIN Hint
The INDEX_COMBINE Hint
The INDEX_ASC Hint
The INDEX_DESC Hint
The INDEX_FFS Hint
The ORDERED Hint
The LEADING Hint
The NO_EXPAND Hint
The DRIVING_SITE Hint
The USE_MERGE Hint
The USE_NL Hint
The USE_HASH Hint
The PUSH_SUBQ Hint
The PARALLEL Hint
The NO_PARALLEL Hint
The APPEND Hint
The NOAPPEND Hint
The CACHE Hint
The NOCACHE Hint
The CLUSTER Hint
The HASH Hint
The CURSOR_SHARING_EXACT Hint
The QB_NAME Hint
Some Miscellaneous Hints and Notes
Notes on Hints and Stored Outlines
Why Isn't My Hint Working?
Hints at a Glance
Tips Review
References
8 Query Tuning: Developer and Beginner DBA
What Queries Do I Tune? Querying V$SQLAREA
Selecting from the V$SQLAREA View to Find the Worst Queries
Selecting from the V$SQL View to Find the Worst Queries
New 10g Views for Locating Resource-Intensive Sessions and Queries
Selecting from V$SESSMETRIC to Find Current Resource-Intensive Sessions
Viewing Available AWR Snapshots
Selecting from the DBA_HIST_SQLSTAT View to Find the Worst Queries
Selecting Query Text from the dba_hist_sqlstat View
Selecting Query Explain Plan from the DBA_HIST_SQL_PLAN View
When Should an Index Be Used?
What Happens When I Forget the Index?
Creating an Index
Check the Index on a Table
What If I Create a Bad Index?
Caution Should Be Exercised When Dropping Indexes
Indexing the Columns Used in the SELECT and WHERE
The Fast Full Scan
A "Magically" Faster Query
Caching a Table in Memory
Using Multiple Indexes (Use the Most Selective)
The Index Merge
Indexes That Get Suppressed
Function-Based Indexes
The "Curious" OR
The EXISTS Function
That Table Is Actually a View!
SQL and Grand Unified Theory
Tuning Changes in Oracle Database 10g
10g Automatic SQL Tuning
Ensure That the Tuning User Has Access to the API
Create the Tuning Task
The Task Can Be Seen in the Advisor Log
Execute the SQL Tuning Task
Check Status of the Tuning Task
Displaying the SQL Tuning Advisor Report
Reviewing the Report Output
Tips Review
References
9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
Join Methods
NESTED LOOPS Joins
SORT-MERGE Joins
CLUSTER Joins
HASH Joins
Index Joins
Table Join Initialization Parameters
SORT-MERGE and HASH Join Parameters
A Two-Table Join: Equal-Sized Tables (Cost-Based)
A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)
Forcing a Specific Join Method
Eliminating Join Records (Candidate Rows) in Multitable Joins
A Two-Table Join Between a Large and Small Table
Three-Table Joins: Not as Much Fun (Cost-Based)
Bitmap Join Indexes
Bitmap Indexes
Third-Party Product Tuning
Tuning Distributed Queries
When You Have Everything Tuned
Miscellaneous Tuning Snippets
External Tables
Snapshot Too Old: Developer Coding Issue
Set Event to Dump Every Wait
14 Hours to 30 Seconds with the Exists Operator
Tuning at the Block Level (Advanced)
Key Sections of a Block Dump
Tuning Using Simple Mathematical Techniques
Traditional Mathematical Analysis
Seven-Step Methodology
Pattern Interpretation
Mathematical Techniques Conclusions
Join Tuning: Relational vs. Object-Relational Performance
Models Used
Results
Conclusion
Tips Review
References
10 Using PL/SQL to Enhance Performance (Developer and DBA)
Use DBMS_APPLICATION_INFO for Real-Time Monitoring
Use a Custom Replacement of DBMS_ APPLICATION_INFO for Real-Time Monitoring in a RAC Environment
Log Timing Information in a Database Table
Reduce PL/SQL Program Unit Iterations and Iteration Time
Use ROWID for Iterative Processing
Standardize on Data Types, IF Statement Order, and PLS_INTEGER
Ensure the Same Data Types in Comparison Operations
Order IF Conditions Based on the Frequency of the Condition
Use the PLS_INTEGER PL/SQL Data Type for Integer Operations
Reduce the Calls to SYSDATE
Reduce the Use of the MOD Function
Shared Pool and Pinning PL/SQL Objects
Pinning (Caching) PL/SQL Object Statements into Memory
Pinning All Packages
Identifying PL/SQL Objects That Need to Be Pinned
Using and Modifying DBMS_SHARED_POOL.SIZES
Finding Large Objects
Get Detailed Object Information from DBA_OBJECT_SIZE
Getting Contiguous Space Currently in the Shared Pool
Finding Invalid Objects
Finding Disabled Triggers
Use PL/SQL Associative Arrays for Fast Reference Table Lookups
Finding and Tuning the SQL When Objects Are Used
The Time Component When Working with DATE Data Types
Tuning and Testing PL/SQL
PL/SQL Object Location Implications
Use Rollback Segments to Open Large Cursors
Use Active Transaction Management to Process Large Quantities of Data
Use Temporary Database Tables for Increased Performance
Integrate a User Tracking Mechanism to Pinpoint Execution Location
Limit the Use of Dynamic SQL
Use Pipelined Table Functions to Build Complex Result Sets
Leave Those Debugging Commands Alone!
The "Look and Feel" Just for the Beginners
PL/SQL Example
Create a Procedure Example
Execute the Procedure from PL/SQL Example
Create a Function Example
Execute the get_cust_name Function from SQL
A Package Example
Database Trigger Example Using PL/SQL
Tips Review
References
11 Tuning RAC and Using Parallel Features
Real Application Clusters (RAC)
Parallel Databases
Architecture of Oracle RAC
Internal Workings of the Oracle RAC System
SCN Processing
RAC Performance Tuning Overview
RAC Wait Events and Interconnect Statistics
Cluster Interconnect Tuning – Hardware Tier
Tuning RAC Using Enterprise Manager Grid Control
Basic Concepts of Parallel Operations
Parallel DML and DDL Statements and Operations
Parallel DML Statements and Operations Since Oracle 9i
Parallelism and Partitions
Inter- and Intraoperation Parallelization
Examples of Using Inter- and Intraoperations (PARALLEL and NO_PARALLEL Hints)
Creating Table and Index Examples Using Parallel Operations
Real-World Example of Distributing Data for Effective Parallel Operations
Parallel DML Statements and Examples
Parallel DML Restrictions
Parallel DML Statement Examples
Monitoring Parallel Operations via the V$ Views
V$PQ_TQSTAT
V$PQ_SYSSTAT
V$PQ_SESSTAT
Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
Tuning Parallel Execution and the Oracle Initialization Parameters
Parallel Loading
Performance Comparisons and Monitoring Parallel Operations
Optimizing Parallel Operations in RAC
Objectives of Parallel Operations
RAC Parallel Usage Models
Initialization Parameters
V$ Views for Viewing Parallel Statistics
Parallel Configuration and Associated Baseline Test
Parallel Query Test Examples
Create Table As
Index Builds
Performance Considerations and Summary
Other Parallel Notes
Oracle Documentation Is Online
Tips Review
References
12 The V$ Views (Developer and DBA)
V$ View Creation and Access
Obtaining a Count and Listing of All V$ Views
Finding the X$ Tables Used to Create the V$ Views
Finding the Underlying Objects That Make Up the DBA_ views
Using Helpful V$ Scripts
Summary of Memory Allocated (V$SGA)
Detail of Memory Allocated (V$SGASTAT)
Finding Initialization Settings in V$PARAMETER
Determining Hit Ratio for Data (V$SYSSTAT)
Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)
Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)
Identifying PL/SQL Objects That Need to Be Kept (Pinned)
Finding Problem Queries by Querying V$SQLAREA
Finding Out What Users Are Doing and Which Resources They Are Using
Finding Out Which Objects a User Is Accessing
Using Indexes
Identifying Locking Issues
Killing the Problem Session
Finding Users with Multiple Sessions
Finding Disk I/O Issues
Finding Rollback Segment Contention
Determining Whether Freelists Are Sufficient
Checking Privileges and Roles
Wait Events V$ Views
Some of the Major V$ View Categories
Tips Review
References
13 The X$ Tables (Advanced DBA)
Introducing the X$ Tables
Misconceptions about the X$ Tables
Granting Access to View the X$ Tables
Creating V$ Views and X$ Tables
Obtaining a List of the X$ Tables That Make Up the V$ Views
Obtaining a List of All the X$ Tables
Obtaining a List of All the X$ Indexes
Using Hints with X$ Tables and Indexes
Shared Pool
Queries to Monitor the Shared Pool
ORA-04031 Errors
Large Allocations Causing Contention
Shared Pool Fragmentation
Low Free Memory in Shared and Java Pools
Library Cache Hit Ratio
High Number of Hard Parses
Latch Waits and/or Sleeps
Miscellaneous
Redo
Initialization Parameters
Case 1
Case 2
Case 3
Case 4
Case 5
Buffer Cache/Data Blocks
Buffer Statuses
Segments Occupying Block Buffers
Hot Data Blocks/Latch Contention and Wait Events
Instance/Database
Effective X$ Table Use and Strategy
Related Oracle Internals Topics
Traces
DBMS_TRACE Package
Events
Dumps
ORADEBUG
trcsess Utility
Reading the Trace File
Wait Information and Response Time
Recursive Calls
Module Information
Commit
Unmap
Bind Variables
Errors
Some Common X$ Table Groups
Some Common X$ Table and Non-V$ Fixed View Associations
Common X$ Table Joins
New Oracle 10gR1 X$ Tables
New Oracle 10gR2 X$ Tables
X$ Table Naming Conventions
Future Version Impact
Tips Review
References
14 Using STATSPACK and the AWR Report to Tune Waits and Latches
What's New in 10gR2 (10.2) STATSPACK
New Features in 10gR2 (10.2) STATSPACK
Installing STATSPACK
Security of the PERFSTAT Account
Post-Installation
Gathering Statistics
Running the Statistics Report
The Automatic Workload Repository (AWR) and the AWR Report
Manually Managing the AWR
AWR Automated Snapshots
AWR Snapshot Reports
Run the AWR Report in Oracle Enterprise Manager Grid Control
Interpreting the STATSPACK Output
The Header Information
The Load Profile
Instance Efficiency
Top Wait Events
Oracle Bugs
The Life of an Oracle Shadow Process
RAC Wait Events and Interconnect Statistics
Top SQL Statements
Instance Activity Statistics
Tablespace and File I/O Statistics
Segment Statistics
Additional Memory Statistics
UNDO Statistics
Latch Statistics
Tuning and Viewing at the Block Level (Advanced)
Dictionary and Library Cache Statistics
SGA Memory Statistics
Non-Default Initialization Parameters
Top 10 Things to Look for in AWR Report and STATSPACK Output
Managing the STATSPACK Data
Upgrading STATSPACK
Deinstalling STATSPACK
Quick Notes on the New ADDM Report
Scripts 10gR2
Tips Review
References
15 Performing a Quick System Review (DBA)
Total Performance Index (TPI)
Education Performance Index (EPI)
System Performance Index (SPI)
Memory Performance Index (MPI)
Buffer Hit Ratio
Dictionary Cache Hit Ratio
Library Cache Hit Ratio
PGA Memory Sort Ratio
Percent of Data Buffers Still Free
Top 10 "Memory Abusers" as a Percent of All Statements
Top 25 "Memory Abusers" Statements Tuned
Pinning/Caching Objects
Disk Performance Index (DPI)
Top 25 "Disk-Read Abuser" Statements Tuned
Top 10 Disk-Read Abusers as Percent of All Statements
Tables/Indexes Separated
Mission-Critical Table Management
Key Oracle Files Separated
Automatic UNDO Management
Temporary Segment Balance
Total Performance Index (TPI)
Overall System Review Example
Rating System
Example System Review Rating Categories
Items Requiring Immediate Action
Other Items Requiring Action
System Information List
Memory-Related Values
Disk-Related Values
CPU-Related Values
Backup- and Recovery-Related Information
Naming Conventions and/or Standards and Security Information
DBA Knowledge Rating
Other Items to Consider in Your TPI and System Review
Tips Review
References
16 Monitor the System Using Unix Utilities (DBA)
Unix/Linux Utilities
Using the sar Command to Monitor CPU Usage
sar –u (Check for CPU Bogged Down)
The sar –d Command (Find I/O Problems)
The sar –b Command (Check the Buffer Cache)
The sar –q Command (Check the Run Queue and Swap Queue Lengths)
Using the sar and vmstat Commands to Monitor Paging/Swapping
Using the –p Switch of sar to Report Paging Activities
Using the –w Switch of sar to Report Swapping and Switching Activities
Using the –r Switch of sar to Report Free Memory and Free Swap
Using the –g Switch of sar to Report Paging Activities
Using the –wpgr Switch of sar
Finding the Worst User on the System Using the top Command
Monitoring Tools
Using the uptime Command to Monitor CPU Load
Using the mpstat Command to Identify CPU Bottlenecks
Combining ps with Selected V$ Views
Using the iostat Command to Identify Disk I/O Bottlenecks
Using the –d Switch of iostat for Disk Drives sd15, sd16, sd17, and sd18
Using the –D Switch of iostat
Using the –x Switch of iostat
Combining –x Switch of iostat with Logic in a Shell Script
Using the ipcs Command to Determine Shared Memory
Using the vmstat Command to Monitor System Load
Monitoring Disk Free Space
The df Command
Monitoring Network Performance
Monitoring Using the spray Command
Monitoring Network Performance with nfsstat –c
Monitoring Network Performance with netstat
Displaying Current Values of Tunable Parameters
Modifying the Configuration Information File
Other Factors That Affect Performance
Tips Review
References
A: Key Initialization Parameters (DBA)
Desupported Initialization Parameters
Deprecated Initialization Parameters
Top 25 Initialization Parameters
Top 10 Initialization Parameters Not to Forget
Top 13 Undocumented Initialization Parameters (As I See It)
Listing of Documented Initialization Parameters (V$PARAMETER)
Listing of Undocumented Initialization Parameters (x$ksppi/x$ksppcv)
Oracle Applications 11i Recommendations (Note: 216205.1)
Top 10 Reasons Not to Write a Book
Tips Review
References
B: The V$ Views (DBA and Developer)
Creation of V$ and GV$ Views and X$ Tables
A List of Oracle 10g (10.2.0.1) GV$ and V$ Views
The Oracle 10g (10.2.0.1) V$ Views
Oracle 10g Scripts of the x$ Tables Used to Create the V$ Views
C: The X$ Tables (DBA)
Oracle 10g X$ Tables Ordered by Name
Oracle 10g X$ Indexes
Oracle 10g V$ Views Cross-Referenced to the X$ Tables
Oracle 10g X$ Tables Not Referenced by a GV$ View
Index
