|
CHAPTER 1 Inside the
Oracle Architecture
This section describes the Oracle architecture in plain English and
shows you how to
1-1: Instance Architecture
Creating the OFA file structure ($DBA, bdump, udump, pfile)
1-2: SGA Architecture
1-3: Background processes
1-4: Control file management
Explaining the use of control files
Listing the Contents of the control File
File locations for control Files
Obtaining Control File Information
Listing control file contents
1-5: UNDO and REDO logs
Displaying and Creating Rollback Segments
Altering Rollback Segments
Determining the Number and Size of Rollback Segments
Obtaining Rollback Segment Information from v$rollstat and
dba_rollback_segs
Troubleshooting Rollback Segment Problems
snapshot too old
Redo log concepts for recovery
Online redo log (log_buffer) online redo logs and archived
redo logs
Oracle ARCH and LGWR background processes
Redo log dictionary queries
Redo log switch frequency and performance
Multiplexing the Online Redo Log Files
Archiving the Oracle Redo Logs
Recovery using the redo log files
1-6: Users and privileges
Creating New Database Users
User default tablespaces and TEMP
Password aging
Altering and Dropping Existing Database Users
Monitoring Information bbout Existing Users
Identifying System and Object Privileges
Granting and Revoking Privileges
Creating and Modifying Roles
Controlling Availability of Roles
Removing Roles
Using Predefined Roles
Displaying Role Information from the Data Dictionary
CHAPTER 2
Managing an Oracle Instance
This section
2-1: Initialization file management
Creating the init.ora file
Displaying init.ora values with v$parameter
2-2: Listener configuration
Creating the listener.ora file
2-3: TNS names configuration
Creating the tnsnames.ora file
2-4: Data buffer configuration & sizing
2-5: Shared pool configuration & Sizing
2-6: Starting and stopping Oracle
Shutdown and Startup an Instance
Mounting and opening an instance
Chapter 3
Oracle DBA tools
This is an introduction to Oracle administration tools and techniques.
3-1: Enterprise Manager (OEM)
3-2: Schema, File & tablespace management
Describing the relationship between data files, tablespaces
and table
Understanding Oracle segments
Creating Tablespaces using the autoextend option
Changing the Size of Tablespaces alter database
datafile command
Defining a TEMP tablespace
Changing the default storage Settings for a tablespace
3-3: Database Maintenance
Reason for reorgs chained rows, imbalanced
freelists
Reorganizing Tables using Export and Import
Using CTAS to reorganize data
Index rebuilding
3-4: Monitoring Oracle
Enterprise manager performance pack
Displaying and managing Oracle sessions with v$session
Monitoring the log and searching for Oracle trace files
3-5: Utilities
Imp and exp utilities
SQL*Loader
|
3-6:
Dictionary and v$ views
The dba_, all_ and user_ structures
Querying the tables, indexes, and segments views
Getting view names from dict view
CHAPTER 4 Managing
Oracle objects
This section explores the methods used for managing all active
components of the Oracle database including
4-1: File and tablespace structures
Review of the storage parameters pctfree, pctused and
freelists.
Chained rows and pctfree
Insert performance and pctused values
Using the APPEND option with inserts
4-2: Table structures
Creating Tables Using Appropriate Storage Settings
Controlling table extents and fragmentation
Analyzing Tables for CBO statistics
Using the dba_tables and dba_segments views
Converting Between Different Formats of ROWID
4-3: Index structures
Listing the Different Types of Indexes and Their Uses
Creating B*-Tree, bitmap and function-based Indexes
Reorganizing Indexes with alter index rebuild
Dropping Indexes
Getting Index Information from the Data Dictionary
4-4: Object-oriented Oracle
Identification of objects - real-world modeling
Object representation
Data storage for composite objects
Differences between existing data storage methods
Benefits of object-oriented systems
Comparisons of methodologies
Retrofitting existing systems into object technology
Improving on traditional systems design methods
Internalizing object technology concepts
4-5: Oracle Object Structures
Object-oriented Oracle SQL
Nested Tables
VARRAYS within tables
Using object IDs
Using abstract data types
4-6: STATSPACK performance management
Installing STATSPACK
Running STATSPACK reports
Interpreting a STATSPACK report
Getting time series reports with STATSPACK
Finding performance signatures with STATSPACK
CHAPTER 5
Performance Management
This section explores the methods used for performance management in
Oracle and shows tips and scripts for monitoring all components of any
Oracle database. You will also learn the proper action to take when any
area of Oracle becomes a bottleneck.
5-1: Bottleneck performance analysis
Top-5 timed events
External Server Bottlenecks (Network, I/O, RAM, CPU)
5-2: Tuning at the Instance Level
Changing init.ora parameters (index_optimizer_cost_adj,
optimizer_mode)
Buffer busy waits
Object parameters (PCTFREE, PCTUSED, INITTRANS, FREELISTS)
5-4: CBO behavior
Introduction to rule-based optimization
Introduction to cost-based optimization
Changing the default optimizer modes
Collecting table and index statistics (dbms_stats)
Using column histograms
Skewonly
5-5: SQL Tuning
Review of Basic joining methods
Merge join
Hash Join
Nested Loop join
Advanced SQL operators
Between operator
Using hints to improve SQL performance
Using parallel query to improve performance
SQL reusability within the library cache
Table high-water mark
Table striping and table partitions
Using indexes to improve performance
Identifying full-table scans
Re-writing SQL queries
Tuning sub-queries
5-6: Explain plan
Using EXPLAIN PLAN
Interpreting EXPLAIN PLAN Output
Using TKPROF
Using SQL*Trace
|
