9iPerformanceTuning

Notes from studying for the 9i Performance Tuning test. Information on this webpage came from one of the links provided

System Tablespace
The system tablespace should only contain objects owned by the user sys

**Emergency Performance Methodology**
The Emergency Performance Method is as follows: Survey the performance problem and collect the symptoms of the performance problem. This process should include the following: User feedback on how the system is underperforming. Is the problem throughput or response time? Ask the question, "What has changed since we last had good performance?" This answer can give clues to the problem; however, getting unbiased answers in an escalated situation can be difficult. Sanity-check the hardware utilization of all components of the application system. Check where the highest CPU utilization is, and check the disk, memory usage, and network performance on all the system components. This quick process identifies which tier is causing the problem. If the problem is in the application, then shift analysis to application debugging. Otherwise, move on to database server analysis. Determine if the database server is constrained on CPU or if it is spending time waiting on wait events. If the database server is CPU-constrained, then investigate the following: Sessions that are consuming large amounts of CPU at the operating system level Sessions or statements that perform many buffer gets at the database level (check V$SESSTAT, V$SQL) Execution plan changes causing sub-optimal SQL execution (these can be difficult to locate) Incorrect setting of initialization parameters Algorithmic issues as a result of code changes or upgrades of all components If the database sessions are waiting on events, then follow the wait events listed in V$SESSION_WAIT to determine what is causing serialization. In cases of massive contention for the library cache, it might not be possible to logon or submit SQL to the database. In this case, use historical data to determine why there is suddenly contention on this latch. If most waits are for I/O, then sample the SQL being run by the sessions that are performing all of the I/Os. Apply emergency action to stabilize the system. This could involve actions that take parts of the application off-line or restrict the workload that can be applied to the system. It could also involve a system restart or the termination of job in process. These naturally have service level implications. Validate that the system is stable. Having made changes and restrictions to the system, validate that the system is now stable, and collect a reference set of statistics for the database. Now follow the rigorous performance method described earlier in this book to bring back all functionality and users to the system. This process may require significant application re-engineering before it is complete.

buffer busy waits

"CHECKPOINT NOT COMPLETE"

v$system_event

When a delete is performed, which type of undo is generated?

Oracle9i Database: Performance Tuning

Exam Topics

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_033

Overview of Oracle9i Performance Tuning
[ ]	Describe the roles associated with the database tuning process
[ ]	Describe the dependency between tuning in different development phases
[ ]	Describe service level agreements
[ ]	Describe appropriate tuning goals
[ ]	Describe the most common tuning problems
[ ]	Describe the tuning considerations during development and production
[ ]	Describe performance and safety tradeoffs

Make sure that there is no serialization or single threading going on within the application

Long-term, it is generally more valuable to increase the efficiency of your application in terms of the number of physical resources used for each business transaction.

designers must create a design that uses the same resources, regardless of user populations and data volumes, and does not put loads on the system resources beyond their limits

Sizing the Buffer Cache
[ ]	Describe how the buffer cache is used by different Oracle processes
[ ]	Describe the tuning issues related to the buffer cache
[ ]	Monitor the use of the buffer cache, also the different pools within the buffer cache
[ ]	Implement dynamic SGA allocation
[ ]	Set the DB_CACHE_ADVICE parameter
[ ]	Create and size multiple buffer pools
[ ]	Detect and resolve free list contention

Sizing other SGA Structures
[ ]	Monitor and size the redo log buffer
[ ]	Monitor and size the java pool
[ ]	Control the amount of Java session memory used by a session
[ ]	Configure the instance to use I/O Slaves
[ ]	Configure and use multiple DBW processors

Monitoring and Detecting Lock Contention
[ ]	Define levels of locking
[ ]	Describe possible causes of contention
[ ]	Use Oracle utilities to detect lock contention
[ ]	Resolve contention in an emergency
[ ]	Prevent locking problems
[ ]	Recognize Oracle errors arising from deadlocks

Using Oracle Blocks Efficiently
[ ]	Describe the correct usage of extents and Oracle blocks
[ ]	Explain space usage and the high water mark
[ ]	Determine the high water mark
[ ]	Recover space from sparsely populated segments
[ ]	Describe and detect chaining and migration of Oracle blocks
[ ]	Perform index reorganization
[ ]	Monitor indexes to determine usage

