Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

Table of Contents
About the Authors
Acknowledgments
Introduction
Chapter 1: Developing Successful Oracle Applications
	My Approach
	The Black Box Approach
	How (and How Not) to Develop Database Applications
		Understanding Oracle Architecture
			Use a Single Connection in Oracle
			Use Bind Variables
		Understanding Concurrency Control
			Implementing Locking
			Preventing Lost Updates
		Multiversioning
			Flashback
			Read Consistency and Nonblocking Reads
		Database Independence?
			The Impact of Standards
			Make Sure You Can Adapt
			Layered Programming
			Features and Functions
			Knowing What’s Out There
			Solving Problems Simply
			Openness
		How Do I Make It Run Faster?
		The DBA-Developer Relationship
	Summary
Chapter 2: Architecture Overview
	Oracle Database Types
		Single-Tenant (Non-container) Database
		Single-Tenant (Non-container) RAC Database
		Multitenant Container Database
		Multitenant RAC Database
		Sharded Database
	The SGA and Background Processes
	Connecting to Oracle
		Dedicated Server
		Shared Server
		Mechanics of Connecting over TCP/IP
	Summary
Chapter 3: Files
	Parameter Files
		What Are Parameters?
		Legacy init.ora Parameter Files
	Server Parameter Files (SPFILEs)
		Converting to SPFILEs
		Setting Values in SPFILEs
		Unsetting Values in SPFILEs
		Creating PFILEs from SPFILEs
		Fixing Corrupted SPFILEs
		Pluggable Databases
		Parameter File Wrap-Up
	Trace Files
		Requested Trace Files
			File Locations
			Naming Convention
			Tagging Trace Files
		Trace Files Generated in Response to Internal Errors
		Trace File Wrap-Up
	Alert Log File
	Datafiles
		A Brief Review of File System Mechanisms
		The Storage Hierarchy in an Oracle Database
			Segments
			Extents
			Blocks
			Tablespaces
			Storage Hierarchy Summary
	Temp Files
	Control Files
	Redo Log Files
		Online Redo Log
		Archived Redo Log
	Password Files
	Change Tracking File
	Flashback Logs
		Flashback Database
		Fast Recovery Area (FRA)
	Data Pump Files
	Flat Files
		Generating a CSV File
		Generating HTML
		Generating a JSON File
	Summary
Chapter 4: Memory Structures
	The Process Global Area and User Global Area
		Manual PGA Memory Management
		Automatic PGA Memory Management
			Setting PGA_AGGREGATE_TARGET
			Setting MEMORY_TARGET
			Determining How the Memory Is Allocated
			Using PGA_AGGREGATE_TARGET to Control Memory Allocation
		PGA and UGA Wrap-Up
	The System Global Area
		Fixed SGA
		Redo Buffer
		Database Block Buffer Cache
			Managing Blocks in the Buffer Cache
			Multiple Block Sizes
		Shared Pool
		Large Pool
		Java Pool
		Streams Pool
		In-Memory Pool
		System Global Area (SGA) Memory Management
			Automatic Memory Management
			Automatic Shared Memory Management (ASMM)
			Manual Shared Memory Management
	Summary
Chapter 5: Oracle Processes
	Server Processes
		Dedicated Server Connections
		Shared Server Connections
		Connections vs. Sessions
		Dedicated Server vs. Shared Server vs. DRCP
			When to Use a Dedicated Server
			When to Use a Shared Server
			Potential Benefits of a Shared Server
				Reduces the Number of Operating System Processes/Threads
				Artificially Limits the Degree of Concurrency
				Reduces the Memory Needed on the System
			DRCP
		Dedicated/Shared Server Wrap-Up
	Background Processes
		Focused Background Processes
			PMON: The Process Monitor
			LREG: Listener Registration Process
			SMON: The System Monitor
			RECO: Distributed Database Recovery
			CKPT: Checkpoint Process
			DBWn: Database Block Writer
			LGWR: Log Writer
			ARCn: Archive Process
			DIAG: Diagnosability Process
			FBDA: Flashback Data Archiver Process
			DBRM: Database Resource Manager Process
			GEN0: General Task Execution Process
			Remaining Common Focused Processes
		Utility Background Processes
			CJQ0 and Jnnn Processes: Job Queues
			QMNC and Qnnn: Advanced Queues
			EMNC: Event Monitor Processes
			MMAN: Memory Manager
			MMON, MMNL, and Mnnn: Manageability Monitors
			CTWR: Change Tracking Processes
			RVWR: Recovery Writer
			DMnn/DWnn: Data Pump Master/Worker Processes
			TMON/TT00: Transport Monitor and Redo Transport Slave
			Remaining Utility Background Processes
	Slave Processes
		I/O Slaves
		Pnnn: Parallel Query Execution Servers
	Summary
