®
Oracle Database 11g
New Features
Robert G. Freeman
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies, Inc. (Publisher). All rights reserved. Manufactured in the United States of
America. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or
distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the
publisher.
0-07-159578-3
The material in this eBook also appears in the print version of this title: 0-07-149661-0.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a
trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of
infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in
corporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-
hill.com or (212) 904-4069.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and
to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to
store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create
derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without
McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work
is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR
WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED
FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK
VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work
will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall
be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages
resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under
no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential
or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the
possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or
cause arises in contract, tort or otherwise.
DOI: 10.1036/0071496610
Professional
Want to learn more?
We hope you enjoy this
McGraw-Hill eBook! If
you’d like more information about this book,
its author, or related books and websites,
please click here.
This book is dedicated to my wife and companion Lisa, who is always there
for me, patient with me, and loves me in spite of my many faults. This book
is dedicated to my kids, Felicia, Sarah, Jacob, Jared, and Elizabeth, who are
the light of my life even if they don’t know this all the time.
About the Author
Robert G. Freeman is a principal DBA and Team Manager at the Church of
Jesus Christ of Latter-day Saints. He has been working with Oracle now for
almost two decades and is the author of over a dozen different works on
Oracle. He resides in Salt Lake City, Utah, with his lovely wife Lisa, three
of his five kids, two grumpy cats, two airplanes, and a dog that whines all
the time.
About the Contributor
Arup Nanda has been an Oracle DBA for more than 12 years working on
all challenges an Oracle DBA can possibly face—from modeling to
performance tuning to disaster recovery and even people problems. He is
a frequent speaker at many Oracle-related conferences and has coauthored
four books and numerous articles including the Oracle Database 10g and
11g New Features series on Oracle Technology Network. In 2003, Oracle
Magazine chose him as DBA of the Year. He lives in Connecticut with his
wife Anindita and son Anish.
About the Technical Editor
Peter Sharman has 18 years of IT experience designing, implementing, and
managing the performance of Oracle solutions. As a solo consultant and
team leader, Pete has provided administrative and technical leadership to
leading Internet-based businesses, as well as several Fortune 100 and
Fortune 500 companies. He has also completed world-class benchmarks
and implementation reviews of the Oracle RDBMS, and performed high-
impact performance tuning. A proven technical leader, Pete has acquired
expert-level skills in Real Application Clusters (RAC) database design,
administration, backup and recovery, operations planning and management,
performance management, system management, and security and
management of complex data centers. Pete has also presented at numerous
conferences around the world, and written a book on Oracle architecture
and administration. Pete has passed all the Oracle DBA Certifications, as
well as being a qualified Oracle9i/10g Certified Master.
Currently, Pete is part of the RAC Quality Assurance group in Server
Technologies Development at Oracle, testing the quality of all parts of the
Oracle code base when run in a RAC environment.
For more information about this title, click here
Contents
FOREWORD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
1 Oracle Database 11g Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Installing Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
The Database Configuration Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Upgrading to Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Saving Time When Upgrading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Supported Upgrade Paths . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Supported Upgrade Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Upgrade with DBUA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Manual Upgrades . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Using Export/Import for Upgrades and Rollback . . . . . . . . . . . . . . . . 23
Upgrade Using Data Copying . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Oracle Parameter Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
New Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Deprecated Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Obsolete Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Undo_Management Parameter Madness . . . . . . . . . . . . . . . . . . . . . . 28
Oracle Dictionary View Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2 Oracle Database New Management Features . . . . . . . . . . . . . . . . . . . . . . . 31
ASM-Related Changes and New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
New ASM-Related Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . 33
ASM Disk Group Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
ASM Fast Disk Resync . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
ASM Compatibility Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
ASM Preferred Mirror Read . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
ASM Rolling Upgrades . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
v
vi Oracle Database 11g New Features
ASM Support for Variable Allocation Unit Sizes . . . . . . . . . . . . . . . . . 38
New SYSASM Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
New asmcmd Commands ................................ 40
Automatic Memory Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Overview of Automatic Memory Management . . . . . . . . . . . . . . . . . 42
New Memory Advisor Functionality and Views . . . . . . . . . . . . . . . . . 45
How Is Oracle Managing My Memory? . . . . . . . . . . . . . . . . . . . . . . . 46
Automatic Memory Management and OEM . . . . . . . . . . . . . . . . . . . 47
Converting to Automatic Memory Management . . . . . . . . . . . . . . . . 48
ADDM New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
ADDM New Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
ADDM Now RAC-Aware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Managing ADDM Through DBMS_ADDM . . . . . . . . . . . . . . . . . . . . 50
Finding Classifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Directives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
AWR New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Default Retention of AWR Snapshots Changed . . . . . . . . . . . . . . . . . 55
AWR Baseline New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Adaptive Metric Thresholds . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Scheduler AutoTask Automated Maintenance Tasks . . . . . . . . . . . . . . . . . . . 60
AutoTask Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
AutoTask Dictionary Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Managing AutoTask Tasks via OEM . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Managing AutoTask Tasks Manually . . . . . . . . . . . . . . . . . . . . . . . . . . 65
AutoTask Maintenance Windows ........................... 67
Parameter File Management Changes and New Features . . . . . . . . . . . . . . . 69
Read/Write Error Handling of SPFILES . . . . . . . . . . . . . . . . . . . . . . . . 69
Easier Conversion to the Use of SPFILES . . . . . . . . . . . . . . . . . . . . . . 69
Users Are Prevented from Setting Invalid Values in SPFILES . . . . . . . . 70
Resource Manager Changes and New Features . . . . . . . . . . . . . . . . . . . . . . 70
IO Calibration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Default Maintenance Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Built-In Resource Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Resource Manager Statistics in AWR ........................ 74
Resource Manager Plan Directive New Features . . . . . . . . . . . . . . . . 74
Finer-Grained Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
DDL WAIT Option Now Default . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
New Add Column Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
3 Oracle Database New Availability and Recovery Features . . . . . . . . . . . . . . 81
Fault Diagnosability Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
The Automatic Diagnostic Repository (ADR) . . . . . . . . . . . . . . . . . . . 82
The Alert Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Contents vii
Trace, Dump, and Core Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
The Support Workbench . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
RMAN New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Interfile Backup Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Faster Backup Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Active Database Duplication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Improved Handling of Long-Term Backups . . . . . . . . . . . . . . . . . . . . 106
Backup Failover for Archived Redo Logs . . . . . . . . . . . . . . . . . . . . . . 107
Archived Redo Log Deletion Policy Enhancements . . . . . . . . . . . . . . 107
Recovery Catalog Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Undo Backup Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Block Media Recovery Performance Improved . . . . . . . . . . . . . . . . . 109
Other RMAN New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Oracle Flashback-Related New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Oracle Flashback Transaction Backout . . . . . . . . . . . . . . . . . . . . . . . 111
Oracle Flashback Data Archives . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Oracle Standby Database New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Lost-Write Detection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Compression of Archived Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . 120
Real-Time Query Capabilities from a Physical Standby Database . . . 120
Snapshot Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Oracle Data Pump New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Exp Utility Deprecated . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Compression of Dump File Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Data Pump Encryption Enhancements . . . . . . . . . . . . . . . . . . . . . . . . 123
Data Pump Data Remapping (Obfuscation) . . . . . . . . . . . . . . . . . . . 123
Data Pump Rename Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Data Pump and Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Overwrite Dump Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Data Pump Data_Options Parameter . . . . . . . . . . . . . . . . . . . . . . . . . 125
The Transportable Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
4 Oracle Database Advisors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
The Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
The SQL Repair Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
The SQL Access Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
The Streams Performance Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Oracle Database 10g Database Advisor Views . . . . . . . . . . . . . . . . . . 147
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
5 Oracle Database Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Database Replay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Using Oracle Database Replay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Database Replay—Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
viii Oracle Database 11g New Features
Database Replay Workload Support and Limitations . . . . . . . . . . . . . 152
Database Replay—Capture Workload . . . . . . . . . . . . . . . . . . . . . . . . 153
Database Replay—PreProcess the Captured Workload . . . . . . . . . . . 165
Database Replay—Replay Workload . . . . . . . . . . . . . . . . . . . . . . . . . 168
The SQL Performance Analyzer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Overview of SQL Performance Analyzer . . . . . . . . . . . . . . . . . . . . . . 181
SQL Performance Analyzer via OEM . . . . . . . . . . . . . . . . . . . . . . . . . 181
SQL Performance Analyzer via PL/SQL . . . . . . . . . . . . . . . . . . . . . . . 188
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
6 Oracle Database 11g Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Password-Related Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Password Settings and the Default Profile . . . . . . . . . . . . . . . . . . . . . 197
Password Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Password Case Sensitivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Hacking Prevention with Failed Logon Delays . . . . . . . . . . . . . . . . . . 199
Password Hashing Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Default Password Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Fine-Grained Access Control on Network Services . . . . . . . . . . . . . . . . . . . . 202
Create the ACL and Define the Associated Privileges . . . . . . . . . . . . . 202
Assign the ACL to Network Hosts . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
ACL-Related Data Dictionary Views . . . . . . . . . . . . . . . . . . . . . . . . . 204
Tablespace Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Overview of Oracle Tablespace Encryption . . . . . . . . . . . . . . . . . . . . 204
Preparing the Database for Tablespace Encryption . . . . . . . . . . . . . . . 205
Creating Encrypted Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Encryption and Database Performance . . . . . . . . . . . . . . . . . . . . . . . 208
TDE and Log Miner, Logical Standby, and Streams . . . . . . . . . . . . . . . . . . . . 209
Oracle SECUREFILE LOBS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
7 Oracle Database BI and Data Warehousing New Features . . . . . . . . . . . . . 211
Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Interval Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Extended Composite Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Reference Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
System Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
System-Managed Domain Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Virtual Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
About Virtual Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Creating Tables with Virtual Columns . . . . . . . . . . . . . . . . . . . . . . . . 223
Partitioning Tables with Virtual Columns . . . . . . . . . . . . . . . . . . . . . . 225
Data Pump Single-Partition Imports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Contents ix
Materialized Views and Query Rewrite . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Online Redefinition for Tables with Materialized View Logs . . . . . . . 227
Query Rewrite During Refresh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Partition Change Tracking Refresh for Union All Mviews . . . . . . . . . . 227
New and Enhanced Materialized View Catalog Views . . . . . . . . . . . . 227
Query Rewrite Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
The Pivot and Unpivot Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
The Pivot Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
The Unpivot Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Table Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
8 Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
New set Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Fast Application Notification Events in an RAC Database . . . . . . . . . 241
Online Application Maintenance and Upgrade . . . . . . . . . . . . . . . . . . . . . . 241
New lock table Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Fewer Exclusive Locks Taken During Online Operations . . . . . . . . . . 242
Invisible Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Read-Only Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
SQL Query Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Client Side Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Regular Expression Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Named and Mixed Notation from SQL . . . . . . . . . . . . . . . . . . . . . . . 250
PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Create Triggers as Enabled or Disabled . . . . . . . . . . . . . . . . . . . . . . . 251
Create Trigger Follows Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Compound Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Inlining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
SIMPLE_INTEGER Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
PL/SQL Function Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Dynamic SQL and REF Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
PLW 06009 Warning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
PL/SQL Sequence Enhancement . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
PL/SQL Continue Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
9 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Enhanced Oracle Process Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Pending and Published Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
x Oracle Database 11g New Features
Recovering Previous Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Extended Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
PL/SQL Native Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
SQL Plan Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
SQL Plan Management Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Plan Capture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Use of SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Querying SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Evolving SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Managing SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Automatic SQL Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Overview of Automatic SQL Tuning . . . . . . . . . . . . . . . . . . . . . . . . . 286
Automatic SQL Tuning with OEM . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Manage Automatic SQL Tuning Manually . . . . . . . . . . . . . . . . . . . . . 291
Manual Creation and Use of SQL Tuning Sets . . . . . . . . . . . . . . . . . . . . . . . 296
Create the Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Create the SQL Tuning Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Load the SQL Tuning Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Link the SQL Tuning Set and the Task . . . . . . . . . . . . . . . . . . . . . . . . 298
Set Any Task Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Execute the Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Review the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Intelligent Cursor Sharing (Bind-Aware Peeking) . . . . . . . . . . . . . . . . . . . . . . 300
About Bind-Aware Peeking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Bind-Aware Peeking Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Starting a System with Bind-Aware Peeking . . . . . . . . . . . . . . . . . . . . 301
Temporary Tablespace Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Temporary Tablespace Shrink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
The DBA_TEMP_FREE_SPACE View . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Real-Time SQL Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Real-Time SQL Monitoring Overview . . . . . . . . . . . . . . . . . . . . . . . . 303
Real-Time SQL Monitoring Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Real-Time SQL Monitoring Report . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Control Real-Time SQL Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Control the Use of OEM Management Packs . . . . . . . . . . . . . . . . . . . . . . . . 305
End of Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
10 Other New Features and Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Real Application Clusters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
OCI Runtime Connection Load Balancing . . . . . . . . . . . . . . . . . . . . . 308
Using XA Transactions with RAC . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
RAC Configuration Assistants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Network Configuration Assistant (NetCA) . . . . . . . . . . . . . . . . . . . . . 310
Contents xi
Database Rolling Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Parallel Execution Honors Service Placement . . . . . . . . . . . . . . . . . . 311
Direct NFS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
XMLDB New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Binary XML Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Partitioning Support for XMP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
XQuery Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Database Native Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
XML DB Repository Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . 321
XML Developers Kit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Oracle JVM-Related Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Enhancements to Existing Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
The ojvmtc Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
JDBC 4.0 Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
JDK Support in Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . 328
New Oracle Supplied Packages and Procedures . . . . . . . . . . . . . . . . . . . . . . 328
A Arup’s Top Ten Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Arup’s Top Feature # 1: Database Replay . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Arup’s Top Feature # 2: SQL Performance Analyzer . . . . . . . . . . . . . . . . . . . 334
Arup’s Top Feature # 3: Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Arup’s Top Feature # 4: Transparent Tablespace Encryption . . . . . . . . . . . . . . 336
Arup’s Top Feature # 5: Flashback Data Archive . . . . . . . . . . . . . . . . . . . . . . 337
Arup’s Top Feature # 6: SQL Plan Management . . . . . . . . . . . . . . . . . . . . . . 338
Arup’s Top Feature # 7: Private Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Arup’s Top Feature # 8: More Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Arup’s Top Feature # 9: Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Arup’s Top Feature # 10: Better-Quality PL/SQL Code . . . . . . . . . . . . . . . . . . 343
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
This page intentionally left blank
Foreword
s of the publication of this book, I’ve been working for Oracle
A Corporation for a little over 14 years—and in those 14 years, I’ve
seen 14 major releases of the Oracle Database go production—from
version 6 through 7, 8, 8i, 9i and 10g—all the way to the current
release, Oracle Database 11g Release 1.
Every new release brings with it hundreds of new features and changes, and
Oracle Database 11g Release 1 is no exception.
Over time, the question everyone asks is “How do you keep up with all of this
change?” Enter Robert Freeman and Arup Nanda, two very respected names in the
Oracle community. Robert and Arup together continue their long tradition of
educating and participating in the Oracle community with the release of this book
Oracle Database 11g New Features. Both are active users of the Oracle Database
software—engaged in the day-to-day production administration of large Oracle
instances—and therefore write from the standpoint of someone who uses the
software every day. It is this perspective, from the viewpoint of production DBAs,
that makes this book both unique and useful.
This book introduces and provides examples of using many of the new 11g
features and functions—educating the reader as to the overall intent and purpose of
the functionality as well as demonstrating how it is implemented and how to use it.
Robert and Arup cover everything from new database management features to
availability and recovery (the authors are well known in the Oracle universe for
their solid knowledge of backup and recovery).
Oracle Database 11g Release 1 will forever change the way Database
Administrators will approach upgrades and changes—with the introduction of Real
Application Testing and the Database Replay feature. The authors dedicated an
entire chapter of the book to this database option—a section I truly appreciate,
given that real-world testing is the only way to introduce change in a production
system. The authors give you the information you need to get started with this
feature and understand what it does and does not do.
xiii
xiv Oracle Database 11g New Features
The chapter on performance tuning and large databases will be one many people will
skip right to and start with. Over time, Oracle has added many features to the database to
facilitate performance tuning as well as features to make things “go faster.” The authors
cover the most relevant new additions in Oracle Database 11g Release 1 including the
new partitioning features and SQL plan management.
All in all, this book will make understanding what Oracle Database 11g Release 1
means to you. Filled with explanations written for DBAs and developers by a pair of
DBAs/developers, this book will be a virtual roadmap to understanding this new release.
Enjoy.
—Tom Kyte, Oracle Corporation
Acknowledgments
ight off the bat I should say that I don’t like to name names. When
R you do that, inevitably people get forgotten, and feelings get hurt,
so only a few names will be mentioned directly here.
The creation of any book takes the work of so many people. First
and foremost, thanks to my wife and companion Lisa who puts up
with me spending my time writing (at least that’s what I’m calling it). Thanks to my
kids, who constantly come into my office to remind me that I’m a father, not just a
writer.
Thanks to my dad, who gave me my drive to succeed and never quit.
Thanks to all my co-workers and friends at the Church of Jesus-Christ of Latter-
day Saints where I work. They are too numerous to mention here, but they are a
great bunch of folks to work with.
An acknowledgement to all those whom I have worked with in the past is most
in order. I dare not print a list of all those people for fear of leaving someone out. To
all of you I owe more thanks than I can say. Thanks to previous employers who
gave me wonderful opportunities.
Super-duper thanks to Tom Kyte for writing an incredible introduction. Thanks,
Tom!!
Thanks to Arup Nanda for his great “Arup Says” contributions. Arup really adds
so much value to this book and it is a much better work with his additions.
Thanks to Pete Sharman, my long-time friend and the technical editor of this
book. He did a great job, and was brutally honest when something wasn’t up to
snuff.
A special thanks to Chapter 10 contributors Dan Norris and Kyle Brokaw.
Without their assistance this book would not have been as complete. They each
did an awesome job, and I appreciate their contributions.
xv
xvi Oracle Database 11g New Features
Thanks to all the folks at Oracle who helped with this book as it was being written.
Thanks to the beta staff and the metalink support staff and development for all the
assistance I received from you!
Thanks to my friends, including those in and out of the Oracle community. One in
particular was going through some rough times during the writing of this book. Divorce is
an ugly business and I wish I could have been more help for you, my friend. I hope 2008
is a better year.
Last but not least, thanks to all the folks at Oracle Press who have made this book
better than it would have otherwise been. Lisa McClain is tops. She’s been there for the
last several of my books and always keeps me in line. Vasundhara Sawhney was a great
help getting this thing put together and Mandy Canales kept me on schedule, making sure
this book got out on time. Thanks to everyone else at Oracle Press for the hard work and
dedication!
Introduction
racle Database 11g is the newest release of Oracle’s flagship
O database product. It contains a number of new innovations, which
we cover in the pages of this book. This is my third Oracle “New
Features” book for Oracle Press, and I’ve actually been writing them
since Oracle 8.0 was released. For each book, it’s been an
incredible experience to go through the product, find the new features, and learn
how to use them.
It can be difficult to write books about new features. First, you don’t start out
running the production product. You write using a beta copy of the software, and just
hope that the final release does not change too much. Also, the marketing reality is
that publishers want to get books out to market. So there is always this battle
between quality and delivery. In fact, the folks at Oracle Press/McGraw Hill are
terrific about this, and understand the battle. In this book we have cut no corners.
We wrote initially on the beta, and after the production code came out we went
over the chapters with the production code looking for any changes.
The first new features book I wrote (in fact, my first book) was for upgrading
from Oracle 7.3 to Oracle 8. This first book was written with the upgrade exam in
mind. The book was written after Oracle 8 was released, so I had documentation at
hand. The first book written from beta code was Oracle9i New Features. The 9i Beta
was difficult. The documentation was not complete, and it was difficult to discover
any information on the new feature sets that were being released. Oracle Database
10g was easier. The beta was a much easier process and documentation was more
plentiful.
The Oracle Database 11g beta has been quite good in many respects. The
documentation and ancillary information on what was in the Oracle Database 11g
beta, and what was to come, was much more complete. As a result of this more
mature beta, I think this book is so far the best of all the new features books that I’ve
written. We started writing this book in early 2007, starting with the beta Oracle
xvii
xviii Oracle Database 11g New Features
product, and then went over it chapter by chapter with the production product to make
sure it was as accurate as it could be.
If you have read my new features books before, you will notice a bit of a difference in
this volume. We have given OEM quite a bit more coverage this time. I can honestly say
that I no longer hate OEM (which was not the case prior to, say, Oracle Database 10g).
You will find much more coverage of OEM functionality. However, fear not; I’ve also
tried to cover command-line methods as well, and in the few cases where it had to boil
down to command-line or OEM coverage, I went with command line.
This is a book principally for the early adopter of Oracle Database 11g. It was written
long before the Oracle certification exams were released, thus it is not a guide for those
exams. Still, this book covers a great deal of the functionality that will no doubt be
covered in the forthcoming certification process, so I suspect that it will help in one’s
attempt to pass the certification tests.
This book represents many hours of work on the part of numerous people. We all hope
you enjoy this book and that it helps you in your efforts to master Oracle Database 11g.