B-Tree Indexes

These are the standard index type, and they are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can be used to retrieve data sorted by the index columns.

Bitmap Indexes

These are suitable for low cardinality data. Through compression techniques, they can generate a large number of rowids with minimal I/O. Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O. Bitmap indexes are particularly efficient in queries with COUNT(), because the query can be satisfied within the index.

Function-based Indexes

These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the cost-based optimizer enabled.

Function-based indexes are particularly useful when querying on composite columns to produce a derived result or to overcome limitations in the way data is stored in the database. An example of this is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.

Partitioned Indexes

Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os. By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.

Reverse Key Indexes

These are designed to eliminate index hot spots on insert applications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.

Diagnostic and Tuning Tools
[ ]	Explain how the alert.log file is used
[ ]	Explain how background trace files are used
[ ]	Explain how user trace files are used
[ ]	Describe the statistics kept in the dynamic performance views
[ ]	Explain how StatsPack collects statisticsd
[ ]	Collect statistics using StatsPack
[ ]	Collect statistics using Enterprise Manager
[ ]	Use other tuning tools

Database Configuration and I/O Issues
[ ]	Explain the advantages of distributing different Oracle file types
[ ]	Describe reasons for partitioning data in tablespaces
[ ]	Diagnose tablespace usage problems
[ ]	Describe how checkpoints work
[ ]	Monitor and tune checkpoints
[ ]	Monitor and tune redo logs

Optimize Sort Operations
[ ]	Describe how sorts are performed
[ ]	Identify the SQL operations which require sorts
[ ]	Differentiate between disk and memory sorts
[ ]	Create and monitor temporary tablespaces
[ ]	Reduce total sorts and disk sorts
[ ]	Determine the number of sorts performed in memory
[ ]	Set old and new sort parameters

Tuning Oracle Shared Server
[ ]	Identify issues associated with managing users in a Shared Server environment
[ ]	Diagnose and resolve performance issues with Oracle Shared Server processes
[ ]	Configure the Oracle Shared Server environment to optimize performance

Application Tuning
[ ]	Describe the role of the DBA in tuning Applications
[ ]	Explain different storage structures, and why one storage structure may be preferred over another
[ ]	Explain the different types of indexes
[ ]	Explain Index Organized Tables
[ ]	Describe partitioning methods
[ ]	Explain the use of the DBMS_STATS procedure
[ ]	Describe Materialized Views and use of Query Rewrites
[ ]	List requirements for OLTP, DSS and Hybrid Systems

SQL Statement Tuning
[ ]	Describe how the Optimizer is used
[ ]	Explain the concept of plan stability
[ ]	Use stored outlines
[ ]	Describe how hints are used
[ ]	Use SQL Trace and TKPROF
[ ]	Collect statistics on indexes and tables
[ ]	Describe the use of histograms
[ ]	Copy statistics between databases

Sizing the Shared Pool
[ ]	Measure and tune the library cache hit ratio
[ ]	Measure and tune the dictionary cache hit ratio
[ ]	Size and pin objects in the shared pool
[ ]	Tune the shared pool reserve space
[ ]	Describe the UGA and session memory considerations
[ ]	Explain other tuning issues related to the shared pool
[ ]	Set the large pool

Diagnosing Contention For Latches
[ ]	Describe the purpose of latches
[ ]	Describe the different types of latch request
[ ]	Diagnose contention for latches
[ ]	Tune the appropriate resources to minimize latch contention

Tuning the Operating System and Using Resource Manager
[ ]	Describe different system architectures
[ ]	Describe the primary steps of OS tuning
[ ]	Identify similarities between OS and DB tuning
[ ]	Understand virtual memory and paging
[ ]	Explain the difference between a process and a thread
[ ]	Set up Database Resource Manager
[ ]	Assign users to Resources Manager groups
[ ]	Create resource plans within groups

Hardware Components

The main hardware components are the following:

CPU
Memory
I/O Subsystem
Network -bandwidth (volume) and latency (speed).

Software Components

Most applications involve the following components:

All you data entry punks, here's why your computers are so slow.

What is the user response time requirement?