Chapter 6: Locking and Latching
	What Are Locks?
	Locking Issues
		Lost Updates
		Pessimistic Locking
		Optimistic Locking
			Optimistic Locking Using a Version Column
			Optimistic Locking Using a Checksum
		Optimistic or Pessimistic Locking?
		Blocking
			Blocked Inserts
			Blocked Merges, Updates, and Deletes
		Deadlocks
		Lock Escalation
	Lock Types
		DML Locks
			TX (Transaction) Locks
			TM (DML Enqueue) Locks
		DDL Locks
		Latches
			Latch “Spinning”
			Measuring the Cost of Latching a Shared Resource
				Setting Up for the Test
				Without Bind Variables
				With Bind Variables
				Performance/Scalability Comparison
		Mutexes
		Manual Locking and User-Defined Locks
			Manual Locking
			Creating Your Own Locks
	Summary
Chapter 7: Concurrency and Multiversioning
	What Are Concurrency Controls?
	Transaction Isolation Levels
		READ UNCOMMITTED
		READ COMMITTED
		REPEATABLE READ
			Getting a Consistent Answer
			Lost Updates: Another Portability Issue
		SERIALIZABLE
		READ ONLY
	Implications of Multiversion Read Consistency
		A Common Data Warehousing Technique That Fails
		An Explanation for Higher Than Expected I/O on Hot Tables
	Write Consistency
		Consistent Reads and Current Reads
		Seeing a Restart
		Why Is a Restart Important to Us?
	Summary
Chapter 8: Transactions
	Transaction Control Statements
	Atomicity
		Statement-Level Atomicity
		Procedure-Level Atomicity
		Transaction-Level Atomicity
		DDL and Atomicity
	Durability
		WRITE Extensions to COMMIT
		COMMITS in a Nondistributed PL/SQL Block
	Integrity Constraints and Transactions
		IMMEDIATE Constraints
		DEFERRABLE Constraints and Cascading Updates
	Bad Transaction Habits
		Committing in a Loop
			Performance Implications
			Snapshot Too Old Error
			Restartable Processes Require Complex Logic
		Using Autocommit
	Distributed Transactions
	Autonomous Transactions
		How Autonomous Transactions Work
		When to Use Autonomous Transactions
	Summary
Chapter 9: Redo and Undo
	What Is Redo?
	What Is Undo?
	How Redo and Undo Work Together
		Example INSERT-UPDATE-DELETE-COMMIT Scenario
			The INSERT
				Hypothetical Scenario: The System Crashes Right Now
				Hypothetical Scenario: The Buffer Cache Fills Up Right Now
			The UPDATE
				Hypothetical Scenario: The System Crashes Right Now
				Hypothetical Scenario: The Application Rolls Back the Transaction
			The DELETE
			The COMMIT
	Commit and Rollback Processing
		What Does a COMMIT Do?
		What Does a ROLLBACK Do?
	Investigating Redo
		Measuring Redo
		Can I Turn Off Redo Log Generation?
			Setting NOLOGGING in SQL
			Setting NOLOGGING on an Index
			NOLOGGING Wrap-Up
		Why Can’t I Allocate a New Log?
		Block Cleanout
		Log Contention
		Temporary Tables and Redo
			Global Temporary Tables
			Private Temporary Tables
	Investigating Undo
		What Generates the Most and Least Undo?
		ORA-01555: Snapshot Too Old Error
		Undo Segments Are in Fact Too Small
		Delayed Block Cleanout
	Summary
