Download Oracle Essentials PDF

TitleOracle Essentials
Tags
LanguageEnglish
File Size3.6 MB
Total Pages408
Table of Contents
                            Table of Contents
Preface
	Goals of This Book
	Audience for This Book
	About the Fourth Edition (Oracle Database 11g)
	Structure of This Book
	Conventions Used in This Book
	How to Contact Us
	Using Code Examples
	Safari® Books Online
	Acknowledgments
Introducing Oracle
	The Evolution of the Relational Database
		Relational Basics
		How Oracle Grew
	The Oracle Database Family
	Summary of Oracle Database Features
	Database Application Development Features
		Database Programming
			SQL
			PL/SQL
			Java
			Oracle and web services
			Large objects
			Object-oriented programming
			Third-generation languages (3GLs)
			Database drivers
			The Oracle Call Interface
			National Language Support
		Database Extensibility
			Oracle Multimedia
			Oracle content management
			Oracle search capabilities
			Oracle Spatial Option
			XML DB
	Database Connection Features
		Database Networking
			Oracle Net
			Oracle Internet Directory
			Oracle Connection Manager
		Oracle Application Server
	Distributed Database Features
		Distributed Queries and Transactions
		Heterogeneous Services
	Data Movement Features
		Transportable Tablespaces
		Advanced Queuing and Oracle Streams
		Extraction, Transformation, and Loading
	Database Performance Features
		Database Parallelization
		Data Warehousing and Business Intelligence
			Bitmap indexes
			Star query optimization
			Materialized views
			Analytic functions
			OLAP Option
			Data Mining Option
			Business intelligence tools
	Database Management Features
		Oracle Enterprise Manager
		Information Lifecycle Management and ILM Assistant
		Backup and Recovery
			Recovery Manager
			Incremental backup and recovery
			Oracle Secure Backup
		Database Availability
			Partitioning option
			Data Guard
			Fail Safe
			Oracle Real Application Clusters
			Data Guard and RAC
			Automated Storage Management
		Real Application Testing Option
	Database Security Features
		Advanced Security Option
		Label Security Option
		Database Vault Option
		Audit Vault Server
	Oracle Development Tools
		Oracle JDeveloper
		Oracle SQL Developer
		Oracle Forms Developer
		Oracle Reports Developer
		Oracle Designer
		Oracle Discoverer Administration Edition
		Oracle Portal
	Embedded Databases
		Oracle TimesTen
		Oracle Berkeley DB
		Oracle Lite
Oracle Architecture
	Databases and Instances
		Oracle Database Structure
			Tablespaces
			Files of a database
		Database Initialization
	Deploying Physical Components
		Control Files
		Datafiles
			Datafile structure
			Extents and segments
		Redo Log Files
			Multiplexing redo log files
			How Oracle uses the redo logs
			Naming conventions for redo logs
			Archived redo logs
			ARCHIVELOG mode and automatic archiving
	Instance Memory and Processes
		Memory Structures for an Instance
			Database buffer cache
			Shared pool
			Redo log buffer
			Other pools in the SGA
			Automatic PGA management
		Background Processes for an Instance
	The Data Dictionary
Installing and Running Oracle
	Installing Oracle
		Optimal Flexible Architecture
		Supporting Multiple Oracle Versions on a Machine
		Upgrading an Oracle Database
	Creating a Database
		Planning the Database
		The Value of Estimating
		Tools for Creating Databases
	Configuring Oracle Net
		Resolving Oracle Net Service Names
		Oracle Net Manager
		Auto-Discovery and Agents
		Oracle Net Configuration Files
	Starting Up the Database
	Shutting Down the Database
	Accessing a Database
		Server Processes and Clients
			Server process
			Client process
		Application Servers and Web Servers As Clients
		Oracle Net and Establishing Network Connections
		The Shared Server/Multi-Threaded Server
			Session memory for shared server processes versus dedicated server processes
			Data dictionary information about the shared server
	Oracle at Work
		Oracle and Transactions
		Flashback
		A Transaction, Step by Step
