| |
| |
| Preface | |
| |
| |
| Why This Book | |
| |
| |
| Who This Book is For | |
| |
| |
| How This Book is Organized | |
| |
| |
| Software and Hardware | |
| |
| |
| How to Use This Book | |
| |
| |
| How to Reach The Author | |
| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| Features of Oracle | |
| |
| |
| Objectives | |
| |
| |
| Conventions | |
| |
| |
| Performance versus Scalability | |
| |
| |
| |
| Getting Started with Oracle | |
| |
| |
| |
| Basic Concepts | |
| |
| |
| |
| Standard versus Flavored SQLS | |
| |
| |
| |
| Relational versus Object-Oriented Databases | |
| |
| |
| |
| An Instance versus a Database | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Installing Oracle Software | |
| |
| |
| |
| Installing Oracle 11g Server Software | |
| |
| |
| |
| Configuring a Listener | |
| |
| |
| |
| Creating an Oracle Database | |
| |
| |
| |
| Installing Oracle 11g Client Software | |
| |
| |
| |
| Oracle Grid Control versus DB Control | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Options for Accessing an Oracle Server | |
| |
| |
| |
| A Command Line Interface (CLI) versus a GUI-Based Console | |
| |
| |
| |
| The Oracle Enterprise Manager Java Console (OEMJC) | |
| |
| |
| |
| Using the SQLPlus Tool | |
| |
| |
| |
| Oracle Enterprise Manager DBConsole | |
| |
| |
| |
| Other Tools for Developers | |
| |
| |
| |
| Case Study: Creating ER Diagrams with Visio via ODBC | |
| |
| |
| |
| Case Study: Accessing Oracle in Java via JDBC | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| A Quick Tour of an Oracle Server | |
| |
| |
| |
| New Oracle Schemas Beyond "Scott" | |
| |
| |
| |
| Oracle Users versus Schemas | |
| |
| |
| |
| Tablespaces, Segments, Extents, and Data Blocks | |
| |
| |
| |
| Tables, Indexes and Index Types for Structured Data | |
| |
| |
| |
| Domain and LOB Index Types for Unstructured Data | |
| |
| |
| |
| Views, Materialized Views, and Synonyms | |
| |
| |
| |
| Stored Procedures, Functions, and Triggers | |
| |
| |
| |
| Referential Integrity with Foreign Keys | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Oracle Architecture From Performance and Scalability Perspectives | |
| |
| |
| |
| Understanding Oracle Architecture | |
| |
| |
| |
| The Version History of Oracle | |
| |
| |
| |
| Oracle Processes | |
| |
| |
| |
| Oracle Memory Areas | |
| |
| |
| |
| Dedicated versus Shared Oracle Server Architecture | |
| |
| |
| |
| Performance Sensitive Initialization Parameters | |
| |
| |
| |
| Oracle Static Data Dictionary Views | |
| |
| |
| |
| Oracle Dynamic Performance (V$) Views | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Oracle 10g Memory Management | |
| |
| |
| |
| SGA Sub-Areas | |
| |
| |
| |
| SGA Sizing: Automatic Shared Memory Management (ASMM) | |
| |
| |
| |
| PGA Sizing: PGA_AGGREGATE_TARGET | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Oracle 11g Memory Management | |
| |
| |
| |
| Automatic Memory Management (AMM) | |
| |
| |
| |
| Memory Sizing Options Configurable at Database Creation Time | |
| |
| |
| |
| Checking Memory Management and Usage Distribution at Run Time | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Oracle Storage Structure | |
| |
| |
| |
| Overview | |
| |
| |
| |
| Managing Tablespaces | |
| |
| |
| |
| Managing Data Files | |
| |
| |
| |
| Managing Redo Logs | |
| |
| |
| |
| Summary | |
| |
| |
| Recommended Reading | |
| |
| |
| Exercises | |
| |
| |
| |
| Oracle Wait Interface (OWI) | |
| |
| |
| |
| Ratio-based versus OWI-based Oracle Performance Tuning Methodologies | |
| |
| |
| |
| Wait Event�The Core Concept of OWI | |
| |
| |
| |
| Classification of Wait Events from OWI | |
| |
| |
| |
| The Other Part (CPU Time) of the Equation Elapsed Time� CPU Time � Wait Time | |
| |
| |
| AWR as a Compass to Tuning Oracle Performance and Scalability | |
| |
| |
| Summary | |
| |
| |
| Oracle Data Consistency and Concurrency | |
| |
| |
| Select� for Update Statement | |
| |
| |
| ACID Properties of Transactions | |
| |
| |
| Read Phenomena and Data Inconsistencies | |
| |
| |
| Oracle Isolation Levels | |
| |
| |
| Multi-Version Concurrency Control (MVCC) and Read Consistency | |
| |
| |
| Oracle Locks | |
| |
| |
| Lock Escalations versus Conversions | |
| |
| |
| Oracle Latches | |
| |
| |
| Oracle Enqueues | |
| |
| |
| Deadlocks | |
| |
| |
| Taking Advantage of Oracle�s Scalable Concurrency Model | |
| |
| |
| Case Study: A JDBC Example | |
| |
| |
| Summary | |
| |
| |
| Importance of Performance Statistics | |
| |
| |
| AWR Report Header | |
| |
| |
| Report Summary | |
| |
| |
| Cache Sizes | |
| |
| |
| Load Profile | |
| |
| |
| Instance Efficiency Percentages (Target 100%) | |
| |
| |
| Shared Pool Statistics | |
| |
| |
| Top Five Timed Events | |
| |
| |
| Main Report | |
| |
| |
| Wait Events Statistics | |
| |
| |
| Time Model Statistics | |
| |
| |
| Wait Class | |
| |
| |
| Wait Events | |
| |
| |
| Background Wait Events | |
| |
| |
| Operating System Statistics | |
| |
| |
| Service Statistics | |
| |
| |
| Service Wait Class Stats | |
| |
| |
| SQL Statistics | |
| |
| |
| SQL ordered by Elapsed Time | |
| |
| |
| SQL ordered by CPU Time | |
| |
| |
| SQL ordered by Gets | |
| |
| |
| SQL ordered by Reads | |
| |
| |
| SQL ordered by Executions | |
| |
| |
| SQL ordered by Parse Calls | |
| |
| |
| SQL ordered by Sharable Memory | |
| |
| |
| SQL ordered by Version Count | |
| |
| |
| Complete List of SQL Text | |
| |
| |
| Instance Activity Statistics | |
| |
| |
| Instance Activity Stats | |
| |
| |
| Instance Activity Stats�Absolute Values | |
| |
| |
| Instance Activity Stats�Thread Activity | |
| |
| |
| Java Pool Advisory | |