Chapter 10: Database Tables
	Types of Tables
	Terminology
		Segment
		Segment Space Management
		High-Water Mark
		PCTFREE
			Row Migration
			Setting PCTFREE Values
		LOGGING and NOLOGGING
		INITRANS
	Heap-Organized Tables
	Index-Organized Tables
		Index-Organized Tables Wrap-Up
	Index Clustered Tables
		Index Clustered Tables Wrap-Up
	Hash Clustered Tables
		Hash Clustered Tables Wrap-Up
	Sorted Hash Clustered Tables
	Nested Tables
		Nested Tables Syntax
		Nested Table Storage
		Nested Tables Wrap-Up
	Temporary Tables
		Global Temporary Tables
		Global Temporary Table Statistics
			Session Statistics
			Shared Statistics
			Statistics for ON COMMIT DELETE ROWS
			Direct Path Load Automatic Statistics Gathering
		Private Temporary Tables
		Temporary Tables Wrap-Up
	Object Tables
		Object Tables Wrap-Up
	Blockchain Tables
		Blockchain Drop Table Clause
		Blockchain Row Retention Clause
		Blockchain Hash and Data Format Clause
		Creating a Blockchain Table
		DBMS_BLOCKCHAIN_TABLE Package
		Blockchain Table Summary
	Summary
Chapter 11: Indexes
	An Overview of Oracle Indexes
	B*Tree Indexes
		Index Key Compression
		Reverse Key Indexes
		Descending Indexes
		When Should You Use a B*Tree Index?
			Physical Organization
			The Clustering Factor
		B*Trees Wrap-Up
	Bitmap Indexes
		When Should You Use a Bitmap Index?
		Bitmap Join Indexes
		Bitmap Indexes Wrap-Up
	Function-Based Indexes
		A Simple Function-Based Index Example
		Indexing Only Some of the Rows
		Implementing Selective Uniqueness
		Caveat Regarding ORA-01743
		Function-Based Indexes Wrap-Up
	Application Domain Indexes
	Invisible Indexes
	Multiple Indexes on the Same Column Combinations
	Indexing Extended Columns
		Virtual Column Solution
		Function-Based Index Solution
	Frequently Asked Questions and Myths About Indexes
		Do Indexes Work on Views?
		Do Nulls and Indexes Work Together?
		Should Foreign Keys Be Indexed?
		Why Isn’t My Index Getting Used?
			Case 1
			Case 2
			Case 3
			Case 4
			Case 5
			Case 6
			Index Case Summary
		Myth: Space Is Never Reused in an Index
		Myth: Most Discriminating Elements Should Be First
	Automatic Indexing
		Managing Automatic Indexing
		Automatic Indexing in Action
		Auto Indexing Wrap-Up
	Summary
Chapter 12: Datatypes
	An Overview of Oracle Datatypes
	Character and Binary String Types
		NLS Overview
		Character Strings
			Character String Syntax
			Bytes or Characters
			The “N” Variant
	Binary Strings: RAW Types
	Extended Datatypes
	Number Types
		NUMBER Type Syntax and Usage
		BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage
		Non-native Number Types
		Performance Considerations
	Long Types
		Restrictions on LONG and LONG RAW Types
		Coping with Legacy LONG Types
	Dates, Timestamps, and Interval Types
		Formats
		DATE Type
			Adding or Subtracting Time from a DATE
			Getting the Difference Between Two DATEs
		TIMESTAMP Type
			TIMESTAMP
			Adding or Subtracting Time to/from a TIMESTAMP
			Getting the Difference Between Two TIMESTAMPs
			TIMESTAMP WITH TIME ZONE Type
			TIMESTAMP WITH LOCAL TIME ZONE Type
		INTERVAL Type
			INTERVAL YEAR TO MONTH
			INTERVAL DAY TO SECOND
	LOB Types
		Internal LOBs
			Creating a SecureFiles LOB
			Creating a BasicFiles LOB
			LOB Components
			LOB Tablespace
			IN ROW Clause
			CHUNK Clause
			RETENTION Clause
				Read Consistency for LOBs
				BasicFiles RETENTION
				SecureFiles RETENTION
			CACHE Clause
			LOB STORAGE Clause
		BFILEs
	ROWID/UROWID Types
	JSON Type
	Summary