Oracle Data Structures
	Datatypes
		Character Datatypes
		Numeric Datatype
		Date Datatype
		Other Datatypes
		Type Conversion
		Concatenation and Comparisons
		NULLs
	Basic Data Structures
		Tables
		Views
		Indexes
			B*-tree indexes
			Reverse key indexes
			Bitmap indexes
			Function-based indexes
			Invisible indexes
		Partitioning
	Additional Data Structures
		Sequences
		Synonyms
		Clusters
		Hash Clusters
	Extended Logic for Data
		Rules Manager
		The Expression Filter
	Data Design
	Constraints
	Triggers
	Query Optimization
		Rule-Based Optimization
		Cost-Based Optimization
			How statistics are used
			Influencing the cost-based optimizer
		Specifying an Optimizer Mode
			Newer database releases and the cost-based optimizer
		Saving the Optimization
		Comparing Optimizations
		Performance and Optimization
	Understanding the Execution Plan
	SQL Advisors
	Data Dictionary Tables
Managing Oracle
	Manageability Features
		Database Advisors
		Automatic Storage Management
	Oracle Enterprise Manager
		Enterprise Manager Architecture
		Oracle Enterprise Manager Consoles
		EM2Go
	Fragmentation and Reorganization
		Resolving Fragmentation
	Backup and Recovery
		Types of Backup and Recovery Options
		Oracle Secure Backup
		Information Lifecycle Management
	Working with Oracle Support
		Reporting Problems
		Automated Patching
Oracle Security, Auditing, and Compliance
	Security
		Usernames, Privileges, Groups, and Roles
		Identity Management
		Security Privileges
		Special Roles: DBA, SYSDBA, and SYSOPER
		Policies
		Restricting Data Access
			View-based security
			Fine-grained access control
		Label Security Option
		Security and Application Roles and Privileges
		Distributed Database and Multitier Security
			Distributed security management
			Multitier security
		Advanced Security Option
		Encryption
		Secure Backup
	Auditing
	Compliance
		Oracle Database Vault Option
		Oracle Audit Vault Server
		Flashback Data Archive
Oracle Performance
	Performance Tuning Basics
		Defining Performance and Performance Problems
		Oracle Server Performance
		AWR, ADDM, and Enterprise Manager
		Machine Resource Usage
		When All Else Fails
		A Final Note on Performance Basics
	Oracle and Disk I/O Resources
		I/O Planning Principles for an Oracle Database
		Using RAID Disk Array Technology
			Volume managers
			Dedicated storage subsystems
			Combined host-based and I/O subsystem volume management
		Flexibility, Manageability, and Disk Arrays
		How Oracle I/O and Striped Arrays Interact
	Oracle and Parallelism
		Block-Range Parallelism
		Parallelism for Tables and Partitions of Tables
		What Can Be Parallelized?
			Degree of parallelism
			Self-tuning adaptive parallelism
		Partition-Based Parallelism
			Parallelism for partitions and subpartitions of a table
			Fast full index scans for nonpartitioned tables
			Parallel insert for nonpartitioned and partitioned tables
	Oracle and Memory Resources
		How Oracle Uses the System Global Area
			Automatic sizing for the SGA
			The database buffer cache
			The shared pool
			The redo log buffer
			Query results caching
		How Oracle Uses the Program Global Area
			Memory for SQL statements
			Memory for sorting within the PGA
		TimesTen
	Oracle and CPU Resources
	Database Resource Manager
Oracle Multiuser Concurrency
	Basics of Concurrent Access
		Transactions
		Locks
		Concurrency and Contention
		Integrity Problems
		Serialization
	Oracle and Concurrent User Access
	Oracle’s Isolation Levels
	Oracle Concurrency Features
	How Oracle Handles Locking
		A Simple Write Operation
		A Conflicting Write Operation
		A Read Operation
	Concurrent Access and Performance
	Workspaces
		Workspace Implementation
		Workspace Operations
		Workspace Enhancements