Consideration of the user type is important. If the user is an executive who requires accurate information to make split second decisions, then user response time cannot be compromised. Other types of users, such as users performing data entry activities, might not need such a high level of performance.

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D11299

Course:Objectives:

Design and configure an Oracle database with an emphasis on good performance
Perform day-to-day monitoring on an operational database using Oracle Enterprise Manager or Statspack
List the important steps in a tuning methodology
Employ Oracle tools to diagnose performance problems
Perform tuning tasks with Oracle Enterprise Manager
Perform tuning tasks using the command line interface
Reconfigure file structures for performance
Identify and resolve I/O, data storage and database configuration problems
Optimize sort operations
Configure Oracle Shared Server
Configure Resource Manager to control resource usage

Course Topics:

Overview of Oracle 9i Database Performance Tuning

Tuning questions, phases, and goals
Common performance problems
Tuning during development
Tuning steps for a production database
Performance and availability trade-offs

Diagnostic and Tuning Tools

Log and trace files
Tuning with Oracle Enterprise Manager
Statspack
Statistics, troubleshooting data, and performance data in the data dictionary
Database and self-built performance statistics collection
Collecting performance statistics with assorted tools

Database Configuration and I/O Issues

Distributing files across devices
Tablespace considerations
Full table scan performance
Checkpoint performance
Redo management and performance
Analyzing I/O activity
Analyzing checkpoint performance

Tuning the Shared Pool

Shared pool components and latches
Monitor and tune the library cache
Manage SQL statements and PL/SQL blocks to improve library cache performance
Monitor and tune the data dictionary cache
Reserved pool and large pool management
Using diagnostic tools to monitor and tune the shared pool

Tuning the Buffer Cache

Buffer cache sizing parameters
Buffer cache advisory
Buffer cache tuning views and statistics
Buffer caches for different purposes and non-standard block sizes
Automatic segment space and free space management
Multiple database writing processes
Monitoring and tuning database buffer cache with available diagnostic tools

Dynamic Instance Resizing

Dynamic allocation units in the SGA
Resize SGA components
Resizing the buffer cache and the shared pool

Sizing Other SGA Structures

Manage redo log buffer
Control redo operations
Manage Java pool memory
Managing the redo log buffer

Tuning the Oracle Shared Server

Use and performance of shared server processes
Shared servers and memory usage
Monitor and troubleshoot shared server activity

Optimizing Sort Operations

Automatic PGA memory management
PGA sizing advisor
Sorts and temporary segments
Tuning, diagnostics, and guidelines for sorts
Reduce sorting
Configuring and monitoring sort space options
Configuring automatic PGA memory management

Using Resource Manager

Database Resource Manager concepts
Administering the Database Resource Manager
Define resource plans limits
Assigning users to consumer groups
Manual and automatic consumer group switching
Examine resource plan information in the data dictionary

SQL Statement Tuning

Optimizer modes
Hints
Plan stability and stored outlines
SQL tuning diagnostic tools
Interpret results from SQL tuning diagnostic tools

Managing Statistics

Automatic segment-level statistics collection
Dynamic sampling
Table statistics, index statistics, and index tuning wizard
Column statistics and histograms
Estimated statistics and automatic statistics collection
Optimizer cost model
System statistics
Copying statistics between databases

Using Oracle Blocks Efficiently

Database storage components
Extent management
High water-mark
Space management for tables
Index management

Using Oracle Data Storage Structures Efficiently

Data storage structures
Selecting physical storage characteristics for performance
Data access methods and structures

Application Tuning

Role of the database administrator
Table movement and redefinition
Creating and managing indexes and index-organized tables
Statistics collection
Considerations for OLTP systems
Considerations for decision support systems and data warehouses
Considerations for hybrid systems

Using Materialized Views

Create and refresh materialized views
Nested materialized views
Query rewrites
The DBMS_MVIEW package

Monitoring and Detecting Lock Contention

Locking mechanisms and management
Data concurrency
Enqueues and lock types
Lock monitoring and resolution of conflicts
Deadlocks
Use diagnostic tools to monitor lock contention

Tuning the Operating System

Workshop Overview

Workshop configuration
Workshop procedure
Workshop scenarios