Chapter 13: Partitioning
	Partitioning Overview
		Increased Availability
		Reduced Administrative Burden
		Enhanced Statement Performance
			Parallel DML
			Query Performance
				OLTP Systems
				Data Warehouse Systems
		Reduced Contention in an OLTP System
	Table Partitioning Schemes
		Range Partitioning
		Hash Partitioning
			How Hash Partitioning Works
			Hash Partition Using Powers of Two
		List Partitioning
		Interval Partitioning
		Reference Partitioning
		Interval Reference Partitioning
		Virtual Column Partitioning
		Composite Partitioning
		Row Movement
		Table Partitioning Schemes Wrap-Up
	Partitioning Indexes
		Local Indexes vs. Global Indexes
		Local Indexes
			Partition Elimination Behavior
			Local Indexes and Unique Constraints
		Global Indexes
			Data Warehousing and Global Indexes
			Sliding Windows and Indexes
			“Live” Global Index Maintenance
			Asynchronous Global Index Maintenance
			OLTP and Global Indexes
		Partial Indexes
	Partitioning and Performance, Revisited
	Ease of Maintenance Features
		Multiple Partition Maintenance Operations
		Cascade Truncate
		Cascade Exchange
	Auditing and Segment Space Compression
	Summary
Chapter 14: Parallel Execution
	When to Use Parallel Execution
		A Parallel Processing Analogy
			One-Page Summary
			Ten-Chapter Report
	Parallel Query
	Parallel DML
	Parallel DDL
		Parallel DDL
		Parallel DDL and Extent Trimming
			Extent Trimming and Locally Managed Tablespaces
			Setting Up for Locally Managed Tablespaces
			Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally Managed Tablespaces
	Procedural Parallelism
		Parallel Pipelined Functions
		Do-It-Yourself Parallelism
		Old-School Do-It-Yourself Parallelism
	Summary
Chapter 15: Data Loading and Unloading
	External Tables
		Introduction to External Tables
		Creating an External Table Using Legacy SQLLDR Control File
		Dealing with Errors
		Using an External Table to Load Different Files
		Multiuser Issues
		Preprocessing
			Monitoring the File System Through SQL
			Reading and Filtering Compressed Files
			Trimming Characters Out of a File
			Preprocessing Wrap-Up
		External Table Summary
	Data Pump Unload
	SQLLDR
		Running SQLLDR in Express Mode
		Loading Data with SQLLDR FAQs
			Why Do I Receive “exceeds maximum length” in My Log File?
			How Do I Load Delimited Data?
			How Do I Load Fixed Format Data?
			How Do I Load Dates?
			How Do I Load Data Using Functions?
			How Do I Load Data with Embedded Newlines?
				Use a Character Other Than a Newline
				Use the FIX Attribute
				Use the VAR Attribute
				Use the STR Attribute
				Embedded Newlines Wrap-Up
			How Do I Load LOBs?
				Loading a LOB via PL/SQL
				Loading LOB Data via SQLLDR
					Loading LOB Data That Is Inline
					Loading LOB Data That Is Out of Line
					Loading LOB Data into Object Columns
			How Do I Call SQLLDR from a Stored Procedure?
		SQLLDR Caveats
			TRUNCATE Appears to Work Differently
			SQLLDR Defaults to CHAR(255)
			Command Line Overrides Control File
		SQLLDR Summary
	Flat File Unload
		Using SQL*Plus
		Using PL/SQL
	Summary
Index