Oracle and Transaction Processing
	OLTP Basics
		What Is a Transaction?
		What Does OLTP Mean?
			General characteristics
			Online versus batch
		OLTP Versus Business Intelligence
	Oracle’s OLTP Heritage
	Architectures for OLTP
		Traditional Two-Tier Client/Server
		Stored Procedures
		Three-Tier Systems
		Application Servers and Web Servers
		The Grid
	Oracle Features for OLTP
		General Concurrency and Performance
		Scalability
			Multi-Threaded Server/shared server
			Database Resource Manager
		Real Application Clusters
	High Availability
	Oracle Streams and Advanced Queuing
		Streams for System Interfaces
		Oracle and Publish-Subscribe Technology
	Object Technologies and Distributed Components
Oracle Data Warehousing and Business Intelligence
	Business Intelligence Basics
		The Evolution of Business Intelligence
		A Topology for Business Intelligence
		Data Marts
		Operational Data Store and Enterprise Warehouse
		OLTP Systems and Business Intelligence
	Data Warehouse Design
	Query Optimization
		Bitmap Indexes and Parallelism
		Summary Tables
		Materialized Views
	Analytics, OLAP, and Data Mining in the Database
		Analytic and Statistical Functions
		MODEL Clause in SELECT
		OLAP and Data Mining Capabilities
		Database Extensibility and the Data Warehouse
			Multimedia
			Spatial Option
			XML
	Managing the Data Warehouse
	Other Software for the Data Warehouse
		Extraction, Transformation, and Loading
		Reporting and Ad Hoc Query Tools
		OLAP and OLAP Applications Building
		Data Mining
		Business Intelligence Applications
	The Metadata Challenge
	Best Practices
		Common Misconceptions
		Effective Strategy
Oracle and High Availability
	What Is High Availability?
		Measuring and Planning Availability
		Causes of Unplanned Downtime
		System Availability Versus Component Availability
	System Failure
		What Is Instance Recovery?
		Phases of Instance Recovery
			Rollforward
			Fast-start fault recovery and bounded recovery time
			Rollback improvements
	Protecting Against System Failure
		Component Redundancy
			Disk redundancy
		Automatic Storage Management
		Simple Hardware Failover
			Outage duration for hardware failover
			Failover and operating system platform
		Real Application Clusters
			Real Application Clusters and hardware failover
			Node failure and Real Application Clusters
			Parallel Fail Safe/RACGuard
		Oracle Transparent Application Failover
			How TAF works
			TAF and various Oracle configurations
	Recovering from Failures
		Developing a Backup-and-Recovery Strategy
		Taking Oracle Backups
		Using Backups to Recover
		Recovery Manager
		Read-Only Tablespaces
		Point-in-Time Recovery
		Flashback
	Complete Site Failure
		Oracle Data Guard: Standby Database for Redundancy
			Logical standby database
			Oracle Data Guard management
		Possible Causes of Lost Data with a Physical Standby Database
			Copying archived redo logs to a standby site
			Unarchived redo information and the role of geo-mirroring
	Data Redundancy Solutions
		Data Replication—Synchronous and Asynchronous
		Old-Fashioned Data Redundancy
	Rolling Upgrades
Oracle and Hardware Architecture
	System Basics
	Uniprocessor Systems
	Symmetric Multiprocessing Systems
	Clusters
	Non-Uniform Memory Access Systems
	Grid Computing
	Disk and Storage Technology
		Disk Deployment Strategies
	Which Platform Deployment Solution?
		Platform Comparison
		Approaches to Choosing Platforms
Oracle Distributed Databases and Distributed Data
	Accessing Multiple Databases As a Single Entity
		Distributed Data Access Across Multiple Oracle Databases
		Access to and from Non-Oracle Databases
		Two-Phase Commits
		Transaction Processing Monitors
	Moving Data Between Distributed Systems
		Advanced Replication
			Managing Advanced Replication
		Advanced Queuing
			Queue creation and management
			Publish-and-subscribe capabilities
		Oracle Streams
		Streams and Grid Computing
		Transportable Tablespaces
