Oracle database 11g performance tuning recipes : a problem-solution approach /

Mostrar otras versiones (1)

Performance problems are rarely "problems" per se. They are more often "crises" during which you're pressured for results by a manager standing outside your cubicle while your phone rings with queries from the help desk. You won't have the time for a leisurely perusal o...

Descripción completa

Detalles Bibliográficos
Clasificación:Libro Electrónico
Autor principal: Alapati, Sam R.
Otros Autores: Kuhn, Darl, Padfield, Bill, Opun, Surachart
Formato: Electrónico eBook
Idioma:Inglés
Publicado: [Berkeley, CA] : Apress, ©2011.
Colección:Expert's voice in Oracle.
Temas:
Oracle (Computer file)
Database management.
Relational databases.
SQL (Computer program language)
Formulas, recipes, etc.
Bases de données > Gestion.
Bases de données relationnelles.
SQL (Langage de programmation)
Conseils pratiques, recettes, trucs, etc.
recipes.
COMPUTERS > Databases > General.
COMPUTERS > Programming Languages > SQL.
Informatique.
Database management
Relational databases
Cookbook
cookbooks.
Recipes
Recipes.
Cookbooks.
Recettes.
Livres de cuisine.
Acceso en línea:Texto completo (Requiere registro previo con correo institucional)
Tabla de Contenidos:
  • Machine generated contents note: ch. 1 Optimizing Table Performance
  • 1-1. Building a Database That Maximizes Performance
  • 1-2. Creating Tablespaces to Maximize Performance
  • 1-3. Matching Table Types to Business Requirements
  • 1-4. Choosing Table Features for Performance
  • 1-5. Avoiding Extent Allocation Delays When Creating Tables
  • 1-6. Maximizing Data Loading Speeds
  • 1-7. Efficiently Removing Table Data
  • 1-8. Displaying Automated Segment Advisor Advice
  • 1-9. Manually Generating Segment Advisor Advice
  • 1-10. Automatically E-mailing Segment Advisor Output
  • 1-11. Rebuilding Rows Spanning Multiple Blocks
  • 1-12. Freeing Unused Table Space
  • 1-13.Compressing Data for Direct Path Loading
  • 1-14.Compressing Data for All DML
  • 1-15.Compressing Data at the Column Level
  • 1-16. Monitoring Table Usage
  • ch. 2 Choosing and Optimizing Indexes
  • 2-1. Understanding B-tree Indexes
  • 2-2. Deciding Which Columns to Index
  • 2-3. Creating a Primary Key Index.
  • Note continued: 2-4. Creating a Unique Index
  • 2-5. Indexing Foreign Key Columns
  • 2-6. Deciding When to Use a Concatenated Index
  • 2-7. Reducing Index Size Through Compression
  • 2-8. Implementing a Function-Based Index
  • 2-9. Indexing a Virtual Column
  • 2-10. Avoiding Concentrated I/O for Index
  • 2-11. Adding an Index Without Impacting Existing Applications
  • 2-12. Creating a Bitmap Index in Support of a Star Schema
  • 2-13. Creating a Bitmap Join Index
  • 2-14. Creating an Index-Organized Table
  • 2-15. Monitoring Index Usage
  • 2-16. Maximizing Index Creation Speed
  • 2-17. Reclaiming Unused Index Space
  • ch. 3 Optimizing Instance Memory
  • 3-1. Automating Memory Management
  • 3-2. Managing Multiple Buffer Pools
  • 3-3. Setting Minimum Values for Memory
  • 3-4. Monitoring Memory Resizing Operations
  • 3-5. Optimizing Memory Usage
  • 3-6. Tuning PGA Memory Allocation
  • 3-7. Configuring the Server Query Cache
  • 3-8. Managing the Server Result Cache.
  • Note continued: 3-9. Caching SQL Query Results
  • 3-10. Caching Client Result Sets
  • 3-11. Caching PL/SQL Function Results
  • 3-12. Configuring the Oracle Database Smart Flash Cache
  • 3-13. Tuning the Redo Log Buffer
  • ch. 4 Monitoring System Performance
  • 4-1. Implementing Automatic Workload Repository (AWR)
  • 4-2. Modifying the Statistics Interval and Retention Periods
  • 4-3. Generating an AWR Report Manually
  • 4-4. Generating an AWR Report via Enterprise Manager
  • 4-5. Generating an AWR Report for a Single SQL Statement
  • 4-6. Creating a Statistical Baseline for Your Database
  • 4-7. Managing AWR Baselines via Enterprise Manager
  • 4-8. Managing AWR Statistics Repository
  • 4-9. Creating AWR Baselines Automatically
  • 4-10. Quickly Analyzing AWR Output
  • 4-11. Manually Getting Active Session Information
  • 4-12. Getting ASH Information from Enterprise Manager
  • 4-13. Getting ASH Information from the Data Dictionary
  • ch. 5 Minimizing System Contention.
  • Note continued: 5-1. Understanding Response Time
  • 5-2. Identifying SQL Statements with the Most Waits
  • 5-3. Analyzing Wait Events
  • 5-4. Understanding Wait Class Events
  • 5-5. Examining Session Waits
  • 5-6. Examining Wait Events by Class
  • 5-7. Resolving Buffer Busy Waits
  • 5-8. Resolving Log File Sync Waits
  • 5-9. Minimizing read by other session Wait Events
  • 5-10. Reducing Direct Path Read Wait Events
  • 5-11. Minimizing Recovery Writer Waits
  • 5-12. Finding Out Who's Holding a Blocking Lock
  • 5-13. Identifying Blocked and Blocking Sessions
  • 5-14. Dealing with a Blocking Lock
  • 5-15. Identifying a Locked Object
  • 5-16. Resolving enq: TM Lock Contention
  • 5-17. Identifying Recently Locked Sessions
  • 5-18. Analyzing Recent Wait Events in a Database
  • 5-19. Identifying Time Spent Waiting Due to Locking
  • 5-20. Minimizing Latch Contention
  • 5-21. Managing Locks from Oracle Enterprise Manager
  • 5-22. Analyzing Waits from Oracle Enterprise Manager.
  • Note continued: ch. 6 Analyzing Operating System Performance
  • 6-1. Detecting Disk Space Issues
  • 6-2. Identifying System Bottlenecks (vmstat)
  • 6-3. Identifying System Bottlenecks (Solaris)
  • 6-4. Identifying Top Server-Consuming Resources (top)
  • 6-5. Identifying CPU and Memory Bottlenecks (ps)
  • 6-6. Identifying I/O Bottlenecks
  • 6-7. Identifying Network-Intensive Processes
  • 6-8. Troubleshooting Database Network Connectivity
  • 6-9. Mapping a Resource-Intensive Process to a Database Process
  • 6-10. Terminating a Resource-Intensive Process
  • ch. 7 Troubleshooting the Database
  • 7-1. Determining the Optimal Undo Retention Period
  • 7-2. Finding What's Consuming the Most Undo
  • 7-3. Resolving an ORA-01555 Error
  • 7-4. Monitoring Temporary Tablespace Usage
  • 7-5. Identifying Who Is Using the Temporary Tablespace
  • 7-6. Resolving the "Unable to Extend Temp Segment" Error
  • 7-7. Resolving Open Cursor Errors
  • 7-8. Resolving a Hung Database.
  • Note continued: 7-9. Invoking the Automatic Diagnostic Repository Command Interpreter
  • 7-10. Viewing an Alert Log from ADRCI
  • 7-11. Viewing Incidents with ADRCI
  • 7-12. Packaging Incidents for Oracle Support
  • 7-13. Running a Database Health Check
  • 7-14. Creating a SQL Test Case
  • 7-15. Generating an AWR Report
  • 7-16.Comparing Database Performance Between Two Periods
  • 7-17. Analyzing an AWR Report
  • ch. 8 Creating Efficient SQL
  • 8-1. Retrieving All Rows from a Table
  • 8-2. Retrieve a Subset of Rows from a Table
  • 8-3. Joining Tables with Corresponding Rows
  • 8-4. Joining Tables When Corresponding Rows May Be Missing
  • 8-5. Constructing Simple Subqueries
  • 8-6. Constructing Correlated Subqueries
  • 8-7.Comparing Two Tables to Finding Missing Rows
  • 8-8.Comparing Two Tables to Finding Matching Rows
  • 8-9.Combining Results from Similar SELECT Statements
  • 8-10. Searching for a Range of Values
  • 8-11. Handling Null Values.
  • Note continued: 8-12. Searching for Partial Column Values
  • 8-13. Re-using SQL Statements Within the Shared Pool
  • 8-14. Avoiding Accidental Full Table Scans
  • 8-15. Creating Efficient Temporary Views
  • 8-16. Avoiding the NOT Clause
  • 8-17. Controlling Transaction Sizes
  • ch. 9 Manually Tuning SQL
  • 9-1. Displaying an Execution Plan for a Query
  • 9-2. Customizing Execution Plan Output
  • 9-3. Graphically Displaying an Execution Plan
  • 9-4. Reading an Execution Plan
  • 9-5. Monitoring Long-Running SQL Statements
  • 9-6. Identifying Resource-Consuming SQL Statements That Are Currently Executing
  • 9-7. Seeing Execution Statistics for Currently Running SQL
  • 9-8. Monitoring Progress of a SQL Execution Plan
  • 9-9. Identifying Resource-Consuming SQL Statements That Have Executed in the Past
  • 9-10.Comparing SQL Performance After a System Change
  • ch. 10 Tracing SQL Execution
  • 10-1. Preparing Your Environment
  • 10-2. Tracing a Specific SQL Statement.
  • Note continued: 10.3. Enabling Tracing in Your Own Session
  • 10-4. Finding the Trace Files
  • 10-5. Examining a Raw SQL Trace File
  • 10-6. Analyzing Oracle Trace Files
  • 10-7. Formatting Trace Files with TKPROF
  • 10-8. Analyzing TKPROF Output
  • 10-9. Analyzing Trace Files with Oracle Trace Analyzer
  • 10-10. Tracing a Parallel Query
  • 10-11. Tracing Specific Parallel Query Processes
  • 10-12. Tracing Parallel Queries in a RAC System
  • 10-13. Consolidating Multiple Trace Files
  • 10-14. Finding the Correct Session for Tracing
  • 10-15. Tracing a SQL Session
  • 10-16. Tracing a Session by Process ID
  • 10-17. Tracing Multiple Sessions
  • 10-18. Tracing an Instance or a Database
  • 10-19. Generating an Event 10046 Trace for a Session
  • 10-20. Generating an Event 10046 Trace for an Instance
  • 10-21. Setting a Trace in a Running Session
  • 10-22. Enabling Tracing in a Session After a Login
  • 10-23. Tracing the Optimizer's Execution Path.
  • Note continued: 10-24. Generating Automatic Oracle Error Traces
  • 10-25. Tracing a Background Process
  • 10-26. Enabling Oracle Listener Tracing
  • 10-27. Setting Archive Tracing for Data Guard
  • ch. 11 Automated SQL Tuning
  • 11-1. Displaying Automatic SQL Tuning Job Details
  • 11-2. Displaying Automatic SQL Tuning Advice
  • 11-3. Generating a SQL Script to Implement Automatic Tuning Advice
  • 11-4. Modifying Automatic SQL Tuning Features
  • 11-5. Disabling and Enabling Automatic SQL Tuning
  • 11-6. Modifying Maintenance Window Attributes
  • 11-7. Creating a SQL Tuning Set Object
  • 11-8. Viewing Resource-Intensive SQL in the AWR
  • 11-9. Viewing Resource-Intensive SQL in Memory
  • 11-10. Populating SQL Tuning Set from High-Resource SQL in AWR
  • 11-11. Populating a SQL Tuning Set from Resource-Consuming SQL in Memory
  • 11-12. Populating SQL Tuning Set with All SQL in Memory
  • 11-13. Displaying the Contents of a SQL Tuning Set.
  • Note continued: 11-14. Selectively Deleting Statements from a SQL Tuning Set
  • 11-15. Transporting a SQL Tuning Set
  • 11-16. Creating a Tuning Task
  • 11-17. Manually Running SQL Tuning Advisor
  • 11-18. Getting SQL Tuning Advice from the Automatic Database Diagnostic Monitor
  • ch. 12 Execution Plan Optimization and Consistency
  • 12-1. Creating and Accepting a SQL Profile
  • 12-2. Automatically Accepting SQL Profiles
  • 12-3. Displaying SQL Profile Information
  • 12-4. Disabling a SQL Profile
  • 12-5. Dropping a SQL Profile
  • 12-6. Moving a SQL Profile
  • 12-7. Automatically Adding Plan Baselines
  • 12-8. Creating a Plan Baseline for One SQL Statement
  • 12-9. Creating Plan Baselines for SQL Contained in SQL Tuning Set
  • 12-10. Altering a Plan Baseline
  • 12-11. Determining If Plan Baselines Exist
  • 12-12. Displaying Plan Baseline Execution Plans
  • 12-13. Adding a New Plan to Plan Baseline (Evolving)
  • 12-14. Disabling Plan Baselines
  • 12-15. Removing Plan Baseline Information.
  • Note continued: 12-16. Transporting Plan Baselines
  • ch. 13 Configuring the Optimizer
  • 13-1. Choosing an Optimizer Goal
  • 13-2. Enabling Automatic Statistics Gathering
  • 13-3. Setting Preferences for Statistics Collection
  • 13-4. Manually Generating Statistics
  • 13-5. Locking Statistics
  • 13-6. Handling Missing Statistics
  • 13-7. Exporting Statistics
  • 13-8. Restoring Previous Versions of Statistics
  • 13-9. Gathering System Statistics
  • 13-10. Validating New Statistics
  • 13-11. Forcing the Optimizer to Use an Index
  • 13-12. Enabling Query Optimizer Features
  • 13-13. Keeping the Database from Creating Histograms
  • 13-14. Improving Performance When Not Using Bind Variables
  • 13-15. Understanding Adaptive Cursor Sharing
  • 13-16. Creating Statistics on Expressions
  • 13-17. Creating Statistics for Related Columns
  • 13-18. Automatically Creating Column Groups
  • 13-19. Maintaining Statistics on Partitioned Tables
  • 13-20. Concurrent Statistics Collection for Large Tables.
  • Note continued: ch. 14 Implementing Query Hints
  • 14-1. Writing a Hint
  • 14-2. Changing the Access Path
  • 14-3. Changing the Join Order
  • 14-4. Changing the Join Method
  • 14-5. Changing the Optimizer Version
  • 14-6. Choosing Between a Fast Response and Overall Optimization
  • 14-7. Performing a Direct-Path Insert
  • 14-8. Placing Hints in Views
  • 14-9. Caching Query Results
  • 14-10. Directing a Distributed Query to a Specific Database
  • 14-11. Gathering Extended Query Execution Statistics
  • 14-12. Enabling Query Rewrite
  • 14-13. Improving Star Schema Query Performance
  • ch. 15 Executing SQL in Parallel
  • 15-1. Enabling Parallelism for a Specific Query
  • 15-2. Enabling Parallelism at Object Creation
  • 15-3. Enabling Parallelism for an Existing Object
  • 15-4. Implementing Parallel DML
  • 15-5. Creating Tables in Parallel
  • 15-6. Creating Indexes in Parallel
  • 15-7. Rebuilding Indexes in Parallel
  • 15-8. Moving Partitions in Parallel.
  • Note continued: 15-9. Splitting Partitions in Parallel
  • 15-10. Enabling Automatic Degree of Parallelism
  • 15-11. Examining Parallel Explain Plans
  • 15-12. Monitoring Parallel Operations
  • 15-13. Finding Bottlenecks in Parallel Processes
  • 15-14. Getting Detailed Information on Parallel Sessions.

Ejemplares similares