Effective Oracle by Design

Name: Effective Oracle by Design
Brand: McGraw-Hill Education
Price: 96.89 EUR
Availability: OnlineOnly

Thomas Kyte(Author)

McGraw-Hill Education (Publisher)

1st Edition

Published on 22. August 2003

724 pages

E-Book

ePUB with Adobe-DRM

System requirements

978-0-07-177678-3 (ISBN)

€96.89incl. 7% vat

System requirements

for ePUB with Adobe-DRM

E-Book Single Licence

Available for download

Description

More details

Other editions

Content

Cover Page
Effective Oracle by Design
Copyright Page
Contents
ACKNOWLEDGMENTS
INTRODUCTION
1 The Right Approach to Building Applications
It's a Team Effort
DBA and Developer Roles
Read the Documentation
A Guide to the Guides
Road Maps to Reading
Avoid the Black Box Syndrome
Database Independence versus Database Dependence
Dangers of Black Box Syndrome
It's a Database, Not a Data Dump
Use Primary and Foreign Keys
Test the Overhead of Referential Integrity
Middle Tier Checking Is Not a Panacea
Build a Test Environment
Test Against Representative Data
Don't Test with a Single User
Don't Test in a Dust-Free Lab
Design to Perform
Don't Tune to Perform
Don't Use Generic Data Models
Design Your Data Model for Efficiency
Define Your Performance Goals from the Start
Work to Clear, Specific Metrics
Collect and Log Metrics over Time
Don't Do It Because "Everyone Knows You Should"
Benchmark, Benchmark, Benchmark
Small-Time Benchmarking
Big-Time Benchmarking
Instrument the System
Trace from asktom.oracle.com
Instrument for Remote Debugging
Use DBMS_APPLICATION_INFO Everywhere
Use DEBUG.F in PL/SQL
Turn on SQL_TRACE in Your Application
Use Industry-Standard APIs
Build Your Own Routines
Audit Is Not a Four-Letter Word
Question Authority
Beware of Universal "Bests"
Suspect Ratios and Other Myths
Don't Look for Shortcuts
Keep It Simple
Consider Alternate Approaches
Let the Database Do What It Does Best
Use Supplied Functionality
We Heard Feature X Is Slow
We Heard Feature X Is Complicated
We Don't Want to
We Didn't Know
We Want Database Independence
Summary
2 Your Performance Toolkit
SQL*Plus
Set Up SQL*Plus
Customize the SQL*Plus Environment
Read the Documentation!
EXPLAIN PLAN
Setup for EXPLAIN PLAN
Use EXPLAIN PLAN
How to Read a Query Plan
Avoid the EXPLAIN PLAN Trap
Use DBMS_XPLAN and V$SQL_PLAN
AUTOTRACE
Set Up AUTOTRACE
Use AUTOTRACE
Format the AUTOTRACE Output
Understand the AUTOTRACE Output
What Are You Looking for in AUTOTRACE Output?
TKPROF
Enable TKPROF
Run TKPROF
Read a TKPROF Report
TKPROF for the Masses
Runstats
Set Up Runstats
Use Runstats
Statspack
Set Up Statspack
Use Statspack
What People Do Wrong with Statspack
Statspack at a Glance
DBMS_PROFILER
Why You Want to Use the Profiler
Profiler Resources
JDeveloper (and Debugging)
Summary
3 Architectural Decisions
Understand Shared Server vs. Dedicated Server Connections
How Do Dedicated Server Connections Work?
How Do Shared Server Connections Work?
Common Misconceptions about Shared Server Connections
Dedicated Server vs. Shared Server Wrap-Up
Take Advantage of Clustering
How Does RAC Work?
What Are the Benefits of RAC?
Clustering Wrap-Up
Know When to Use Partitioning
Partitioning Concepts
The Partitioning Myth
Why Use Partitioning?
Partitioning Wrap-Up
Know When to Use Parallel Operations
The Parallel Myth
Parallel Administration
Parallel Query
Parallel DML
DIY Parallelism
Parallel Processing Wrap-Up
Summary
4 Effective Administration
Use SPFILEs to Start Your Database
The Problems with PFILEs
How SPFILEs Work
Convert a Database to Use SPFILE
Save System Parameter Changes
Are PFILEs Obsolete?
Help, My SPFILE Is Broken and I Cannot Start
SPFILE Wrap-Up
Let Oracle Manage Your Datafiles
When Is OMF Useful?
How OMF Works
OMF Wrap-Up
Bulletproof Your Recovery
Backup Guidelines
Backup and Recovery Wrap-Up
Use Locally Managed Tablespaces
Why Are DMTs Obsolete?
Use System-Managed LMTs When You Do Not Know How Big Your Objects Will Become
Use Uniform Extent Sizes When You Know the Ultimate Size of an Object
Some LMT Caveats
LMT and DMT Wrap-Up
Let Oracle Manage Your Segment Space
Understand Freelists and Freelist Groups
How PCTFREE and PCTUSED Control Freelists
The Case for ASSM
ASSM Wrap-Up
Let Oracle Manage Your Rollback Segments
Set UNDO_RETENTION
An UNDO Tablespace Caveat
UNDO Tablespace Wrap-Up
Summary
5 Statement Processing
Understand the Types of SQL Statements
How Are Statements Executed?
Parsing
Optimization and Row-Source Generation
Execution
Statement Execution Wrap-Up
Queries from Start to Finish
A Quick-Return Query
A Slow-Return Query
Consistent Reads
Modification DML from Start to Finish
DDL Processing
Use Bind Variables
What Are the Advantages of Using Bind Variables?
Use Bind Variables with Java and VB
There Are Exceptions to Every Rule
Bind Variable Peeking
Parse as Little as Possible
The Cost of Parsing
Use PL/SQL to Reduce Parses
Move SQL Out of Triggers to Reduce Parsing
Prepare Once
Execute Many
Summary
6 Getting the Most Out of the Cost-Based Optimizer
Why the RBO Is Dead
Make the CBO Do Its Best
Adjust the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ Parameters
Use SYSTEM Statistics
Optimize the CBO
Set COMPATIBLE for Upgrades
Set DB_FILE_MULTIBLOCK_READ_COUNT to Reduce Full-Scan Costs
Set HASH_JOIN_ENABLED to Control Hash Joins
Set OPTIMIZER_DYNAMIC_SAMPLING to Gather Statistics Dynamically
Set OPTIMIZER_FEATURES_ENABLE to Control Feature Choices
Set OPTIMIZER_MAX_PERMUTATIONS to Control Permutations
Set OPTIMIZER_MODE to Pick a Mode
Rewrite Queries with QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY
Control PGA Memory with BITMAP_MERGE_AREA_ SIZE, SORT_AREA_SIZE, and HASH_AREA_SIZE
Use STAR_TRANSFORMATION_ENABLED for Star Queries
Set Others Parameters that Affect the Optimizer
Use the 10053 Event to Trace CBO Choices
Summary
7 Effective Schema Design
Fundamental Schema Design Principles
Let the Database Enforce Data Integrity
Use the Correct Datatype
Optimize to Your Most Frequently Asked Questions
Overview of Table Types
B*Tree Index Clustered Tables
Create Clusters
Use Clusters
Clusters Wrap-Up
Index-Organized Tables (IOTs)
Use IOTs as a Space-Saving Alternative to Association Tables
Use IOTs to Colocate Randomly Inserted Data
IOTs Wrap-Up
External Tables
Set Up External Tables
Modify External Tables
Use External Tables for Direct-Path Loading
Use External Tables for Parallel Direct-Path Loading
Use External Tables for Merging
Handle Errors with External Tables
Indexing Techniques
Use FBIs-Think Outside the Box
Use Domain Indexes
Compression
Use Index Key Compression
Use Table Compression for Read-Only/Read-Mostly Tables
Compression Wrap-Up
Summary
8 Effective SQL
What You Need to Write Efficient SQL
Understand Access Paths
Full Scans
ROWID Access
Index Scans
Cluster Scans
Understand Joins
Nested Loops
Hash Joins
Sort-Merge Joins
Cartesian Joins
Anti-Joins
Full Outer Joins
Schema Matters (Physical)
Really Know SQL
The ROWNUM Pseudo Column
Scalar Subqueries
Analytics
Don't Tune a Query
Understand the Question
A Proof-of-Concept Example
Overview of Other SQL Techniques
Summary
9 Effective PL/SQL Programming
Why PL/SQL?
PL/SQL Is the Most Efficient Language for Data Manipulation
PL/SQL Is Portable and Reusable
Write as Little as You Can
Not Doing It Procedurally
Fit Your Code on the Screen
Use Packages
Advantages of Packages
Break the Dependency Chain
Packages Wrap-Up
Use Static SQL
Advantages of Static SQL
Look for Opportunities to Replace Dynamic SQL
Static SQL Wrap-Up
Bulk Processing
Use Bulk Processing When It Has Dramatic Effects
Use Bulk Processing for ETL Operations
Bulk Processing Wrap-Up
Returning Data
Advantages of Ref Cursors
Use Ref Cursors to Return Result Sets
Use %TYPE and %ROWTYPE
Base Record Types on a Table
Base Record Types on a Cursor
Base Datatypes on a Column
Using Invoker Rights
Invoker Rights and Multiple Schemas
Criteria for Invoker Rights Routines
Make Your Lookups Work Efficiently
Single-Row Fetching for Lookups
Bulk Processing for Lookups
Single-Statement Operations for Lookups
Lookup Wrap-Up
Be Careful with Autonomous Transactions
Criteria for Autonomous Transactions
Autonomous Transactions Can Impact Data Integrity
Choose Whether to Use Implicit or Explicit Cursors
Use Implicit Cursors for Single-Row Selections
Use Implicit Cursors for Result Sets with a Limited Number of Rows
Implicit/Explicit Cursors Wrap-Up
Summary
10 So, You Had an Accident
Find Out What's Different
Start Collecting History Today
Detective Work
Change One Thing at a Time
Have a Sound Reason for Changing that One Thing
Have a Goal
Validate Your Hypothesis
Be Able to Unchange Things
Build a Test Case
Test Case Requirements
Keep Your Test Cases as Small as Possible
Summary
A Setting Up and Some Scripts
Setting Up BIG_TABLE
Frequently Used Scripts
PRINT_TABLE
SHOW_SPACE
COLS_AS_ROWS
GEN_DATA
Index

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

Effective Oracle by Design

Description

More details

Other editions

Additional editions

Content

System requirements