Oracle Extended Datatypes
	Object-Oriented Development
		Object-Relational Features
			Objects in Oracle
			Other extensibility features
		Java’s Role and Web Services
		Enterprise JavaBeans
	Extensibility Features and Options
		Oracle Multimedia and Oracle Text
		Oracle Content Management
		Oracle Ultra Search
		Oracle Spatial Option
	Using the Extensibility Framework in Oracle
Beyond the Oracle Database
	Application Express
	Oracle Fusion Middleware
		Oracle Application Server Editions
		Oracle Application Server Installation
		Oracle Application Server Components
			HTTP Server
			Containers for J2EE (OC4J)
			TopLink
			Development tools
			Development servers
			Portal
			Wireless
			Security
			Business intelligence
			Integration
		Oracle Application Server System Services
			Management
			Caching
			Clustering and load balancing
			RFID handling in Oracle Sensor Edge Server
	Oracle SOA Suite
		Oracle BPEL Process Manager
		Business Activity Monitoring
		Business Rules
		Enterprise Service Bus
		Web Services Manager
		Oracle JDeveloper
		Adapters
		Oracle Service Registry
What’s New in This Book for Oracle Database 11g
	Chapter1: Introducing Oracle
	Chapter2: Oracle Architecture
	Chapter3: Installing and Running Oracle
	Chapter4: Data Structures
	Chapter5: Managing Oracle
	Chapter6: Oracle Security, Auditing, and Compliance
	Chapter7: Oracle Performance
	Chapter8: Oracle Multiuser Concurrency
	Chapter9: Oracle and Transaction Processing
	Chapter10: Oracle Data Warehousing and Business Intelligence
	Chapter11: Oracle and High Availability
	Chapter12: Oracle and Hardware Architecture
	Chapter13: Oracle Distributed Databases and Distributed Data
	Chapter14: Oracle Extended Datatypes
	Chapter15: Beyond the Oracle Database
Additional Resources
	Web Sites
	Books and Oracle Documentation
		Chapter1: Introducing Oracle
		Chapter2: Oracle Architecture
		Chapter3: Installing and Running Oracle
		Chapter4: Data Structures
		Chapter5: Managing Oracle
		Chapter6: Oracle Security, Auditing, and Compliance
		Chapter7: Oracle Performance
		Chapter8: Oracle Multiuser Concurrency
		Chapter9: Oracle and Transaction Processing
		Chapter10: Oracle Data Warehousing and Business Intelligence
		Chapter11: Oracle and High Availability
		Chapter12: Oracle and Hardware Architecture
		Chapter13: Oracle Distributed Databases and Distributed Data
		Chapter14: Oracle Extended Datatypes
		Chapter15: Beyond the Oracle Database
Index
                        
Document Text Contents
Page 204

184 | Chapter 7: Oracle Performance

process, both at peak and average workloads. Most organizations don’t have the
time or resources for the system analysis and prototyping required to answer
these questions. This is perhaps why CPU shortages are so common, and why
the equally common solution is to simply add more CPUs to the machine until
the problem goes away. Real Application Clusters and the grid are attempts to at
least make adding more CPU horsepower easier.

Nondatabase workload
Not all organizations have the luxury of dedicating an entire machine to an Ora-
cle database to ensure that all CPU resources are available for that database. Use
operating system utilities to identify the top CPU consumers on the machine.
You may find that non-Oracle processes are consuming the bulk of the CPU
resources and adversely impacting database performance.

Database Resource Manager
The previous section described some of the ways that you can end up with poor per-
formance through a lack of CPU resources. The Database Resource Manager (DRM)
was first introduced in Oracle8i and can help you automatically avoid some of these
problems.

DRM works by leveraging consumer groups you’ve identified and enabling you to
place limits on the amount of computer resources that can be used by that group.
Implementing the DRM ensures that one group or member of a group does not end
up using an excessive amount of any one resource, as well as acting to deliver guar-
anteed service levels for different sets of users. You can create DRM hierarchies in
which you specify the amount of resources for groups within groups.

The following DRM features can be combined to protect against poor performance:

Predicting resource utilization
The DRM can leverage the query optimizer cost computations to predict the
amount of resources that a given query will take and the query execution time.
Note that, by default, the query optimizer uses a CPU + I/O cost model since
Oracle Database 10g. In Oracle9i, the query optimizer used an I/O cost model
based on single block reads.

Switching consumer groups
The DRM can switch consumer groups dynamically. You might want to give a
particular consumer group a high allocation of CPU resources. But if a single
query from that group looks as if it will take up too many CPU resources and
affect the overall performance of the machine, the consumer group can be
switched to another group that has a smaller CPU allocation—for example, a
consumer group designed for batch operations.

Page 205

Database Resource Manager | 185

Limiting number of connections
The DRM can limit the number of connections for any particular consumer
group. If the limit on connections for a group has been reached and another
connection request comes in, the connection request is queued until an existing
connection is freed. By limiting the overall number of connections for a con-
sumer group, you can place some rough limits on the overall resources that par-
ticular group might require.

In Oracle Database 11g, the database installs with a default DRM plan. The default
plan is designed to limit the amount of resources used by automated maintenance
tasks such as optimizer statistics gathering, the Automatic Segment Advisor, and the
Automatic SQL Tuning Advisor.

Page 407

About the Authors
���� �����
�� has been active in the world of computer software for more than
two decades, including stints with Data General, Cognos, Gupta Technologies, and
Oracle. He is currently a developer evangelist with Salesforce.com. He has published
15 books and countless articles on a variety of technical topics, and has spoken at
conferences and training sessions across six continents. Rick’s other books include
coauthoring Oracle in a Nutshell (O’Reilly) and Professional Oracle Programming
(WROX). Rick lives in Arizona with his wife and three daughters.


���� ��
��

� has worked for more than 20 years in business intelligence and
IT-related roles that have included sales and sales consulting, business development,
management of software development, and systems engineering. As vice president of
Business Intelligence in Oracle’s Technology Business Unit, he is recognized world-
wide for his work in business intelligence and data warehousing. His papers
regarding business intelligence and computer and software technology have
appeared in publications such as President & CEO Magazine, Database Trends and
Applications, and The Data Warehousing Institute’s publications. He has also coau-
thored the books Oracle Application Server 10g Essentials (O’Reilly), Professional
Oracle Programming (WROX), and Oracle Data Warehousing and Business Intelli-
gence Solutions (Wiley).



��
� ����� used more than 13 years of IT experience in contributing to the orig-
inal edition of this book. His background included senior positions in consulting,
systems architecture, and technical sales. Especially useful in his early work on this
book was his in-depth experience with the Oracle database across all major open
systems’ hardware and operating systems, covering tuning, scaling, parallelism,
Oracle Parallel Server, high availability, data warehousing, and OLTP. He authored
numerous papers and presented at internal and external conferences on topics such
as scaling with Oracle’s dynamic parallelism and the role of reorganizing segments in
an Oracle database.

Colophon
The animals on the cover of Oracle Essentials: Oracle Database 11g are cicadas.
There are about 1,500 species of cicada. In general, cicadas are large insects with
long thin wings that are perched above an inch-long abdomen. Their heads are also
large and contain three eyes and a piercing and sucking mechanism with which to
extrude sap from trees. Cicadas are known for their characteristic shrill buzz, which
is actually the male’s mating song, one of the loudest known insect noises.

http://salesforce.com
http://salesforce.com
http://salesforce.com

Page 408

Cicadas emerge from the ground in the spring or summer, molt, then shed their skin
in the form of a shell. They stay near trees and plants, where they live for four to six
weeks with the sole purpose of mating. The adult insects then die, and their young
hatch and burrow into the ground. They attach to tree roots and feed off the sap for
4 to 17 years, after which time they emerge and continue the mating cycle. Cicadas
have one of the longest life spans of any insect; the most common species is the peri-
odical cicada, which lives underground for 13 to 17 years.

The cover image is an original 19th-century engraving from Cuvier’s Animals. The
cover font is Adobe ITC Garamond. The text font is Linotype Birka; the heading font
is Adobe Myriad Condensed; and the code font is LucasFont’s TheSans Mono
Condensed.

Similer Documents